Crear script para respaldo de base de datos PostgreSQL

Similar al post script para respaldo de bases de datos de MySQL podemos generar un bash en nuestro servidor para que genere un respaldo de nuestra base de datos deseada y posteriormente con el cron crear nuestra tarea automatizada.

Creamos un archivo con extensión .sh en la carpeta que deseemos, el script es el siguiente (le llamaremos /home/miusuario/scripts/backup.sh):

#!/bin/bash
# variables
directorio="/home/respaldos/postgresql";
database="dbbasedatos";
fecha="`date +%Y%m%d%H%M%S`";
usuario="miusuario"
host="11.1.1.1"
export PGPASSWORD=miclave

# generar el dump y crear el log
pg_dump -U $usuario -h $host -F t -d $database|gzip -9 > $directorio/$fecha.tar.gz && echo 'Respaldo realizado con exito.' >> $directorio/bitacora.log;
Aquí una breve explicación:
#Nuestras variables
directorio: el directorio donde generaremos nuestros respaldos.
database: la base de datos a respaldar.
fecha: la fecha y hora actual que la concatenaremos con nuestro nombre de archivo.
usuario: el usuario de postgresql para generar el respaldo (debe tener permisos sobre nuestra base).
host: servidor de base de datos, aquí muy importante si nuestro usuario no tiene acceso al host de localhost nos mostrara un error, deben verificar con que host fue creado.

#Nuestros comandos
export PGPASSWORD: la contraseña de nuestro usuario, en caso de no tener contraseña omitir esta linea.

Ahora viene nuestro comando que realiza toda la operación, para mas detalle puede consultar pg_dump --help a través de la consola.
-U el nombre de usuario
-h el servidor de base de datos
-F t el formato de salida, para nuestro caso es tar
-d la base de datos a respaldar
gzip -9 comprime nuestra salida con el parámetro de la mejor compresión
$directorio/$fecha.tar.gz con nuestros parametros de las variables es donde se generara nuestro respaldo (p.e. /home/respaldos/postgresql/1701191257.tar.gz).

Nota: si nuestro usuario no tiene contraseña, podemos agregar el parámetro de -w (o bien --no-password) a la linea de nuestro comando pg_dump.

Para probarlo podemos ejecutar nuestro bash y ver nuestro resultado.

$ sh backup.sh
Hasta este punto no debería existir algún problema, solo faltaría crear nuestro cron para automatizar nuestra tarea:

$ crontab -e 

nuestras lineas:

10 1 * * * /home/respaldos/scripts/backup.sh 1> /dev/null 2> /home/respaldos/errores.log

 y listo hemos terminado.

Comprobar la ejecución de un CRON y guardar el log

Muchas veces cuando creamos un CRON necesitamos saber si la tarea se ejecutará correctamente o contiene errores, debido a que no se ejecuta en una consola visible, no podemos saber el resultado una vez que se pone en marcha. Para ello mostraremos algunos trucos que podemos usar.

Guardar la salida en un log

Con esta opción solo se guardaran los errores que se generen en un archivo que seleccionemos:

* * * * * /home/miusuario/script 1> /dev/null 2> /home/miusuario/logs/archivo.log

Hay que recordar los números de la salida (redireccionamiento con el simbolo >):

• 0 stdin (standard input): el comando
• 1 stdout (standard output): /dev/null la salida se descarta
• 2 stderr (standard error): /home/miusuario/logs/archivo.log la ruta y el archivo de log.

Con esta otra opción guardaremos tanto la salida como los errores:

* * * * * /home/miusuario/script &> /home/miusuario/logs/archivo.log

Enviar el resultado por email

Es similar a la anterior pero envía la información a un correo predeterminado.

* * * * * /home/miusuario/script 1> /dev/null 2>&1 | mail  -s "Errores del cron" correo@correo.com

Cabe mencionar que para ello debe estar instalado y configurado correctamente el comando mail.

Simular el entorno de ejecución

Esta opción permite ejecutar un cron en el momento que deseemos

$ env -i /home/miusuario/script

De esta forma no tenemos que esperar hasta cuando se ejecute de acuerdo a lo programado, podemos realizar las pruebas que sean necesarias en un entorno similar a las que hace el cron.

Script para generar respaldos MySQL mediante el CRON

Cuando tenemos varias bases de datos en nuestros servidor y ya por algun tiempo, es necesario contar con alguna herramienta que permita generar respaldos de forma automatica, con la finalidad de prevenir inconvenientes con la información que manejamos y poder recuperarnos de la eventualidad lo mas antes posible.

A continuación describiremos los pasos para poder generar nuestro script y asignarlos a las tareas automaticas de nuestro servidor.

1. Preparando todo

Generalmente el servicio de crond viene instalado por defecto en nuestro servidor y es el que se encarga de ejecutar programas, comandos o script en un horario determinado. Para saber el estado en el que se encuentra el servicio escriba la siguiente sentencia:

$ sudo service crond status

En caso de no contar con este servicio, sera necesario su instalación. Seguido sera necesario contar con una carpeta donde ingresaremos nuestros script, en mi caso dentro de la carpeta home

$ mkdir /home/miusuario/ejecutar

Creamos nuestra carpeta donde iremos guardando nuestros respaldos (lo ideal es contar con otro servidor donde tengamos estos respaldos)

$ mkdir /home/miusuario/respaldos

Para nuestro caso sera necesario contar con un usuario en MySQL que tenga los permisos suficientes para poder ejecutar los comando mysql y mysqldump, así como el permiso para generar los respaldos correspondientes a todas las bases de datos.

Verificamos que la fecha y hora de nuestro servidor sea la correcta para no tener algunos resultados no deseados,

$ date
mié ago 10 15:00:01 CDT 2015

y en caso de ser necesario definimos el horario correcto:

$ sudo date --set "2015-08-19 15:12:10"

Por ultimo, cuando ejecutemos el crond se ejecutara el editor por defecto, en la mayoría de los casos el editor vi es el que viene configurado, aquí dependerá de con cual se sienta uno más cómodo. En mi caso habilite el nano, he aquí la instrucción para habilitarlo momentáneamente:

$ sudo export EDITOR="nano"

Si deseamos que sea permanente habrá que agregarlo en el .bashrc o en el .bash_profile del usuario deseado.


2. Generamos nuestro script

Una vez que tenemos todo listo generamos nuestro script en la carpeta deseada (/home/miusuario/ejecutar):

$ nano /home/miusuario/ejecutar/respaldarMySQL.sh


#!/bin/bash

# Configurar variables de entorno
FECHA=`date +%d%m%Y-%H%M%S`
DIRECTORIO="/home/miusuario/respaldos"

# Obtener los nombres de las bases de datos
misbases=`mysql -uusuariomysql -pmiclave --skip-column-names -e'SHOW DATABASES'`

# Crear el respaldo de las bases de datos
for db in $misbases; do
    # Nombre del archivo de respaldo
    archivo="$db-$FECHA.sql.gz"
    if [ $db != "mysql" ] && [ $db != "information_schema" ]; then
        # Crear respaldo, comprimirlo
        # y si fue exitoso guardar una bitacora
        mysqldump -uusuariomysql -pmiclave --databases $db | gzip -9 > \
        $DIRECTORIO/$archivo && echo "Respaldo realizado con exito: \
        $archivo" >> $DIRECTORIO/bitacora.log
    fi
done

Nota: El caracter \ indica que la cadena continua con la linea inferior

Describiremos algunas lineas para que quede mas claro este script:

• La variable FECHA guardara la fecha y hora actual, que se concatenara para formar el nombre del archivo. (%d%m%Y-%H%M%S => 19082015-100001)
• DIRECTORIO sera la ruta donde guardaremos los respaldos
• misbases obtendra todas las bases de datos de nuestro servidor
• archivo tendra la concatenacion de la base de datos y la fecha y hora para formar el nombre de nuestro respaldo
• el if verificara que no sean las bases mysql e information_schema, las cuales no respaldaremos
• Ahora viene lo interesante:
• mysqldump generara el respaldo de cada una de las bases de datos
• el caracter | indica que una vez que se tenga el resultado de mysqldump sera como parametro del siguiente comando (gzip)
• gzip realiza la compresion del respaldo de la bd y con el caracter > se indica que se guardara en el archivo $DIRECTORIO/$archivo, si no existe el archivo se crea uno nuevo y si existe reemplazara al anterior.
• con el caracter && se indica que el siguiente comando se ejecutara solo si el anterior se ejecuto satisfactoriamente
• Si se ejecuto correctamente el respaldo y la compresion, se guarda el registro de que todo fue un exito en el archivo bitacora.log, con el caracter >> se indica que si el archivo bitacora.log no existe se creara uno nuevo y si existe se agregara una nueva linea al final del archivo
• Este proceso se realiza por cada una de las bases de datos

Para probar que funciona este script, simplemente lo ejecutamos y verificamos el resultado, no se olvide cambiar los permisos necesarios para que el archivo sea ejecutable.

3. Creamos el cron

Bueno hasta este momento ya tenemos listo el script  probado, procederemos a definir cuando se ejecutara para que se realicen los respaldos correspondientes, primero aqui una explicación de como se compone un archivo crontab:

.---------------------- Minuto (0 - 59)
| .-------------------- Hora (0 - 23)
| |  .----------------- Día del mes (1 - 31)
| |  | .--------------- Mes (1 - 12)
| |  | | .------------- Día de la semana (0 - 6) (0 = domingo)
| |  | | | .----------- Usuario
| |  | | | |    .------ Script o programa a ejecutar
↓ ↓  ↓ ↓ ↓ ↓    ↓
5 15 * * * user /home/user/ejecutar/miscript.sh
En este ejemplo se ejecutara miscript.sh a las 15:05 todos los días, todos los meses, cada dia de la semana como usuario user. Aqui se listan algunos caracteres especiales que se pueden usar:

• utilice un espacio para separar cada campo
• para varios valores separelos por comas
• para un rango de valores separelos por un guión
• el * se utiliza como comodin para incluir todos los valores
• el # al inicio de la linea se utiliza como comentario

Todos los comandos del archivo crontab deben ir en una sola linea por muy larga que sea esta, ya que no se reconocen los retorno de carros adicionales.

Ahora si creamos nuestro archivo crontab, el cual se ejectuara todos los dias de la semana de todos los meses a la 1:00am para realizar nuestro respaldo:

$ sudo crontab -e -u miusuario
00 1 * * * miusuario /home/miusuario/ejectuar/respaldarMySQL.sh

Guardamos nuestro archivo y listo, tenemos un archivo crontab preparado.

4. Algunos comentarios

Para consultar nuestros archivos crontab, utilicemos la siguiente instruccion

$ sudo crontab -l

Para editar el archivo crontab de algun usuario

$ sudo crontab -e -u miusuario

Los archivos crontab se guardan en la siguiente ruta /var/spool/cron, debe tener privilegios de root para consultar el listado.

5. Actualización (10/01/2019)

Para evitar que en nuestro script queden expuestas las credenciales de nuestro usuario que realizara el respaldo, podemos agregar las lineas de acceso al archivo my.cnf (generalmente esta en /etc/my.cnf) de la configuración del mysql, asi nuestro script solo tendra las lineas sin el usuario:

respaldarMySQL.sh

# Obtener los nombres de las bases de datos

misbases=`mysql --skip-column-names -e'SHOW DATABASES'`

.

.

.

# y si fue exitoso guardar una bitacora

        mysqldump --databases $db | gzip -9 > \

.

.

.

En nuestro archivo my.cnf agregamos las siguientes lineas:

[mysqldump]

# Usuario para el respaldo

user=usuariomysql

password=miclave

[mysql]

# Usuario para la consulta de las bases

user=usuariomysql

password=miclave

Reiniciamos nuestro servicio para que podamos ejecutar nuestro script, y listo.