.

Cómo crear y verificar backups de PostgreSQL con pg_dump y pg_restore

Un backup que nunca se restaura es solo una esperanza comprimida. Muchos problemas aparecen recién durante un incidente: permisos insuficientes, extensiones ausentes, archivos incompletos, versiones incompatibles o procedimientos que nadie ejecutó de punta a punta.

pg_dump genera una exportación lógica consistente de una base PostgreSQL, incluso mientras existen lectores y escritores concurrentes. pg_restore permite inspeccionar y recuperar los formatos de archivo no textuales, como custom.

Este enfoque debe incluir todo el ciclo:

Base origen → Dump temporal → Validación → Checksum → Copia externa → Restore aislado → Pruebas → Retención

¿Cuándo conviene utilizarlo?

Los dumps lógicos son especialmente útiles para:

• Bases pequeñas o medianas.
• Migraciones entre servidores o versiones.
• Respaldos previos a cambios de esquema.
• Restauraciones selectivas de tablas o esquemas.
• Creación de entornos de prueba.
• Plataformas donde todavía no existe PITR.

pg_dump no reemplaza una estrategia de recuperación continua. No contiene los archivos físicos ni los segmentos WAL necesarios para recuperar la base en un instante determinado.

Para bases grandes o RPO exigentes, evalúa una estrategia con backup físico, pg_basebackup y archivado continuo de WAL. Documentación oficial sobre PITR.

Qué incluye y qué no incluye pg_dump

Un dump de una base puede contener:

• Esquemas.
• Tablas y datos.
• Índices y constraints.
• Funciones y procedimientos.
• Vistas.
• Extensiones declaradas en la base.
• Permisos y propietarios asociados a sus objetos.

No incluye objetos globales del clúster, como:

• Roles.
• Tablespaces.
• Configuración del servidor.
• Archivos físicos.
• WAL.
• Contraseñas externas o secretos de la aplicación.

Si la recuperación debe reproducir propietarios y permisos, también será necesario respaldar los objetos globales con pg_dumpall --globals-only.

Ver la Imagen 1: ciclo completo de backup y restauración verificada.

Requisitos previos

Antes de comenzar, verifica:

• Versión del servidor PostgreSQL.
• Versión de pg_dump, pg_restore y psql.
• Espacio disponible en origen, destino externo y entorno de restauración.
• Permisos de lectura sobre todos los objetos necesarios.
• Disponibilidad de extensiones en el servidor de prueba.
• Conectividad segura hacia el almacenamiento externo.
• RPO y RTO definidos.
• Un entorno aislado donde ejecutar la recuperación.

Consulta las versiones:

psql --version
pg_dump --version
pg_restore --version

psql -h 127.0.0.1 -d appdb \
  -c "SELECT version();"

pg_dump puede conectarse con servidores de su misma versión mayor o anteriores, pero se niega a respaldar un servidor cuya versión mayor sea más nueva que la herramienta. La restauración hacia una versión posterior es el escenario esperado; restaurar hacia una versión anterior no está garantizado. Compatibilidad oficial de pg_dump.

Paso 1. Crear un usuario de backup

Conceder únicamente CONNECT no alcanza: pg_dump ejecuta consultas para leer los objetos y sus datos.

Como administrador, crea un usuario dedicado:

CREATE ROLE backup_user LOGIN;

GRANT CONNECT ON DATABASE appdb TO backup_user;

Después, conectado a appdb, concede acceso a cada esquema respaldado:

GRANT USAGE ON SCHEMA public TO backup_user;

GRANT SELECT ON ALL TABLES
IN SCHEMA public
TO backup_user;

GRANT SELECT ON ALL SEQUENCES
IN SCHEMA public
TO backup_user;

Para que los objetos futuros también sean legibles, cada rol propietario debe configurar sus privilegios predeterminados:

ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA public
GRANT SELECT ON TABLES TO backup_user;

ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA public
GRANT SELECT ON SEQUENCES TO backup_user;

Repite el procedimiento para todos los esquemas y propietarios relevantes.

En versiones que incluyen el rol predefinido pg_read_all_data, puede concederse:

GRANT pg_read_all_data TO backup_user;

Este permiso es más amplio: permite leer tablas, vistas y secuencias, además de utilizar todos los esquemas. No incluye BYPASSRLS. Si existen políticas Row-Level Security, un backup completo normalmente requiere una cuenta autorizada para omitirlas; revisa ese privilegio con especial cuidado. Roles predefinidos de PostgreSQL.

También deben comprobarse objetos grandes, foreign tables y cualquier extensión con requisitos particulares.

Paso 2. Proteger las credenciales

No utilices PGPASSWORD en scripts permanentes ni incluyas contraseñas directamente en comandos.

Una alternativa es un archivo de contraseñas separado:

127.0.0.1:5432:appdb:backup_user:password_segura

Protege el archivo:

chmod 600 /ruta/segura/backup.pgpass

Y referencia su ubicación:

export PGPASSFILE=/ruta/segura/backup.pgpass

En sistemas Unix, PostgreSQL ignora un archivo de contraseñas si sus permisos permiten acceso al grupo o a otros usuarios. Documentación oficial de .pgpass.

En producción, utiliza preferentemente el gestor de secretos de la plataforma y una cuenta sin permisos de escritura.

Paso 3. Generar un dump seguro

Utiliza una marca de tiempo calculada una sola vez. Así se evita que el nombre del dump y el checksum difieran si el proceso cruza la medianoche.

set -Eeuo pipefail

backup_dir=/backups/postgres
timestamp=$(date -u +%Y%m%dT%H%M%SZ)
backup_name="appdb-${timestamp}"

partial_file="${backup_dir}/${backup_name}.dump.partial"
dump_file="${backup_dir}/${backup_name}.dump"

umask 077
mkdir -p -- "$backup_dir"

trap 'rm -f -- "$partial_file"' EXIT

pg_dump \
  --host=127.0.0.1 \
  --port=5432 \
  --username=backup_user \
  --dbname=appdb \
  --format=custom \
  --file="$partial_file" \
  --verbose

pg_restore --list "$partial_file" >/dev/null

mv -- "$partial_file" "$dump_file"
trap - EXIT

El archivo utiliza .partial mientras se genera. Solo recibe su nombre definitivo cuando pg_dump termina correctamente y pg_restore puede leer su tabla de contenidos.

Usar --file es más claro que redirigir stdout y reduce el riesgo de conservar como válido un archivo parcial.

El formato custom permite:

• Compresión integrada.
• Restauración selectiva.
• Inspección mediante pg_restore --list.
• Restauración paralela con pg_restore --jobs.

No permite un dump paralelo; para paralelizar la creación del backup se necesita el formato directory.

Paso 4. Registrar metadatos del backup

Conserva junto al respaldo información útil para la recuperación:

psql \
  --host=127.0.0.1 \
  --username=backup_user \
  --dbname=appdb \
  --tuples-only \
  --command="SELECT version();" \
  > "${backup_dir}/${backup_name}.metadata.txt"

También conviene registrar:

• Fecha y hora UTC.
• Host y base de origen.
• Versión de PostgreSQL.
• Versión de pg_dump.
• Duración.
• Tamaño del archivo.
• Esquemas incluidos.
• Conteos de tablas críticas.
• Identificador del proceso automatizado.

No almacenes contraseñas ni cadenas de conexión con secretos.

Paso 5. Calcular y verificar el checksum

Calcula el hash desde el directorio del backup para que el archivo .sha256 contenga un nombre relativo:

(
  cd "$backup_dir"
  sha256sum "${backup_name}.dump" \
    > "${backup_name}.dump.sha256"
)

Protege los archivos:

chmod 600 \
  "${dump_file}" \
  "${dump_file}.sha256" \
  "${backup_dir}/${backup_name}.metadata.txt"

Comprueba inmediatamente el checksum:

(
  cd "$backup_dir"
  sha256sum --check "${backup_name}.dump.sha256"
)

SHA-256 permite detectar corrupción accidental o transferencias incompletas. No demuestra autenticidad frente a una modificación intencional; para eso se necesita una firma, MAC o mecanismo equivalente administrado de forma segura.

Paso 6. Respaldar roles y tablespaces

pg_dump no incluye objetos globales. Si la recuperación debe conservar propietarios y privilegios, genera un archivo adicional con una cuenta administrativa autorizada:

pg_dumpall \
  --host=127.0.0.1 \
  --port=5432 \
  --username=backup_admin \
  --globals-only \
  --file="${backup_dir}/${backup_name}.globals.sql"

Este archivo puede contener información sensible sobre roles y hashes de contraseñas. Protégelo y cífralo con el mismo nivel que el dump.

pg_dumpall --globals-only incluye roles y tablespaces, pero no crea en el sistema operativo las rutas físicas requeridas por los tablespaces. Esas ubicaciones deben prepararse antes de una recuperación fiel. Documentación oficial de pg_dumpall.

Paso 7. Copiar el backup fuera del servidor

Transfiere el dump, checksum y metadatos hacia un destino independiente:

rsync \
  --archive \
  --partial \
  "${dump_file}" \
  "${dump_file}.sha256" \
  "${backup_dir}/${backup_name}.metadata.txt" \
  backup@destino:/data/postgres/

rsync con un destino SSH cifra el transporte, pero no necesariamente los datos almacenados. Para información personal o sensible, cifra el archivo antes de transferirlo o utiliza almacenamiento con cifrado administrado.

Verifica la copia en el destino:

ssh backup@destino \
  "cd /data/postgres && sha256sum --check '${backup_name}.dump.sha256'"

Antes de automatizar este comando:

• Verifica la clave del host SSH.
• Restringe la cuenta remota.
• Evita que el servidor de producción pueda borrar respaldos históricos.
• Utiliza versionado o retención inmutable cuando sea posible.

No elimines copias locales hasta confirmar que la copia externa existe y supera su checksum.

Paso 8. Inspeccionar el archivo

Antes de restaurar:

sha256sum --check "${dump_file}.sha256"
pg_restore --list "$dump_file" \
  > "${backup_name}.toc.txt"

Revisa la tabla de contenidos:

less "${backup_name}.toc.txt"

Confirma que incluya:

• Esquemas esperados.
• Tablas críticas.
• Datos de las tablas.
• Índices.
• Constraints.
• Funciones.
• Extensiones.

Esta inspección todavía no reemplaza una restauración completa.

Paso 9. Crear una base aislada y limpia

Utiliza un servidor de prueba compatible y una base creada desde template0:

restore_db="restore_test_${timestamp}"

createdb \
  --host=127.0.0.1 \
  --port=5432 \
  --username=restore_admin \
  --template=template0 \
  "$restore_db"

template0 evita conflictos con objetos agregados localmente a template1.

La base debe estar aislada de aplicaciones, tareas programadas, colas, webhooks y servicios externos. Una restauración no debe activar accidentalmente procesos sobre datos reales.

Paso 10. Restaurar el backup

Para una prueba portable centrada en esquema y datos:

pg_restore \
  --host=127.0.0.1 \
  --port=5432 \
  --username=restore_admin \
  --dbname="$restore_db" \
  --exit-on-error \
  --no-owner \
  --no-acl \
  --verbose \
  "$dump_file"

Las opciones significan:

• --exit-on-error: detiene el proceso ante el primer error SQL.
• --no-owner: no intenta restaurar los propietarios originales.
• --no-acl: omite instrucciones GRANT y REVOKE.
• --verbose: registra el avance y los objetos procesados.

Esta modalidad no valida propietarios ni permisos. Para una recuperación fiel:

1. Restaura primero los objetos globales.
2. Prepara tablespaces y extensiones.
3. Omite --no-owner y --no-acl.
4. Utiliza una cuenta con permisos suficientes.

pg_restore continúa después de ciertos errores de forma predeterminada. Para una prueba automatizada, --exit-on-error es esencial. Documentación oficial de pg_restore.

Restauración atómica o paralela

Para una restauración que se confirme o revierta como una unidad:

pg_restore \
  --dbname="$restore_db" \
  --single-transaction \
  --no-owner \
  --no-acl \
  "$dump_file"

--single-transaction implica --exit-on-error.

Para reducir el tiempo en una base aislada:

pg_restore \
  --dbname="$restore_db" \
  --jobs=4 \
  --exit-on-error \
  --no-owner \
  --no-acl \
  "$dump_file"

No combines --jobs con --single-transaction. Cada trabajo paralelo utiliza una conexión independiente y un fallo puede dejar una restauración parcial; elimina y recrea la base antes de repetirla.

Paso 11. Actualizar estadísticas

Después de cargar una base desde un dump, genera estadísticas para el optimizador:

vacuumdb \
  --host=127.0.0.1 \
  --username=restore_admin \
  --dbname="$restore_db" \
  --analyze-in-stages

El análisis por etapas produce rápidamente estadísticas iniciales y luego las refina. Está pensado para bases recién restauradas o sin estadísticas válidas. Referencia oficial de vacuumdb.

Ver la Imagen 2: validación ordenada de la recuperación.

Paso 12. Validar esquema y datos

Una restauración exitosa debe superar varias comprobaciones.

Verificar tablas y extensiones

psql \
  --host=127.0.0.1 \
  --username=restore_admin \
  --dbname="$restore_db" \
  --command="\dt"

psql \
  --host=127.0.0.1 \
  --username=restore_admin \
  --dbname="$restore_db" \
  --command="\dx"

Buscar constraints pendientes de validación

psql \
  --host=127.0.0.1 \
  --username=restore_admin \
  --dbname="$restore_db" \
  --command="
    SELECT conrelid::regclass, conname
    FROM pg_constraint
    WHERE NOT convalidated;
  "

Comparar datos críticos

Define consultas acordes con la aplicación:

SELECT count(*) FROM clientes;
SELECT count(*) FROM pagos;
SELECT min(created_at), max(created_at) FROM pagos;
SELECT sum(total) FROM pagos WHERE estado = 'confirmado';

Los conteos de referencia deben capturarse como parte del backup o inmediatamente después con una consistencia temporal documentada. No compares valores obtenidos muchas horas después en una base que continúa recibiendo escrituras.

Ejecutar un smoke test

Prueba al menos:

• Autenticación de solo lectura.
• Consultas principales.
• Funciones y vistas.
• Lectura de datos recientes.
• Relaciones críticas.
• Codificación y zona horaria.
• Acceso con los roles que utilizaría la aplicación.

No conectes la aplicación restaurada a proveedores de pagos, correo, colas o integraciones reales.

Paso 13. Registrar el resultado

Conserva evidencia de:

• Checksum validado.
• Inicio y fin de la restauración.
• Versión de origen y destino.
• Código de salida de pg_restore.
• Duración.
• Tamaño restaurado.
• Conteos comparados.
• Consultas ejecutadas.
• Errores y advertencias.
• Responsable de la prueba.

Solo considera recuperable un respaldo si la restauración y las validaciones terminan correctamente.

Al finalizar:

dropdb \
  --host=127.0.0.1 \
  --username=restore_admin \
  --if-exists \
  "$restore_db"

Verifica dos veces el nombre y el servidor antes de ejecutar dropdb. Nunca utilices variables ambiguas ni una conexión de producción en el proceso de prueba.

Paso 14. Aplicar retención

Primero previsualiza qué archivos serían eliminados:

find /backups/postgres \
  -type f \
  \( -name 'appdb-*.dump' -o -name 'appdb-*.dump.sha256' \) \
  -mtime +14 \
  -print

Después de verificar copia externa, checksum y restauración:

find /backups/postgres \
  -type f \
  \( -name 'appdb-*.dump' -o -name 'appdb-*.dump.sha256' \) \
  -mtime +14 \
  -delete

La retención debe tratar el dump, checksum, metadatos y objetos globales como un conjunto. Una política madura también contempla:

• Copias diarias, semanales y mensuales.
• Almacenamiento en más de un medio.
• Una copia fuera del sitio.
• Versionado o inmutabilidad.
• Eliminación segura al vencer la retención.
• Requisitos legales y de privacidad.

Monitorización del proceso

Supervisa:

• Fecha del último backup exitoso.
• Antigüedad del último restore probado.
• Duración de pg_dump y pg_restore.
• Tamaño y variación del dump.
• Espacio libre.
• Estado del checksum.
• Estado de la copia externa.
• Código de salida de cada comando.
• Fallos consecutivos.
• Cumplimiento de RPO y RTO.

Un archivo nuevo no equivale automáticamente a un backup exitoso. La alerta debe depender de la finalización y verificación de todo el proceso.

Impacto sobre producción

pg_dump obtiene un snapshot consistente y no bloquea el uso normal de la base por lectores y escritores. Sin embargo:

• Consume CPU, disco y red.
• Puede ejecutarse durante mucho tiempo.
• Mantiene una transacción abierta.
• Toma bloqueos que pueden entrar en conflicto con determinados cambios DDL.
• Puede aumentar la presión sobre almacenamiento y mantenimiento.

Programa los dumps fuera de los momentos de mayor carga y evita cambios de esquema concurrentes.

Problemas frecuentes

pg_dump: permission denied

CONNECT no es suficiente. Revisa USAGE en schemas, SELECT en tablas y secuencias, objetos futuros, RLS, foreign tables y objetos grandes.

pg_restore falla por propietarios o roles inexistentes

Restaura previamente los objetos globales o utiliza --no-owner --no-acl para una prueba portable. Esta última opción no valida el modelo real de permisos.

Faltan extensiones

Instala en el destino los paquetes que proporcionan las extensiones antes de restaurar. El dump puede incluir CREATE EXTENSION, pero no los archivos binarios ni de control del sistema operativo.

El dump está corrupto

Verifica SHA-256 y consulta pg_restore --list. Si el checksum remoto no coincide, no intentes corregir el archivo: repite la transferencia desde una copia validada.

La restauración termina con errores, pero continúa

Utiliza --exit-on-error o --single-transaction. De forma predeterminada, pg_restore puede continuar y mostrar el total de errores al final.

La restauración funciona, pero las consultas son lentas

Ejecuta ANALYZE o vacuumdb --analyze-in-stages. Los datos restaurados pueden no contar con estadísticas apropiadas para el optimizador.

El dump tarda demasiado

Evalúa:

• Formato directory y dump paralelo.
• Restore paralelo.
• Ejecución desde una réplica compatible.
• Backups físicos.
• pg_basebackup y archivado WAL.
• Una política de PITR.

Un dump lógico periódico puede resultar insuficiente para bases grandes o RPO de pocos minutos.

Buenas prácticas para producción

• Define RPO y RTO antes de elegir la frecuencia.
• Usa una cuenta dedicada y de solo lectura.
• Protege credenciales fuera del script.
• Genera archivos temporales y promuévelos solo tras validar.
• Conserva checksum y metadatos.
• Incluye roles y tablespaces cuando sean necesarios.
• Cifra datos sensibles en tránsito y reposo.
• Mantén copias externas e inmutables.
• Automatiza restores periódicos.
• Valida esquema, datos y aplicación.
• Monitoriza duración, tamaño y último éxito.
• Documenta responsables y escalamiento.
• Prueba el procedimiento después de cambios de versión.

Conclusión

pg_dump crea un respaldo lógico consistente, pero solo una restauración controlada demuestra que ese respaldo puede utilizarse.

El flujo recomendado es: generar temporalmente → validar → calcular checksum → copiar fuera del servidor → restaurar en aislamiento → comprobar datos → aplicar retención.

Para sistemas críticos, complementa los dumps lógicos con backups físicos y archivado WAL. La estrategia correcta no es la que produce más archivos, sino la que permite recuperar el servicio dentro del RPO y RTO acordados.

Cloud Servers by Donweb

Ya tienes backups que sabes restaurar. Guardarlos fuera del servidor es lo siguiente.
Alojamiento ultra rápido, escalable y con alta disponibilidad.

• Performance que te sorprenderá
• Rápida escalabilidad y sin limitaciones
• Arquitectura de alta disponibilidad
• Soporte experto y ejecutivos de cuenta
• Pagos en tu moneda y facturación local

Guarda tus copias en la nube