La recuperación de datos es un aspecto crítico en la administración de cualquier sistema de bases de datos. En PostgreSQL, existen diversas estrategias para restaurar la información después de un incidente, que van desde restauraciones de copias de seguridad lógicas hasta recuperaciones a un momento específico (PITR, del inglés Point-In-Time Recovery) basadas en copias de seguridad físicas y archivos WAL (Write-Ahead Log).
Este artículo explorará cómo realizar una recuperación de datos en PostgreSQL, centrándose primero en un escenario de recuperación utilizando la herramienta de copia de seguridad lógica pg_dump y posterior restauración con pg_restore para revertir una base de datos a un estado anterior a una operación errónea. Posteriormente, se introducirá la recuperación a un momento específico (PITR) para una visión más completa de las capacidades de recuperación de PostgreSQL.
Entorno de Prueba
Para este experimento, se utilizará la siguiente configuración:
| Parámetro | Valor |
|---|---|
| Base de Datos | PostgreSQL 16.13 |
| Sistema Operativo | Ubuntu 24.04.4 LTS |
| Nivel de WAL | replica |
| Modo de Archivo | Activado |
Verificación de Prerrequisitos
Antes de comenzar, es fundamental asegurarse de que el nivel de registro WAL y el modo de archivado estén configurados correctamente, especialmente si se planea usar PITR en el futuro.
-- Comprobar el nivel de WAL (debe ser replica o logical)
SHOW wal_level;
-- Comprobar el modo de archivado
SHOW archive_mode;
-- Comprobar el comando de archivado
SHOW archive_command;
-- Obtener la posición actual del LSN (Log Sequence Number)
SELECT pg_current_wal_lsn();
Métodos de Copia de Seguridad y Recuperación en PostgreSQL
PostgreSQL ofrece una variedad de herramientas para la gestión de copias de seguridad y recuperación, adaptándose a diferentes necesidades:
| Método | Tipo | Descripción | Uso Común |
|---|---|---|---|
pg_dump |
Lógica | Copia de seguridad de una sola base de datos, en formato SQL o personalizado. | BD pequeñas, migraciones, recuperación a nivel de objeto. |
pg_dumpall |
Lógica | Copia de seguridad de todo el clúster (incluyendo roles y tablespaces). | Migraciones de clústeres completos. |
pg_basebackup |
Física | Copia de seguridad a nivel de sistema de archivos, copia el directorio de datos. | PITR, replicación, recuperación de desatsres. |
pgBackRest |
Física (Tercero) | Herramienta avanzada de código abierto para copias de seguridad y restauración. | Entornos empresariales, copias incrementales/diferenciales. |
| Archivado WAL | Incremental | Captura los registros de cambios, esencial para PITR y replicación. | Recuperación a un momento específico (PITR). |
Diagrama del Flujo del Expreimento
El siguiente esquema ilustra el flujo de nuestro experimento de recuperación utilizando una copia de seguridad lógica con pg_dump y pg_restore para corregir una operación errónea.
┌─────────────────────────────────────────────────────────────────────────────┐
│ Flujo del Experimento de Recuperación PostgreSQL │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Línea de tiempo: │
│ ──────────────────────────────────────────────────────────────────────▶ │
│ │
│ T1: Datos Iniciales T2: Respaldo pg_dump T3: Operaciones OK T4: Acción Errónea │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ 5 registros│ ──▶ │ pg_dump │ ───▶ │ INSERT │ ──▶ │ DELETE │ │
│ │ │ │ -F c │ │ UPDATE │ │ Tabla Completa │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌───────────┐ │
│ │ Archivo de │ │ Objetivo de │ │
│ │ Respaldo │ │ Recuperación│ │
│ │ .dump │ │ Momento T2 │ │
│ └─────────────┘ └───────────┘ │
│ │ ▲ │
│ │ ┌─────────────┐ │ │
│ └──────────▶│ pg_restore │─────────────┘ │
│ │ Restaura DB │ │
│ └─────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Pasos Detallados del Experimento
Paso 1: Habilitar Modo de Archivado (Prerrequisito para PITR)
Aunque nuestro primer experimento se centra en pg_dump, es una buena práctica configurar el archivado WAL, ya que es un componente esencial para PITR.
# Crear directorio para archivos WAL archivados
sudo mkdir -p /var/lib/postgresql/16/archivos_wal
sudo chown postgres:postgres /var/lib/postgresql/16/archivos_wal
# Modificar la configuración en postgresql.conf
sudo tee -a /etc/postgresql/16/main/postgresql.conf > /dev/null << 'EOF'
# Configuracion de Archivo WAL (PITR)
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/16/archivos_wal/%f'
EOF
# Reiniciar el servicio de PostgreSQL
sudo systemctl restart postgresql
# Verificar la configuración
psql -c "SHOW archive_mode;"
psql -c "SHOW archive_command;"
Resultado de la verificación:
archive_mode
--------------
on
archive_command
---------------------------------------
cp %p /var/lib/postgresql/16/archivos_wal/%f
Paso 2: Preparar Datos de Prueba
Se creará una base de datos y una tabla con datos iniciales para simular un escenario real.
-- Eliminar la base de datos si ya existe
DROP DATABASE IF EXISTS db_recuperacion;
-- Crear la base de datos de prueba
CREATE DATABASE db_recuperacion;
-- Conectarse a la base de datos de prueba
\c db_recuperacion
-- Crear la tabla de personal
CREATE TABLE personal (
id SERIAL PRIMARY KEY,
nombre_completo VARCHAR(100) NOT NULL,
area VARCHAR(50),
remuneracion DECIMAL(10,2),
fecha_ingreso DATE,
fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insertar datos iniciales
INSERT INTO personal (nombre_completo, area, remuneracion, fecha_ingreso) VALUES
('Ana García', 'Desarrollo', 15000.00, '2020-01-15'),
('Luis Pérez', 'Ventas', 12000.00, '2020-03-20'),
('Marta López', 'Contabilidad', 13000.00, '2020-06-10'),
('Pedro Sánchez', 'Desarrollo', 16000.00, '2020-08-25'),
('Sofía Ruiz', 'Recursos Humanos', 11000.00, '2021-01-05');
Datos iniciales en la tabla personal:
| id | nombre_completo | area | remuneracion | fecha_ingreso |
|---|---|---|---|---|
| 1 | Ana García | Desarrollo | 15000.00 | 2020-01-15 |
| 2 | Luis Pérez | Ventas | 12000.00 | 2020-03-20 |
| 3 | Marta López | Contabilidad | 13000.00 | 2020-06-10 |
| 4 | Pedro Sánchez | Desarrollo | 16000.00 | 2020-08-25 |
| 5 | Sofía Ruiz | Recursos Humanos | 11000.00 | 2021-01-05 |
Paso 3: Realizar Copia de Seguridad con pg_dump
En este punto, se realizará una copia de seguridad lógica de la base de datos de prueba. Este será el punto de restauración al que regresaremos.
# Crear directorio para almacenar el respaldo
mkdir -p /opt/pg_backups
# Ejecutar pg_dump para el respaldo (formato personalizado)
pg_dump -U postgres -h localhost -d db_recuperacion \
-F c \
-f /opt/pg_backups/respaldo_logico_20240423.dump
# Listar el archivo de respaldo
ls -la /opt/pg_backups/
Parámetros clave de pg_dump:
| Parámetro | Descripción |
|---|---|
-F c |
Formato personalizado (comprimido, permite recuperación paralela). |
-F t |
Formato tar. |
-F p |
Formato de texto SQL simple. |
-F d |
Formato de directorio (permite copias de seguridad paralelas). |
-j N |
Número de trabajos paralelos (solo con formato de directorio). |
Momento de la copia de seguridad: 2024-04-23 11:45:00 (aproximado)
Paso 4: Simular Operaciones de Negocio Normales
Se insertarán nuevos registros y se actualizarán algunos existentes, simulando la actividad diaria después de la copia de seguridad.
-- Insertar nuevo personal
INSERT INTO personal (nombre_completo, area, remuneracion, fecha_ingreso) VALUES
('Carlos Gómez', 'Desarrollo', 14000.00, '2021-03-15'),
('Laura Torres', 'Ventas', 12500.00, '2021-05-20');
-- Actualizar remuneraciones (incremento del 10% para el área de Desarrollo)
UPDATE personal SET remuneracion = remuneracion * 1.1 WHERE area = 'Desarrollo';
Datos después de operaciones normales:
| id | nombre_completo | area | remuneracion | fecha_ingreso |
|---|---|---|---|---|
| 1 | Ana García | Desarrollo | 16500.00 | 2020-01-15 |
| 2 | Luis Pérez | Ventas | 12000.00 | 2020-03-20 |
| 3 | Marta López | Contabilidad | 13000.00 | 2020-06-10 |
| 4 | Pedro Sánchez | Desarrollo | 17600.00 | 2020-08-25 |
| 5 | Sofía Ruiz | Recursos Humanos | 11000.00 | 2021-01-05 |
| 6 | Carlos Gómez | Desarrollo | 15400.00 | 2021-03-15 |
| 7 | Laura Torres | Ventas | 12500.00 | 2021-05-20 |
Momento de finalización de operaciones normales: 2024-04-23 11:46:30 (aproximado)
Paso 5: Simular una Operación Errónea (Pérdida de Datos)
Ahora, se simulará una situación de pérdida de datos, como un DELETE sin una cláusula WHERE.
-- ⚠️ Simulación de un error: Eliminación de todos los registros
DELETE FROM personal;
Datos después del error:
(Tabla vacía, 0 registros)
Momento del error: 2024-04-23 11:47:15 (aproximado)
Paso 6: Ejecutar el Proceso de Recuperación
Para recuperar los datos al estado de la copia de seguridad, se seguirá el siguiente procedimiento:
- Eliminar la base de datos actual para asegurar una restauración limpia.
- Crear una nueva base de datos con el mismo nombre.
- Restaurar la copia de seguridad usando
pg_restore.
# Paso 1: Eliminar la base de datos dañada
psql -U postgres -d postgres -c "DROP DATABASE IF EXISTS db_recuperacion;"
# Paso 2: Volver a crear la base de datos
psql -U postgres -d postgres -c "CREATE DATABASE db_recuperacion;"
# Paso 3: Restaurar la copia de seguridad
pg_restore -U postgres -h localhost -d db_recuperacion \
/opt/pg_backups/respaldo_logico_20240423.dump
Paso 7: Verificar el Resultado de la Recuperación
Finalmente, se consultará la base de datos para confirmar que los datos han sido restaurados correctamente al momento de la copia de seguridad.
-- Consultar los datos después de la recuperación
SELECT * FROM personal;
-- Obtener el conteo total de registros y la suma de remuneraciones
SELECT COUNT(*) AS total_registros, SUM(remuneracion) AS remuneracion_total FROM personal;
Datos después de la recuperación:
| id | nombre_completo | area | remuneracion | fecha_ingreso |
|---|---|---|---|---|
| 1 | Ana García | Desarrollo | 15000.00 | 2020-01-15 |
| 2 | Luis Pérez | Ventas | 12000.00 | 2020-03-20 |
| 3 | Marta López | Contabilidad | 13000.00 | 2020-06-10 |
| 4 | Pedro Sánchez | Desarrollo | 16000.00 | 2020-08-25 |
| 5 | Sofía Ruiz | Recursos Humanos | 11000.00 | 2021-01-05 |
Comparación de los Resultados de la Recuperación
La siguiente tabla resume el estado de los datos en las diferentes etapas del experimento:
| Fase | Registros | Remuneración Total | Descripción |
|---|---|---|---|
| Datos Iniciales | 5 | 67000.00 | Antes del respaldo |
| Después de Ops. Normales | 7 | 98000.00 | Antes de la operación errónea |
| Después de Ops. Erróneas | 0 | 0 | DELETE de toda la tabla |
| Después de Recuperación | 5 | 67000.00 | Restaurado al punto del respaldo |
Recuperación a un Momento Específico (PITR) en PostgreSQL
Mientras que pg_dump es ideal para recuperaciones de bases de datos individuales y puede ser usado para recuperar a un punto anterior si no hay mucha actividad posterior, la verdadera recuperación a un momento específico (PITR) en PostgreSQL se logra a través de copias de seguridad de bajo nivel de archivo (físicas) y el archivado continuo de los registros WAL.
Principio de PITR
La PITR permite restaurar un clúster de PostgreSQL a un estado exacto en el pasado (por ejemplo, antes de un error crítico). Esto se logra restaurando una copia de seguridad base y luego aplicando los registros WAL archivados hasta el momento deseado.
┌─────────────────────────────────────────────────────────────────────────────┐
│ Principio de Recuperación PITR PostgreSQL │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │ pg_basebackup │ │ Archivos WAL │ │ Objetivo de │ │
│ │ (Copia Base) │ │ (Registros Inc.)│ │ Recuperación │ │
│ └──────┬─────────┘ └──────┬──────────┘ └──────┬──────────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Proceso de Recuperación │ │
│ │ 1. Detener el servicio PostgreSQL. │ │
│ │ 2. Restaurar la copia base al directorio de datos vacío. │ │
│ │ 3. Configurar 'restore_command' y 'recovery_target_time/lsn'. │ │
│ │ 4. Crear 'recovery.signal' en el directorio de datos. │ │
│ │ 5. Iniciar PostgreSQL; se aplicarán los WAL automáticamente. │ │
│ │ 6. La base de datos se detiene o se promueve en el punto deseado.│ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Pasos para una Recuperación PITR Completa
Este es un ejemplo simplificado de los pasos para una recuperación PITR. Requiere una copia de seguridad base previamente realizada con pg_basebackup.
# 1. Detener el servicio de PostgreSQL
sudo systemctl stop postgresql
# 2. Limpiar el directorio de datos actual (¡CUIDADO! Esto elimina todos los datos actuales)
sudo rm -rf /var/lib/postgresql/16/main/*
# 3. Restaurar la copia de seguridad base (ej. de un archivo tar.gz)
# Asumiendo que /opt/pg_backups/base_completa.tar.gz es un respaldo de pg_basebackup
sudo tar -xzf /opt/pg_backups/base_completa.tar.gz -C /var/lib/postgresql/16/main/
# 4. Crear archivo de configuración de recuperación (postgresql.auto.conf)
# y especificar el punto de recuperación.
# La fecha/hora debe ser ANTERIOR al momento del error que queremos revertir.
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/16/archivos_wal/%f %p'
recovery_target_time = '2024-04-23 11:46:00 CEST' # Ejemplo: antes del error
recovery_target_action = 'promote' # 'promote' para que se convierta en maestro al finalizar
EOF
# 5. Crear el archivo de señal de recuperación (anteriormente recovery.conf)
touch /var/lib/postgresql/16/main/recovery.signal
# 6. Iniciar PostgreSQL. Automáticamente realizará la recuperación.
sudo systemctl start postgresql
# 7. Una vez que el servicio esté iniciado, verificar los datos recuperados
psql -c "SELECT * FROM personal;"
Opciones de Destino de Recuperación
PostgreSQL ofrece varias maneras de especificar el punto final para una recuperación:
| Parámetro | Descripción |
|---|---|
recovery_target_time |
Recupera hasta un momento específico (timestamp). |
recovery_target_xid |
Recupera hasta un ID de transacción específico. |
recovery_target_lsn |
Recupera hasta una posición específica en el registro WAL (LSN). |
recovery_target_name |
Recupera hasta un punto de recuperación nombrado (creado con pg_create_restore_point()). |
recovery_target_immediate |
Detiene la recuperación tan pronto como se alcanza un estado consistente. |
Comparativa con Otros Sistemas de Bases de Datos
La recuperación a un momento específico es una característica fundamental en la mayoría de los RDBMS. Aquí una breve comparación:
| Característica | Oracle | MySQL | PostgreSQL |
|---|---|---|---|
| Tipo de Recuperación | Recuperación Incompleta | PITR | PITR |
| Copia Física | RMAN | mysqlbackup |
pg_basebackup |
| Copia Lógica | expdp |
mysqldump |
pg_dump |
| Registros Incrementales | Archived Redo Logs | Binlog | WAL (Write-Ahead Log) |
| Recuperación por Tiempo | RECOVER UNTIL TIME |
--stop-datetime |
recovery_target_time |
| Recuperación por SCN/LSN | RECOVER UNTIL SCN |
--stop-position |
recovery_target_lsn |
| Recuperación a Nivel de Tabla | Transporte de Tablespace | Filtro de binlog | pg_restore -t |
Recomendaciones y Mejores Prácticas
Estrategia de Copias de Seguridad
| Estrategia | Directriz |
|---|---|
| Frecuencia de Copias Base | Al menos una vez al día para reducir el tiempo de recuperación. |
| Archivado WAL | Archivar en tiempo real a un almacenamiento remoto y seguro. |
| Retención de Respaldos | Mantener copias de seguridad y WALs por al menos 7 días (o más según SLA). |
| Verificación | Realizar pruebas de restauración periódicas en un entorno aislado. |
Configuración de Archivando Recomendada
# Configuración óptima para el archivado WAL
archive_mode = on
archive_command = 'rsync -a %p usuario_backup@servidor_remoto:/ruta/al/archivo_wal/%f'
archive_timeout = 300 # Forzar el cambio de WAL cada 5 minutos
wal_keep_size = 1GB # Retener suficiente WAL para standby/recuperación
Procedimiento de Recuperación
En caso de una contingencia, seguir estos pasos es crucial:
- Detener inmediatamente las operaciones de escritura de la aplicación.
- Registrar el momento exacto en que ocurrió la operación errónea.
- Realizar una copia de seguridad de los archivos WAL actuales y del directorio de datos si es posible (como medida precautoria).
- Restaurar la última copia de seguridad base.
- Configurar el destino de recuperación (hora, LSN, etc.).
- Iniciar la base de datos para que comience a aplicar los WALs hasta el punto deseado.
- Verificar la integridad y consistencia de los datos recuperados.
- Restaurar los servicios de la aplicación.
Consideraciones Importantes
| Punto Clave | Explicación |
|---|---|
wal_level |
Debe ser replica o logical para permitir el archivado WAL. |
archive_mode |
Debe estar on para que funcione la PITR. |
| Retención de WAL | Asegurarse de que los archivos WAL necesarios para la recuperación no sean eliminados. |
| Pruebas de Recuperación | Siempre probar los procedimientos de recuperación en un entorno de desarrollo antes de aplicarlos en producción. |
Referencia Rápida de Comandos
Comandos de Copia de Seguridad
# Copia de seguridad lógica (formato personalizado)
pg_dump -U postgres -F c -f respaldo_db_personal.dump db_recuperacion
# Copia de seguridad lógica (texto SQL)
pg_dump -U postgres -F p -f respaldo_db_personal.sql db_recuperacion
# Copia de seguridad de todo el clúster
pg_dumpall -U postgres > respaldo_clúster_completo.sql
# Copia de seguridad física base
pg_basebackup -h localhost -D /opt/pg_backups/base_fs -Ft -z -Xs -P
Comandos de Recuperación
# Restaurar copia de seguridad de formato personalizado
pg_restore -U postgres -d db_recuperacion respaldo_db_personal.dump
# Restaurar copia de seguridad de texto SQL
psql -U postgres -d db_recuperacion < respaldo_db_personal.sql
# Restauración paralela (para formatos personalizados o de directorio)
pg_restore -U postgres -d db_recuperacion -j 4 respaldo_db_personal.dump
# Restaurar solo el esquema de la base de datos
pg_restore -U postgres -s -d db_recuperacion respaldo_db_personal.dump
Comandos Relacionados con WAL
-- Consultar la posición LSN actual
SELECT pg_current_wal_lsn();
-- Listar los archivos WAL en el directorio pg_wal
SELECT * FROM pg_ls_waldir();
-- Forzar el cambio a un nuevo archivo WAL
SELECT pg_switch_wal();
-- Crear un punto de restauración nombrado
SELECT pg_create_restore_point('antes_de_actualizacion_critica');