El backup y la restauración en PostgreSQL son la base de cualquier plan de recuperación ante desastres. Tanto si proteges una única base de datos de aplicación como si gestionas cientos de instancias, dominar pg_dump y pg_restore te garantiza la capacidad de recuperarte ante fallos de hardware, eliminaciones accidentales y migraciones fallidas. Esta guía cubre todos los aspectos prácticos: desde volcados básicos hasta programación automatizada, restauraciones selectivas y el paso crítico que la mayoría de los equipos omite: verificar realmente que los backups se pueden restaurar.
Requisitos Previos
- PostgreSQL 12 o posterior instalado y en ejecución
- Acceso a una base de datos PostgreSQL con privilegios suficientes (
pg_dumprequiere al menos acceso de lectura a todos los objetos) - Acceso
sudoo al usuariopostgresen el servidor de base de datos - Familiaridad básica con SQL y los conceptos de PostgreSQL (bases de datos, esquemas, tablas)
Creación de Backups con pg_dump
pg_dump genera backups lógicos: una representación de tu base de datos como sentencias SQL o un archivo comprimido. A diferencia de las copias a nivel de sistema de archivos, estos backups son portables entre versiones de PostgreSQL y plataformas.
Volcado SQL Simple (Por Defecto)
El backup más sencillo crea un archivo .sql con todos los comandos SQL necesarios para recrear la base de datos:
# Volcar una sola base de datos a un archivo SQL
pg_dump -U postgres -h localhost mydb > mydb_backup.sql
# Incluir la sentencia CREATE DATABASE
pg_dump -U postgres -h localhost -C mydb > mydb_backup_with_create.sql
# Volcar con marca de tiempo en el nombre de archivo
pg_dump -U postgres mydb > "mydb_$(date +%Y%m%d_%H%M%S).sql"El formato SQL simple es legible por humanos y se puede restaurar con psql. La desventaja: no admite restauración paralela ni restauración selectiva por tabla.
Formato Custom (-Fc) — Recomendado para Producción
El formato custom genera un archivo comprimido no textual que ofrece las opciones de restauración más flexibles:
# Backup en formato custom (comprimido por defecto)
pg_dump -U postgres -Fc mydb > mydb_backup.dump
# Formato custom con compresión máxima
pg_dump -U postgres -Fc -Z 9 mydb > mydb_backup.dump
# Formato custom con volcado en paralelo (4 workers)
pg_dump -U postgres -Fc -j 4 mydb > mydb_backup.dumpPor qué se prefiere el formato custom:
- Comprimido automáticamente (típicamente entre 5 y 10 veces más pequeño que el SQL simple)
- Admite restauración paralela con
pg_restore -j - Permite restauración selectiva (tablas específicas, esquemas o solo datos)
- Permite reordenar elementos durante la restauración para optimizar la velocidad de carga
Formato Directorio (-Fd) — Mejor para Bases de Datos Grandes
El formato directorio crea un directorio con un archivo por tabla, lo que permite volcados verdaderamente paralelos:
# Formato directorio con volcado en paralelo (8 workers)
pg_dump -U postgres -Fd -j 8 -f /backup/mydb_dir mydbEsto crea un directorio con un archivo toc.dat (tabla de contenidos) y un archivo comprimido por tabla. El volcado paralelo reduce significativamente el tiempo de backup en bases de datos grandes.
Volcado de Todas las Bases de Datos (pg_dumpall)
Para hacer backup de todas las bases de datos del clúster PostgreSQL, incluidos los objetos globales (roles, tablespaces):
# Volcar todas las bases de datos y objetos globales
pg_dumpall -U postgres > all_databases.sql
# Volcar solo los objetos globales (roles, tablespaces)
pg_dumpall -U postgres --globals-only > globals.sqlpg_dumpall siempre produce formato SQL simple. En clústeres grandes, vuelca los objetos globales por separado y usa pg_dump por base de datos en formato custom.
Volcados Selectivos
# Volcar una sola tabla
pg_dump -U postgres -t users mydb > users_table.sql
# Volcar varias tablas específicas
pg_dump -U postgres -t users -t orders -t products mydb > selected_tables.sql
# Volcar un esquema específico
pg_dump -U postgres -n public mydb > public_schema.sql
# Volcar solo el esquema (sin datos)
pg_dump -U postgres -s mydb > schema_only.sql
# Volcar solo los datos (sin esquema)
pg_dump -U postgres -a mydb > data_only.sql
# Excluir una tabla grande del volcado
pg_dump -U postgres -T audit_log mydb > mydb_no_audit.sqlRestauración de Backups
Restaurar desde SQL Simple
# Restaurar en una base de datos existente
psql -U postgres -h localhost mydb < mydb_backup.sql
# Crear la base de datos y restaurar (si el volcado incluye el flag -C)
psql -U postgres -h localhost < mydb_backup_with_create.sql
# Restaurar con salida detallada
psql -U postgres -v ON_ERROR_STOP=1 mydb < mydb_backup.sqlEl flag ON_ERROR_STOP=1 hace que psql se detenga en el primer error en lugar de continuar silenciosamente. Úsalo siempre al restaurar bases de datos de producción.
Restaurar desde Formato Custom
# Restauración básica
pg_restore -U postgres -d mydb mydb_backup.dump
# Restauración paralela (8 workers — mucho más rápida en bases de datos grandes)
pg_restore -U postgres -d mydb -j 8 mydb_backup.dump
# Restaurar en una nueva base de datos
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored mydb_backup.dump
# Limpiar (eliminar) objetos existentes antes de restaurar
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dumpRestaurar Tablas Específicas
# Restaurar una sola tabla desde un volcado en formato custom
pg_restore -U postgres -d mydb -t users mydb_backup.dump
# Restaurar usando un archivo de lista (control granular)
pg_restore -l mydb_backup.dump > restore_list.txt
# Edita restore_list.txt — comenta los elementos que no quieras restaurar
pg_restore -U postgres -d mydb -L restore_list.txt mydb_backup.dumpRestaurar un Esquema Específico
# Restaurar solo el esquema 'public'
pg_restore -U postgres -d mydb -n public mydb_backup.dump
# Restaurar solo los datos (el esquema ya existe)
pg_restore -U postgres -d mydb -a mydb_backup.dumpComparación de Métodos de Backup en PostgreSQL
| Característica | pg_dump (Lógico) | pg_basebackup (Físico) | Snapshot de sistema de archivos |
|---|---|---|---|
| Alcance del backup | Base de datos individual | Clúster completo | Clúster completo |
| Restauración entre versiones | Sí | No (misma versión mayor) | No |
| Restauración selectiva de tablas | Sí (flag -t) | No | No |
| Recuperación puntual | No | Sí (con WAL) | Sí (con WAL) |
| Backup con el servidor activo | Sí (snapshot consistente) | Sí | Requiere fsync/freeze |
| Tamaño del backup | Menor (datos comprimidos) | Mayor (directorio de datos completo) | Mayor (disco completo) |
| Velocidad de backup | Más lento (lectura vía SQL) | Más rápido (streaming) | El más rápido (nivel de bloque) |
| Mejor para | Bases de datos individuales, migraciones | DR de clúster completo, PITR | Snapshots de VM/cloud |
Usa pg_dump para backups diarios de bases de datos individuales y para migraciones entre versiones de PostgreSQL. Usa pg_basebackup cuando necesites recuperación puntual o estés configurando replicación en streaming. Usa snapshots del sistema de archivos como complemento, no como sustituto: son rápidos pero requieren un manejo cuidadoso de los archivos WAL.
Automatización de Backups con Cron
Script de Backup
#!/bin/bash
# /usr/local/bin/pg_backup.sh
# Script automatizado de backup de PostgreSQL
set -euo pipefail
# Configuración
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=30
PG_USER="postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"
# Crear el directorio de backup si no existe
mkdir -p "$BACKUP_DIR"
# Función de log
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}
log "Iniciando backup de PostgreSQL"
# Volcar objetos globales (roles, tablespaces)
pg_dumpall -U "$PG_USER" --globals-only > "$BACKUP_DIR/globals_${TIMESTAMP}.sql" 2>> "$LOG_FILE"
log "Objetos globales volcados"
# Volcar cada base de datos en formato custom
for DB in $(psql -U "$PG_USER" -At -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
DUMP_FILE="$BACKUP_DIR/${DB}_${TIMESTAMP}.dump"
pg_dump -U "$PG_USER" -Fc -Z 6 "$DB" > "$DUMP_FILE" 2>> "$LOG_FILE"
SIZE=$(du -h "$DUMP_FILE" | cut -f1)
log "Volcado $DB ($SIZE)"
done
# Eliminar backups más antiguos que el período de retención
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete
find "$BACKUP_DIR" -name "globals_*.sql" -mtime +${RETENTION_DAYS} -delete
log "Limpiados backups con más de $RETENTION_DAYS días"
log "Backup completado"# Hacer ejecutable
chmod +x /usr/local/bin/pg_backup.sh
# Probar manualmente primero
sudo -u postgres /usr/local/bin/pg_backup.shProgramación con Cron
# Editar el crontab del usuario postgres
sudo crontab -u postgres -e# Backup diario a las 2:00 AM
0 2 * * * /usr/local/bin/pg_backup.sh
# Backup completo semanal los domingos a la 1:00 AM (retención mayor)
0 1 * * 0 /usr/local/bin/pg_backup.shAutenticación sin Contraseña con .pgpass
Para los trabajos de cron necesitas autenticación sin contraseña. Crea un archivo .pgpass:
# Crear .pgpass para el usuario postgres
sudo -u postgres bash -c 'cat > ~/.pgpass << EOF
localhost:5432:*:postgres:your_secure_password
EOF'
# Establecer los permisos requeridos (pg_dump rechaza un .pgpass con permisos abiertos)
sudo -u postgres chmod 600 ~/.pgpassFormato: hostname:port:database:username:password. Usa * como comodín para el campo de base de datos para cubrir todas las bases de datos.
Verificación de la Integridad del Backup
Un backup que no se puede restaurar no es un backup. Programa pruebas de restauración periódicas:
#!/bin/bash
# /usr/local/bin/pg_restore_test.sh
# Verificar la integridad del backup restaurando en una base de datos temporal
set -euo pipefail
BACKUP_FILE="$1"
TEST_DB="restore_test_$(date +%s)"
PG_USER="postgres"
# Crear la base de datos temporal
createdb -U "$PG_USER" "$TEST_DB"
# Intentar la restauración
if pg_restore -U "$PG_USER" -d "$TEST_DB" "$BACKUP_FILE" 2>/dev/null; then
# Verificar los datos
TABLES=$(psql -U "$PG_USER" -At -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'" "$TEST_DB")
ROWS=$(psql -U "$PG_USER" -At -c "SELECT sum(n_live_tup) FROM pg_stat_user_tables" "$TEST_DB")
echo "OK: Restauradas $TABLES tablas con $ROWS filas en total"
else
echo "FALLO: La restauración encontró errores"
fi
# Limpiar
dropdb -U "$PG_USER" "$TEST_DB"# Probar el backup más reciente
sudo -u postgres /usr/local/bin/pg_restore_test.sh /var/backups/postgresql/mydb_latest.dumpSolución de Problemas Comunes
”pg_dump: error: connection to server failed"
# Comprobar que PostgreSQL está en ejecución
sudo systemctl status postgresql
# Verificar que puedes conectarte
psql -U postgres -h localhost -l
# Revisar pg_hba.conf para las reglas de autenticación
sudo cat /etc/postgresql/16/main/pg_hba.conf | grep -v '^#' | grep -v '^$'"pg_restore: error: could not execute query: ERROR: relation already exists”
La base de datos de destino ya tiene objetos. Usa --clean --if-exists para eliminar los objetos existentes antes de restaurar:
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dumpO restaura en una base de datos vacía nueva:
dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb mydb_backup.dumpEl backup es demasiado lento
# Usar volcado en paralelo (solo con formato custom o directorio)
pg_dump -U postgres -Fd -j $(nproc) -f /backup/mydb_dir mydb
# Excluir tablas grandes que se pueden regenerar
pg_dump -U postgres -Fc -T large_cache_table -T session_data mydb > mydb.dump
# Comprimir con menos agresividad para mayor velocidad
pg_dump -U postgres -Fc -Z 1 mydb > mydb_fast.dumpLa restauración es demasiado lenta
# Usar restauración en paralelo (formato custom o directorio)
pg_restore -U postgres -d mydb -j $(nproc) mydb_backup.dump
# Deshabilitar los triggers durante la carga de datos (acelera significativamente)
pg_restore -U postgres -d mydb --disable-triggers mydb_backup.dump
# Aumentar maintenance_work_mem para la sesión de restauración
psql -U postgres -d mydb -c "SET maintenance_work_mem = '1GB';"
pg_restore -U postgres -d mydb mydb_backup.dumpEl archivo .pgpass es ignorado
# Comprobar permisos (deben ser 600)
ls -la ~/.pgpass
# Corregir permisos
chmod 600 ~/.pgpass
# Verificar el formato (sin espacios extra)
cat ~/.pgpass
# Debe ser: hostname:port:database:username:passwordCasos Especiales y Advertencias
Objetos grandes (BLOBs): pg_dump incluye los objetos grandes por defecto, pero pg_restore -t NO los restaura. Usa pg_restore -L con una lista personalizada para incluir objetos grandes en restauraciones selectivas.
Extensiones: pg_dump incluye las sentencias CREATE EXTENSION, pero la restauración falla si la extensión no está instalada en el servidor de destino. Instala las extensiones antes de restaurar.
Propietario y permisos: pg_dump registra la propiedad de los objetos. Si el rol propietario no existe en el servidor de destino, la restauración falla. Usa --no-owner para omitir las asignaciones de propiedad, o crea primero los roles con el volcado de objetos globales.
Acceso concurrente durante el volcado: pg_dump toma una instantánea al inicio y lee de forma consistente desde ella. Las demás transacciones continúan con normalidad. Sin embargo, las operaciones DDL (ALTER TABLE, DROP) que mantienen un AccessExclusiveLock bloquearán el volcado. Programa los backups durante períodos de baja actividad.
Incompatibilidad de codificaciones: Si la base de datos de origen y la de destino usan codificaciones diferentes, puede producirse corrupción de datos. Verifica siempre que las codificaciones coincidan:
psql -U postgres -c "SHOW server_encoding;"Resumen
- pg_dump con formato custom (-Fc) es el método recomendado para backups en producción: comprime los datos, admite restauración paralela y permite recuperación selectiva de tablas
- pg_dumpall captura los objetos globales (roles, tablespaces) que pg_dump omite: vuelca siempre los objetos globales por separado junto con tus backups de bases de datos
- Automatiza con cron y un script de backup que incluya rotación, logging y gestión de errores: nunca dependas de procedimientos de backup manuales
- Prueba tus restauraciones periódicamente restaurando en una base de datos temporal y validando los conteos de filas: un backup que nunca has probado es un backup que podría fallar cuando más lo necesitas
- Usa .pgpass para autenticación sin contraseña en trabajos de cron con permisos
chmod 600: pg_dump se niega a leer archivos de credenciales con permisos abiertos - El volcado y restauración en paralelo (flag
-j) reduce drásticamente el tiempo en bases de datos grandes: usa el formato directorio (-Fd) para el mejor rendimiento paralelo