TL;DR — Resumen Rápido

Estrategias de backup en PostgreSQL: pg_dump, pg_dumpall, pg_basebackup, archivado WAL, PITR y scripts automatizados con rotación y subida a S3.

PostgreSQL es la columna vertebral de innumerables aplicaciones en producción — perder datos sin un plan de restauración probado es catastrófico. Esta guía cubre todas las principales estrategias de backup y restauración en PostgreSQL: backups lógicos con pg_dump y pg_dumpall, backups físicos con pg_basebackup, archivado WAL continuo para recuperación punto en tiempo (PITR), scripts automatizados, cifrado y subida a la nube.

Requisitos Previos

  • PostgreSQL 14 o superior (los comandos funcionan desde la versión 12).
  • El usuario postgres o un rol con privilegios pg_read_all_data / SUPERUSER.
  • Espacio en disco suficiente (al menos 1,5× el tamaño de sus bases de datos).
  • aws-cli o rclone instalado si usa almacenamiento en la nube.
  • gpg instalado para backups cifrados.

Backups Lógicos con pg_dump

pg_dump exporta una única base de datos a SQL o a un formato binario personalizado. Es el método de backup más portable.

Backup Completo de la Base de Datos

# Formato personalizado (recomendado) — comprimido, soporta restauración selectiva
pg_dump -U postgres -Fc -d midb -f /backup/midb_$(date +%Y%m%d).pgdump

# SQL plano — legible pero más grande, restaurar con psql
pg_dump -U postgres -Fp -d midb -f /backup/midb_$(date +%Y%m%d).sql

Backup de Una Sola Tabla

pg_dump -U postgres -Fc -d midb -t public.pedidos -f /backup/pedidos_$(date +%Y%m%d).pgdump

Formato Personalizado vs SQL Plano

CaracterísticaFormato Personalizado (-Fc)SQL Plano (-Fp)
CompresiónIntegrada (zlib)No (use gzip aparte)
Restauración selectivaSí (-t, -n, -T)No
Restauración paralelaSí (pg_restore -j)No
Legible por humanosNoSí
Herramienta de restauraciónpg_restorepsql

Use formato personalizado para backups en producción.

Backups de Clúster con pg_dumpall

pg_dumpall respalda el clúster PostgreSQL completo: todas las bases, roles y tablespaces.

# Backup completo del clúster
pg_dumpall -U postgres -f /backup/cluster_$(date +%Y%m%d).sql

# Solo roles y tablespaces (sin datos)
pg_dumpall -U postgres --globals-only -f /backup/globals_$(date +%Y%m%d).sql

Restauración:

psql -U postgres -f /backup/cluster_20260322.sql

Backups Físicos con pg_basebackup

pg_basebackup crea una copia binaria del directorio de datos de PostgreSQL — la base para PITR y servidores standby.

pg_basebackup -U replicator -h localhost -D /backup/base \
  -Ft -z -P --wal-method=stream

Requisitos:

  • Rol con privilegio REPLICATION: CREATE ROLE replicator REPLICATION LOGIN PASSWORD '...';
  • max_wal_senders >= 2 en postgresql.conf.
  • Entrada en pg_hba.conf: host replication replicator 127.0.0.1/32 md5

Archivado WAL y Recuperación Punto en Tiempo

El archivado WAL combinado con un backup base permite PITR — restaurar a cualquier momento pasado.

Habilitar el Archivado WAL

Edite /etc/postgresql/16/main/postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'
archive_timeout = 300

Para S3:

archive_command = 'aws s3 cp %p s3://mis-backups-pg/wal/%f'

Recargue PostgreSQL:

sudo systemctl reload postgresql

Verificar que el Archivado Funciona

SELECT last_archived_wal, last_archived_time, last_failed_wal
FROM pg_stat_archiver;

Recuperación a un Punto Específico

  1. Detenga PostgreSQL y reemplace el directorio de datos con el backup base.
  2. Cree recovery.signal en el directorio de datos (PostgreSQL 12+).
  3. Configure los parámetros de recuperación en postgresql.conf:
restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-03-22 14:30:00'
recovery_target_action = 'promote'
  1. Inicie PostgreSQL — reproducirá el WAL hasta el momento especificado.

Restauración desde Backups

Restaurar con pg_restore (Formato Personalizado)

createdb -U postgres midb_restaurada
pg_restore -U postgres -d midb_restaurada /backup/midb_20260322.pgdump

# Restauración paralela (mucho más rápida en servidores multi-núcleo)
pg_restore -U postgres -j 4 -d midb_restaurada /backup/midb_20260322.pgdump

# Restaurar solo una tabla
pg_restore -U postgres -d midb_restaurada -t public.pedidos /backup/midb_20260322.pgdump

Restaurar con psql (SQL Plano)

psql -U postgres -d midb_restaurada -f /backup/midb_20260322.sql

Ver el Contenido de un Backup

pg_restore --list /backup/midb_20260322.pgdump | grep TABLE

Scripts de Backup Automatizado

Script Diario con Rotación

#!/bin/bash
# /usr/local/bin/pg-backup.sh

set -euo pipefail

BACKUP_DIR="/backup/postgresql"
DIAS_A_CONSERVAR=7
PG_USER="postgres"
FECHA=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR/daily"

for DB in $(psql -U "$PG_USER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    DB=$(echo "$DB" | xargs)
    pg_dump -U "$PG_USER" -Fc -d "$DB" \
        -f "$BACKUP_DIR/daily/${DB}_${FECHA}.pgdump"
done

pg_dumpall -U "$PG_USER" --globals-only \
    -f "$BACKUP_DIR/daily/globals_${FECHA}.sql"

find "$BACKUP_DIR/daily" -type f -mtime +"$DIAS_A_CONSERVAR" -delete
0 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Seguridad del Backup — Compresión, Cifrado y S3

Compresión con Gzip

pg_dump -U postgres -Fp -d midb | gzip > /backup/midb_$(date +%Y%m%d).sql.gz
gunzip -c /backup/midb_20260322.sql.gz | psql -U postgres -d midb_restaurada

Cifrado con GPG

pg_dump -U postgres -Fc -d midb | \
    gpg --encrypt --recipient admin@ejemplo.com \
    -o /backup/midb_$(date +%Y%m%d).pgdump.gpg

gpg --decrypt /backup/midb_20260322.pgdump.gpg | \
    pg_restore -U postgres -d midb_restaurada

Subida a S3

aws s3 cp /backup/midb_20260322.pgdump \
    s3://mis-backups-pg/daily/ --storage-class STANDARD_IA

aws s3 sync /backup/postgresql/ s3://mis-backups-pg/ --storage-class STANDARD_IA

Comparativa de Métodos de Backup

MétodoVelocidadTamañoRestauración SelectivaSoporte PITRCaso de Uso
pg_dump (personalizado)MediaPequeñoSíNoBackup diario por base
pg_dump (SQL plano)MediaGrandeNoNoBases pequeñas, portabilidad
pg_dumpallLentaGrandeNoNoClúster completo + roles
pg_basebackupRápidaDir. completoNoSí (con WAL)PITR base, standby
Archivado WALContinuoVariableNoSíProtección continua

Escenario Real: Recuperación ante Desastres

Situación: Su servidor PostgreSQL 16 en producción falla a las 15:47 un martes. El último pg_basebackup se ejecutó el domingo a la 01:00. El archivado WAL a S3 ha funcionado continuamente.

Objetivo: Restaurar al estado de las 15:45.

Paso 1 — Aprovisionar un Nuevo Servidor

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

Paso 2 — Restaurar el Backup Base

cd /var/lib/postgresql/16/main
sudo -u postgres tar -xzf /backup/base/20260320/base.tar.gz .
sudo -u postgres tar -xzf /backup/base/20260320/pg_wal.tar.gz pg_wal/

Paso 3 — Configurar la Recuperación

restore_command = 'aws s3 cp s3://mis-backups-pg/wal/%f %p'
recovery_target_time = '2026-03-22 15:45:00'
recovery_target_action = 'promote'
sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl start postgresql
sudo tail -f /var/log/postgresql/postgresql-16-main.log

Paso 4 — Verificar Datos y Reanudar Operaciones

SELECT COUNT(*) FROM pedidos WHERE created_at < '2026-03-22 15:46:00';

Casos Especiales y Errores Comunes

  • Bases de datos grandes (>100 GB): Use pg_dump -j y pg_restore -j para dumps y restauraciones paralelas.
  • Bloqueos durante el backup: pg_dump adquiere un bloqueo ACCESS SHARE. Ejecute backups en ventanas de bajo tráfico.
  • Diferencias de codificación: Cree la base destino con el mismo encoding y locale que el origen.
  • Secuencias: El formato personalizado captura los valores de secuencia al momento del dump.
  • Extensiones: Asegúrese de que las mismas extensiones estén disponibles en el servidor de restauración.
  • Tablespaces personalizados: Deben existir con las rutas correctas antes de ejecutar pg_restore.

Solución de Problemas

ProblemaCausa ProbableSolución
ERROR: permission denied en pg_dumpEl rol carece de privilegios de lecturaOtorgue pg_read_all_data o use el superusuario postgres
ERROR: relation already exists en pg_restoreRestaurando en una base no vacíaElimine y recree la base destino o use el flag --clean
archive_command falla silenciosamenteRuta incorrecta o permisosVerifique pg_stat_archiver.last_failed_wal y pruebe el comando manualmente
PITR bloqueado reproduciendo WALrecovery_target_time en el futuro o zona horaria incorrectaUse marcas de tiempo UTC; verifique show timezone en psql
pg_basebackup: error: could not connectSin entrada en pg_hba.confAgregue host replication replicator 127.0.0.1/32 md5 a pg_hba.conf
Restauración muy lentaRestauración monohilo de un dump grandeUse pg_restore -j N donde N = número de núcleos CPU

Resumen

  • Use pg_dump (formato personalizado) diariamente para backups lógicos por base con restauración selectiva.
  • Use pg_dumpall —globals-only diariamente para capturar roles y tablespaces.
  • Use pg_basebackup semanalmente como base binaria para restauración completa rápida.
  • Habilite el archivado WAL para protección continua y PITR.
  • Cifre los backups con GPG y almacénelos en S3 u otro almacenamiento externo.
  • Pruebe las restauraciones regularmente — un backup no probado no es un backup.
  • Use cron + scripts de rotación para automatizar y eliminar backups antiguos.

Artículos Relacionados