Administración PostgreSQL: Técnicas de Backup y Recuperación

Esta clase te dará una visión general de todas las técnicas y herramientas disponibles para hacer copias de seguridad y restauración en bases de datos PostgreSQL.

Aprenderás la diferencia entre una copia de seguridad lógica y una física, entre una copia de seguridad en frío y una en caliente. Verás que existe la posibilidad de realizar copias de seguridad incrementales, incluso copias de seguridad continuas que nos permitirán recuperar nuestras bases de datos hasta un punto cualquiera en el tiempo.. y mucho más.

Ahora analizaremos cada una de las técnicas de backup y recuperación de bases de datos PostgreSQL atendiendo a sus características, para conocerlas y diferenciarlas aún más, lo que te ayudará a determinar cuál o cuales necesitarías realizar para mantener bien aseguradas tus bases de datos.

En esta clase exploramos la herramienta pg_dumpall, cómo se utiliza y sus opciones generales.

Haremos nuestras primeras copias de seguridad del cluster y, como son legibles, las visualizamos y analizamos con detenimiento para conocer cómo esta herramienta recorre todo el cluster explorando los objetos que existen en él y genera las sentencias SQL.

En esta clase vemos las primeras opciones de backup de las más de 30 que tiene PostgreSQL 12.

En concreto, de una forma práctica, veremos la función y la utilidad de las opciones --clean, --if-exists, --data-only, --schema-only, --globals-only, --roles-only, --tablespaces-only y --no-tablespaces.

En esta clase práctica conoceremos las opciones --encoding, --superuser, --extra-float-digits y --use-set-session-authorization de la herramienta pg_dumpall.

Seguimos con las opciones de backup de pg_dumpall y esta vez veremos cómo excluir información de nuestra copia de seguridad como por ejemplo: --no-comments, --no-privileges, --no-role-passwords, --exclude-database, --no-owner.

Practicamos con todas estas opciones y veremos que se puede usar la misma opción más de una vez.

Le toca el turno a la opción --binary-upgrade y a otras opciones que excluyen configuraciones de nuestras tablas como --no-security-labels, --no-publications y --no-subscriptions.

Siguiendo con las opciones de backup de pg_dumpall, veremos ahora cómo decirle que utilice sentencias INSERT en el backup, con la posibilidad de configurar con un poco más detalle el backup resultante a través de las opciones --inserts, --column-inserts, --rows-per-insert y --on-conflict-do-nothing.

Las últimas opciones de backup que facilita PostgreSQL para esta herramienta son las que veremos en esta clase:

--disable-dollar-quoting, --quote-all-identifiers, --disable-triggers, --load-via-partition-root y --no-unlogged-table-date.

Las opciones se pueden repetir, y si no presentan alguna incompatibilidad, se pueden combinar para lograr el backup resultante que más se adapte a nuestras necesidades.

Además de explorar todas las opciones en esta, y en el resto de clases, analizamos también las restricciones para conocerlas, identificarlas y saber el por qué de esa limitación.

Las opciones de conexión que permite el uso de pg_dumpall, son bastante genéricas con respecto al resto de herramientas de PostgreSQL y en esta clase las analizamos y utilizamos para hacer diferentes tipos de backups.

Son las opciones --host, --port, --username, --dbname y --database.

Hablaremos sobre la diferencia entre las opciones --dbname y --database que, a priori, parecen lo mismo.

También usaremos las opciones --role, --no-password y --password para entender su uso.

Nos ponemos en la situación de tener que recuperar una base de datos que hemos perdido pero sólo disponemos de un backup que hemos realizado con la herramienta pg_dumpall.

Analizamos los problemas con los que nos vamos encontrando y les vamos poniendo solución.

¿Lo conseguiremos al final?.

Y si es así, ¿cómo?.

Lo descubrirás gracias a este taller práctico y juntos sacaremos varias conclusiones.

En este taller afrontamos la tarea de tener que hacer una copia de un clúster que tenemos en un servidor PostgreSQL en otro, con las herramientas que conocemos hasta el momento.

Y.. ¿qué pasa si la base de datos de origen está distribuida entre varios tablespaces? Vamos a verlo..

Ahora vamos a migrar el cluster de un servidor a otro, como en el taller anterior, salvo porque el servidor de destino en este caso tiene un sistema operativo y versión de PostgreSQL distinto al del origen.

En este taller configuramos un fichero de dónde PostgreSQL leerá la contraseña, dado un host, puerto, base de datos y usuario de manera que pg_dumpall (y cualquier herramienta de PostgreSQL en general) no nos la pida.

Veremos también cómo proteger ese fichero de miradas indiscretas para impedir que, esa flexibilidad que proporciona el motor de base de datos, se convierta en un agujero de seguridad.

En esta clase práctica conoceremos la utilidad y sintaxis de la herramienta para copias de seguridad de bases de datos pg_dump. así como sus opciones generales: opciones de formato y opciones de compresión del fichero del backup resultante.

Haremos las primeras copias de seguridad, de bases de datos individuales, en formato SQL.

Llega la hora de conocer y practicar con las opciones que permiten el backup selectivo de objetos de una base de datos.

En concreto, en esta clase, vamos a ver las opciones --create, --blobs y --no-blobs, --schema y --exclude-schema, --table y --exclude-table, y otras dos relacionadas con ellas, --exclude-table-data y --strict-names.

Muchas de las opciones para pg_dump son las mismas que para la herramienta pg_dumpall y, en esta clase, veremos las últimas opciones de pg_dump que te quedan por conocer: --enable-row-security, --snapshot, --serializable-deferrable, --no-synchronized-snapshots y --section.

En este taller avanzado vemos cómo recuperar una base de datos que ha sido eliminada por error, utilizando las herramientas que conocemos hasta el momento.

Queremos copiar una base de datos en otro servidor PostgreSQL. Después queremos copiar la misma base de datos, en otro servidor PostgreSQL, pero con otro nombre.

Con los backups que sabemos hacer con pg_dump en formato SQL lo resolvemos.

Repetimos lo que hemos hecho en el taller anterior pero añadiendo un nivel más de dificultad porque ahora, el servidor PostgreSQL de destino tiene un sistema operativo y una versión de PostgreSQL distintas a las del servidor de origen.

¡Que no cunda el pánico! Los backups lógicos son nuestros grandes aliados.

Con este taller avanzado demostramos que pg_dump en formato SQL permite hacer backups selectivos pero no recuperaciones selectivas.

Al principio perdemos un objeto de la base de datos que podemos resolver gracias a un backup selectivo pero después perdemos otro del que no hicimos backup.

¿Borramos la base de datos y la restauramos entera?

No es necesario.. pero es cierto que una restauración selectiva habría sido más rápida y eficiente. Además, tener un backup SQL de cada objeto, y de los datos de cada objeto para el caso de las tablas, tiene un mantenimiento muy costoso.

Por suerte vamos a aprender a hacer backups que permitan recuperación selectiva muy pronto, en el siguiente capítulo.

Un escenario real llevado a vídeo.

Perder datos puede haber tenido consecuencias invisibles al ojo humano, más allá de la pérdida que si ha sido visible.

Salvo que el humano dueño de ese ojo sepa que, esto que vemos en este taller, puede pasar, sepa cómo averiguar si le ha pasado y sepa cómo resolverlo.

En este taller hacemos uso de la variable de entorno PGDATABASE para determinar la base de datos sobre la que queremos hacer un backup.

¡Muy útil para programar scripts!

pg_dumpall tiene algunas limitaciones que se pueden sortear con un script similar al que haremos en este taller.

Hablaremos sobre las limitaciones de pg_dumpall y la razón por la que esta solución es una alternativa que lo mejora.

En esta clase práctica haremos backups en formato custom, el primero de los tres formatos no-SQL que veremos.

No es editable pero hay una forma de ver lo que contiene ese fichero de backup.

También conoceremos la opción --compress y sus niveles de compresión.

Le toca el turno al formato tar.

Exploramos un backup realizado con este formato para ver qué construye exactamente pg_dump.

Llega el momento de ver el formato dir, una opción de formato muy interesante puesto que permite la paralelización del backup en varios procesos.

La herramienta que veremos en esta clase práctica, pg_restore, permite la recuperación de cualquier objeto que esté incluido en un backup que se le pasa como parámetro.

Daremos un repaso a todas las opciones que hay disponibles para esta herramienta y que nos permite configurar la recuperación exacta que necesitamos realizar.

En esta clase hacemos varios tipos de recuperaciones selectivas para poner en práctica lo aprendido en la clase anterior.

En concreto, repetimos la recuperación del taller avanzado de la clase 26 pero esta vez utilizando un backup en formato no-SQL y recuperando con pg_restore.

¡Notaréis la diferencia!

Otra forma de hacer recuperaciones selectivas que nos permite pg_restore es a través de la tabla de contenidos del backup.

En esta clase práctica aprendemos a generar la tabla de contenidos, a modificarla según nuestras necesidades y a utilizarla en la restauración para que haga justo la recuperación que queremos.

En este taller experimentamos con la paralelización de los procesos de backup y recuperación, hacemos un estudio de los tiempos con distintos números de procesos paralelos y analizamos los resultados.

Haciendo una recuperación con la opción de pg_restore --exit-on-error obtenemos un resultado distinto que si hacemos la recuperación con la opción --single-transaction, y distinta también si no utilizamos ninguna de las dos opciones en la recuperación.

Probamos a hacer la misma recuperación en cada uno de los tres supuestos anteriores y analizamos los resultados.

En este taller avanzado vamos a abordar la tarea de tener que hacer migraciones de bases de datos a otras versiones de PostgreSQL distintas.

¿Qué binarios usar? ¿los de la versión menor? ¿los de la versión que sea mayor? ¿es indistinto?

Lo veremos.

Llegó la hora de empezar a hacer copias de seguridad físicas y en esta primera clase práctica vamos a ver el procedimiento a seguir para hacer una copia de seguridad de este tipo, teniendo en cuenta que nuestra base de datos puede estar distribuida, así como el procedimiento de recuperación.

En esta clase práctica explicamos el procedimiento a seguir para hacer backups incrementales, un tipo de backup más rápido porque sólo copia los cambios ocurridos desde la última copia de seguridad.

Copiamos un cluster de base de datos PostgreSQL en otro servidor de bases de datos PostgreSQL con el mismo sistema operativo, arquitectura y versión de PostgreSQL. Copia física claro, para poner en práctica lo aprendido hasta el momento.

Fácil ¿no?

¿Y si hacemos lo mismo que en el taller anterior, pero esta vez con un servidor de bases de datos PostgreSQL en otro sistema operativo que tiene un sistema de ficheros diferente?

En este taller avanzado no ponemos manos a la obra con esa tarea.. y a ver qué pasa..

De la mano de una presentación animada, en esta clase conocerás qué es un fichero WAL, en qué consiste el archivado continuo, qué es un punto de recuperación y cómo realizar backups que te permitan recuperar tus bases de datos hasta el instante anterior en el que ocurrió ese error humano, fallo en el sistema o en la tecnología, o desastre natural que haya comprometido tus datos o el acceso a los mismos.

Prepara tu servidor de bases de datos PostgreSQL para que archive automáticamente todos los ficheros WAL, antes de reutilizarlos, y poder hacer así uso de la técnica de backup y recuperación que se enseña en este capítulo y que es la única técnica que permite la recuperación hasta un punto determinado en el tiempo.

Con la herramienta pg_basebackup, facilitada por PostgreSQL, podemos hacer copias de seguridad físicas sin parar el servicio que nuestra base de datos esté prestando.

En esta clase práctica conoceremos la herramienta y sus opciones de backup. Algunas de ellas tan interesantes como --tablespace-mapping que permite reubicar los tablespaces, --waldir que te permite incluir en el backup los ficheros WAL necesarios para hacer funcionar esa copia de seguridad de forma autónoma, --max-rate que te permite limitar la velocidad de transferencia del directorio de datos para que éste no se vea afectado por la copia de seguridad.. entre otras.

En esta clase seguimos conociendo la herramienta pg_basebackup en profundidad y toca el turno de ver las opciones generales y las opciones de conexión.

En esta clase práctica, y en las dos siguientes, nos centramos en PostgreSQL 12 para trabajar con el sistema de backup y recuperación bajo la técnica del archivado continuo.

En particular, en esta clase, haremos un backup y lo exploraremos para ver lo que contiene.

En un momento determinado crearemos un punto de recuperación que marca ese instante en nuestra base de datos y nos permite regresar a él gracias al backup base que hemos realizado en la clase anterior y al conjunto de ficheros WAL que hemos ido archivando.

De esta forma seguimos un procedimiento para realizar recuperaciones PITR utilizando un punto de recuperación pre-establecido.

Cuando ocurre un desastre en nuestra base de datos que no estaba previsto, tenemos varias formas de averiguar el instante temporal en que esto ocurrió.

Una vez que conocemos ese dato, establecemos un PITR que sea anterior a ese momento y gracias al backup base de la clase número 46, y al conjunto de ficheros WAL que hemos ido archivando, podremos recuperar nuestra base de datos hasta el instante anterior en que ocurrió el desastre.

En esta clase, y en las dos próximas, vemos cómo hacer un backup y recuperaciones del tipo PITR en versiones de PostgreSQL anteriores a la 12, pues con la versión 12 se han introducido algunas mejoras que las diferencian.

En concreto, en esta clase práctica, haremos un backup base de nuestro cluster de PostgreSQL 10.

En este taller avanzado simulamos una pérdida importante en nuestro sistema de base de datos, lo que nos obliga a averiguar el instante en el que ocurrió y realizar la recuperación estableciendo un PITR anterior en el que ocurrió esa pérdida.

Ahora somos nosotros los que establecemos un punto de recuperación en nuestra base de datos PostgreSQL 10 porque sabemos que se van a realizar unas operaciones de mantenimiento en nuestro servidor que son críticas y prevemos que algo puede salir mal.

Efectivamente sale mal y.. gracias al punto de recuperación preestablecido, al backup base realizado en el tema 49 de este curso y a la colección de ficheros WAL que hemos ido conservando, llevaremos a la base de datos hasta el instante anterior en el que comenzó esa actuación de mantenimiento crítica.

Interesante taller avanzado donde resolvemos varias incógnitas acerca de las TimeLines, en una sola clase en vídeo, para versiones de PostgreSQL anteriores a la 12.

Porque en la versión 12, sí, los viajes en el tiempo son.. distintos..

No es exactamente un taller avanzado éste. Es un laboratorio en toda regla.

Durante esta sesión intentaremos que nuestra base de datos realice los mismos viajes en el tiempo que en la clase anterior solo que, con diferente resultado porque las realizaremos sobre un servidor de base de datos PostgreSQL 12.

En este taller abordamos la tarea de realizar un backup base sin utilizar la herramienta pg_basebackup.

¿Para qué?

Para aprender.

¿No te gustaría saber qué hace por debajo pg_basebackup sin necesidad de leer el código fuente de PostgreSQL?

Pues aquí lo tienes ;-)

Una vez adquiridos los conocimientos necesarios sobre técnicas y herramientas para hacer copias de seguridad de bases de datos PostgreSQL, y saber cuándo es más adecuado el uso de una u otra, estás en disposición de comenzar a diseñar una política de backups para las bases de datos que debes mantener respaldadas.

En esta clase te doy consejos que te ayudarán a realizar esa planificación.

Aquí tienes un artículo donde explico, paso a paso, cómo programar un backup diario para una base de datos PostgreSQL alojada en un servidor Windows 10.