Copia de seguridad y restauración de bases de datos de PostgreSQL

Introducción

Hacer copias de seguridad periódicas de la base de datos es una tarea de mantenimiento esencial y una estrategia de recuperación de puntos de falla para cualquier persona responsable de una base de datos.

Un nombre inapropiado común para los desarrolladores de software es que habrá un administrador de base de datos que se encargará de estas cosas por nosotros. Desafortunadamente, en mi experiencia, esto está lejos de la verdad. De los muchos proyectos en los que he trabajado para múltiples compañías diferentes, nunca me he dado el lujo de poder posponer esas tareas al fable unicornio conocido como administrador de bases de datos (DBA). En cambio, he tenido que hacer todo lo posible para mantener las bases de datos en los sistemas en los que trabajo en la mejor situación posible, lo que ha implicado configurar una estrategia de copia de seguridad de rutina y saber cómo recuperar una si se enfrenta a tal situación. tragedia.

En este artículo, cubriré los métodos que he utilizado para realizar copias de seguridad y recuperar bases de datos PostgreSQL en servidores Linux.

Creación de copias de seguridad de la base de datos

Hay dos formas en las que estoy al tanto de cómo crear una copia de seguridad de una base de datos PostgreSQL, que discutiré en esta sección. Ambas formas utilizan copias puntuales de la base de datos. Uno utiliza lo que se conoce como archivo WAL y el otro utiliza un volcado SQL.

A. Creación de una copia archivada de su base de datos en el nivel del sistema de archivos con WAL Archiving

Antes de ir demasiado lejos en la explicación de este método, debo decir que es necesario crear un directorio en el sistema que contendrá los archivos archivados y debe ser propiedad del usuario de postgres. En mis ejemplos utilizaré el directorio /home/username/pg que es propiedad de postgres y contendrá dos subdirectorios wals y cluster, también propiedad de postgres.

Este primer método usa el par de funciones de PostgreSQL pg_start_backup(...) y pg_stop_backup(...). Para utilizar estas funciones, el usuario que las ejecuta debe tener privelliges de replicación, que se definen en el archivo de configuración pg_hba.conf. Además, para utilizar este método de copia de seguridad, debe agregar algunas configuraciones y comandos al archivo postgresql.conf, que discutiré a continuación.

En el archivo postgresql.conf necesita configurar archive_mode a on y proporcione un comando de archivo que se verá así:

archive_mode = on
archive_command = 'cp %p /home/username/pg/wals/%f'

Aquí, «% p» se reemplaza con la ruta del archivo WAL (registro de escritura anticipada) que se está archivando y «% f» es el nombre del archivo real.

Las funciones de Postgres mencionadas anteriormente funcionan creando una copia de punto de control (también conocido como un único punto en el tiempo) del directorio de datos de Postgres como un archivo comprimido. Normalmente he usado esta combinación de funciones dentro de un script bash programado para ejecutarse a intervalos establecidos dentro de una tarea cron.

Por ejemplo, un script bash en un servidor Linux que aloja una base de datos de Postgres podría implementar una tarea cron para hacer una copia de seguridad de la base de datos de esta manera:

1 1 * * * bash /path/to/backup.sh

En el ejemplo anterior, la entrada de la tarea cron ejecutaría el script, backup.sh, en el primer minuto de la primera hora de cada día.

A continuación se muestra el script de bash backup.sh del ejemplo para realizar la función de crear la copia archivada:

#! /bin/bash

function backup_pg() {
   ARCHIVE=$1
   echo Backing up database ...
   psql -U username -h localhost -c "SELECT pg_start_backup('$ARCHIVE');" databasename
   tar czf $ARCHIVE $SRC
   psql -U username -h localhost -c "SELECT pg_stop_backup();" databasename
   echo Completed!
}

SRC=/var/lib/pgsql/9.6/data
DST=/home/username/pg/cluster/

TODAY=`date +%F`
ARCHIVE=databasename-backup-$TODAY.tgz
BAKDIR=Archive-$TODAY

mkdir -p $DST
cd $DST/$BACKDIR

backup_pg $ARCHIVE

Como mencioné anteriormente, esto crea una copia puntual de la base de datos y archiva todo el directorio de datos. Querrías reemplazar databasename, username, tanto como SRC y DST con los valores que tengan sentido para su configuración. Para obtener más información sobre el uso de este método de creación de copias de seguridad, consulte el documentos oficiales.

B. Creación de un volcado SQL de la base de datos

Esta segunda forma de hacer una copia de seguridad de una base de datos, que utiliza la pg_dump comando, es mi método preferido, ya que creo que requiere menos pasos y, por lo tanto, es menos complicado. Este método aún crea una copia puntual de la base de datos, pero lo hace mediante la creación de un solo archivo que contiene comandos que recrearán tablas, restricciones e índices, así como completarán las tablas con datos.

Hay diferentes formas de utilizar este comando que se detallan en la Documentos de PostgreSQL, pero difieren principalmente en el formato de la salida.

Las dos formas más comunes en que los he usado son para producir un archivo de volcado de la base de datos que esté en texto legible lleno de comandos SQL o para producir la salida en el formato binario personalizado de PostgreSQL. Aprecio la legibilidad de la salida de texto sin formato por curiosidad y poder ver lo que está sucediendo, pero en realidad hay muy poco uso práctico ya que estas cosas suelen ser bastante grandes.

Por el contrario, el volcado binario personalizado no es legible por humanos, pero está comprimido para ahorrar espacio y maneja mejor la restauración sin causar errores confusos asociados con violaciones de restricciones al cargar datos de la tabla, que aparecen mucho en el método de texto sin formato.

Nuevamente, podría usar la misma entrada de tarea cron que ejecutaría el script backup.sh en el primer minuto de la primera hora de cada día.

1 1 * * * bash /path/to/backup.sh

Aquí hay un script de bash de ejemplo para crear una copia de seguridad usando pg_dump en formato binario personalizado:

#!/bin/bash

TODAY=`date +%F`
ARCHIVE=databasename-$TODAY.bk
echo creating archive file $ARCHIVE
cd /var/lib/pgsql/9.6/backups
pg_dump -Fc databasename > $ARCHIVE
echo 'archive complete'

Si uno quisiera producir la salida SQL de texto sin formato, cambiaría la línea pg_dump -Fc databasename > $ARCHIVE con pg_dump databasename > $ARCHIVE.

Restaurar la base de datos

Recuperar una base de datos puede ser una tarea desalentadora, ya que esto generalmente se hace en un momento en el que ha experimentado un accidente grave y varios miembros del equipo y partes interesadas pueden estar en un frenesí cargado de emociones. Espero que esto sea algo que usted, como desarrollador, nunca tendrá que experimentar, pero estoy escribiendo este artículo sabiendo que es probable que sea un evento desafortunado para un cierto porcentaje de sus lectores. Dicho esto, recuerde simplemente relajarse y dar un paso o dar órdenes a la vez. Después de todo, lo peor ya ha pasado, así que no tienes a dónde ir más que hacia arriba.

A. Restauración desde una copia de seguridad del sistema de archivos

Para restaurar desde la copia de seguridad del sistema de archivos / WAL, debe seguir estos pasos:

• Detenga el servidor PostgreSQL si se está ejecutando en lo que probablemente sea un nuevo servidor Linux
• Elimine los archivos y directorios existentes en el directorio de datos que generalmente se encuentra en / var / lib / pgsql / xx / data, donde «xx» es la versión de PostgreSQL que está ejecutando, como 9.6. También tome nota de quién es el propietario de estos archivos y directorios, que generalmente son propiedad del usuario de postgres
• Tome su última copia archivada de la base de datos que se creó en el script backup.sh y depcomprímala en el directorio de datos postresql. Asegúrese de que la propiedad de estos archivos sea la misma que anotó en el paso 2
• Apague todas las demás aplicaciones o servicios que puedan intentar acceder a la base de datos. También es una buena idea modificar su archivo pg_hba.conf para asegurarse de que ningún otro usuario pueda acceder a esa base de datos.
• Cree un archivo recovery.conf en el directorio de datos y coloque el siguiente comando en él, que es básicamente un reverso del archive_command en el archivo postgresql.conf, discutido anteriormente. Nuevamente, asegúrese de que este archivo tenga el mismo propietario que se indicó en el paso 2

recovery.conf

restore_command 'cp /home/username/pg/wals/%f %p'

• Inicie el servicio PostgreSQL, que pondrá la base de datos en modo de recuperación utilizando el comando del archivo recovery.conf para restaurar cualquier dato que residía en el WAL (registro de escritura anticipada) después de que se realizó la copia en un momento determinado.

¡Uf! Ya terminaste.

B. Restauración desde una copia de seguridad de volcado de SQL

Restaurar una base de datos a partir de un volcado SQL que se creó utilizando pg_dump implica muchos menos pasos que el método anterior, pero es casi seguro que recuperará menos datos con este método, ya que no utilizará el WAL para recoger datos adicionales que no se incluyeron en la copia de un momento determinado.

Para restaurar la base de datos, debe asegurarse de que el servicio postgresql se esté ejecutando en la máquina en la que desea restaurar la base de datos. Entonces, todo lo que se requiere es emitir el siguiente comando como un usuario que tiene permisos de escritura completos en la base de datos PostgreSQL.

pg_restore -Fc databasename-YYYY-MM-DD.bk

Postgres leerá el archivo de respaldo y volverá a crear las tablas, índices y restricciones, así como cargará las tablas con los datos presentes en el momento en que se copió la base de datos original.

Y listo, ¡has terminado!

Prefiero este método no solo porque es menos complicado, sino que también prefiero este método de copia de seguridad y recuperación porque, como desarrollador, a menudo me resulta útil, o incluso necesario, que pueda cargar fácilmente una base de datos de producción en mi máquina de desarrollo. Esto me permite depurar o desarrollar una nueva función con datos recientes y similares a los del sistema en vivo, y los volcados de SQL lo hacen posible.

Por lo tanto, incluso si está utilizando el método WAL / sistema de archivos para copias de seguridad y recuperación, aún puede usar pg_dump y pg_restore para hacer copias y restaurar su máquina de desarrollo local.

Conclusión

En este artículo, he demostrado dos enfoques para crear copias de seguridad programadas para una base de datos PostgreSQL que se ejecuta en un servidor Linux y realizar la recuperación en caso de que la base de datos se corrompa o se vuelva inutilizable. También he analizado los pros y los contras de cada método, así como la necesidad de que un desarrollador sepa cómo realizar estas tareas.

Como siempre, les agradezco su lectura y agradezco los comentarios y críticas a continuación.