Laboratorio de Respaldo y Recuperación de PostgreSQL 18 en Ubuntu 24.04 — pg_dump, pg_basebackup y pgBackRest

Si trabajas con PostgreSQL en producción, las copias de seguridad no son opcionales.

En este laboratorio, construí un entorno completo de respaldo y recuperación de PostgreSQL 18 en Ubuntu 24.04 y probé los tres enfoques principales de respaldo:

• copias de seguridad lógicas con pg_dump
• copias de seguridad físicas con pg_basebackup
• copias de seguridad de nivel de producción con pgBackRest, incluida la archivación de WAL y la recuperación a un punto en el tiempo (PITR)

Todo fue probado en una VM real con simulacros de recuperación reales, no simulaciones.

El medioambiente

Instalando PostgreSQL 18

Usé el repositorio oficial de PGDG en lugar del paquete predeterminado de Ubuntu, que incluye una versión anterior.

Instalar el ayudante de repositorio:

sudo apt install -y postgresql-common

Ejecuta el script de configuración de PGDG:

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Instalar PostgreSQL 18:

sudo apt install -y postgresql-18

Verificar que el clúster esté en ejecución:

pg_lsclusters

Salida esperada:

Ver Puerto del Clúster Estado Propietario Directorio de Datos
18  principal   5432 en línea postgres /var/lib/postgresql/18/main

Creando la base de datos del laboratorio

Crear el usuario de la aplicación:

sudo -u postgres psql -c "CREATE USER banking WITH PASSWORD 'banking';"

Crear la base de datos:

sudo -u postgres psql -c "CREATE DATABASE bankingdb OWNER banking;"

Conectar como usuario bancario y crear el esquema:

psql -h localhost -U banking -d bankingdb -c "CREATE SCHEMA banking AUTHORIZATION banking;"

Creando las tablas

Creé cinco tablas que representan un pequeño esquema bancario: sucursales, empleados, clientes, cuentas y transacciones. La cadena de claves foráneas entre ellas ejercita las restricciones que importan en escenarios de restauración reales.

-- Connected as banking@bankingdb
CREATE TABLE banking.branches (
  branch_id    INTEGER PRIMARY KEY,
  branch_name  VARCHAR(100) NOT NULL,
  city         VARCHAR(50),
  country      VARCHAR(50),
  opened_date  DATE
);

CREATE TABLE banking.employees (
  employee_id  INTEGER PRIMARY KEY,
  branch_id    INTEGER NOT NULL REFERENCES banking.branches(branch_id),
  full_name    VARCHAR(100) NOT NULL,
  role         VARCHAR(50),
  hire_date    DATE
);

CREATE TABLE banking.customers (
  customer_id  INTEGER PRIMARY KEY,
  full_name    VARCHAR(100) NOT NULL,
  email        VARCHAR(100),
  country      VARCHAR(50),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE banking.accounts (
  account_id    INTEGER PRIMARY KEY,
  customer_id   INTEGER NOT NULL REFERENCES banking.customers(customer_id),
  branch_id     INTEGER NOT NULL REFERENCES banking.branches(branch_id),
  account_type  VARCHAR(20),
  balance       NUMERIC(15,2) DEFAULT 0,
  opened_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE banking.transactions (
  txn_id       INTEGER PRIMARY KEY,
  account_id   INTEGER NOT NULL REFERENCES banking.accounts(account_id),
  employee_id  INTEGER REFERENCES banking.employees(employee_id),
  txn_type     VARCHAR(20),
  amount       NUMERIC(15,2) NOT NULL,
  txn_date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  description  VARCHAR(200)
);

Inserté 18 filas en las cinco tablas — tres ramas, cuatro empleados, tres clientes, cuatro cuentas, cuatro transacciones. Este es el estado base que cada copia de seguridad en el laboratorio debe capturar y cada restauración debe reproducir.

Creando directorios de copia de seguridad

# pg_dump output — runs as the fernando OS user
mkdir -p /home/fernando/backups/pg-dump

# pg_basebackup output — runs as postgres
sudo mkdir -p /var/lib/postgresql/backups/pg-basebackup
sudo chown -R postgres:postgres /var/lib/postgresql/backups
sudo chmod -R 750 /var/lib/postgresql/backups

El directorio de repositorio de pgBackRest se crea por separado en la Parte 3.

Parte 1 — pg_dump

pg_dump crea una exportación lógica de una base de datos — sentencias SQL o un formato binario comprimido que se puede reproducir en cualquier instancia compatible de PostgreSQL.

Lo mejor para:

• migraciones entre versiones
• restauraciones de base de datos única o tabla única
• exportaciones solo de esquema para documentación o control de versiones de DDL

No apto para PITR o bases de datos grandes donde el tiempo de restauración debe medirse en minutos en lugar de horas.

Copia de seguridad completa de la base de datos

Usé formato personalizado (-F c) — binario comprimido, restaurable únicamente con pg_restore, admite la restauración paralela y opciones de selección de tablas.

PGPASSWORD=banking pg_dump \
  -h localhost \
  -U banking \
  -F c \
  -d bankingdb \
  -f /home/fernando/backups/pg-dump/bankingdb.dump

Inspeccionar el contenido del volcado sin restaurar:

pg_restore -l /home/fernando/backups/pg-dump/bankingdb.dump | head -30

La tabla de contenido mostró esquemas, tablas, secuencias, restricciones y datos de tablas para las cinco tablas.

Copia de seguridad solo de esquema

PGPASSWORD=banking pg_dump \
  -h localhost \
  -U banking \
  -F c \
  --schema-only \
  -d bankingdb \
  -f /home/fernando/backups/pg-dump/bankingdb-schema.dump

Útil para migraciones, control de versiones de DDL y para recrear entornos de destino vacíos antes de una carga de datos.

Copia de seguridad de una sola tabla

PGPASSWORD=banking pg_dump \
  -h localhost \
  -U banking \
  -F c \
  -t banking.transactions \
  -d bankingdb \
  -f /home/fernando/backups/pg-dump/transactions.dump

El volcado solo contiene la tabla de transacciones y sus restricciones, no las tablas padre. Restaurarlo requiere que las tablas padre ya existan en el destino.

Prueba de restauración completa

Creé una base de datos nueva y vacía y restauré el volcado completo en ella:

sudo -u postgres psql -c "CREATE DATABASE bankingdb_restore OWNER banking;"

PGPASSWORD=banking pg_restore \
  -h localhost \
  -U banking \
  -d bankingdb_restore \
  /home/fernando/backups/pg-dump/bankingdb.dump

Verificar que los recuentos de filas coincidan con la fuente en las cinco tablas:

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb_restore -c "
SELECT 'branches'     AS table_name, COUNT(*) FROM banking.branches
UNION ALL SELECT 'employees',    COUNT(*) FROM banking.employees
UNION ALL SELECT 'customers',    COUNT(*) FROM banking.customers
UNION ALL SELECT 'accounts',     COUNT(*) FROM banking.accounts
UNION ALL SELECT 'transactions', COUNT(*) FROM banking.transactions;"

Entonces se eliminó la base de datos de prueba:

sudo -u postgres psql -c "DROP DATABASE bankingdb_restore;"

Simulacro de recuperación ante desastres: Restaurar una tabla

Simulé una caída accidental de tabla:

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
DROP TABLE banking.transactions CASCADE;" 

Restaurada solo esa tabla del volcado de transacciones dedicado:

PGPASSWORD=banking pg_restore \
  -h localhost \
  -U banking \
  -d bankingdb \
  -t transactions \
  /home/fernando/backups/pg-dump/transactions.dump

Se verificó que las cuatro filas estuvieran de vuelta. Aquí es donde pg_dump brilla genuinamente — recuperación granular a nivel de objeto sin tocar el resto de la base de datos.

Parte 2 — pg_basebackup

pg_basebackup realiza una copia binaria a nivel de sistema de archivos de todo el clúster de PostgreSQL. No entiende bases de datos ni tablas: copia archivos.

Lo mejor para:

• arranque de réplicas de streaming
• instantáneas de recuperación ante desastres de clúster completo
• migración de clúster a un nuevo servidor

No apto para recuperación a nivel de tabla o base de datos — la restauración es todo o nada a nivel de clúster.

Comprobando la configuración de WAL

sudo -u postgres psql -c "SHOW wal_level; SHOW max_wal_senders;"

Salida esperada:

 wal_level
-----------
 réplica

 max_wal_senders
-----------------
 10

Ambos están configurados correctamente por defecto en PostgreSQL 18.

Creando la Copia de Seguridad Física

Usé el formato tar-F t) — produce comprimido base.tar.gz y pg_wal.tar.gz archivos en lugar de un árbol de directorios descomprimido.

sudo -u postgres pg_basebackup \
  -D /var/lib/postgresql/backups/pg-basebackup/cluster \
  -F t \
  -z \
  -P \
  -c fast

-c rápido emite un punto de control al inicio de la copia de seguridad en lugar de esperar al próximo programado — añade un breve pico de E/S pero es la opción correcta para un laboratorio interactivo.

Verificando la copia de seguridad

ls -lh /var/lib/postgresql/backups/pg-basebackup/cluster/

Archivos esperados:

Verificar la integridad antes de confiar en la copia de seguridad:

sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup --no-parse-wal \
  /var/lib/postgresql/backups/pg-basebackup/cluster

Una copia de seguridad que nunca has verificado es una copia de seguridad en la que no puedes confiar.

Simulacro de Restauración Física Completa

Detener PostgreSQL:

sudo systemctl stop postgresql

Mueva el directorio de datos existente a un lado:

sudo mv /var/lib/postgresql/18/main /var/lib/postgresql/18/main.before-restore
sudo mkdir -p /var/lib/postgresql/18/main
sudo chown postgres:postgres /var/lib/postgresql/18/main
sudo chmod 700 /var/lib/postgresql/18/main

Extraer el archivo base:

sudo -u postgres tar -xzf \
  /var/lib/postgresql/backups/pg-basebackup/cluster/base.tar.gz \
  -C /var/lib/postgresql/18/main

Extraer el archivo WAL:

sudo -u postgres mkdir -p /var/lib/postgresql/18/main/pg_wal
sudo -u postgres tar -xzf \
  /var/lib/postgresql/backups/pg-basebackup/cluster/pg_wal.tar.gz \
  -C /var/lib/postgresql/18/main/pg_wal

Iniciar PostgreSQL:

sudo systemctl start postgresql
sudo systemctl status postgresql@18-main

Verifique que los recuentos de filas coincidan con la línea de base en las cinco tablas:

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
SELECT 'branches'     AS table_name, COUNT(*) FROM banking.branches
UNION ALL SELECT 'employees',    COUNT(*) FROM banking.employees
UNION ALL SELECT 'customers',    COUNT(*) FROM banking.customers
UNION ALL SELECT 'accounts',     COUNT(*) FROM banking.accounts
UNION ALL SELECT 'transactions', COUNT(*) FROM banking.transactions;"

Eliminar el directorio movido aparte:

sudo rm -rf /var/lib/postgresql/18/main.before-restore

Parte 3 — pgBackRest

pgBackRest es la solución de copias de seguridad de nivel de producción para PostgreSQL. Combina copias de seguridad físicas con archivado de WAL integrado, tipos de copia de seguridad incremental y diferencial, gestión de retención, E/S paralela y recuperación a un punto en el tiempo, todo en una sola herramienta.

Instalando pgBackRest

sudo apt install -y pgbackrest

Creando el Directorio del Repositorio

pgBackRest no crea el directorio del repositorio automáticamente; este debe existir y ser propiedad de postgres antes de que se cree la estrofa.

sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest

Configuración de pgBackRest

sudo mkdir -p /etc/pgbackrest
sudo tee /etc/pgbackrest/pgbackrest.conf > /dev/null << 'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
log-level-console=info
log-level-file=detail

[main]
pg1-path=/var/lib/postgresql/18/main
EOF

El nombre de la estrofa principal coincide con el nombre del clúster de PostgreSQL de Ubuntu — el predeterminado en cualquier instalación de PostgreSQL de Ubuntu.

Configuración del archivado de WAL

Se necesitan tres parámetros en /etc/postgresql/18/main/postgresql.conf:

modo_archivo requiere un reinicio completo de PostgreSQL — no es un parámetro recargable.

sudo systemctl restart postgresql

Verifica que los parámetros surtieron efecto:

sudo -u postgres psql -c "SHOW archive_mode; SHOW archive_command; SHOW wal_level;"

Creando la estrofa y ejecutando la comprobación

Una stanza es la configuración con nombre de pgBackRest para un clúster de PostgreSQL. stanza-crear inicializa la estructura de directorios del repositorio — ejecútelo exactamente una vez.

sudo -u postgres pgbackrest --stanza=main stanza-create

Luego ejecuta checar — pgBackRest fuerza a un cambio de WAL, archiva el segmento y lo lee de vuelta:

sudo -u postgres pgbackrest --stanza=main check

Este es el paso de verificación previo a la copia de seguridad más importante. Si checar si falla, las copias de seguridad no se podrán recuperar.

Copia de seguridad completa

sudo -u postgres pgbackrest --stanza=main --type=full backup

Copia de seguridad diferencial

Después de insertar una nueva fila de transacción, realicé una copia de seguridad diferencial:

sudo -u postgres pgbackrest --stanza=main --type=diff backup

El tamaño de la copia de seguridad fue una pequeña fracción del total; solo se almacenaron los bloques modificados.

Copia de seguridad incremental

Después de insertar otra fila:

sudo -u postgres pgbackrest --stanza=main --type=incr backup

La cadena de copias de seguridad se convirtió en: Completa → Diferencial → Incremental.

Inspeccionar el catálogo completo:

sudo -u postgres pgbackrest info

Recuperación en un momento dado (PITR)

Esta fue la parte más importante del laboratorio.

Capturar la marca de tiempo actual antes del desastre:

RECOVERY_TARGET=$(sudo -u postgres psql -d bankingdb -Atc "SELECT now()::text;")
echo "Recovery target: $RECOVERY_TARGET"
sleep 2

RECUPERACIÓN_OBJETIVO es una variable de shell de bash. Almacena la marca de tiempo exacta de PostgreSQL capturada antes del desastre.

Todos los pasos de PITR se ejecutan en la misma sesión de terminal, por lo que la variable permanece activa entre comandos.

Se pasa directamente a pgBackRest --objetivo parámetro, que le indica que reproduzca el WAL hasta ese momento exacto y se detenga.

Simula el desastre — elimina la tabla de transacciones:

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
DROP TABLE banking.transactions CASCADE;" 

Detener PostgreSQL:

sudo systemctl stop postgresql

Restaurar a la marca de tiempo anterior a la caída:

sudo -u postgres pgbackrest --stanza=main \
  --type=time \
  "--target=$RECOVERY_TARGET" \
  --target-action=promote \
  --delta \
  restore

--delta compara el directorio de datos existente con la copia de seguridad y reemplaza solo los archivos modificados — mucho más rápido que una reextracción completa cuando la mayoría de los archivos no han cambiado.

Inicia PostgreSQL — entra en modo de recuperación, reproduce WAL hasta la marca de tiempo objetivo, luego se promueve a lectura/escritura:

sudo systemctl start postgresql
sudo tail -20 /var/log/postgresql/postgresql-18-main.log

Verificar que la tabla está de vuelta:

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
SELECT COUNT(*) FROM banking.transactions;" 

Esperado: 6 filas — 4 iniciales más las 2 insertadas durante los pasos diferencial e incremental. PITR funcionó.

Resumen

Reglas de decisión prácticas:

• Utilice pg_dump para migraciones entre versiones, copias de seguridad selectivas de tablas y capturas puntuales ad hoc.
• Utilice pg_basebackup principalmente para arrancar réplicas de streaming.
• Utilice pgBackRest para todo en producción — incremental diario, PITR, retención, archivado de WAL, todo en una sola herramienta.

En entornos reales los combinas: pgBackRest para protección continua, pg_dump para migraciones y recuperación a nivel de objeto.

Reflexiones finales

Una copia de seguridad solo es útil si la recuperación funciona realmente.

La disciplina más importante en este laboratorio no fue ejecutar los comandos de respaldo, sino ejecutar los simulacros de restauración y verificar los recuentos de filas después.

Probar las restauraciones no es opcional.

Si estás planeando una migración de Oracle a PostgreSQL y necesitas ayuda para diseñar la estrategia de copia de seguridad y recuperación para el entorno de destino, ver mis servicios →