Backup y restore de PostgreSQL con pg_dump y pg_restore

Respaldos sin drama

Hacer backup de PostgreSQL es sencillo una vez conoces sus herramientas. El problema es que mucha gente se entera de que los formatos existen solo cuando intenta restaurar y algo no encaja. Vamos a hacerlo bien desde el principio.

Las dos herramientas clave

PostgreSQL trae dos utilidades que debes conocer:

pg_dump: hace backup de una sola base de datos.
pg_dumpall: hace backup de todo el clúster, incluyendo usuarios y roles globales.

Y para restaurar tienes psql o pg_restore, según el formato que hayas elegido.

Formato plain (SQL puro)

Es el más simple. El resultado es un archivo de texto con sentencias SQL que reconstruyen todo:

pg_dump -U miapp -d tienda -F p -f backup_tienda.sql

Es legible, lo puedes abrir con cualquier editor y funciona incluso para restaurar en otra base de datos distinta. La desventaja: ocupa más y restaura más lento en bases grandes. Para restaurar:

psql -U miapp -d tienda_nueva -f backup_tienda.sql

Formato custom (recomendado)

El formato custom (-F c) comprime por defecto y permite restauraciones selectivas:

pg_dump -U miapp -d tienda -F c -f backup_tienda.dump

Para restaurar necesitas pg_restore:

pg_restore -U miapp -d tienda_nueva -c backup_tienda.dump

La opción -c le dice que borre los objetos antes de recrearlos, útil si restauras sobre una base que ya existe. Puedes también restaurar solo una tabla:

pg_restore -U miapp -d tienda_nueva -t clientes backup_tienda.dump

Comprimir más

Con el formato plain puedes pipear a gzip:

pg_dump -U miapp tienda | gzip > backup_tienda_$(date +%F).sql.gz

Y restaurar:

gunzip -c backup_tienda_2026-04-15.sql.gz | psql -U miapp tienda_nueva

Automatizar con cron

Crea un script /usr/local/bin/backup_pg.sh:

#!/bin/bash
DEST=/var/backups/postgres
FECHA=$(date +%Y-%m-%d_%H%M)
mkdir -p $DEST
pg_dump -U miapp -d tienda -F c -f "$DEST/tienda_$FECHA.dump"
find $DEST -name "tienda_*.dump" -mtime +14 -delete

Dale permisos de ejecución:

sudo chmod +x /usr/local/bin/backup_pg.sh

Añádelo al cron del usuario postgres:

sudo crontab -u postgres -e
# Backup cada día a las 3 de la madrugada
0 3 * * * /usr/local/bin/backup_pg.sh

El find del final elimina backups con más de 14 días para no llenar el disco. Ajusta a tu política de retención.

Autenticación sin contraseña

Para que el cron no pida clave, crea un archivo ~/.pgpass en el home del usuario:

localhost:5432:tienda:miapp:tu_clave

Y protégelo: chmod 600 ~/.pgpass. PostgreSQL lo leerá automáticamente.