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.

     

    Etiquetas:

    Deja una respuesta

    Tu direcci贸n de correo electr贸nico no ser谩 publicada. Los campos obligatorios est谩n marcados con *