Backup automatici di un database con pg_dump e crontab

PostgreSQL mette a disposizione uno strumento molto utile, pg_dump, che consente di effettuare un dump di un database (o di porzioni di esso) su un file di testo. Il file prodotto contiene i comandi SQL per la creazione e ripopolazione del database. E' quindi possibile utilizzare pg_dump per il backup dei propri database, magari utilizzando assieme ad una schedulazione automatica quale quella di crontab.

Nel seguito viene presentato un semplice script shell che, basandosi su pg_dump, effettua il backup di un database specifico. In particolare, lo script può funzionare in tre modalità:

• single (default): effettua il backup del database e ne salva il dump in una directory con nome pari al giorno di backup (es. mer, gio, ven,...). Utile per fare il backup giornaliero del database, con rotazione settimanale.
• dated: effettua il backup del database salvando il dump in un file isolato che include le informazioni relative alla data di backup. Utile per effettuare un checkpoint del database ad una specifica data; il backup non viene mai sovrascritto.
• isolated: simile a single, ma memorizza i dump in una directory specifica per ogni database.

In sostanza, supponendo di dover fare il backup del database myDB, e immaginando di voler memorizzare i backup nella directory /backup/postgresql si ottiene il seguente layoyut per la modalità single:

+ /backup
+ postgresql/
+ lun/
  myDB.sql
+ mar/
  myDB.sql
+ mer/
  myDB.sql
+ gio/
  myDB.sql
+ ven/
  myDB.sql
+ sab/
  myDB.sql
+ dom/
  myDB.sql

Si noti che ogni backup è contenuto in una cartella relativa al giorno della settimana nel quale è stato effettuato il backup. Nel caso della modalità dated si ottiene invece:

+ /backup
+ postgresql/
 + myDB/
   myDB_01_01_08.sql
   myDB_05_01_08.sql

Ossia si isola una directory con nome uguale a quello del database che si sta backuppando, e al suo interno si memorizzano file diversi secondo la data di backup (si noti che la data viene memorizzata nel nome del file). Infine, per il caso isolated si ha che:

+ /backup
+ postgresql/
 + myDB/
   + lun/
     myDB.sql
   + mar/
     myDB.sql
   + mer/
     myDB.sql
   + gio/
     myDB.sql
   + ven/
     myDB.sql
   + sab/
     myDB.sql
   + dom/
     myDB.sql

ossia simile a quella single, dove però i giorni della settimana sono memorizzati all'interno di una directory specifica per il database.


Lo script è il seguente:

#!/bin/bash

DATABASE_NAME=$1
USERNAME=$2
HOST=$3
WORKING_DIR=$4
RUNNING_MODE=$5
PG_DUMP=/usr/lib/postgresql/8.2/bin/pg_dump


if [ "$RUNNING_MODE" = "--dated" ]
then
DATA=`date +'%h_%d_%y'`
WORKING_DIR="${WORKING_DIR}"/"${DATABASE_NAME}"
OUTFILE="${DATABASE_NAME}_${DATA}.sql"
else
DATA=`date +'%a'`

if [ "$RUNNING_MODE" = "--isolated" ]
    then
    WORKING_DIR="${WORKING_DIR}"/"${DATABASE_NAME}/${DATA}"
    OUTFILE="${DATABASE_NAME}.sql"

else
# data = giorno della settimana
    WORKING_DIR="${WORKING_DIR}"/"${DATA}"
    OUTFILE="${DATABASE_NAME}.sql"
fi

fi


# Creo la directory se non esiste
if ! test -d "$WORKING_DIR"
then
mkdir "$WORKING_DIR" > /dev/null 2>&1
if [ $? -ne 0 ]
    then
    echo "Errore nella creazione della directory $WORKING_DIR"
    exit
fi
fi



# entro nella directory di lavoro
cd "$WORKING_DIR"
START_TIME=`date`                # istante di inizio


# esecuzione del backup effettivo
$PG_DUMP --create --column-inserts -f $OUTFILE --encoding=Latin1 -U $USERNAME -h $HOST  $DATABASE_NAME
END_TIME=`date`

Come si può notare lo script prepara anzitutto la directory che deve contenere i file di dump, a seconda della modalità con la quale lo script viene eseguito. Successivamente ci si sposta nella directory relativa e si effettua il backup mediante pg_dump. L'opzione --column-inserts ordina a pg_dump di inserire nel file di dump dei comandi INSERT per la popolazione delle tabelle, al posto del default COPY. Si noti che quest'ultimo è più veloce e consente di ridurre la dimensione del file di dump (che contiene meno caratteri), ma la soluzione con INSERT risulta maggiormente portabile anche fra database differenti.

Si noti come lo script accetti i parametri relativi all'host al quale occorre connettersi e con quale username. Solitamente è bene specificare l'utente amministratore per il database, avendo cura di verificare il file pg_hba.conf affinché contenga un'entry che consenta la connessione dall'host dal quale si effettua il backup.

Qualora il database richieda una password per l'utente specificato, occorre specificare tale password mediante il file .pgpass, che deve trovarsi nella directory dell'utente che lancia lo script di backup. Il file .pgpass ha la seguente struttura:

host:porta:database:utente:password

come ad esempio

sede:5432:myDB:luca:L)c(
192.168.1.2:5432:postfix:Mailer:$escCTRL

A questo punto è possibile inserire la schedulazione dello script in cron, con ad esempio delle entry simili alle seguenti:

00 19 * * 5 backup_postgresql.sh   myDB luca     sede /backup/vari/postgres   --dated
00 19 * * 1-4 backup_postgresql.sh myDB luca     sede /backup/vari/postgres

Si noti che per cinque giorni la settimana viene fatto il backup a rotazione settimanale (modalità single), mentre una volta alla settimana viene effettuato quello dated. In questo modo si ha un backup per ogni giorno della settimana, e un backup persistente per ogni settimana.