Automatiser les sauvegardes de base de données avec pg_dump

pg_dump mydatabase > /backups/mydatabase.sql

Cela produit un script SQL qui reconstruit entièrement la base de données si vous l'exécutez plus tard avec `psql`. Le résultat est lisible, vérifiable, et vous pouvez y rechercher des éléments avec `grep`.

Pour les petites bases de données, c'est parfait. Pour les plus grandes, cela coince : le script est non structuré, vous ne pouvez pas restaurer des tables individuelles, et la récupération prend du temps car tout se fait séquentiellement.

Étape 1 : Choisissez le bon format

Pour les environnements de production, vous n'utilisez pas le dump SQL brut mais le format personnalisé :

pg_dump -Fc mydatabase > /backups/mydatabase.dump

`-Fc` signifie format personnalisé. Le fichier est compressé, ce qui le rend plus petit que la version SQL. Plus important : vous pouvez en restaurer des tables individuelles plus tard et lancer une restauration parallèle avec `pg_restore`. Le fichier n'est pas lisible sous forme de texte, donc la vérification ultérieure se fait avec `pg_restore -l`, pas avec `less`.

Le format répertoire (`-Fd`) divise la sauvegarde en plusieurs fichiers dans un dossier et permet des dumps parallèles :

pg_dump -Fd -j 4 -Z0 mydatabase -f /backups/mydatabase_dir

`-j 4` permet de dumper quatre tables en même temps, `-Z0` désactive la compression. C'est plus rapide pour les bases de données avec beaucoup de tables, mais vous payez en espace de stockage. Azure Document Database for PostgreSQL Flexible Server utilise ce format dans ses exemples, mais pour un serveur autonome, `-Fc` est généralement le meilleur choix : un seul fichier, compressé, flexible à restaurer.

Étape 2 : Automatisez avec cron

Faire des sauvegardes manuelles fonctionne jusqu'à ce que vous l'oubliiez. Un cronjob qui tourne chaque jour à 02h00 se trouve dans le fichier crontab de l'utilisateur postgres :

crontab -e -u postgres

Ajoutez cette ligne :

0 2 <em> </em> * /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump

La sauvegarde reçoit la date dans le nom du fichier, afin que vous sachiez de quand date chaque dump. L'heure de 02h00 est un choix, pas une loi : choisissez un moment où la base de données est peu chargée, sinon vous ressentirez le dump dans vos temps de requête.

Attention à l'échappement `\%` dans la règle cron. Sans cette barre oblique inverse, cron interprète le signe `%` comme un saut de ligne et le job ne tourne pas comme prévu.

Étape 3 : Nettoyez les anciennes sauvegardes

Une sauvegarde chaque jour, conservée sans limite, remplit votre disque. Mettez en place une politique de rétention dans la même règle cron :

0 2 <em> </em> <em> /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump && find /backups -name "mydatabase_</em>.dump" -mtime +7 -delete

La commande `find` supprime tous les dumps plus vieux que sept jours. Sept jours est une estimation de départ : déterminez vous-même jusqu'où vous voulez pouvoir remonter. Chaque jour supplémentaire coûte de l'espace de stockage ; chaque jour en moins coûte une capacité de restauration. Une entreprise qui doit montrer aux auditeurs qu'elle conserve des sauvegardes d'un an n'a rien à faire avec sept jours.

Étape 4 : Automatisez avec systemd au lieu de cron

Les distributions Linux modernes tournent sur systemd, et les timers systemd offrent une alternative à cron avec une meilleure journalisation et une gestion des dépendances. Un timer se compose de deux fichiers.

Créez `/etc/systemd/system/pg_backup.service` :

[Unit]
Description=PostgreSQL backup with pg_dump</p><p>[Service]
User=postgres
ExecStart=/usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump
ExecStartPost=/usr/bin/find /backups -name "mydatabase_*.dump" -mtime +7 -delete

Créez ensuite `/etc/systemd/system/pg_backup.timer` :

[Unit]
Description=Run PostgreSQL backup daily at 02:00</p><p>[Timer]
OnCalendar=<em>-</em>-* 02:00:00
Persistent=true</p><p>[Install]
WantedBy=timers.target

Activez le timer :

systemctl daemon-reload
systemctl enable --now pg_backup.timer

La différence avec cron réside dans le contrôle : `systemctl status pg_backup.timer` montre quand le timer a tourné pour la dernière fois et quand il tournera la prochaine fois. `journalctl -u pg_backup.service` affiche la sortie du dump. Avec cron, vous devez fouiller dans des fichiers journaux qui souvent n'existent même pas.

La règle `Persistent=true` est l'avantage que cron ne connaît pas : si le serveur était éteint à 02h00, la sauvegarde tourne immédiatement après le démarrage. Cron saute simplement ce jour-là.

Étape 5 : Sécurisez les sauvegardes

Les fichiers de sauvegarde contiennent les mêmes données sensibles que votre base de données. Un dump d'une base de données clients dans un emplacement lisible par tous est une fuite de données qui n'attend qu'un `chmod`.

Veillez à ce que le dossier de stockage ne soit accessible qu'à l'utilisateur qui exécute la sauvegarde :

chown postgres:postgres /backups
chmod 700 /backups

`pg_dump` ne prend pas en charge le chiffrement lui-même. Pour le cryptage, combinez-le avec `gpg` :

/usr/bin/pg_dump -Fc mydatabase | /usr/bin/gpg --encrypt --recipient back-up@example.com > /backups/mydatabase_$(date +\%Y\%m\%d).dump.gpg

Cela vous coûte une étape supplémentaire lors de la restauration — vous devez d'abord déchiffrer — mais cela rend un fichier de sauvegarde volé sans valeur. Pour une base de données contenant des données personnelles, ce n'est pas une option mais une obligation en vertu du RGPD.

Étape 6 : Testez la restauration

Une sauvegarde que vous ne restaurez jamais n'est pas une sauvegarde mais un fichier. Au moment où vous en avez besoin, le dump s'avère corrompu ou incomplet.

Testez régulièrement si vos dumps sont utilisables. Restaurez la sauvegarde dans une base de données séparée :

/usr/bin/createdb -U postgres restore_test
/usr/bin/pg_restore -U postgres -d restore_test /backups/mydatabase_20260825.dump

Si cela s'exécute sans erreur, le dump est intact. Supprimez ensuite la base de données de test :

/usr/bin/dropdb -U postgres restore_test

Planifiez ce test, tout comme la sauvegarde elle-même. Un test de restauration mensuel détecte la plupart des problèmes : un disque qui produit silencieusement des blocs corrompus, une version de `pg_dump` qui ne correspond plus au serveur, un script qui écrit un fichier vide parce que le mot de passe de l'utilisateur de la base de données a changé.

Étape 7 : Sauvegardez toutes les bases de données avec pg_dumpall

`pg_dump` crée une sauvegarde d'une seule base de données. Si vous gérez plusieurs bases de données sur un même cluster, les dumps individuels vous font manquer les objets globaux : rôles, utilisateurs, tablespaces. Ceux-ci ne se trouvent pas dans une base de données individuelle mais au niveau du cluster.

`pg_dumpall` y remédie :

/usr/bin/pg_dumpall > /backups/cluster_$(date +\%Y\%m\%d).sql

Cette commande dumpe toutes les bases de données ainsi que les objets globaux dans un seul script SQL. L'inconvénient : le format est toujours du SQL brut, donc pas de format personnalisé, pas de restauration parallèle. Pour une sauvegarde complète du cluster avant une mise à niveau ou une migration, c'est parfait. Pour les sauvegardes quotidiennes de bases de données individuelles, restez avec `pg_dump -Fc` et exécutez `pg_dumpall` séparément, par exemple chaque semaine.

Étape 8 : Limitez les droits de l'utilisateur de sauvegarde

L'utilisateur qui exécute la sauvegarde a accès à toutes les données de la base de données. Un compte de sauvegarde compromis est donc une base de données compromise.

Créez un utilisateur séparé qui ne peut que dumper, pas écrire :

CREATE USER backup_user WITH PASSWORD 'een_lang_wachtwoord';
GRANT CONNECT ON DATABASE mydatabase TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

Cet utilisateur ne peut rien modifier, seulement lire. Pour `pg_dumpall`, vous avez besoin de plus de droits — il doit lire toutes les bases de données et tous les rôles — donc utilisez-le uniquement lorsque c'est nécessaire.

Vérification

Vérifiez que la sauvegarde a bien été créée et qu'elle est lisible :

ls -lh /backups/
pg_restore -l /backups/mydatabase_$(date +\%Y\%m\%d).dump | head -20

La première commande affiche les fichiers avec leur taille. La seconde affiche le contenu du dump : si vous y voyez des tables et des données, le fichier n'est pas un script vide.

Pour une minuterie systemd :

systemctl status pg_backup.timer

Cela affiche la prochaine exécution planifiée et la dernière exécution.

Problèmes fréquents

Le cronjob s'exécute, mais aucun fichier n'est créé. Il est fort probable que l'utilisateur postgres n'ait pas les droits d'écriture sur le dossier de destination, ou que le mot de passe de l'utilisateur de la base de données soit incorrect. Vérifiez la sortie en exécutant manuellement la ligne cron en tant qu'utilisateur postgres et examinez le message d'erreur. Cron lui-même ne journalise souvent rien d'utile.

`pg_dump` avertit d'une différence de version. Si vous utilisez une version de `pg_dump` différente de celle du serveur, cela fonctionne parfois mais pas toujours. Installez les outils clients qui correspondent à la version de votre serveur, ou utilisez `pg_dump` directement depuis le serveur.

La sauvegarde est beaucoup plus petite que prévu. Un dump d'une base de données vide fait quelques kilo-octets. Vérifiez que vous utilisez le bon nom de base de données et que la base n'est pas vide : `psql -c "\dt" mydatabase` affiche les tables.

L'auto-hébergement signifie que vous êtes le service d'assistance. Une stratégie de sauvegarde que vous n'automatisez pas et ne testez pas est une stratégie que vous ne découvrez qu'au moment où elle échoue. Et ce moment arrive toujours un dimanche soir.