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

Hacer copias de seguridad regulares 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 error común...

Introducción

Hacer copias de seguridad regulares 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 varias empresas diferentes, nunca me he dado el lujo de poder posponer esas tareas al unicornio de fábula conocido como administrador de base de datos (DBA). En cambio, tuve 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 implicó configurar una estrategia de copia de seguridad de rutina y saber cómo recuperar una si ante tal situación. tragedia.

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

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

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

A. Creación de una copia archivada de su base de datos en el nivel del sistema de archivos con archivado WAL {#acreación de una copia archivada de su base de datos en el nivel del sistema de archivos con archivado wal}

Antes de llegar demasiado lejos en la explicación de este método, debo decir que se debe crear un directorio en el sistema que contendrá los archivos almacenados 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 utiliza el par de funciones de PostgreSQL pg_start_backup(...) y pg_stop_backup(...). Para utilizar estas funciones, el usuario que las ejecuta debe tener privilegios de replicación, que se definen en el archivo de configuración pg_hba.conf. Además, para utilizar este método de respaldo, debe agregar algunas configuraciones y comandos al archivo postgresql.conf, que analizaré a continuación.

En el archivo postgresql.conf, debe configurar archive_mode en on y proporcionar un comando de archivo que se verá así:

1
2
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 antes mencionadas funcionan mediante la creación de una copia de punto de control (también conocida como un único punto en el tiempo) del directorio de datos de Postgres como un archivo comprimido. Por lo general, 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 de este tipo para hacer una copia de seguridad de la base de datos de esta manera:

1
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 bash backup.sh del ejemplo para realizar la función de crear la copia archivada:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
#! /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. Le gustaría reemplazar databasename, username, así 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 los 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 el comando pg_dump, 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, además de llenar las tablas con datos.

Hay diferentes formas de usar este comando que se detallan en los 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 completo con 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.

En contraste, 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 tablas, 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 1 * * * bash /path/to/backup.sh

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

1
2
3
4
5
6
7
8
#!/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 plano, entonces cambiaría la línea pg_dump -Fc nombre de base de datos > $ARCHIVO con pg_dump nombre de base de datos > $ARCHIVO.

Restauración de 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 bloqueo grave y varios miembros del equipo y partes interesadas pueden estar en un frenesí emocionalmente cargado. Espero que esto sea algo que usted, como desarrollador, nunca tenga 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 relajarse y dar un paso o comando a la vez. Después de todo, lo peor ya pasó, así que no tienes a dónde ir sino 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:

  1. Detenga el servidor PostgreSQL si se está ejecutando en lo que probablemente sea un nuevo servidor Linux
  2. Elimine los archivos y directorios existentes en el directorio de datos que normalmente se encuentra en /var/lib/pgsql/x.x/data, donde "x.x" 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.
  3. Tome su última copia archivada de la base de datos que se creó en el script backup.sh y descomprí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
  4. 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.
  5. Cree un archivo recovery.conf en el directorio de datos y coloque el siguiente comando en él, que es básicamente un reverso de archive_command en el archivo postgresql.conf, discutido anteriormente. Nuevamente, asegúrese de que este archivo tenga el mismo propietario que se indica en el paso 2

recuperación.conf

restore_command 'cp /home/nombre de usuario/pg/wals/%f %p'

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

¡Uf! Estás listo.

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

Restaurar una base de datos desde un volcado de SQL que se creó con 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á WAL para recoger datos adicionales que no 't hacer en la copia de punto en el tiempo.

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. Luego, todo lo que se requiere es emitir el siguiente comando como usuario que tiene permisos completos de escritura en la base de datos de PostgreSQL.

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

Postgres leerá el archivo de copia de seguridad y recreará las tablas, los índices y las restricciones, además de cargar las tablas con los datos presentes en el momento en que se copió la base de datos original.

Y listo, ¡ya está!

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 lo encuentro útil, o incluso necesario, para poder cargar fácilmente una base de datos de producción en mi servidor local. 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 quede inutilizable. También he discutido 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.