Après une journée de tentatives pour restaurer une base de données PostgreSQL à partir de sauvegardes systèmes, j’ai décidé de revoir la politique de sauvegardes de certains serveurs.

Je pense que les sauvegardes applicatives sont aussi importantes, si ce n’est plus, que les sauvegardes systèmes.

Nous allons donc voir comment sauvegarder l’intégralité d’un serveur PostgreSQL à partir de la ligne de commande.

Préparation du serveur

Pour cela, nous devons créer un nouvel utilisateur avec seulement le droit de lecture sur toutes les bases.
Il sera utilisé pour se connecter au serveur et effectuer les sauvegardes des différents base de données.

$ su - postgres
$ psql
postgres=# CREATE USER backup WITH ENCRYPTED PASSWORD 'MaMTNv3B';

L’authentification de l’utilisateur se fera avec mot de passe crypté au format md5.
Nous pouvons tout aussi bien autoriser l’utilisateur à se connecter sans mot de passe.

Maintenant nous devons autoriser la connexion depuis une autre machine sur le serveur.

$ tail -1 /var/lib/pgsql/data/pg_hba.conf
host    all     backup  192.168.100.100  md5

On retrouve notre utilisateur backup avec son mot de passe encrypté au format md5. Il est autorisé à se connecter sur le serveur depuis la machine cliente 192.168.100.100.

Il faut indiquer au serveur de relire son fichier de configuration pour prise en compte de l’accès de notre nouvel utilisateur backup.

$ su - postgres
$ pg_ctl reload

Sauvegarde et restauration

Il y a 2 possibilités pour sauvegarder le serveur PostgreSQL.

1. Base par base

Sauvegarde :

$ pg_dump -h 192.168.100.20 -u backup -p ma_base > /tmp/ma_base.dump

Le fichier produit contiendra toutes les commandes SQL nécessaires pour reconstruire la base de données.

Restauration :

$ psql -h 192.168.100.20 -u backup -p ma_base < /tmp/ma_base.dump

2. Serveur complet
Le problème avec la sauvegarde précédente c'est que les rôles et les tablespaces définis sur le serveur ne sont pas sauvegardés, seulement les bases de données.
A MODIFIER CAR DEMANDE DROITS ADMIN SUR LE SERVEUR ET NON BACKUP !!!
Heureusement il existe une commande permettant de faire cela :

$ pg_dumpall -h 192.168.100.20 -u backup -p > /tmp/postgesql_full.dump

La restauration se faisant ainsi :

$ psql -h 192.168.100.20 -u backup -p ma_base > /tmp/ma_base.dump

Script de sauvegarde

Pour finir, le script de sauvegarde qui tourne toutes les nuits sur les serveurs à sauvegarder.

#!/bin/bash
# ------------------------------------- #
# Sauvegarde du serveur PostgreSQL base par base
# ~ Jaax ~
# Date: 19/10/2010
# ------------------------------------- #

export PGHOST="192.168.100.20"
export PGUSER="backup"
export PGPASSWORD="MaMTNv3B"

LOG="/root/scripts/logs/backup_postgresql-`date +%Y%m%d`.log"
BACKUP_DIR=/media/postgresql
DATE=`date +%Y%m%d`
PSQL=/usr/bin/psql
PGDUMP=/usr/bin/pg_dump

echo "[START] `date +%H:%M`" > $LOG
echo "[INFO] Lancement des sauvegardes PostgreSQL" >> $LOG

if [ ! -d $BACKUP_DIR/$DATE ]
then
echo -e "[INFO] Creation du repertoire date" >> $LOG
mkdir $BACKUP_DIR/$DATE
fi

# Recuperation des base de donnees
for DB in `$PSQL -A -l -t | awk -F '|' '{print $1}'`
do
echo -e "t[+] Database: $DB" >> $LOG
echo -e -n "tt[+] Sauvegarde: " >> $LOG

# Sauvegarde de la base de donnees
$PGDUMP -Fc $DB > $BACKUP_DIR/$DATE/$DB.dump
RESULT=$?

[ "$RESULT" -eq 0 ] && echo "Ok" >> $LOG || echo "Ko" >> $LOG

echo >> $LOG
done

echo "[FIN] `date +%H:%M`" >> $LOG

exit 0

Si vous avez des remarques ou juste pour dire un petit "merci", laissez un commentaire.