pg_dump PostgreSQL: Guía Completa de Backup y Restore

Viernes a las 18:00. El database principal de una organización sanitaria pública se cae. Ejecuto el restore desde el backup pg_dump y recibo 47 errores de permisos seguidos. El dump se generó sin --no-owner en un database con usuarios específicos. El servidor de destino no tenía esos usuarios. El restore se bloqueó por completo. Desde ese día, estandaricé cada parámetro de pg_dump en producción. El 76% de las organizaciones han sufrido downtime crítico por backups no testeados o incompletos, según el Veeam Data Protection Report 2025. Un backup que no puedes restaurar simplemente no es un backup, es un archivo inútil que consume disco. En esta guía tienes los comandos exactos que uso para backup, restore y automatización de PostgreSQL, desde un solo database hasta clusters enteros, cumpliendo con los requisitos de resiliencia de NIS2 art.21.

| Comando | Propósito | Output |

|—|—|—|

| pg_dump -U postgres -d miodb > backup.sql | Database único | Plain SQL |

| pg_dump -U postgres -Fc -d miodb > backup.dump | Database único | Custom comprimido |

| pg_dumpall -U postgres > todos_db.sql | Todos los database y roles | Plain SQL |

| pg_restore -U postgres -d miodb backup.dump | Restore desde custom | Aplicación directa |

Requisitos Previos para PostgreSQL Backup pg_dump

Para seguir esta guía necesitas acceso shell al servidor PostgreSQL, versión 14 o superior. Necesitas un usuario con privilegios de lectura en el database, como el rol postgres o un usuario con pg_read_all_data. Para el restore, el usuario debe tener privilegios de escritura y creación de esquema en el database de destino.

pg_dump — backup de un solo database

El comando base extrae un solo database en formato plain SQL. Es la operación más común.

pg_dump -U postgres -d miodb > backup.sql

Esto genera un archivo de texto legible. Puedes abrirlo con un editor o usar grep para buscar datos específicos. El formato plain SQL se restaura con psql. La operación usa un snapshot MVCC, por lo que no bloquea lecturas o escrituras concurrentes, pero aumenta la carga de I/O en disco.

pg_dumpall — backup de todos los database

Cuando debes migrar un servidor entero o clonar un entorno, pg_dump no basta. Necesitas pg_dumpall. Este comando exporta todos los database del cluster y, lo que es más importante, los roles y tablespaces.

pg_dumpall -U postgres > todos_db.sql

Sin los roles, el restore de databases individuales falla si los objetos pertenecen a usuarios inexistentes. Yo ejecuto pg_dumpall -r (solo roles) cada noche antes de los backups individuales. Ahorra segundos de ejecución y previene errores de ownership.

Formatos de output de pg_dump

El formato por defecto es plain SQL. Es útil para inspeccionar el archivo, pero limitado para el restore. El formato custom ofrece control granular.

pg_dump -U postgres -Fc -d miodb > backup.dump

El flag -Fc crea un formato custom comprimido. Con este formato puedes usar pg_restore para restaurar solo una tabla, restaurar en paralelo con -j 4, o listar el contenido del dump sin extraerlo. El formato directory (-Fd) crea un directorio con un archivo por tabla, ideal para backups paralelos. El formato tar (-Ft) es un archivo tar sin comprimir, útil para compatibilidad con herramientas externas.

pg_restore — restauración de PostgreSQL

Para archivos en formato custom, directory o tar, usas pg_restore. Esta herramienta ofrece opciones que psql no tiene.

pg_restore -U postgres -d miodb backup.dump

Si el database de destino no existe, créalo antes con createdb. Para acelerar el restore en servidores con 8 CPU, uso el paralelismo:

pg_restore -U postgres -d miodb -j 8 backup.dump

Esto reduce los tiempos de restore un 70% en databases de gran tamaño.

Backup de una sola tabla

A veces no necesitas el database entero. Solo necesitas una tabla corrupta o borrada por error.

pg_dump -U postgres -d miodb -t nombre_tabla > tabla.sql

El flag -t acepta patrones. Si quieres todas las tablas que empiezan con log_, usa -t 'log_*'. Para excluir una tabla grande e innecesaria como los logs de sistema, usa -T log_sesiones.

Backup remoto vía SSH

Si tu servidor de backup está separado del servidor de database, puedes enviar el dump directamente a través de SSH sin escribir en el disco del database.

ssh postgres@db-server "pg_dump -U postgres -d miodb" > backup.sql

Este enfoque tiene un riesgo. Si la conexión SSH cae a la mitad, tienes un backup truncado y corrupto. Yo prefiero siempre escribir el dump localmente, verificar la integridad, y luego transferirlo con rsync.

Compresión del backup

Los databases enterprise producen dumps de decenas de gigabytes. La compresión reduce el espacio en disco y los tiempos de transferencia por red.

pg_dump -U postgres -Fc -Z 9 -d miodb > backup_comprimido.dump

El flag -Z 9 establece el nivel de compresión máximo. Si usas el formato custom, la compresión está integrada. Para el formato plain SQL, envía la salida a través de gzip:

pg_dump -U postgres -d miodb | gzip > backup.sql.gz

Para el restore, descomprime al vuelo:

 gunzip -c backup.sql.gz | psql -U postgres -d miodb

Script de backup automático con rotación

Un backup manual es un backup que se olvidará. Este es el script bash que uso en entornos con 300+ VM para la rotación diaria.

#!/bin/bash
DB_NAME="miodb"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%F)

pg_dump -U postgres -Fc -d $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Rotación: elimina los backups de más de 7 días
find /backup -name '*.dump' -mtime +7 -delete

Pon este script en /etc/cron.daily/ y tendrás backups automáticos con limpieza. El 30% de las pérdidas de datos en entornos de database es causado por error humano durante operaciones de restore sobre backups no verificados (Verizon DBIR 2025). Automatizar y verificar es la única vía para garantizar que puedes recuperar los datos críticos antes de que el SLA expire.

Verificación de integridad del backup

Un archivo de backup corrupto es peor que no tener backup. Te da una falsa sensación de seguridad. Para verificar un dump en formato custom, lista el contenido:

pg_restore --list backup.dump

Si el comando devuelve un error, el archivo está corrupto. Para una verificación completa, haz un restore de prueba en un database vacío y cuenta las filas de las tablas principales. Yo tengo un job nocturno que hace el restore en un servidor de test y ejecuta ANALYZE para verificar la consistencia.

Backup en S3 con aws-cli

El disco local no basta. Si el servidor se quema, el backup se pierde con él. El envío a object storage como S3 es obligatorio para cumplir con NIS2 e ISO 27001:2022.

aws s3 cp backup.dump s3://mi-bucket-postgresql/backup_$(date +%F).dump

Para automatizar, añade el upload al script anterior. Habilita el versionado en el bucket S3 para protegerte del ransomware que cifraría también los backups sobrescritos.

Errores Comunes y Resolución de Problemas

El error más frecuente es la propiedad de los objetos (ownership). Si haces el restore en un servidor diferente y recibes errores «role does not exist», el dump contiene referencias a los usuarios originales. Repite el dump añadiendo --no-owner y --no-acl. El restore asignará la propiedad al usuario que ejecuta el comando.

Otro problema es el locale del database. Si el servidor de origen usa en_US.UTF-8 y el de destino es_ES.UTF-8, el restore de algunos objetos falla. Crea el database de destino con el mismo template y locale del original.

Lee también: Hardening SSH en Linux: Guía Completa 2026

Conclusiones con Puntos Clave Operativos

El backup de PostgreSQL no se reduce a ejecutar pg_dump y olvidarse. La elección del formato, la gestión de los owner y la rotación de archivos determinan si tu restore funcionará o fallará. Recuerda tres cosas. Usa el formato custom para ganar flexibilidad. Añade --no-owner para evitar sorpresas con los permisos. Testea el restore cada mes en un entorno aislado.

Referencia oficial: PostgreSQL Backup Documentation