Copia de seguridad lógica y restauración de PostgreSQL (pg_dump / pg_restore)

Copia de seguridad lógica y restauración con pg_dump, pg_dumpall y pg_restore a nivel de tabla, esquema, base de datos o cluster completo; solo estructura (DDL) o solo datos, traslado a otro servidor/versión, restauración de una sola tabla desde un dump y copias programadas con systemd/cron, verificadas y con periodo de retención. Cada camino termina con una verificación que compara el número de filas. En streaming replication y en clusters Patroni el dump se toma de una réplica (puerto de lectura de HAProxy o el standby) y la restauración se hace solo en el primary/leader.

Una copia lógica vuelve al momento del dump y no permite recuperación a un punto en el tiempo (PITR); en producción la copia principal debe ser física: pgBackRest (recomendado) o, sin herramientas adicionales, pg_basebackup + archivo de WAL. Para trasladar una base de datos grande a una nueva versión con segundos de interrupción, use logical replication; para una actualización in situ en el mismo servidor, pg_upgrade.

Topología: 1 servidor, streaming replication (primary + standby) o un cluster Patroni; en un traslado, origen + destino; en copias remotas programadas, además un servidor de copias

Escenarios

• Exportar / importar una o varias tablas — Se vuelcan la estructura y los datos de las tablas seleccionadas (incluidos índices, constraints y triggers) y se cargan en otra base de datos.
• Exportar / importar un esquema — Se vuelcan todos los objetos del esquema (tablas, vistas, funciones, secuencias).
• Copia / restauración de una sola base de datos — Una base de datos completa (estructura + datos) se vuelca en un único dump y se restaura en la misma o en otra base de datos. Los roles (globals) no se incluyen.
• Cluster completo (roles + tablespaces + cada base de datos) — Globals con pg_dumpall -g y un pg_dump separado por cada base de datos; la restauración se ensaya en un cluster de prueba temporal.
• Solo la estructura del esquema (DDL) — Definiciones de tablas, índices, vistas y funciones sin datos; para revisión, control de versiones o para montar un entorno vacío.
• Solo datos — Se transfieren solo las filas (COPY) cuando la estructura ya existe en el destino.
• Trasladar a otro servidor / versión — En el servidor de destino, el pg_dump de la nueva versión se conecta al origen por la red; los datos se transfieren mediante un pipe o un archivo.
• Restaurar una sola tabla / objeto desde un dump — A partir de un dump custom/directory existente se restaura solo una tabla, con sus índices, constraints y datos, mediante un archivo de lista (-L).
• Copia programada (systemd timer / cron) — Copia diaria de globals + bases de datos, comprobación de legibilidad, retención, log y notificación de errores.

Antes de empezar

• …El cluster Patroni está en funcionamiento; en este servidor están instaladas las herramientas cliente de PostgreSQL … (…) y se alcanzan los puertos de escritura (…)… y de lectura (…)… de HAProxy/VIP (…). Dispone de la contraseña del rol postgres (con Patroni, PATRONI_SUPERUSER_PASSWORD en /etc/patroni/patroni.env).…PostgreSQL … se ejecuta en el primary y el usuario postgres puede conectarse localmente (peer); el standby … está en estado streaming y su puerto … es accesible desde el primary. Como el dump se conecta al standby por la red, el rol postgres debe tener contraseña (si no la tiene, ejecute \password postgres en el primary; la contraseña llega al standby a través del WAL).…PostgreSQL … está en funcionamiento y el usuario postgres del sistema operativo puede conectarse localmente (peer). Si se instaló con la guía de DBAtlas Instalación de PostgreSQL en servidor único, las rutas son las mismas.…
• …El cluster está sano: un Leader, al menos una réplica en streaming y retraso bajo. Compruébelo en uno de los nodos Patroni.…El standby … está conectado al primary y su retraso es bajo (el dump toma la imagen del standby en ese momento).…
• El directorio de copias … está en un disco separado del disco de datos y tiene espacio libre suficiente: un dump custom/directory comprimido suele ocupar el 10–30% del tamaño de la base de datos (los índices entran en el dump solo como definición); el formato plano y los dumps sin compresión son mayores.
• pg_dump es de la misma versión mayor o más reciente que el servidor del que se hace la copia (un pg_dump nuevo puede leer un servidor antiguo; al revés no funciona). pg_restore debe ser de la misma versión o más reciente que el pg_dump que creó el dump.
• pg_dump/pg_restore en el destino (…) son de la misma versión mayor o más reciente que el servidor de origen (…): el traslado se hace con las herramientas nuevas del destino.
• El software de las extensiones usadas en el origen (postgis, pgvector, pg_partman…) también está instalado en el servidor de destino. Una copia lógica solo traslada la línea CREATE EXTENSION, no la extensión en sí.
• Si hay tablespaces definidos por el usuario (la consulta de abajo devuelve filas), deben prepararse los mismos directorios en el destino o la restauración debe hacerse en el tablespace por defecto con --no-tablespaces.
• En el servidor de destino … PostgreSQL … está instalado, en funcionamiento y vacío (solo postgres, template0, template1). El destino alcanza el puerto … del origen.
• El servidor de copias … alcanza …el puerto … de la dirección HAProxy/VIP ……el puerto … del standby ……el puerto … del servidor de base de datos… y tiene acceso al repositorio PGDG (o a un mirror interno).
• El servidor puede enviar correo: el MTA local (postfix) reenvía al relay SMTP corporativo.
• Hay una ventana de mantenimiento planificada para una restauración o un traslado grande; si se va a sobrescribir el destino, se ha hecho una copia/snapshot del destino.

Fases de la guía

• Preparación — Crear el directorio de copias · Guardar la contraseña de conexión en .pgpass · Permitir el acceso en pg_hba.conf del standby · Verificar a qué rol llegan las conexiones · Comprobar el tamaño de las bases de datos y las transacciones largas
• Restauración (import) — Preparar la base de datos de destino · Preparar el destino: extensiones, esquemas y funciones de trigger · Restaurar · Restaurar sobrescribiendo el destino · Recopilar estadísticas (ANALYZE) · Comparar el número de filas
• Solo la estructura del esquema (DDL) — Hacer el dump del DDL · Crear la base de datos de destino · Aplicar el DDL en el destino · Verificar que las estructuras son idénticas
• Solo datos — Hacer el dump de datos · Crear la base de datos de destino · Crear la estructura de las tablas en el destino · Vaciar las tablas de destino · Cargar los datos · Recopilar estadísticas y comparar el número de filas
• Copia del cluster completo — Copiar los globals y todas las bases de datos · Verificar que la copia es legible
• Ensayo de restauración (cluster de prueba temporal) — Preparar el directorio de prueba · Crear e iniciar el cluster temporal · Cargar los globals · Cargar las bases de datos · ANALYZE y comparación del número de filas · Detener y eliminar el cluster temporal
• Permiso de acceso en el servidor de origen — Permitir el servidor de destino en pg_hba.conf · Permitir el servidor de destino en pg_hba (Patroni) · Abrir el puerto al destino en el firewall · Detener la aplicación (punto de corte)
• Transferencia en el servidor de destino — Guardar la contraseña de conexión al origen en .pgpass · Comprobar la compatibilidad de versión y extensiones · Trasladar los roles (globals) · Transferir las bases de datos (mediante archivos, en paralelo) · Transferir las bases de datos (mediante pipe) · Recopilar estadísticas (ANALYZE) …
• Después del cambio — Retirar el acceso temporal en el origen · Retirar el acceso temporal en el origen (Patroni) · Dirigir la aplicación al destino
• Restaurar una sola tabla desde un dump — Inspeccionar el contenido del dump · Crear el archivo de lista (-L) · Preparar la base de datos de destino · Restaurar la tabla · Restaurar la tabla en la base de datos original · Comparar el número de filas …
• Servidor de copias y rol de copia — Crear el rol de copia · Conceder lectura sobre los objetos grandes · Asignar contraseña al rol de copia · Permitir el servidor de copias en pg_hba.conf · Permitir el servidor de copias en pg_hba (Patroni) · Instalar las herramientas cliente de PostgreSQL (servidor de copias) …
• Script de copia — Verificar las rutas completas de las herramientas · Instalar la herramienta de notificación · Escribir el script de copia · Hacer ejecutable el script · Hacer la primera copia a mano
• Programación (systemd timer) — Escribir el servicio systemd · Escribir el systemd timer · Activar el timer y lanzar el servicio a mano
• Programación (cron) — Preparar el servicio cron · Escribir la tarea de cron · Ajustar los permisos del archivo de cron y probar en un entorno de cron · Verificar que cron ejecuta la tarea
• Verificación y monitorización — Restaurar la última copia en una base de datos de prueba · Monitorizar la antigüedad de la última copia
• Siguiente paso: copia física — Cubrir las limitaciones de la copia lógica