PostgreSQL: cómo crear y restaurar backups de bases de datos

En esta entrada voy a hacer una guía muy básica sobre la gestión de copias de seguridad de bases de datos PostgreSQL. La más común es mediante volcados SQL, aunque hay otras como la copia a nivel de fichero.

Backups mediante volcado (SQL Dump con pg_dump)

Si conocéis mysqldump veréis que el funcionamiento es similar. Se trata de volcar en un fichero de texto que contendrá los comandos y datos SQL necesarios para restaurar una copia exacta de la base de datos en el momento de la copia. La herramienta a utilizar es pg_dump.

La forma más sencilla de hacer un backup de una base de datos completa es la siguiente:

# pg_dump basededatos > fichero.sql

En el caso de tener que especificar un host remoto o puerto de conexión distinto usaríamos los parámetros correspondientes, -h para el host y -p para el puerto, lo mismo se tenemos que especificar usuario, clave, etc.

La restauración del backup es el mismo proceso pero a la inversa:

# psql basededatos < fichero.sql

Especificando parámetros:

# psql -U usuario -d basededatos -p 5432 -h 192.168.0.111 < fichero.sql

Mediante el frontend de PostgreSQL psql volcamos el contenido del fichero SQL en la base de datos a restaurar. Al igual que con la exportación se pueden especificar un gran número de parámetros para personalizar la restauración (revisad la ayuda).

Backup de todas las bases de datos del servidor PostgreSQL

Mediante pg_dumpall podemos hacer con un único comando una copia de seguridad de todas las bases de datos del servidor:

# pg_dumpall > backup_server.sql

Posteriormente para restaurar todas las bases de datos:

# psql -f backup_server.sql postgres

Backup a nivel de ficheros

Este método implica copiar directamente los ficheros de la base de datos o comprimirlos a modo de copia de seguridad. El mayor inconveniente de este método es que obligatoriamente el servidor PostgreSQL debe estar apagado.

Si quisiéramos hacer un backup comprimido de todas las bases de datos del servidor, suponiendo que se encuentran en /var/pgsql/data sería así (recordad que el servidor PostgreSQL debe estar parado):

# tar -czvf backup.tar.gz /var/pgsql/data

Restaurarlo sería tan sencillo como parar el servicio, mover los ficheros a su ruta y levantarlo de nuevo. Es un sistema rápido pero que implica más espacio en disco utilizado que el volcado SQL. Otra opción podría ser usar rsync para sincronizar remotamente las bases de datos, aunque también tendría que estar parado el servicio.

Archivado continuo y puntos de recuperación (PITR)

PostgreSQL, al igual que otros motores SQL guarda un log (WAL) que almacena todos los cambios que se realizan contra las bases de datos, algo similar al log binario de MySQL. Esto nos puede permitir en caso de fallo recuperar datos de una base de datos desde el punto concreto que necesitemos. En la documentación de PostgreSQL recomiendan combinar este sistema con el de copiado a nivel físico para una estrategia compleja de backups.

Si estáis interesados en este sistema os recomiendo leer directamente la documentación de PostgreSQL sobre este tema ya que es algo extenso y ahí está perfectamente explicado.

basededatos