Programar copias de seguridad automáticas de PostgreSQL

Si hace unos días hablábamos de cómo hacer backup y restaurar copias de seguridad con MySQL, hoy vamos a hablar de cómo hacerlo con PostgreSQL, incluyendo un detalle más: la automatización para que se ejecute de forma periódica y automática.

pg_dump

Si con MySQL usamos mysqldump, con PostgreSQL utilizamos pg_dump. ¿Cuál es la sintaxis básica?

pg_dump -h direccion_servidor -U nombre_usuario nombre_base_datos > "fichero.sql"

Cuando ejecutas te pide tus credenciales y se genera el fichero con el volcado. Recuerda que para poder autenticarte usando la contraseña, debes indicar método “PASSWORD” dentro del fichero pg_hba.conf (más información en esta entrada: Permitir conexiones entrantes a un servidor PostgreSQL).

Fichero pgpass

pg_dump no permite como mysqldump pasarle como parámetro la contraseña, de forma que a la hora de automatizar el proceso a través de un cron deberemos usar una de las siguientes dos técnicas:

• Crear un fichero pgpass
• Crear una variable de entorno

Elegimos el primer método por flexibilidad.

Debemos crear un fichero de .pgpass en el raíz de nuestro usuario con los permisos muy restringidos: 600 como mucho (lectura y escritura para el usuario), tened en cuenta que el fichero va a contener credenciales.

Se trata de un fichero en texto plano con la siguiente estructura:

servidor:puerto:base_datos:usuario:contraseña

Para comprobar que todo marcha correctamente podemos ejecutar:

$ psql -h direccion_servidor -U usuario base_datos

Si la conexión es válida y no nos pide contraseña, el fichero .pgpass funciona correctamente (para salir recuerda “\q” en el intérprete).

Ya tenemos la conexión configurada, sólo nos queda configurar el crontab para automatizar la copia.

Automatización con crontab

Para terminar, vamos a crear un pequeño script que automatice esta tarea y lo vamos a programar  para que se ejecute periódicamente gracias a cron. Lo primero será crear un fichero .sh con permisos de ejecución con la orden de copia de seguridad. Si queréis que os genere un fichero del estilo “db_back_Lunes.sql”, podéis usar el siguiente código:

#! /bin/bash
fecha=`date +%A`
pg_dump -h direccion_servidor -U usuario base_datos > "db_back_$fecha.sql"

Ahora accedemos a crontab (situado en /etc) lo editamos y estará listo, agregamos una línea como la siguiente para que se ejecute a diario (en esta línea en concreto se ejecuta a las 2:00 am, buena hora para este tipo de operaciones):

0 2 * * * usuario /ruta_a_fichero/backup_pg.sh

Esto es todo, si tenéis dudas, objeciones, correcciones o comentarios estaremos encantados de atenderos vía comentarios.