La majorité des incidents de perte de données ne se produisent pas au moment de la panne, mais au moment de la restauration : la sauvegarde existe, le script tourne depuis des mois, et c'est seulement en situation réelle qu'on découvre qu'elle est corrompue, incomplète, ou qu'elle ne couvre pas la fenêtre de temps nécessaire. Ce billet détaille comment construire une stratégie de sauvegarde PostgreSQL qui tient la route, et pourquoi "on a un cron qui fait un pg_dump" n'est pas une stratégie.
Le problème : pg_dump seul ne couvre pas tous les scénarios
pg_dump quotidien est le réflexe le plus commun, et il a une limite structurelle : il ne capture l'état de la base qu'au moment où il s'exécute. Si l'incident se produit (suppression accidentelle d'une table, migration ratée, corruption) seize heures après le dernier dump, seize heures de données sont perdues, point final. Pas de restauration partielle à "il y a dix minutes".
Le second problème est le temps de restauration lui-même. Un pg_dump au format custom restauré avec pg_restore doit reconstruire les index et les contraintes depuis zéro. Sur une base de plusieurs centaines de gigaoctets, cela peut prendre plusieurs heures, ce qui est incompatible avec un objectif de reprise (RTO) de l'ordre de la minute.
Pourquoi ça arrive : confondre sauvegarde logique et sauvegarde physique avec PITR
PostgreSQL offre deux familles de sauvegarde fondamentalement différentes, et le choix entre elles change tout.
Sauvegarde logique (pg_dump / pg_dumpall) : exporte les données sous forme de commandes SQL ou d'un format custom, indépendamment de la version ou de l'architecture PostgreSQL cible. Portable, mais lente à restaurer sur de gros volumes, et incapable de restaurer à un instant précis entre deux dumps.
Sauvegarde physique avec PITR (Point-In-Time Recovery) : combine une copie binaire des fichiers de données (pg_basebackup ou un snapshot du disque) avec l'archivage continu des journaux de transactions (WAL, Write-Ahead Log). En rejouant les WAL depuis la base de référence jusqu'à un instant T précis, on peut restaurer la base exactement comme elle était à cet instant, à la seconde près - typiquement juste avant un DROP TABLE accidentel.
C'est cette seconde approche qui donne un vrai objectif de point de récupération (RPO) fin, mais elle demande une configuration explicite que beaucoup d'équipes n'activent jamais parce que pg_dump "marche" en apparence.
Mettre en place le PITR : archivage des WAL
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
max_wal_senders = 3
Sans serveur de gestion de sauvegarde dédié, archive_command peut simplement copier les segments WAL vers un stockage durable (bucket S3, NFS distant) :
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
En pratique, gérer soi-même l'archivage WAL avec de simples scripts shell devient vite source d'erreurs (segments manquants, pas de vérification d'intégrité, pas de gestion de rétention). Les outils dédiés comme pgBackRest ou WAL-G existent précisément pour ça : ils gèrent la compression, le chiffrement, la rétention, la vérification d'intégrité et la restauration parallélisée.
# pgBackRest - sauvegarde complète puis incrémentale
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=incr backup
# Restauration à un instant précis, juste avant un DROP TABLE accidentel survenu à 14h32
pgbackrest --stanza=main --type=time \
--target="2026-02-10 14:31:55" \
--delta restore
Diagnostiquer : savoir ce qu'on restaure réellement avant l'incident
Un script de sauvegarde qui se termine sans erreur ne prouve rien sur la validité de son contenu. Un pg_dump peut se terminer avec succès sur une base dont certaines tables sont verrouillées et exclues silencieusement d'une transaction concurrente, ou sur un export tronqué si le disque de destination s'est rempli en cours de route sans que le code de retour reflète correctement le problème.
#!/usr/bin/env bash
# Vérification minimale après un pg_dump : le fichier doit être non vide
# et pg_restore doit pouvoir en lire la table des matières sans erreur
set -euo pipefail
DUMP_FILE="/backups/prod_$(date +%Y%m%d).dump"
pg_dump -Fc -d prod_db -f "$DUMP_FILE"
if [ ! -s "$DUMP_FILE" ]; then
echo "ERREUR : le fichier de sauvegarde est vide" >&2
exit 1
fi
pg_restore --list "$DUMP_FILE" > /dev/null
echo "Sauvegarde valide : $(pg_restore --list "$DUMP_FILE" | wc -l) objets"
Cette vérification reste superficielle : elle confirme que le fichier est lisible, pas que les données qu'il contient sont correctes. La seule vérification qui compte réellement est une restauration complète sur un environnement séparé, suivie de requêtes de cohérence métier (comptage de lignes attendu, vérification de contraintes applicatives).
La correction : tester la restauration, pas seulement la sauvegarde
Automatiser une restauration de bout en bout, régulièrement
#!/usr/bin/env bash
# Restaure la dernière sauvegarde sur une instance de test jetable
# et exécute des contrôles de cohérence avant de déclarer le test réussi
set -euo pipefail
RESTORE_DB="restore_test_$(date +%s)"
createdb "$RESTORE_DB"
pg_restore -d "$RESTORE_DB" --jobs=4 /backups/latest.dump
ROW_COUNT=$(psql -d "$RESTORE_DB" -t -c "SELECT count(*) FROM orders;")
if [ "$ROW_COUNT" -lt 1000 ]; then
echo "ALERTE : nombre de commandes anormalement bas après restauration ($ROW_COUNT)" >&2
exit 1
fi
echo "Restauration validée : $ROW_COUNT commandes trouvées"
dropdb "$RESTORE_DB"
Exécuté chaque semaine (ou chaque nuit pour des données critiques) sur un environnement isolé, ce script transforme "on suppose que la sauvegarde fonctionne" en "on sait qu'elle a fonctionné il y a X heures". --jobs=4 sur pg_restore parallélise la restauration des index, ce qui mesure aussi un temps de restauration réaliste - une donnée indispensable pour fixer un RTO crédible.
Mesurer et documenter RPO et RTO réels
Le RPO (combien de données peut-on perdre) et le RTO (combien de temps la restauration prend-elle) ne sont pas des chiffres qu'on décide à priori, ce sont des mesures issues de tests réels. Un PITR avec archivage WAL continu donne un RPO proche de zéro (quelques secondes, le temps que le dernier segment WAL soit archivé), mais le RTO dépend du volume de WAL à rejouer depuis la dernière sauvegarde complète - ce qui justifie des sauvegardes complètes régulières plutôt que de ne compter que sur l'archivage continu depuis l'origine.
Les compromis
L'archivage WAL continu augmente le volume de stockage nécessaire et la complexité opérationnelle (surveillance de l'espace disque sur le serveur d'archive, gestion de la rétention). Pour une base non critique avec un RPO acceptable de 24 heures, un pg_dump quotidien avec restauration testée reste une solution raisonnable et beaucoup plus simple à maintenir.
Les outils comme pgBackRest ajoutent une dépendance supplémentaire et une courbe d'apprentissage, mais évitent de réinventer la gestion de rétention, la vérification de checksums et le archivage parallèle - des aspects qui sont difficiles à bien faire avec des scripts maison.
Quand le plan standard ne suffit pas
Pour une base en réplication avec des exigences de haute disponibilité, la sauvegarde seule ne suffit pas : un réplica physique en standby (streaming replication) traite la disponibilité (basculement en quelques secondes en cas de panne du primaire), tandis que la sauvegarde avec PITR traite un scénario différent, celui de la corruption logique (un UPDATE sans WHERE exécuté par erreur, qui sera répliqué tout aussi fidèlement vers le standby). Les deux mécanismes sont complémentaires, pas substituables l'un à l'autre.
Le point à retenir : une sauvegarde qu'on n'a jamais restaurée n'est pas une sauvegarde, c'est une hypothèse. Le PITR via archivage WAL donne la granularité nécessaire pour revenir juste avant l'erreur, mais sa valeur réelle ne se mesure qu'en testant la restauration complète, régulièrement, sur un environnement séparé.
