Copia de Seguridad y Recuperación de Bases de Datos PostgreSQL: Una Guía Completa para Usuarios de AlexHost

Por qué la estrategia de copia de seguridad de PostgreSQL es más importante de lo que crees

La pérdida de datos no es un riesgo hipotético — es una certeza operativa que todo administrador de bases de datos enfrentará en algún momento. Los fallos de hardware, las eliminaciones accidentales, las transacciones corruptas y los ataques de ransomware pueden llevar un entorno de producción a sus rodillas en segundos. Para los usuarios de PostgreSQL, tener una estrategia de copia de seguridad robusta, probada y automatizada es la diferencia entre un incidente menor y un fracaso comercial catastrófico.

AlexHost Dedicated Servers proporciona una base ideal para alojar y proteger bases de datos PostgreSQL. Con almacenamiento NVMe SSD de nivel empresarial que ofrece un rendimiento de E/S excepcional, acceso root completo para control de configuración total, y protección DDoS integrada, AlexHost te proporciona el rendimiento de infraestructura y la postura de seguridad que las cargas de trabajo de bases de datos serias demandan.

Ya sea que estés ejecutando una plataforma de comercio electrónico de alto tráfico, una aplicación SaaS, una instalación de WordPress respaldada por una base de datos relacional, o un sistema empresarial personalizado, esta guía te lleva a través de cada método mayor de copia de seguridad y recuperación de PostgreSQL — desde volcados SQL simples hasta Recuperación Point-in-Time (PITR) avanzada — todo optimizado para entornos de producción.

Tabla de contenidos

1. Comprender las opciones de copia de seguridad de PostgreSQL
2. Requisitos previos y permisos de privilegios
3. Método 1 — Volcado SQL con pg_dump
4. Método 2 — Copia de seguridad de todas las bases de datos con pg_dumpall
5. Método 3 — Copias de seguridad en formato personalizado para bases de datos grandes
6. Restauración desde volcados SQL
7. Restauración desde volcados en formato personalizado
8. Método 4 — Archivado continuo y recuperación Point-in-Time (PITR)
9. Automatización de copias de seguridad con Cron
10. Aseguración y almacenamiento de copias de seguridad fuera del sitio
11. Resumen de mejores prácticas

1. Comprender las opciones de copia de seguridad de PostgreSQL

PostgreSQL viene con varios mecanismos de copia de seguridad maduros y bien documentados. Elegir el correcto depende del tamaño de tu base de datos, objetivos de tiempo de recuperación (RTO), objetivos de punto de recuperación (RPO), y tolerancia de complejidad operativa.

Comprender estos compromisos antes de comenzar es esencial. La mayoría de los entornos de producción se benefician de combinar al menos dos enfoques — por ejemplo, volcados en formato personalizado nocturnos junto con archivado WAL continuo para capacidad de recuperación granular.

2. Requisitos previos y permisos de privilegios

Antes de ejecutar cualquier operación de copia de seguridad, confirma que los siguientes requisitos previos estén en su lugar:

Privilegios de usuario:

• Debes ser un superusuario de PostgreSQL o el propietario de la base de datos de destino para realizar una copia de seguridad completa.
• Para pg_dumpall, los privilegios de superusuario son obligatorios.

Verifica tu versión de PostgreSQL:

psql --version

Verifica el espacio en disco disponible antes de hacer copia de seguridad:

df -h /var/lib/postgresql/

Asegúrate de que tu destino de copia de seguridad tenga suficiente espacio libre — como mínimo 1.5× el tamaño de la base de datos de la que se está haciendo copia de seguridad para tener en cuenta archivos temporales y sobrecarga de compresión.

Conecta a tu servidor a través de SSH:

ssh root@your-server-ip

Si estás usando un plan de VPS Hosting, tendrás acceso SSH completo y la capacidad de instalar, configurar y administrar PostgreSQL sin restricciones.

3. Método 1 — Volcado SQL con pg_dump

La utilidad pg_dump es la herramienta de copia de seguridad de PostgreSQL más comúnmente utilizada. Produce una instantánea consistente de una única base de datos, incluso mientras la base de datos se está utilizando activamente. La salida es un script SQL de texto plano que puede ser revisado, editado y reproducido en cualquier instalación de PostgreSQL compatible.

Paso 1: Abre una terminal y accede a tu servidor

ssh root@your-alexhost-server-ip

Paso 2: Ejecuta el comando pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Desglose de parámetros:

Ejemplo práctico:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Consejo profesional: Añadir una marca de tiempo usando $(date +%Y%m%d_%H%M%S) a tu nombre de archivo de copia de seguridad asegura que nunca sobrescribas accidentalmente una copia de seguridad anterior y crea un archivo cronológico natural.

Paso 3: Verifica el archivo de copia de seguridad

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

El archivo debe comenzar con comentarios de encabezado de PostgreSQL y declaraciones SET, confirmando que se creó un volcado válido.

4. Método 2 — Copia de seguridad de todas las bases de datos con pg_dumpall

Cuando necesitas hacer copia de seguridad de cada base de datos en una instancia de PostgreSQL — incluyendo objetos globales como roles y espacios de tabla — pg_dumpall es la herramienta correcta.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

Este comando exporta:

• Todas las bases de datos
• Todos los roles (usuarios y grupos)
• Todos los espacios de tabla
• Toda la configuración global

Importante: El archivo de salida de pg_dumpall puede ser muy grande en servidores ocupados. Asegúrate de que tu partición de copia de seguridad tenga espacio adecuado y considera comprimir la salida inmediatamente:

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Método 3 — Copias de seguridad en formato personalizado para bases de datos grandes

Para bases de datos de producción que superan algunos gigabytes, el formato personalizado (-F c) se recomienda fuertemente sobre volcados SQL planos. Las copias de seguridad en formato personalizado son:

• Comprimidas por defecto — reduciendo significativamente los requisitos de almacenamiento
• Más rápidas de restaurar — soportando operaciones de restauración paralela con bandera -j
• Restaurables selectivamente — permitiéndote restaurar tablas o esquemas individuales

Creación de una copia de seguridad en formato personalizado

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Creación de una copia de seguridad en formato de directorio comprimido (soporta paralelismo)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

Este enfoque reduce dramáticamente la duración de la copia de seguridad en servidores multi-núcleo, haciéndolo ideal para los entornos de servidor dedicado de alto rendimiento disponibles en AlexHost.

6. Restauración desde volcados SQL

Restaura una única base de datos desde un volcado SQL plano

Primero, asegúrate de que la base de datos de destino exista. Si no existe, créala:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Luego restaura:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Desglose de parámetros:

Monitorea el progreso de la restauración

Para archivos SQL grandes, puedes monitorear el progreso usando pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Restauración desde volcados en formato personalizado

Los volcados en formato personalizado requieren la utilidad pg_restore en lugar de psql.

Restauración básica

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Restaura y crea la base de datos automáticamente

Usa la bandera -C para instruir a pg_restore que cree la base de datos antes de poblarla:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Restauración paralela para recuperación más rápida

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Usar -j 4 con una copia de seguridad en formato de directorio puede reducir el tiempo de restauración hasta un 75% en un servidor de cuatro núcleos — una ventaja significativa al minimizar el tiempo de inactividad durante la recuperación ante desastres.

Restaura una tabla específica solamente

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Esta capacidad granular es una de las ventajas clave del formato personalizado sobre volcados SQL planos.

8. Método 4 — Archivado continuo y recuperación Point-in-Time (PITR)

PITR es el estándar de oro para implementaciones de PostgreSQL críticas. Te permite restaurar tu base de datos a cualquier momento específico — no solo la última copia de seguridad — reproduciendo segmentos de Write-Ahead Log (WAL) en una copia de seguridad base. Esto es esencial para escenarios donde necesitas recuperarte de un error lógico (como un DROP TABLE accidental) que ocurrió en una marca de tiempo conocida.

Paso 1: Habilita el archivado WAL en postgresql.conf

Localiza y edita tu archivo de configuración de PostgreSQL:

nano /etc/postgresql/15/main/postgresql.conf

Añade o modifica las siguientes directivas:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Explicación de parámetros:

Crea el directorio de archivo y establece los permisos correctos:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Reinicia PostgreSQL para aplicar los cambios:

systemctl restart postgresql

Paso 2: Toma una copia de seguridad base con pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Paso 3: Restaura a un punto específico en el tiempo

Para restaurar desde una copia de seguridad base y archivos WAL:

1. Detén PostgreSQL:

systemctl stop postgresql

1. Borra el directorio de datos existente:

rm -rf /var/lib/postgresql/15/main/*

1. Extrae la copia de seguridad base:

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Crea un archivo recovery.conf (PostgreSQL 11 y anteriores) o configura postgresql.conf y crea un archivo recovery.signal (PostgreSQL 12+):

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Añade a postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Establece la propiedad correcta e inicia PostgreSQL:

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL reproducirá segmentos WAL hasta la marca de tiempo especificada y luego se promoverá a un estado normal de lectura-escritura.

9. Automatización de copias de seguridad con Cron

Las copias de seguridad manuales no son confiables. Automatizar tu cronograma de copia de seguridad con cron asegura consistencia y elimina el factor de error humano.

Crea un script de copia de seguridad

nano /usr/local/bin/pg_backup.sh

#!/bin/bash

# PostgreSQL Automated Backup Script
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="my_production_db"
RETENTION_DAYS=14

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Perform the backup
pg_dump -U "$DB_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +"$RETENTION_DAYS" -delete

# Log completion
echo "[$TIMESTAMP] Backup of $DB_NAME completed successfully." >> /var/log/pg_backup.log

Haz el script ejecutable:

chmod +x /usr/local/bin/pg_backup.sh

Programa con Cron

crontab -e

Añade la siguiente línea para ejecutar una copia de seguridad cada noche a las 2:00 AM:

0 2 * * * /usr/local/bin/pg_backup.sh

Para copias de seguridad completas semanales más archivado WAL incremental diario, combina esto con la configuración de PITR descrita en la sección anterior.

10. Aseguración y almacenamiento de copias de seguridad fuera del sitio

Una copia de seguridad almacenada en el mismo servidor que tu base de datos de producción no es una copia de seguridad real — es un único punto de fallo. Implementa las siguientes prácticas de seguridad y almacenamiento fuera del sitio:

Encripta copias de seguridad antes de transferir

gpg --symmetric --cipher-algo AES256 /backups/my_production_db.dump

Transfiere copias de seguridad a una ubicación remota con rsync

rsync -avz --progress /backups/postgresql/ user@remote-backup-server:/remote/backups/postgresql/

Usa pg_dump con tubería SSH para copia de seguridad remota directa

pg_dump -U postgres my_production_db | gzip | ssh user@remote-server "cat > /backups/my_production_db_$(date +%Y%m%d).sql.gz"

Reglas de firewall para PostgreSQL (UFW)

Restringe el acceso al puerto de PostgreSQL solo a IPs