Como realizar backup físico y/o lógico en PostgreSQL

Primero vamos a ver la diferencia entre los backups físicos y lógicos para saber de que estamos hablando.

1) Diferencias entre backups físicos y lógicos

• Backup lógico
  • Es un backup con instrucción SQL para recrear la estructura y datos y lo realizamos con pg_dump
  • Es independiente de la versión y arquitectura del servidor de PostgreSQL por lo que puede ser compatible entre versiones.
  • Los archivos resultantes son legibles y editables
  • Se puede hacer backups de bases de datos independientes y no necesariamente de todo el servidor. Permite hacer backups y restores selectivos.
  • No hace backup de los ficheros de configuración de PostgreSQL.
  • Para hacer backup utilizamos pg_dump para bases individuales y pg_dumpall para todas las bases
  
  
• Backup físico
  • Consiste en copiar los archivos físicos del clúster PostgreSQL, incluyendo datos, WALs (logs), y configuración.
  • Es dependiente de la versión y arquitectura, se debe restaurar en el mismo entorno.
  • No es legible ni editable en forma directa.
  • No permite restauraciones parciales ni selectivas.
  • Permite recuperación punto en el tiempo (PITR).
  • Uitliza pg_basebackup para el backup se pueda hacer en caliente y se puede usar rsync o copias filesystem pero para usar estas hay que parar el servidor.

2) Backup y restore de un backup físico

Es una buena opcón para hacer un backup de una instancia PostgreSQL completa (todas las bases de datos + configuración). Es útil para recuperación ante fallos o réplicas.

El comando para realizar el backup físico es:

pg_basebackup -U tu_usuario -h localhost -D /ruta/destino -Fp -Xs -P -v

Donde:

• -U tu_usuario: Usuario con permisos (normalmente replication o postgres).
• -h localhost: Host del servidor PostgreSQL, si estas en el mismo servidor no es necesario.
• -D /ruta/destino: Carpeta donde se almacenará el backup.
• -Fp: Formato plano (también puedes usar -Ft para formato tar).
• -Xs: Incluir archivos de WAL necesarios para consistencia.
• -P: Mostrar progreso.
• -v: Verbose.

Y para restaurar un backup fisico hay que reempazar el directorio data de la instancia de PostgreSQL parando el servidor.

El directorio de datos se encuentra normalmente en /var/lib/postgresql/16/main pero por si alguna distribución lo cambia de sitio lo podeis buscar haciendo un filtro al fichero de configuración postgresql.conf de la siguiente forma

cat /etc/postgresql/16/main/postgresql.conf | grep data

Y os dará un resultado similar a este

3) Backup y restore de un backup lógico

Este método es el más común para respaldar una base de datos, o muchas, en formato SQL, personalizado o comprimido.

El comando utilizado es:

pg_dump -U tu_usuario -h localhost -F c -b -v -f backup_nombre.backup nombre_base_datos

Donde:

• -U tu_usuario: Usuario de PostgreSQL con permisos suficientes.
• -h localhost: Host donde corre PostgreSQL (puede ser una IP o nombre DNS).
• -F c: Formato personalizado (también puedes usar t para tar o p para texto plano).
• -b: Incluir datos binarios (por ejemplo, objetos grandes - LOBs).
• -v: Salida detallada (verbose).
• -f backup_nombre.backup: Nombre del archivo de salida.
• nombre_base_datos: Nombre de la base de datos a respaldar.

Para restaurar el backup utilizaremos el comando pg_restore

pg_restore -U tu_usuario -d nombre_base_datos -v backup_nombre.backup

Si con pg_dump NO se utilizo -F c, se podra restaurar con psql

psql -U tu_usuario -d nombre_base_datos < backup.sql

4) Cuando usar cada uno

Aqui depende de cada uno pero ...

• Backup Lógico
  • Respaldar una sola base de datos o tabla.
  • Migrar entre versiones de PostgreSQL.
  • Automatizar respaldos diarios.
  • Restaurar en otro servidor con diferente configuración.
• Backup Físico
  • Recuperación ante desastres (fallo del servidor o disco).
  • Configurar réplicas streaming.
  • Restauración completa y rápida del clúster.
  • Habilitar recuperación punto en el tiempo (PITR).

Buenas practicas podrían ser:

• Lógicos diarios o semanales para respaldos parciales.
• Físicos periódicos (por ejemplo, cada noche) como respaldo completo del sistema.

5) Conclusiones

Como podemos ver hay varias formas para realizar los backups en PostgreSQL dos formas que los realizan de formas diferentes para necesidades diferentes.

Utilizar cada una de la forma que mejor os vaya.

Y recordar un backup no sirve de nada si no se prueba que ha funcionado correctamente.


... y esto es todo amig@s!!! hasta la próxima ...
Saludos
Alex
:-)
/