Cómo hacer copias de seguridad y restaurar bases de datos usando pg_dump y pg_restore

Necesitas una copia de seguridad de PostgreSQL que puedas restaurar, no solo un archivo que parezca una copia de seguridad. pg_dump y pg_restore son las herramientas estándar para copias de seguridad lógicas: copian objetos de base de datos y datos en un volcado portátil, y luego los reconstruyen más tarde en el mismo servidor, un servidor de prueba o una nueva instancia de PostgreSQL.

Esta guía se centra en copias de seguridad lógicas de una sola base de datos. Para la recuperación ante desastres de todo el clúster, la recuperación a un punto en el tiempo o instalaciones muy grandes, combina esto con copias de seguridad físicas y archivado WAL.

Entendiendo pg_dump

pg_dump se conecta a una base de datos y exporta su esquema y datos. No vuelca roles, tablespaces ni objetos globales del clúster; usa pg_dumpall --globals-only cuando los necesites.

Formatos de salida

pg_dump admite múltiples formatos de salida:

• Texto plano (-Fp, el predeterminado): Escribe SQL que restauras con psql. Es fácil de inspeccionar y editar, pero las restauraciones grandes pueden ser más lentas y menos flexibles.
• Archivo personalizado (-Fc): Escribe un archivo comprimido para pg_restore. Admite restauración selectiva y restauración paralela.
• Formato directorio (-Fd): Escribe un directorio de archivos. Admite volcado paralelo y restauración paralela, lo que ayuda para bases de datos grandes.
• Formato tar (-Ft): Escribe un archivo tar para pg_restore. Es un solo archivo, pero no admite compresión dentro del formato de la misma manera que los archivos personalizados.

Opciones comunes de pg_dump

Aquí tienes algunas de las opciones más utilizadas para pg_dump:

• -h <nombrehost>: Conectarse a un host.
• -p <puerto>: Conectarse a un puerto.
• -U <nombreusuario>: Conectarse como un usuario.
• -W: Forzar un aviso de contraseña antes de conectarse.
• -f <nombrearchivo>: Escribir en un archivo o directorio en lugar de la salida estándar.
• -F <formato>: Elegir p, c, d o t.
• -Z <nivel>: Establecer el nivel de compresión para formatos que admiten compresión.
• --exclude-table=<patrón>: Excluir tablas coincidentes.
• -j <número> o --jobs=<número>: Usar trabajos paralelos al volcar en formato directorio.
• --clean: Incluir comandos de eliminación antes de los comandos de creación en la salida SQL plano.
• --create: Incluir comandos para crear la base de datos en sí.

Ejemplo: Copia de seguridad básica en texto plano

Para crear una copia de seguridad en texto plano de una base de datos llamada mibasededatos propiedad del usuario miusuario y guardarla en mibasededatos_backup.sql:

pg_dump -U miusuario -f mibasededatos_backup.sql mibasededatos

Si necesitas especificar un host y puerto:

pg_dump -h localhost -p 5432 -U miusuario -f mibasededatos_backup.sql mibasededatos

Dependiendo de tu método de autenticación, esto puede solicitar la contraseña de miusuario.

Ejemplo: Copia de seguridad en formato personalizado (recomendado)

El formato personalizado es una buena opción predeterminada para muchas copias de seguridad en producción porque funciona con pg_restore y admite restauración selectiva.

pg_dump -U miusuario -Fc -f mibasededatos_backup.dump mibasededatos

Esto crea mibasededatos_backup.dump en un formato adecuado para pg_restore.

Ejemplo: Copia de seguridad en formato directorio con trabajos paralelos

Para bases de datos grandes, el volcado paralelo puede reducir el tiempo de copia de seguridad. Requiere formato directorio:

pg_dump -U miusuario -Fd -j 4 -f mibasededatos_backup_dir mibasededatos

Este comando creará un directorio llamado mibasededatos_backup_dir que contiene múltiples archivos, utilizando 4 trabajos paralelos.

Entendiendo pg_restore

pg_restore restaura archivos personalizados, de directorio o tar creados por pg_dump. Los archivos SQL planos se restauran con psql, no con pg_restore.

Opciones comunes de pg_restore

• -h <nombrehost>: Conectarse a un host.
• -p <puerto>: Conectarse a un puerto.
• -U <nombreusuario>: Conectarse como un usuario.
• -W: Forzar un aviso de contraseña antes de conectarse.
• -d <basededatos>: Conectarse a esta base de datos para la restauración.
• -v: Imprimir salida detallada de la restauración.
• -c o --clean: Eliminar objetos de la base de datos antes de recrearlos.
• --create: Crear la base de datos volcada antes de restaurarla. Conéctate con -d a una base de datos de mantenimiento existente, comúnmente postgres.
• -j <número>: Usar trabajos paralelos para la restauración donde sea compatible.
• -F <formato>: Especificar el formato del archivo si la detección automática no es suficiente.

Restaurar desde copias de seguridad SQL en texto plano

Si creaste una copia de seguridad SQL en texto plano usando pg_dump (por ejemplo, mibasededatos_backup.sql), la restauras usando psql:

psql -U miusuario -d mibasededatos -f mibasededatos_backup.sql

Si el archivo de copia de seguridad incluye comandos CREATE DATABASE y \connect de pg_dump --create, conéctate a una base de datos de mantenimiento como postgres:

psql -U miusuario -f mibasededatos_backup.sql postgres

Ejemplo: Restaurar desde una copia de seguridad en formato personalizado

Para restaurar la copia de seguridad en formato personalizado mibasededatos_backup.dump en una base de datos llamada basedatos_restaurada:

pg_restore -U miusuario -d basedatos_restaurada mibasededatos_backup.dump

Si el archivo fue creado con pg_dump --create, puedes pedirle a pg_restore que cree la base de datos volcada. El valor de -d debe ser una base de datos existente utilizada para la conexión inicial, no la nueva base de datos que se está creando:

pg_restore -U miusuario --create -d postgres mibasededatos_backup.dump

Esto crea la base de datos nombrada dentro del volcado y restaura en ella. Si deseas restaurar en una base de datos con un nombre diferente, crea esa base de datos primero y restaura sin --create:

createdb -U miusuario basedatos_restaurada
pg_restore -U miusuario -d basedatos_restaurada mibasededatos_backup.dump

Ejemplo: Restauración paralela

Para una restauración más rápida, especialmente con conjuntos de datos grandes, usa la opción -j:

pg_restore -U miusuario -d basedatos_restaurada -j 4 mibasededatos_backup.dump

Esto restaura con 4 trabajos de trabajo. La restauración paralela es más útil cuando el volcado tiene múltiples objetos independientes para cargar e indexar.

Ejemplo: Restaurar desde una copia de seguridad en formato directorio

Para restaurar una copia de seguridad creada en formato directorio (por ejemplo, mibasededatos_backup_dir):

pg_restore -U miusuario -d basedatos_restaurada -j 4 mibasededatos_backup_dir

Ten en cuenta que el último argumento es la ruta del directorio.

Mejores prácticas para copias de seguridad

• Programa las copias de seguridad: Usa cron, temporizadores systemd o tu plataforma de orquestación. Elige un cronograma basado en la cantidad de datos que puedas permitirte perder.
• Usa el formato adecuado: El formato personalizado es una buena opción predeterminada. El formato directorio es mejor cuando necesitas volcado paralelo para una base de datos grande.
• Prueba las restauraciones: Restaura a una base de datos de prueba de forma programada. Una copia de seguridad que nunca pruebas es solo una suposición.
• Captura objetos globales: Vuelca roles y tablespaces por separado con pg_dumpall --globals-only al reconstruir un servidor desde cero.
• Almacena copias fuera del sitio: Mantén las copias de seguridad lejos del servidor principal y protégelas con cifrado y controles de acceso.
• Monitorea los trabajos: Alerta sobre volcados fallidos, archivos de copia de seguridad inusualmente pequeños y marcas de tiempo de copias de seguridad antiguas.
• Planifica la retención: Decide cuántas copias de seguridad por hora, día, semana y mes conservas, luego automatiza la limpieza.
• Ten cuidado con --clean: Elimina objetos en la base de datos de destino. Úsalo solo cuando quieras reemplazar objetos existentes.

Conclusión

Usa pg_dump -Fc para una copia de seguridad flexible de un solo archivo, pg_dump -Fd -j cuando necesites volcado paralelo, psql para restauraciones SQL planas y pg_restore para restauraciones de archivos. Luego, prueba el proceso restaurando en una base de datos de prueba antes de que lo necesites en una emergencia.