Estrategias de respaldo en PostgreSQL: lógico, físico y PITR (con un laboratorio que corre en tu navegador)

Autor: Luis Fernando Vilca Barrientos, estudiante de Ingeniería de Sistemas de la Universidad Privada de Tacna, curso Base de Datos II.

🔗 Demo en vivo: https://fernan101002.github.io/backup-strategies-postgresql/

💻 Repositorio público: https://github.com/FeRnAn101002/backup-strategies-postgresql

1. El día que cinco copias no sirvieron de nada

El 31 de enero de 2017, un administrador de GitLab.com ejecutó por error un borrado sobre el directorio de datos del servidor PostgreSQL principal. La empresa tenía réplicas, volcados diarios con pg_dump hacia Amazon S3 y snapshots de disco. Aun así perdió cerca de seis horas de datos de producción: los volcados fallaban en silencio, porque usaban pg_dump 9.2 contra un servidor 9.6 y las alertas por correo eran rechazadas. Los demás mecanismos tampoco permitían una recuperación limpia (GitLab, 2017).

La lección se resume en una frase del libro de Site Reliability Engineering de Google: “nadie quiere realmente hacer respaldos; lo que la gente quiere son restauraciones” (Blum & Singh, 2016). En este artículo reviso las tres estrategias de respaldo que ofrece PostgreSQL, cuándo conviene cada una y cómo comprobar que de verdad se pueden restaurar. Para practicarlas construí PgBackupLab, una aplicación web que ejecuta un PostgreSQL 18 real en el navegador.

2. Dos números antes de elegir: RPO y RTO

La guía de contingencia del NIST define dos objetivos que deberían guiar cualquier política de respaldo (Swanson et al., 2010):

• RPO (Recovery Point Objective): cuántos datos, medidos en tiempo, puede perder el negocio. Si respaldas una vez al día a medianoche y el servidor falla a las 6 p. m., pierdes 18 horas de transacciones.
• RTO (Recovery Time Objective): cuánto tiempo puede estar caído el sistema mientras se restaura.

A estos dos objetivos se suma la regla 3-2-1, que recomienda tener tres copias de los datos, en dos medios distintos y una de ellas fuera del sitio (Ruggiero & Heckathorn, 2012). Las guías actuales contra ransomware agregan que esas copias deben estar cifradas, desconectadas y probadas periódicamente (CISA et al., 2023).

3. Las tres estrategias de PostgreSQL

La documentación oficial organiza los respaldos en tres enfoques (PostgreSQL Global Development Group, 2025a).

3.1 Respaldo lógico (SQL dump)

pg_dump genera un archivo con las sentencias SQL necesarias para recrear la base. Toma una instantánea consistente sin bloquear a los demás usuarios, porque trabaja dentro de una transacción REPEATABLE READ.

pg_dump -Fc -d tienda -f tienda.dump      # formato personalizado y comprimido
pg_restore -d tienda_nueva tienda.dump    # restauración, se puede paralelizar con -j

• ✅ Es portable entre versiones y arquitecturas, y permite restaurar una sola tabla.
• ❌ En bases grandes es lento de generar y de restaurar, porque hay que reconstruir los índices. Además, el RPO es tan amplio como el intervalo entre volcados.

3.2 Respaldo físico (a nivel de sistema de archivos)

Se copian los archivos del directorio de datos (PGDATA). La forma segura de hacerlo con el servidor encendido es pg_basebackup, que coordina la copia con el WAL para que el resultado sea consistente.

pg_basebackup -D /respaldos/base_2026_10_03 -Ft -z -P

• ✅ La restauración es muy rápida, porque basta copiar los archivos y arrancar el servidor.
• ❌ Solo sirve para la misma versión mayor de PostgreSQL y siempre copia el clúster completo.

3.3 Archivado continuo y PITR

PostgreSQL escribe cada cambio primero en el WAL (Write-Ahead Log). Si esos segmentos se archivan de forma continua (archive_mode = on y archive_command), un respaldo físico base más el WAL archivado permiten reproducir la historia hasta cualquier instante: es la recuperación a un punto en el tiempo, o PITR (PostgreSQL Global Development Group, 2025a).

# postgresql.conf
archive_mode = on
archive_command = 'test ! -f /archivo/%f && cp %p /archivo/%f'
# Durante la recuperación:
restore_command = 'cp /archivo/%f %p'
recovery_target_time = '2026-10-03 21:23:52'

• ✅ El RPO queda en segundos y permite volver justo al momento previo a un DELETE accidental.
• ❌ Requiere más configuración y espacio para el WAL, y hay que vigilar que el archivado no falle.

Desde PostgreSQL 17 existe además el respaldo incremental nativo: pg_basebackup --incremental copia solo los bloques modificados desde un respaldo anterior, y pg_combinebackup reconstruye el respaldo completo antes de restaurarlo (PostgreSQL Global Development Group, 2025b). En producción, herramientas como pgBackRest, Barman o WAL-G automatizan todo lo anterior: compresión, cifrado, retención y envío a almacenamiento en la nube.

3.4 Comparación rápida

Estrategia	RPO típico	RTO	Portabilidad	Granularidad
Lógico (pg_dump)	Horas (intervalo entre volcados)	Alto en bases grandes	Alta (entre versiones)	Base, esquema o tabla
Físico (pg_basebackup)	Horas (intervalo entre copias)	Bajo	Misma versión mayor	Clúster completo
Físico + WAL (PITR)	Segundos	Bajo a medio	Misma versión mayor	Cualquier instante

En la práctica se combinan: PITR como estrategia principal y pg_dump periódico como respaldo secundario y portable.

4. PgBackupLab: practicar sin instalar nada

Para no quedarme en la teoría, desarrollé una aplicación web que ejecuta PostgreSQL 18.3 real dentro del navegador. Usa PGlite, una compilación de PostgreSQL a WebAssembly desarrollada por ElectricSQL (ElectricSQL, 2025). No necesita servidor, así que se publica gratis en GitHub Pages.

La app crea una base tienda con cuatro tablas relacionadas (clientes, productos, pedidos y detalle_pedido) y permite recorrer el ciclo completo:

1. Respaldar de forma lógica o física.
2. Generar actividad del negocio.
3. Provocar un desastre: DELETE sin WHERE, UPDATE masivo, TRUNCATE o DROP TABLE.
4. Recuperar y verificar.

4.1 Respaldo lógico con el pg_dump real

PGlite incluye el propio pg_dump compilado a WebAssembly, así que el respaldo lógico es el auténtico:

import { pgDump } from '@electric-sql/pglite-tools/pg_dump'

export async function backupLogico(pg) {
  const archivo = await pgDump({ pg })          // tienda.sql
  const sql = await archivo.text()
  return { tipo: 'logico', datos: new Blob([sql]), sha256: await sha256(sql), huella: await huella(pg) }
}

4.2 Respaldo físico del directorio de datos

export async function backupFisico(pg) {
  await pg.exec('CHECKPOINT')                   // fuerza a disco las páginas pendientes
  const datos = await pg.dumpDataDir('gzip')    // PGDATA comprimido, como pg_basebackup -Ft -z
  return { tipo: 'fisico', datos, sha256: await sha256(datos), huella: await huella(pg) }
}

En mis pruebas, el respaldo lógico de la base de demostración ocupó 45 KB y tardó unos 120 ms. El físico ocupó 4,46 MB y tardó unos 400 ms, porque incluye el catálogo del sistema y todo el clúster. Este es justamente el compromiso entre ambas estrategias.

4.3 PITR: respaldo base y reproducción de cambios

PGlite no expone el WAL físico, así que lo emulé con un WAL lógico. Un trigger registra cada fila insertada, modificada o borrada con un número de secuencia (LSN) y una marca de tiempo, y la aplicación copia esos registros fuera de la base, igual que lo haría archive_command:

CREATE TABLE _journal (lsn bigserial PRIMARY KEY, ts timestamptz DEFAULT clock_timestamp(),
                       tabla text, op text, nuevo jsonb, viejo jsonb);

CREATE FUNCTION _journal_fila() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO _journal (tabla, op, nuevo, viejo) VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END,
    CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END);
  RETURN NULL;
END $$;

La recuperación restaura el respaldo físico en una instancia nueva y reproduce los cambios archivados hasta el LSN elegido. Durante la reproducción desactiva los triggers con session_replication_role = 'replica', como hace una réplica:

export async function restaurarPITR(base, archivo, lsnObjetivo) {
  const { pg } = await restaurar(base)                         // 1. respaldo base
  const cambios = archivo.filter((c) => c.lsn > base.lsn && c.lsn <= lsnObjetivo)
  await pg.transaction(async (tx) => {
    await tx.exec("SET LOCAL session_replication_role = 'replica'")
    for (const c of cambios) await aplicarCambio(tx, c)        // 2. reproducir hasta el punto
  })
  return pg
}

4.4 Verificar antes de confiar

Cada restauración se compara contra una huella del estado original: el número de filas y el MD5 del contenido de cada tabla, ordenado por clave primaria. Además, el SHA-256 de cada archivo se comprueba antes de restaurarlo, así que un respaldo alterado se rechaza.

SELECT count(*) AS filas,
       md5(string_agg(x::text, '|' ORDER BY x.id)) AS md5
FROM pedidos x;

En la demostración del video ocurre lo siguiente:

1. Tomo un respaldo físico y luego registro 10 pedidos nuevos.
2. Ejecuto DELETE FROM pedidos sin WHERE.
3. Con PITR recupero todo, incluidos los pedidos posteriores al respaldo. Se reprodujeron 50 cambios y la restauración tardó unos 250 ms.

Con solo el respaldo completo, esos 10 pedidos se habrían perdido.

5. Despliegue automatizado

El repositorio tiene dos workflows de GitHub Actions:

• deploy.yml: en cada push a main ejecuta las pruebas con Vitest contra PostgreSQL real (respaldo lógico, físico, PITR, detección de archivos alterados y retención GFS). Después construye la app con Vite, la publica en GitHub Pages y termina con una prueba de humo sobre la URL pública.
• simulacro.yml: cada lunes repite el ciclo respaldar → desastre → restaurar → verificar. Es la versión automatizada del consejo de probar periódicamente las restauraciones (CISA et al., 2023).

jobs:
  build:
    steps:
      - uses: actions/checkout@v5
      - run: npm ci
      - run: npm test          # pruebas de respaldo y restauración
      - run: npm run build
      - uses: actions/upload-pages-artifact@v4
        with: { path: dist }
  deploy:
    needs: build
    steps:
      - uses: actions/deploy-pages@v4

6. Lista de verificación para producción

1. Define el RPO y el RTO con el negocio antes de elegir herramientas.
2. Usa respaldo físico + archivado de WAL (pgBackRest, Barman o WAL-G) como estrategia principal, y pg_dump como copia portable.
3. Aplica 3-2-1: al menos una copia en otra región o proveedor, y una inmutable o desconectada.
4. Cifra los respaldos y guarda las claves separadas de los datos.
5. Verifica sumas de control y restaura siempre en una instancia nueva, nunca sobre la dañada.
6. Monitorea que el archivado no falle: un archive_command roto deja el PITR inservible sin avisar.
7. Define una retención clara (por ejemplo, GFS: 7 diarios, 4 semanales y 12 mensuales).
8. Automatiza los simulacros de restauración y mide el RTO real.

7. Conclusión

Un respaldo es una promesa; la restauración es la prueba de que se cumple. En PostgreSQL, pg_dump aporta portabilidad, el respaldo físico aporta velocidad y el archivado continuo convierte un desastre de horas en una recuperación al segundo exacto. Lo que de verdad protege los datos es combinarlos, verificarlos y ensayar la recuperación hasta que sea rutina. Puedes intentarlo tú mismo en la demo.

Referencias

Blum, R., & Singh, R. (2016). Data integrity: What you read is what you wrote. En B. Beyer, C. Jones, J. Petoff & N. R. Murphy (Eds.), Site reliability engineering: How Google runs production systems (cap. 26). O’Reilly Media. https://sre.google/sre-book/data-integrity/

CISA, MS-ISAC, NSA & FBI. (2023). #StopRansomware guide. Cybersecurity and Infrastructure Security Agency. https://www.cisa.gov/stopransomware/ransomware-guide

ElectricSQL. (2025). PGlite documentation. https://pglite.dev/docs/

GitLab. (2017, 10 de febrero). Postmortem of database outage of January 31. https://about.gitlab.com/blog/postmortem-of-database-outage-of-january-31/

PostgreSQL Global Development Group. (2025a). Chapter 25. Backup and restore. En PostgreSQL 18 documentation. https://www.postgresql.org/docs/current/backup.html

PostgreSQL Global Development Group. (2025b). pg_basebackup. En PostgreSQL 17 documentation. https://www.postgresql.org/docs/17/app-pgbasebackup.html

Ruggiero, P., & Heckathorn, M. A. (2012). Data backup options. United States Computer Emergency Readiness Team (US-CERT). https://www.cisa.gov/sites/default/files/publications/data_backup_options.pdf

Swanson, M., Bowen, P., Phillips, A. W., Gallup, D., & Lynes, D. (2010). Contingency planning guide for federal information systems (NIST Special Publication 800-34 Rev. 1). National Institute of Standards and Technology. https://doi.org/10.6028/NIST.SP.800-34r1