Migración de Base de Datos con pg_dump y pg_restore

pg_dump y pg_restore son las herramientas oficiales de PostgreSQL para exportar e importar bases de datos de forma fiable y flexible. Dominando estos comandos puedes migrar bases de datos entre servidores, realizar backups programados, hacer exports selectivos de esquemas o tablas específicas, y ejecutar restauraciones paralelas para minimizar el tiempo de inactividad en bases de datos grandes.

Requisitos Previos

PostgreSQL instalado en el servidor origen y destino
Acceso de superusuario o con privilegios suficientes en ambas instancias
Suficiente espacio en disco para el archivo de dump
Herramientas cliente de PostgreSQL (postgresql-client) en el servidor de migración

# Instalar las herramientas cliente de PostgreSQL (Ubuntu/Debian)
apt update && apt install -y postgresql-client

# Para CentOS/Rocky Linux
yum install -y postgresql

# Verificar las versiones disponibles
pg_dump --version
pg_restore --version
psql --version

Formatos de Dump

pg_dump soporta cuatro formatos de salida, cada uno con sus ventajas:

Formato	Flag	Extensión	Ventajas
SQL plano	`-Fp`	`.sql`	Legible, importable con psql
Custom	`-Fc`	`.dump`	Comprimido, restauración selectiva
Directory	`-Fd`	directorio	Paralelizable
Tar	`-Ft`	`.tar`	Compatible, restauración selectiva

# Formato SQL plano (el más simple)
pg_dump -Fp -d mibase > mibase.sql

# Formato custom (recomendado para producción)
pg_dump -Fc -d mibase -f mibase.dump

# Formato directory (para dumps paralelos)
pg_dump -Fd -d mibase -f ./mibase-dump-dir/

# Formato tar
pg_dump -Ft -d mibase -f mibase.tar

Dumps Básicos y Completos

# Variables de entorno para evitar ingresar la contraseña repetidamente
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=mi-contraseña-segura

# Dump completo de una base de datos específica
pg_dump \
  --host=localhost \
  --port=5432 \
  --username=postgres \
  --dbname=mibase \
  --format=custom \
  --verbose \
  --file=mibase_$(date +%Y%m%d_%H%M%S).dump

# Dump solo del esquema (sin datos)
pg_dump \
  --schema-only \
  --format=custom \
  --file=mibase_esquema.dump \
  mibase

# Dump solo de los datos (sin estructura)
pg_dump \
  --data-only \
  --format=custom \
  --file=mibase_datos.dump \
  mibase

# Dump global: roles de usuario y tablespaces (NO incluye datos de tablas)
pg_dumpall \
  --globals-only \
  --file=globals.sql

# Dump completo de todas las bases de datos del servidor
pg_dumpall \
  --host=localhost \
  --username=postgres \
  --file=servidor_completo_$(date +%Y%m%d).sql

Dumps Paralelos para Bases de Datos Grandes

# El formato directory es el único que soporta dumps paralelos
# -j N: número de workers paralelos (recomendado = número de CPUs)
pg_dump \
  --format=directory \
  --jobs=8 \
  --verbose \
  --dbname=mibase-grande \
  --file=./dump-paralelo/

# Monitorear el progreso del dump con --verbose
# Aparecerá una línea por cada tabla que se está volcando

# Comprimir el directorio resultante para archivado
tar -czf mibase-grande_$(date +%Y%m%d).tar.gz ./dump-paralelo/

# Verificar el tamaño del dump
du -sh ./dump-paralelo/
ls -lh ./dump-paralelo/

# Dump paralelo con compresión por tabla (PostgreSQL 16+)
pg_dump \
  --format=directory \
  --jobs=4 \
  --compress=gzip:6 \
  --dbname=mibase-grande \
  --file=./dump-comprimido/

Exports Selectivos por Esquema o Tabla

# Exportar solo un esquema específico
pg_dump \
  --format=custom \
  --schema=public \
  --file=esquema_public.dump \
  mibase

# Exportar múltiples esquemas
pg_dump \
  --format=custom \
  --schema=public \
  --schema=api \
  --schema=reporting \
  --file=esquemas_seleccionados.dump \
  mibase

# Exportar solo tablas específicas
pg_dump \
  --format=custom \
  --table=usuarios \
  --table=pedidos \
  --table=productos \
  --file=tablas_clave.dump \
  mibase

# Excluir tablas específicas del dump
pg_dump \
  --format=custom \
  --exclude-table=logs_audit \
  --exclude-table=sesiones_temporales \
  --file=mibase_sin_logs.dump \
  mibase

# Exportar solo los datos de tablas que cumplen una condición
pg_dump \
  --format=custom \
  --table=pedidos \
  --where="fecha_creacion >= '2024-01-01'" \
  --file=pedidos_2024.dump \
  mibase

# Ver el contenido de un dump custom sin restaurarlo
pg_restore --list mibase.dump | head -50

Restauración con pg_restore

# Crear la base de datos destino antes de restaurar
createdb \
  --host=servidor-destino \
  --username=postgres \
  nueva-base

# Restauración básica desde formato custom
pg_restore \
  --host=servidor-destino \
  --port=5432 \
  --username=postgres \
  --dbname=nueva-base \
  --verbose \
  mibase.dump

# Restauración paralela (solo con formato directory o custom)
pg_restore \
  --host=servidor-destino \
  --username=postgres \
  --dbname=nueva-base \
  --jobs=8 \
  --verbose \
  mibase.dump

# Restaurar solo el esquema (sin datos)
pg_restore \
  --schema-only \
  --dbname=nueva-base \
  mibase.dump

# Restaurar una tabla específica de un dump completo
pg_restore \
  --dbname=nueva-base \
  --table=usuarios \
  mibase.dump

# Restaurar en una base de datos existente, omitiendo errores de objetos duplicados
pg_restore \
  --dbname=nueva-base \
  --if-exists \
  --clean \
  --no-owner \
  --no-privileges \
  mibase.dump

# Restaurar desde SQL plano usando psql
psql \
  --host=servidor-destino \
  --username=postgres \
  --dbname=nueva-base \
  --file=mibase.sql

Migración entre Versiones de PostgreSQL

# Escenario: migrar de PostgreSQL 14 a PostgreSQL 16

# Paso 1: En el servidor ORIGEN (PG 14)
# Exportar los roles y configuración global
pg_dumpall \
  --globals-only \
  --host=postgres-14.miempresa.local \
  --username=postgres \
  > globals_pg14.sql

# Exportar la base de datos en formato custom
pg_dump \
  --host=postgres-14.miempresa.local \
  --username=postgres \
  --format=directory \
  --jobs=8 \
  --no-privileges \
  --dbname=mibase-produccion \
  --file=./dump-migracion/

# Paso 2: En el servidor DESTINO (PG 16)
# Importar roles y usuarios
psql \
  --host=postgres-16.miempresa.local \
  --username=postgres \
  --file=globals_pg14.sql

# Crear la base de datos
createdb \
  --host=postgres-16.miempresa.local \
  --username=postgres \
  --owner=miapp_user \
  mibase-produccion

# Restaurar los datos
pg_restore \
  --host=postgres-16.miempresa.local \
  --username=postgres \
  --dbname=mibase-produccion \
  --jobs=8 \
  --no-owner \
  --verbose \
  ./dump-migracion/

# Paso 3: Verificar la migración
psql --host=postgres-16.miempresa.local --username=postgres \
  --dbname=mibase-produccion \
  --command="SELECT schemaname, tablename, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;"

Estrategias para Bases de Datos Grandes

# Streaming directo entre servidores sin archivo intermedio
pg_dump \
  --host=origen.miempresa.com \
  --username=postgres \
  --format=custom \
  mibase | pg_restore \
  --host=destino.miempresa.com \
  --username=postgres \
  --dbname=mibase \
  --jobs=4

# Dump comprimido para reducir espacio en disco
pg_dump \
  --format=custom \
  --compress=9 \
  mibase \
  --file=mibase_comprimido.dump

# Verificar la integridad del dump custom
pg_restore --list mibase_comprimido.dump > /dev/null && echo "Dump OK"

# Dump incremental usando logical replication (para bases de datos muy grandes)
# Crear una publicación en el servidor origen
psql -c "CREATE PUBLICATION migracion_pub FOR ALL TABLES;" mibase

# En el servidor destino, crear la suscripción (replica los cambios en tiempo real)
psql -c "CREATE SUBSCRIPTION migracion_sub 
  CONNECTION 'host=origen port=5432 dbname=mibase user=replicador password=pass'
  PUBLICATION migracion_pub;" nueva-base

# Monitorear el progreso de la replicación
psql -c "SELECT * FROM pg_stat_subscription;" nueva-base

# Una vez sincronizado, redirigir la aplicación al nuevo servidor
# y luego eliminar la suscripción
psql -c "DROP SUBSCRIPTION migracion_sub;" nueva-base

Solución de Problemas

Error de permisos durante el dump:

# Verificar los privilegios del usuario
psql -c "\du" 

# Para acceder a tablas de otros esquemas
GRANT USAGE ON SCHEMA schema_privado TO mi_usuario;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_privado TO mi_usuario;

Dump muy lento:

# Verificar el tamaño de las tablas para identificar las más grandes
psql -d mibase -c "
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;"

# Usar dumps paralelos y streaming para reducir el tiempo
pg_dump -Fd --jobs=16 mibase -f ./dump-dir/

Error durante la restauración: tabla ya existe:

# Usar --clean para eliminar objetos antes de recrearlos
pg_restore \
  --clean \
  --if-exists \
  --dbname=nueva-base \
  mibase.dump

# O usar --create para crear la base de datos automáticamente
pg_restore \
  --create \
  --dbname=postgres \
  mibase.dump

Conclusión

pg_dump y pg_restore son herramientas extremadamente versátiles que cubren desde el backup diario más sencillo hasta la migración de terabytes de datos entre versiones de PostgreSQL. El formato custom con dumps paralelos es la combinación óptima para entornos de producción, ya que permite restauraciones selectivas y paralelas que minimizan el tiempo de indisponibilidad durante las migraciones. Complementar estas herramientas con logical replication para las fases finales de una migración en caliente es la estrategia más efectiva para bases de datos de misión crítica.

Database Migration with pg_dump and pg_restore

On this page