Recuperación a un punto en el tiempo en PostgreSQL: guía de producción

Un pg_dump nocturno solo te devuelve a anoche. Si alguien ejecuta una migración errónea a las 14:31, lo que quieres es la base de datos como estaba a las 14:30. Eso hace la recuperación a un punto en el tiempo en PostgreSQL (PITR). Esta guía explica cómo construirla como sistema: la arquitectura, un archivado que reporta los fallos con honestidad, un repositorio de backups difícil de destruir, una ruta de restauración que ya hayas practicado y un monitoreo que te avise antes de que necesites restaurar. Para la parte de negocio (la regla 3-2-1, RPO, RTO y quién es responsable del plan), lee nuestra guía del plan de respaldo de datos para pequeñas empresas. Este artículo cubre la ingeniería.

Cómo funciona la recuperación a un punto en el tiempo en PostgreSQL

PITR combina dos cosas: un backup base físico del directorio de datos y un archivo continuo de los segmentos del write-ahead log (WAL) generados después. Para recuperar, restauras el backup base y reproduces el WAL archivado hasta llegar al objetivo que elijas: un timestamp, un ID de transacción, un LSN o un punto de restauración con nombre. La documentación oficial, Continuous Archiving and Point-in-Time Recovery, explica cómo funciona. Léela una vez de principio a fin antes de elegir una herramienta.

Tres hechos de esa documentación definen todo el diseño:

• Para activar el archivado, pones wal_level en replica o superior, archive_mode en on y configuras un comando de archivado.
• El comando de archivado solo se ejecuta sobre segmentos de WAL completos. Una transacción confirmada hace un minuto puede seguir en un segmento que todavía no se ha archivado.
• En casos raros, PostgreSQL puede intentar archivar un archivo WAL que ya había archivado, así que tu archivador debe manejar los duplicados de forma segura.

Elegir un enfoque: los trade-offs

• Dumps lógicos (pg_dump): son portables entre versiones mayores y permiten restaurar una tabla a la vez con facilidad. Pero son solo fotos: tu punto de recuperación es el último dump, y en bases grandes tanto hacer el dump como restaurarlo es lento. Consérvalos además de PITR, no en su lugar.
• Réplicas por streaming: te dan failover rápido, pero una réplica no es un backup. Un DROP TABLE llega a la réplica en instantes. Una réplica con retraso (recovery_min_apply_delay) ayuda, pero no reemplaza un archivo fuera del servidor.
• PITR de bases de datos gestionadas (RDS, Cloud SQL, Azure y otras): es la opción que menos trabajo da. Aun así, revisa cuánto tiempo guarda los backups el proveedor, si sobreviven cuando se borra la instancia o la cuenta y si puedes exportarlos a otro lugar.
• PITR autogestionado con una herramienta dedicada: pgBackRest, WAL-G y Barman se encargan de la transferencia en paralelo, la compresión, el cifrado, la retención y la verificación. Salvo que tengas una restricción poco común, elige una de ellas en lugar de escribir tu propio script de archive_command.

El resto de esta guía muestra la mecánica básica, para que entiendas qué hacen esas herramientas y puedas evaluarlas.

Arquitectura de referencia

1. Base de datos primaria con el archivado de WAL activado.
2. Archivador: el comando o la biblioteca de archivado, que corre en el servidor de la base de datos.
3. Repositorio: almacenamiento de objetos en una cuenta o proyecto separado. Las credenciales del servidor de base de datos pueden escribir en él, pero no borrar.
4. Tarea de backup base: una tarea programada, normalmente ejecutada desde un servidor de backup en lugar de la primaria.
5. Worker de restauración: una máquina o contenedor desechable que reconstruye la base desde el repositorio de forma programada.
6. Monitor: revisa el retraso del archivado, los fallos de archivado, la antigüedad del backup base y el resultado de la última prueba de restauración.

Paso 1: un archivado de WAL que reporta los fallos con honestidad

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/archive_wal.sh %p %f'
archive_timeout = 60   # fuerza el cambio de segmento en bases con poca actividad

PostgreSQL interpreta un código de salida cero como "archivado". Si el script devuelve 0 antes de que los datos queden guardados de forma duradera en el repositorio, tienes un hueco irrecuperable y nada te avisa. La lógica central, en pseudo-bash:

#!/usr/bin/env bash
# archive_wal.sh %p %f  (pseudocódigo: put_object/head_sha256 envuelven el CLI de tu almacenamiento)
set -euo pipefail
src="$1"; name="$2"
key="pg-main/wal/$name"
local_sum=$(sha256sum "$src" | cut -d' ' -f1)

if remote_sum=$(head_sha256 "$key" 2>/dev/null); then
  # Ya archivado: éxito solo si el contenido es idéntico
  [ "$remote_sum" = "$local_sum" ] && exit 0
  echo "refusing to overwrite $key with different content" >&2
  exit 1
fi

encrypt "$src" | put_object "$key" --checksum-sha256 "$local_sum"
[ "$(head_sha256 "$key")" = "$local_sum" ] || exit 1

Notas de diseño:

• Los duplicados son seguros; las sobrescrituras, no. Un archivo idéntico que ya está en el archivo cuenta como éxito. Un archivo distinto con el mismo nombre es un incidente, no algo que se sobrescribe en silencio.
• El RPO depende del cambio de segmento. Como solo se archivan segmentos completos, una base con poca actividad puede mantener un segmento parcial abierto mucho tiempo. archive_timeout limita esa ventana, y puedes forzar el cambio con pg_switch_wal(). Si necesitas un RPO cercano a cero, transmite el WAL de forma continua (pg_receivewal o el modo asíncrono/streaming de tu herramienta) en lugar de acortar el timeout.
• Un archivador que falla llena el disco. Los segmentos de WAL sin archivar se quedan en pg_wal. Por eso una credencial rota puede terminar en un disco lleno en la primaria. Alerta sobre esto mucho antes de que ocurra.

Paso 2: backups base y retención

dir=/backups/base/$(date -u +%Y%m%dT%H%M%SZ)
pg_basebackup -h primary -U backup -D "$dir" -Fp -X stream -c fast
pg_verifybackup "$dir"          # verifica los archivos contra el manifiesto del backup
encrypt_and_upload "$dir"        # después elimina la copia local

La documentación indica que para PITR hay que hacer backups base a nivel de sistema de archivos, no pg_dump, y que no hace falta detener la base de datos para hacerlos. El trade-off principal es con qué frecuencia los haces frente a cuánto tarda una restauración. La recuperación reproduce cada segmento de WAL entre el backup base y el objetivo. En una base con mucha escritura, un backup base de hace una semana puede significar horas de replay. Haz backups completos con más frecuencia, o usa backups incrementales (pgBackRest y WAL-G los soportan, y las versiones recientes de PostgreSQL incluyen backup incremental nativo), hasta que tu tiempo de restauración medido quepa en tu RTO.

Regla de retención: nunca borres WAL más reciente que tu backup base más antiguo. Si guardas backups base durante 14 días, debes conservar todo el WAL desde el más antiguo en adelante, o ese backup no se podrá recuperar más allá de su punto final. Las herramientas dedicadas aplican esta regla por ti. Las reglas de ciclo de vida escritas a mano normalmente no.

Paso 3: haz que el repositorio sea difícil de destruir

• Limita el radio de impacto. Pon el repositorio en otra cuenta o proyecto de la nube. Si comprometen el servidor de la base de datos, el atacante solo debería obtener credenciales que puedan subir archivos, no borrarlos ni cambiar las reglas de ciclo de vida.
• Inmutabilidad. La retención de escritura única a nivel de objeto, como Amazon S3 Object Lock o sus equivalentes en otros proveedores, impide el borrado hasta que termina el periodo de retención. Configura ese periodo para que dure al menos lo mismo que tu ventana de retención de backups.
• Cifrado del lado del cliente. Cifra antes de subir y guarda la llave fuera de la cuenta del repositorio. Comprueba que el worker de restauración puede obtener la llave sin acceso a producción.
• Una segunda copia. Replica el repositorio a otra región o a otro proveedor. Es la parte "1 fuera del sitio" de la regla 3-2-1, aplicada al WAL.

Paso 4: la ruta de restauración

Desde PostgreSQL 12, la configuración de recuperación va en postgresql.conf (o postgresql.auto.conf), y la recuperación se activa creando un archivo recovery.signal en el directorio de datos. Consulta la referencia de configuración del WAL.

# después de restaurar el backup base en $PGDATA (propiedad de postgres, no de root)
restore_command = '/usr/local/bin/fetch_wal.sh %f %p'
recovery_target_time = '2025-03-14 14:30:00+00'   # objetivo de ejemplo
recovery_target_action = 'pause'

• Define un solo objetivo de recuperación. Las versiones recientes dan error si defines más de uno.
• Usa pause en las restauraciones por incidente. Ejecuta consultas de solo lectura para confirmar que el cambio erróneo no está. Si necesitas avanzar más, define un objetivo posterior y reinicia; cuando el estado sea el correcto, promueve con pg_promote().
• Escribe siempre los timestamps con zona horaria explícita. Convertir "14:30 hora local" con prisa durante un incidente es una fuente habitual de errores.
• Restaura en una instancia nueva. No sobrescribas la primaria dañada, que quizá aún necesites para el análisis forense o para copiar filas que la restauración no incluye.
• Corrige el propietario y los permisos después de copiar los archivos. La documentación señala que restaurar los archivos como root es un error común.

Si sabes que viene una operación arriesgada, como una migración grande o una importación masiva, crea antes un punto con nombre con SELECT pg_create_restore_point('before_migration_42'); y apunta a él con recovery_target_name.

Paso 5: automatiza las pruebas de restauración

Hasta que hayas restaurado un backup, no sabes si funciona. Ejecuta esto de forma programada en el worker de restauración:

start=$(date +%s)
base=$(latest_base_backup)
fetch_and_decrypt "$base" /restore/data
chown -R postgres:postgres /restore/data && chmod 700 /restore/data
touch /restore/data/recovery.signal
cat >> /restore/data/postgresql.auto.conf <<'EOF'
restore_command = '/usr/local/bin/fetch_wal.sh %f %p'
recovery_target_action = 'promote'
EOF
sudo -u postgres pg_ctl -D /restore/data -w -t 7200 start

# Verificaciones de negocio, no solo "arrancó"
psql -U postgres -d app -Atc "SELECT max(created_at) FROM orders"   # frescura
psql -U postgres -d app -Atc "SELECT count(*) FROM customers"        # sanidad
report_metric restore_seconds $(( $(date +%s) - start ))
report_metric restore_ok 1

Sin objetivo, la recuperación reproduce el WAL hasta el final del archivo. La diferencia entre max(created_at) y el inicio de la prueba es tu RPO tal como ocurrió en realidad. El tiempo transcurrido es tu RTO real. Guarda ambos valores a lo largo del tiempo. Una prueba que no se ejecutó es un fallo, así que alerta cuando la última ejecución exitosa sea demasiado antigua, no solo cuando una ejecución falle.

Un monitoreo que te avisa antes de que necesites restaurar

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_failed_wal,
       last_failed_time,
       now() - last_archived_time AS since_last_archive
FROM pg_stat_archiver;

Alerta sobre esto:

• since_last_archive por encima de varias veces archive_timeout.
• last_failed_time más reciente que last_archived_time, lo que significa que el archivador está fallando en este momento.
• Uso de disco de pg_wal que crece sin parar.
• Antigüedad del backup base verificado más reciente.
• Antigüedad y resultado de la última prueba de restauración.

Errores comunes

• Tratar una réplica o un snapshot de disco como el backup.
• Un comando de archivado que devuelve 0 cuando la subida falla, por ejemplo un pipeline sin pipefail.
• Reglas de ciclo de vida que borran WAL que todavía necesita el backup base más antiguo.
• Credenciales del servidor de base de datos que pueden borrar el repositorio.
• Llaves de cifrado guardadas solo en el servidor que intentas recuperar.
• No medir nunca el tiempo de replay, de modo que el RTO es una suposición.
• Runbooks de restauración escritos una vez y nunca ejecutados.

Checklist

• Archivado activado; el comando de archivado maneja los duplicados de forma segura y verifica las subidas.
• archive_timeout o streaming de WAL configurado según tu RPO.
• Backups base verificados con pg_verifybackup o con el comando de verificación de tu herramienta.
• La retención de WAL cubre el backup base más antiguo.
• Repositorio en una cuenta separada, con credenciales de solo escritura, object lock y una segunda región.
• Cifrado del lado del cliente; llave guardada y probada fuera de producción.
• Restauración automática programada con verificaciones de negocio y RTO/RPO registrados.
• Alertas sobre retraso del archivado, fallos de archivado, crecimiento de pg_wal, antigüedad de los backups y antigüedad de la última prueba de restauración.

Si tu producto corre sobre PostgreSQL y nadie es responsable de este pipeline, es un hueco que vale la pena cerrar. Es el tipo de infraestructura que diseñamos y construimos como parte del desarrollo de software a la medida.

Preguntas frecuentes

¿Se puede hacer PITR en una base de datos gestionada?

Normalmente sí, y suele activarse con un ajuste de retención. Revisa hasta qué punto del pasado puedes restaurar, si los backups sobreviven cuando se borra la instancia y si puedes exportarlos a un almacenamiento que controles.

¿Debo seguir haciendo backups con pg_dump?

Sí, como capa adicional. Los dumps lógicos funcionan entre versiones mayores y facilitan restaurar una sola tabla, algo que el PITR físico no permite hacer directamente.

¿Con qué frecuencia debo probar las restauraciones?

Con la frecuencia suficiente para que un backup roto no pase inadvertido más tiempo del que podrías tolerar. Automatiza la prueba y alerta cuando falle o cuando no se haya ejecutado.