Respaldos con pg_dump

Los respaldos son el seguro de vida de cualquier proyecto. PostgreSQL trae herramientas excelentes: pg_dump para respaldar una base, pg_restore para recuperarla con flexibilidad, pg_dumpall para todo el servidor y \copy para mover datos en CSV. En esta lección aprenderás a usarlas, a automatizarlas con scripts en Linux y Windows, y conocerás la recuperación a un punto en el tiempo (PITR).

Concepto

Herramienta	Respalda	Formato	Uso típico
pg_dump	Una base de datos	SQL plano o formatos propios	Respaldo diario, migraciones
pg_dumpall	Todo el cluster (todas las bases + roles)	SQL plano	Migrar un servidor completo
pg_restore	Restaura formatos -Fc, -Fd, -Ft	—	Restauración selectiva y en paralelo
pg_basebackup	Copia física del cluster	Archivos	Réplicas y PITR
\copy / COPY	Una tabla o consulta	CSV / texto	Importar y exportar datos

pg_dump genera un respaldo consistente sin bloquear a la aplicación: toma una "foto" de la base al inicio (gracias a MVCC) y la exporta mientras los usuarios siguen trabajando.

Formatos de pg_dump

Formato	Opción	Archivo	Restaurar con	Ventajas
Plain (SQL)	-Fp (por defecto)	.sql	psql	Legible, editable, portable
Custom	-Fc	.dump	pg_restore	Comprimido, restauración selectiva. Recomendado
Directory	-Fd	carpeta	pg_restore	Respaldo y restauración en paralelo (-j)
Tar	-Ft	.tar	pg_restore	Poco usado

Recomendación

Usa el formato custom (-Fc) para respaldos rutinarios: ocupa poco, puedes restaurar una sola tabla y pg_restore es flexible. Usa plain cuando quieras leer o editar el SQL (por ejemplo, para compartir el esquema).

Respaldar

# Formato custom (recomendado)
pg_dump -h localhost -U tienda_owner -d tienda -Fc -f tienda_$(date +%F).dump

# SQL plano comprimido
pg_dump -h localhost -U tienda_owner -d tienda | gzip > tienda_$(date +%F).sql.gz

# Solo la estructura (esquema)
pg_dump -d tienda --schema-only -f tienda_esquema.sql

# Solo los datos
pg_dump -d tienda --data-only -Fc -f tienda_datos.dump

# Solo algunas tablas o esquemas
pg_dump -d tienda -t productos -t categorias -Fc -f catalogo.dump
pg_dump -d tienda -n ventas -Fc -f esquema_ventas.dump

# Excluir tablas pesadas (logs) o solo sus datos
pg_dump -d tienda --exclude-table-data='logs*' -Fc -f tienda_sin_logs.dump

# Respaldo en paralelo (formato directorio, 4 procesos) para bases grandes
pg_dump -d tienda -Fd -j 4 -f tienda_dir/

Opciones útiles para migrar entre servidores o servicios:

Opción	Para qué
--no-owner	No incluir ALTER ... OWNER TO: los objetos quedan a nombre de quien restaura
--no-privileges (-x)	No incluir GRANT/REVOKE
--clean --if-exists	Incluir DROP ... IF EXISTS antes de crear (formato plain)
--create	Incluir CREATE DATABASE

# Respaldo "portable" para llevar a Supabase, Neon u otro servidor con roles distintos
pg_dump "postgresql://usuario:clave@localhost:5432/tienda" \
  -Fc --no-owner --no-privileges -f tienda_portable.dump

Versiones de pg_dump

Usa un pg_dump de la misma versión o más nueva que el servidor que respaldas. Un pg_dump 14 contra un servidor 17 falla con "server version mismatch". Con Docker puedes usar la versión exacta: docker run --rm postgres:17 pg_dump ....

Restaurar

Desde formato custom o directorio: pg_restore

# 1. Crear la base vacía
createdb -h localhost -U postgres -O tienda_owner tienda_restaurada

# 2. Restaurar
pg_restore -h localhost -U tienda_owner -d tienda_restaurada tienda_2024-07-01.dump

# En paralelo (más rápido en bases grandes)
pg_restore -d tienda_restaurada -j 4 tienda_2024-07-01.dump

# Borrar y recrear objetos existentes
pg_restore -d tienda --clean --if-exists tienda_2024-07-01.dump

# Restaurar una sola tabla
pg_restore -d tienda -t productos tienda_2024-07-01.dump

# Sin dueños ni permisos (destino con otros roles)
pg_restore -d tienda --no-owner --no-privileges tienda_portable.dump

# Detenerse en el primer error (por defecto continúa y reporta al final)
pg_restore -d tienda_restaurada --exit-on-error tienda_2024-07-01.dump

Ver el contenido de un respaldo sin restaurarlo:

pg_restore -l tienda_2024-07-01.dump | head -20
# Generar el SQL que ejecutaría
pg_restore -f - --schema-only tienda_2024-07-01.dump | less

Restauración selectiva avanzada: genera la lista, comenta con ; lo que no quieras y restaura con -L:

pg_restore -l tienda.dump > lista.txt
# edita lista.txt...
pg_restore -d tienda_restaurada -L lista.txt tienda.dump

Desde SQL plano: psql

psql -h localhost -U tienda_owner -d tienda_restaurada -f tienda.sql -v ON_ERROR_STOP=1

# Comprimido
gunzip -c tienda_2024-07-01.sql.gz | psql -d tienda_restaurada -v ON_ERROR_STOP=1

# Todo en una transacción (si algo falla, no queda nada a medias)
psql -d tienda_restaurada -1 -f tienda.sql

Restaurar sobre la base de producción

Antes de restaurar sobre una base en uso, restaura primero en una base nueva (tienda_restaurada), verifica que esté bien y luego cambia la aplicación o renombra las bases. Un --clean sobre producción con el archivo equivocado es irreversible.

Respaldar todo el servidor: pg_dumpall

pg_dump no incluye los roles ni sus contraseñas (son globales del cluster). Para eso:

# Solo roles y tablespaces (complemento ideal de los pg_dump por base)
pg_dumpall -h localhost -U postgres --globals-only -f globales.sql

# Todo el cluster en SQL plano
pg_dumpall -U postgres | gzip > cluster_completo.sql.gz

# Restaurar
psql -U postgres -f globales.sql

Estrategia habitual: pg_dumpall --globals-only + un pg_dump -Fc por cada base.

Importar y exportar CSV con \copy

COPY (comando SQL) lee y escribe archivos en el servidor y requiere privilegios especiales. \copy (metacomando de psql) hace lo mismo pero con archivos de tu computadora, sin permisos especiales. Para el trabajo diario, usa \copy.

-- Exportar una tabla a CSV con encabezados
\copy productos TO 'productos.csv' WITH (FORMAT csv, HEADER)

-- Exportar el resultado de una consulta
\copy (SELECT id, nombre, precio FROM productos WHERE precio < 500 ORDER BY precio) TO 'baratos.csv' WITH (FORMAT csv, HEADER)

-- Importar un CSV (las columnas en el orden del archivo)
\copy productos (nombre, categoria_id, precio, stock) FROM 'nuevos.csv' WITH (FORMAT csv, HEADER)

-- CSV con punto y coma (Excel en español)
\copy productos (nombre, precio) FROM 'excel.csv' WITH (FORMAT csv, HEADER, DELIMITER ';', ENCODING 'UTF8')

Desde la terminal, sin entrar a psql:

psql -d tienda -c "\copy (SELECT * FROM v_ventas) TO 'ventas.csv' WITH (FORMAT csv, HEADER)"

COPY es la forma más rápida de cargar datos masivos en PostgreSQL: cientos de miles de filas por segundo, muchísimo más que miles de INSERT.

Importación segura

Igual que en MySQL: carga el CSV en una tabla de staging (CREATE TABLE importacion.productos_csv (...) con todas las columnas como TEXT), limpia y valida con SQL, y luego inserta en la tabla real con INSERT ... SELECT. Si cargaste ids explícitos, recuerda sincronizar la secuencia.

Contraseñas sin interacción

Los scripts automáticos no pueden escribir la contraseña. Opciones:

# 1. Archivo ~/.pgpass (recomendado)
echo "localhost:5432:tienda:tienda_backup:CLAVE_SEGURA" >> ~/.pgpass
chmod 600 ~/.pgpass

# 2. Variable de entorno (útil en contenedores; no la dejes en el historial)
export PGPASSWORD='CLAVE_SEGURA'

# 3. Cadena de conexión en una variable de entorno cargada desde un archivo protegido
pg_dump "$DATABASE_URL" -Fc -f respaldo.dump

Rol mínimo para respaldos:

CREATE ROLE tienda_backup WITH LOGIN PASSWORD 'CLAVE_SEGURA';
GRANT pg_read_all_data TO tienda_backup;     -- PostgreSQL 14+: lectura de todas las tablas
GRANT CONNECT ON DATABASE tienda TO tienda_backup;

Automatizar en Linux (bash + cron)

/usr/local/bin/respaldo-pg.sh:

#!/usr/bin/env bash
# Respaldo diario de PostgreSQL con rotación y verificación
set -euo pipefail

export PGHOST=localhost
export PGUSER=tienda_backup          # contraseña en ~/.pgpass
BASES=("tienda")
DESTINO="/var/backups/postgres"
DIAS_CONSERVAR=14
FECHA=$(date +%F_%H%M)

mkdir -p "$DESTINO"

# Roles y configuración global (requiere un rol con permisos suficientes;
# si usas un rol limitado, quita esta línea y respáldalos aparte)
# pg_dumpall --globals-only -f "$DESTINO/globales_${FECHA}.sql"

for BD in "${BASES[@]}"; do
  ARCHIVO="$DESTINO/${BD}_${FECHA}.dump"
  echo "[$(date '+%F %T')] Respaldando $BD..."
  pg_dump -d "$BD" -Fc -Z 6 -f "$ARCHIVO"

  # Verificar que pg_restore pueda leer el archivo
  if ! pg_restore -l "$ARCHIVO" > /dev/null; then
    echo "ERROR: respaldo de $BD ilegible" >&2
    exit 1
  fi
  echo "  OK -> $ARCHIVO ($(du -h "$ARCHIVO" | cut -f1))"
done

# Rotación
find "$DESTINO" -name "*.dump" -mtime +"$DIAS_CONSERVAR" -delete

# Copia fuera del servidor (configura rclone antes)
# rclone copy "$DESTINO" remoto-b2:respaldos-postgres --max-age 24h

echo "[$(date '+%F %T')] Listo."

sudo chmod +x /usr/local/bin/respaldo-pg.sh
/usr/local/bin/respaldo-pg.sh     # pruébalo a mano

crontab -e

# Todos los días a las 3:30 a. m.
30 3 * * * /usr/local/bin/respaldo-pg.sh >> /var/log/respaldo-pg.log 2>&1

Con Docker, el mismo comando funciona desde el host:

docker exec pg-tienda pg_dump -U tienda -d tienda -Fc > /var/backups/postgres/tienda_$(date +%F).dump

Automatizar en Windows (PowerShell)

C:\respaldos\respaldo-pg.ps1:

$ErrorActionPreference = "Stop"

$pgBin   = "C:\Program Files\PostgreSQL\17\bin"
$destino = "C:\respaldos\postgres"
$bases   = @("tienda")
$dias    = 14
$fecha   = Get-Date -Format "yyyy-MM-dd_HHmm"

# La contraseña se lee de %APPDATA%\postgresql\pgpass.conf
$env:PGHOST = "localhost"
$env:PGUSER = "tienda_backup"

New-Item -ItemType Directory -Force -Path $destino | Out-Null

foreach ($bd in $bases) {
    $archivo = Join-Path $destino "$($bd)_$fecha.dump"
    & "$pgBin\pg_dump.exe" -d $bd -Fc -f $archivo
    if ($LASTEXITCODE -ne 0) { throw "pg_dump falló para $bd" }

    & "$pgBin\pg_restore.exe" -l $archivo | Out-Null
    if ($LASTEXITCODE -ne 0) { throw "Respaldo ilegible: $archivo" }
    Write-Output "OK: $archivo"
}

Get-ChildItem $destino -Filter *.dump |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$dias) } |
    Remove-Item

Prográmalo igual que en MySQL con Register-ScheduledTask.

Probar las restauraciones

Un respaldo no probado es solo una esperanza. Automatiza una prueba periódica:

#!/usr/bin/env bash
set -euo pipefail
ULTIMO=$(ls -t /var/backups/postgres/tienda_*.dump | head -1)

dropdb --if-exists tienda_prueba
createdb tienda_prueba
pg_restore -d tienda_prueba --no-owner --exit-on-error "$ULTIMO"

psql -d tienda_prueba -At -c "SELECT 'pedidos: ' || COUNT(*) FROM pedidos;"
psql -d tienda_prueba -At -c "SELECT 'último pedido: ' || MAX(fecha) FROM pedidos;"
dropdb tienda_prueba

Si el último pedido es de hace días cuando debería ser de ayer, tu respaldo tiene un problema.

PITR: recuperación a un punto en el tiempo

pg_dump diario significa que, en el peor caso, pierdes un día de datos. Para sistemas críticos (pagos, sistemas médicos) existe PITR (Point-In-Time Recovery):

pg_basebackup (domingo)  +  archivos WAL continuos  =  restaurar a cualquier segundo
     │                         │││││││││││││││
     ▼                         ▼▼▼▼▼▼▼▼▼▼▼▼▼▼▼
  [copia física] ─────────── [registro de cada cambio] ──► "Restaura al martes 14:32:10,
                                                            justo antes del DELETE erróneo"

1. Activar el archivado de WAL (wal_level = replica, archive_mode = on, archive_command).
2. Tomar copias base periódicas con pg_basebackup.
3. Para recuperar: restaurar la copia base y reproducir los WAL hasta recovery_target_time.

Configurarlo a mano es delicado; herramientas como pgBackRest, Barman o WAL-G lo simplifican. Y los servicios administrados (Supabase Pro, Neon, RDS) lo incluyen: Neon, por ejemplo, permite crear una rama de la base "como estaba hace 2 horas".

Ejercicios

1. Respalda tu base tienda en formato custom y restáurala en tienda_copia.
2. Del respaldo anterior, restaura solo la tabla clientes en una base vacía solo_clientes (con su estructura).
3. Exporta a CSV los pedidos de junio de 2024 con el nombre del cliente y su total.
4. Crea una tabla categorias_import (nombre TEXT) e importa un CSV con tres categorías.
5. ¿Qué no incluye pg_dump y cómo lo respaldas?

Ver solución

# 1
pg_dump -d tienda -Fc -f tienda.dump
createdb tienda_copia
pg_restore -d tienda_copia tienda.dump

# 2
createdb solo_clientes
pg_restore -d solo_clientes -t clientes tienda.dump

-- 3 (dentro de psql conectado a tienda; \copy debe ir en UNA sola línea)
\copy (SELECT p.id, p.fecha, c.nombre, SUM(d.cantidad * d.precio_unitario) AS total FROM pedidos p JOIN clientes c ON c.id = p.cliente_id JOIN detalle_pedido d ON d.pedido_id = p.id WHERE p.fecha >= '2024-06-01' AND p.fecha < '2024-07-01' GROUP BY p.id, p.fecha, c.nombre ORDER BY p.fecha) TO 'pedidos_junio.csv' WITH (FORMAT csv, HEADER)

-- 4   (categorias.csv:  nombre / Juguetes / Mascotas / Jardín)
CREATE TABLE categorias_import (nombre TEXT NOT NULL);
\copy categorias_import (nombre) FROM 'categorias.csv' WITH (FORMAT csv, HEADER)
SELECT * FROM categorias_import;

5. Los objetos globales del cluster: roles (usuarios y contraseñas) y tablespaces. Se respaldan con pg_dumpall --globals-only.

Resumen

• pg_dump respalda una base de forma consistente sin bloquearla; usa el formato custom (-Fc).
• pg_restore restaura formatos custom/directorio: en paralelo (-j), selectivo (-t), sin dueños (--no-owner).
• pg_dumpall --globals-only respalda los roles, que pg_dump no incluye.
• \copy importa y exporta CSV desde tu computadora; COPY es la vía más rápida para cargas masivas.
• Automatiza con scripts + cron o el Programador de tareas, guarda copias fuera del servidor y prueba las restauraciones.
• Para pérdidas mínimas de datos, PITR con archivado de WAL (o un servicio administrado que lo incluya).