Point-in-Time Recovery y backup avanzado

Tipos de backup

| Tipo | Qué captura | Velocidad restore | Granularidad | |------|-------------|-------------------|--------------| | Lógico (pg_dump, mysqldump) | Sentencias SQL | Lento | Esquema/tabla concreta | | Físico (pg_basebackup) | Ficheros de datos | Rápido | Cluster completo | | PITR (physical + WAL) | Ficheros + WAL continuo | Rápido | Segundo exacto |

Los backups lógicos son portables (funcionan entre versiones mayores) y permiten restore selectivo por tabla. Los físicos son rápidos pero atados a la misma versión. PITR combina físico + WAL para restaurar a un instante concreto.

pg_dump: backup lógico

# Backup de toda la base
pg_dump -h localhost -U postgres -d mi_bd -F c -f mi_bd.dump

# Solo esquema (sin datos)
pg_dump -s -d mi_bd -f esquema.sql

# Solo una tabla
pg_dump -t pedidos -d mi_bd -f pedidos.dump

# Varias bases (pg_dumpall)
pg_dumpall -h localhost -U postgres -f todas.sql

# Restore
pg_restore -h nuevo_host -U postgres -d mi_bd mi_bd.dump

Formato -F c (custom) es el recomendado: comprimido, permite restore paralelo (-j 8) y selectivo por tabla.

Limitaciones

• Lento con bases grandes (exporta + importa SQL).
• Inconsistente en caliente si no usa --serializable-deferrable (toma un snapshot pero puede fallar por conflictos).
• No captura estado preciso: devuelve el estado al inicio del dump, no transaccional cross-database.

Para bases > 100GB, pg_dump no escala bien. Ahí entra PITR.

pg_basebackup: backup físico

Copia todos los ficheros del data directory mientras el primary sigue corriendo:

pg_basebackup -h primary -U replicator \
              -D /backup/base_$(date +%Y%m%d) \
              -F tar -z -P \
              --wal-method=stream

• -F tar: empaqueta en un .tar.
• -z: comprime gzip.
• --wal-method=stream: copia también el WAL necesario durante el backup (consistente).

Velocidad: mucho más rápido que pg_dump (copia bytes, no ejecuta SQL). Pero solo se restaura como cluster completo, no por tabla.

Point-in-Time Recovery: configuración

Paso 1: archive_mode y archive_command

En el primary, postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

Cada WAL terminado se copia a /archive/. Así acumulas todos los cambios desde que el archivado se activó.

Para producción, en lugar de cp usa herramientas con compresión y cifrado:

archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_command = 'wal-g wal-push %p'

Paso 2: backup base periódico

# Cada domingo
pg_basebackup -D /backup/base_semanal -F tar -z --wal-method=stream

O con pgBackRest:

pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=diff backup   # durante la semana

Paso 3: restore a un momento exacto

Supón que a las 14:32 alguien ejecutó DROP TABLE pedidos por error. Quieres restaurar al 14:31.

# 1. Para el servidor
systemctl stop postgresql

# 2. Borra el data directory actual
mv /var/lib/postgresql/data /var/lib/postgresql/data.broken

# 3. Restaura el backup base mas reciente
tar -xzf /backup/base_semanal/base.tar.gz -C /var/lib/postgresql/data
tar -xzf /backup/base_semanal/pg_wal.tar.gz -C /var/lib/postgresql/data/pg_wal

# 4. Configura recovery en postgresql.conf (PG 12+)
cat >> /var/lib/postgresql/data/postgresql.auto.conf <<EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-04-18 14:31:00+02'
recovery_target_action = 'promote'
EOF

# 5. Indica que es recovery
touch /var/lib/postgresql/data/recovery.signal

# 6. Arranca
systemctl start postgresql

# El server replica WAL desde el backup hasta las 14:31 y se promociona.

Parámetros de recovery_target

• recovery_target_time = 'YYYY-MM-DD HH:MM:SS': al momento exacto.
• recovery_target_xid = '12345': justo antes de esa transacción.
• recovery_target_lsn = '0/1A2B3C4D': a un LSN concreto.
• recovery_target_name = 'mi_punto': a un punto nombrado (creado con pg_create_restore_point()).
• recovery_target = 'immediate': se detiene en cuanto alcanza consistencia (útil para test).

recovery_target_action:

• promote: se promociona como primary al alcanzar el target (normal).
• pause: se queda en pausa para inspeccionar, luego SELECT pg_wal_replay_resume().
• shutdown: se para.

pgBackRest: estándar de facto

pgBackRest es una herramienta especializada con ventajas sobre pg_basebackup manual:

• Backups full, diff, incr con política de retención.
• Compresión paralela (lz4, zst).
• Encriptación AES-256.
• Repositorio local o S3/GCS/Azure Blob.
• Verificación automática.

pgbackrest.conf:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=2
compress-type=lz4
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=secreto-largo

[main]
pg1-path=/var/lib/postgresql/data

Uso:

# Inicializar
pgbackrest --stanza=main stanza-create

# Backup completo (semanal)
pgbackrest --stanza=main --type=full backup

# Backup diferencial (diario)
pgbackrest --stanza=main --type=diff backup

# Backup incremental (horario)
pgbackrest --stanza=main --type=incr backup

# Verificar
pgbackrest --stanza=main check

# Restore a momento exacto
pgbackrest --stanza=main \
           --type=time \
           --target='2026-04-18 14:31:00+02' \
           --delta \
           restore

El flag --delta solo copia los ficheros distintos, acelerando el restore enormemente.

WAL-G: backup a cloud

Alternativa a pgBackRest enfocada en almacenamiento en cloud (S3, GCS, Azure):

export WALG_S3_PREFIX=s3://my-bucket/wal-backup
export AWS_REGION=eu-west-1

# Backup base
wal-g backup-push /var/lib/postgresql/data

# archive_command en postgresql.conf
archive_command = 'wal-g wal-push %p'

# Restore
wal-g backup-fetch /var/lib/postgresql/data LATEST
# ... configurar recovery.signal y recovery_target_time ...

Simple, barato (S3 Standard-IA o Glacier), y con deduplicación.

MySQL: Point-in-Time Recovery

El equivalente en MySQL se hace con mysqldump (o Percona XtraBackup para físico) + binlog:

# 1. Backup logico
mysqldump --single-transaction \
          --master-data=2 \
          --all-databases \
          --result-file=/backup/all_$(date +%Y%m%d).sql

# 2. Entre backups, conservar binlogs
# my.cnf:
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7

# 3. Restore a momento exacto
# a) Restaura el dump completo
mysql < /backup/all_20260418.sql

# b) Aplica binlog hasta el momento
mysqlbinlog --stop-datetime='2026-04-18 14:31:00' \
            /var/lib/mysql/mysql-bin.000042 \
            /var/lib/mysql/mysql-bin.000043 \
            | mysql

Para bases grandes, Percona XtraBackup hace backups físicos en caliente:

xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

Verificación periódica

Un backup no verificado es un no-backup. Añade al cron:

# 1. Verificar el backup fisico
pg_verifybackup /backup/base_semanal

# 2. Restaurar en un servidor de pruebas y hacer queries

Un backup mensual debería restaurarse por completo en staging al menos una vez al trimestre.

RTO y RPO

Dos métricas clave:

• RTO (Recovery Time Objective): cuánto tarda en volver operativo. PITR suele ser minutos-horas según tamaño de WAL a replay.
• RPO (Recovery Point Objective): cuántos datos perderás como máximo. Con archivado continuo de WAL, el RPO es prácticamente cero (lo que no haya llegado al archive).

Con replicación síncrona + archivado a cloud geo-distribuido, puedes tener RTO < 5 minutos y RPO = 0 real.

Checklist

• [ ] archive_mode = on y archive_command configurado y verificado.
• [ ] Backups base periódicos (diario o semanal según tamaño).
• [ ] Política de retención clara (ej. 4 semanas).
• [ ] Storage redundante (S3 con versioning, Glacier para retención larga).
• [ ] Cifrado en reposo.
• [ ] Restauración probada trimestralmente en staging.
• [ ] Alerta si archive_command falla (se bloquea el WAL).
• [ ] Documentación del procedimiento de restore.