Sauvegarder PostgreSQL vers S3 : de pg_dump à la restauration vérifiée

La plupart des guides de sauvegarde PostgreSQL s'arrêtent à la ligne de commande pg_dump. C'est la partie facile. Ce qui décide si vous récupérez vos données, c'est tout ce qui l'entoure : ce que le dump laisse dehors, le format choisi, le fait que le script échoue vraiment quand le dump échoue, et le fait que quelqu'un ait déjà rejoué le fichier au moins une fois.

Ce guide monte la chaîne entière. À la fin, vous saurez ce qu'une sauvegarde PostgreSQL doit contenir en plus de la base, comment choisir entre SQL brut, custom et directory, comment écrire un script de sauvegarde qui ne peut pas annoncer un succès sur un fichier vide, comment restaurer le dump à la main dans un conteneur jetable, et quelles quatre requêtes SQL disent qu'une base restaurée contient réellement vos données.

Tout cela fonctionne avec pg_dump, docker et l'AWS CLI, et rien d'autre. La dernière section montre comment faire tourner cette même restauration et ces mêmes vérifications sur une planification plutôt qu'à la main — c'est le travail de RestoreProof — mais la procédure tient debout seule, et le jour où ça compte, c'est elle que vous suivrez.

Ce qu'une sauvegarde PostgreSQL doit contenir

pg_dump sauvegarde une base : ses tables, ses données, ses vues, ses fonctions. Il ne sauvegarde pas ce qui vit à côté, au niveau du serveur :

• les rôles et leurs mots de passe ;
• les paramètres appliqués au serveur (postgresql.conf, pg_hba.conf) ;
• les autres bases du même serveur.

Les rôles se récupèrent avec pg_dumpall --globals-only, qui produit un petit fichier SQL séparé. Sans lui, la restauration se fait, mais les comptes applicatifs n'existent plus et personne ne peut se connecter.

Les extensions, elles, sont dans le dump — sous la forme d'un CREATE EXTENSION. Elles doivent donc être installées sur le serveur où vous restaurez, sinon le dump s'arrête sur cette ligne. C'est le piège classique de postgis et de pgcrypto.

Choisir le format du dump

Pour une base de quelques gigaoctets, le SQL brut compressé suffit et reste le plus simple à inspecter. Au-delà, directory avec -j divise le temps de dump et de restauration par le nombre de cœurs que vous lui donnez.

Ne compressez pas un dump -Fc ou -Fd : il l'est déjà, et le regzipper ne fait que consommer du processeur pour quelques pourcents.

Le script de sauvegarde

#!/bin/bash
set -euo pipefail

ts=$(date +%Y%m%d_%H%M%S)
dest=s3://sauvegardes-facturation/postgres

pg_dump -h db.interne -U sauvegarde -d facturation --no-owner --no-acl \
  | gzip > "/backups/facturation_${ts}.sql.gz"

pg_dumpall -h db.interne -U sauvegarde --globals-only \
  | gzip > "/backups/globals_${ts}.sql.gz"

aws s3 cp "/backups/facturation_${ts}.sql.gz" "${dest}/"
aws s3 cp "/backups/globals_${ts}.sql.gz" "${dest}/"

--no-owner --no-acl retire du dump les propriétaires et les droits d'origine : sans eux, la restauration réclame des rôles qui n'existent que sur le serveur de production.

Sans pipefail, un dump raté ressort en succès

Dans pg_dump | gzip, le shell ne regarde que le code de sortie du dernier maillon. gzip réussit à compresser une sortie vide, donc le script rend 0 et le cron est content. set -o pipefail — inclus dans le set -euo pipefail ci-dessus — fait échouer la ligne entière dès que pg_dump échoue. C'est la première cause de sauvegardes vides qui passent au vert pendant des mois.

Gardez l'horodatage dans le nom du fichier : un fichier toujours écrasé au même nom ne laisse aucune chance de revenir à la veille. Pour la rétention, une règle de cycle de vie sur le bucket fait le travail sans script à maintenir : les objets de plus de N jours sont supprimés par le stockage lui-même.

Reste à le lancer chaque nuit. Un cron suffit, à condition de deux choses : que sa sortie d'erreur aille quelque part où quelqu'un la lit, et qu'une exécution qui s'éternise n'en croise pas une autre — flock -n sur un fichier verrou règle le second point en une ligne.

Restaurer une fois, à la main

Une sauvegarde n'est prouvée que restaurée. Faites-le une fois, entièrement, sur un poste jetable — c'est aussi la procédure que vous suivrez le jour où ça compte.

docker run -d --name pg-essai -e POSTGRES_PASSWORD=essai postgres:17-alpine
until docker exec pg-essai pg_isready -q; do sleep 1; done

docker exec pg-essai createdb -U postgres facturation
gunzip -c /backups/globals_20260918_010000.sql.gz \
  | docker exec -i pg-essai psql -U postgres -v ON_ERROR_STOP=1 -d postgres
gunzip -c /backups/facturation_20260918_010000.sql.gz \
  | docker exec -i pg-essai psql -U postgres -v ON_ERROR_STOP=1 -d facturation

ON_ERROR_STOP=1 est indispensable : sans lui, psql continue après une erreur et vous finissez avec une base à moitié restaurée qui a l'air de marcher.

Prenez le temps que ça a pris. C'est votre durée de restauration réelle, la seule à comparer au délai que vous avez promis.

Ce qu'on vérifie dans une base restaurée

Le dump s'est rejoué sans erreur ne veut pas dire que les données sont là. Quatre questions, dans cet ordre :

select count(*) from information_schema.tables where table_schema = 'public';
select count(*) from factures;
select max(created_at) from factures;
select last_value from factures_id_seq;

1. Les tables sont là. Zéro table, c'est un dump vide qui s'est rejoué parfaitement.
2. Les lignes sont là. Comparez à l'ordre de grandeur de la production, pas à un chiffre exact : une base qui grossit, c'est normal.
3. Les données sont récentes. La date la plus récente doit être celle d'hier, pas celle du mois dernier. C'est ce qui attrape un job de sauvegarde qui a cessé de tourner.
4. Les séquences ont suivi. Une séquence restée à 1 provoque des collisions de clés dès la première écriture après la restauration.

Puis détruisez tout : docker rm -f pg-essai.

Automatiser cette vérification

Ce qui précède coûte une heure ou deux, à chaque fois. C'est pour cette raison que ces tests, faits à la main, finissent par ne plus être faits.

RestoreProof rejoue exactement ces étapes en tâche planifiée, sur votre infrastructure : un runner récupère la sauvegarde, la restaure dans un conteneur jetable, pose les mêmes questions que ci-dessus, détruit tout, et signe le résultat. Les données ne sortent pas de chez vous.

Déclarez d'abord la sauvegarde comme source — le bucket, le préfixe, le motif *.sql.gz, et la stratégie le plus récemment modifié. Les clés d'accès ne se saisissent pas : le plan porte une référence, env://AWS_ACCESS_KEY_ID, que le runner résout dans son propre environnement. Voir les références de secrets.

L'assistant PostgreSQL écrit ensuite le plan, et ce plan est la manipulation que vous venez de faire à la main, ligne pour ligne :

Le seul ajout est max_age: 26h, et c'est celui qu'une restauration ne peut pas déduire du contenu : il fait échouer l'exécution quand la sauvegarde la plus récente trouvée à la source est plus vieille que ce délai. Le plan complet, prêt à coller dans l'éditeur, est dans la recette bases de données.

La version du bac à sable

postgres:17-alpine doit reprendre la version majeure de votre serveur. Un dump pris sur un serveur plus récent ne se rejoue pas sur un serveur plus ancien.

Les seuils ne se recopient pas depuis cette page. Un essai restaure votre sauvegarde, compte ce qu'elle contient réellement, et propose chaque seuil 5 % sous la valeur mesurée, avec l'opérateur gte.

Il reste à choisir une fréquence. Chaque nuit place l'exécution à 2 h du matin : votre script de sauvegarde tourne à 1 h, le dump a donc une heure quand il est éprouvé. L'autre déclenchement possible est un appel HTTP à la fin de ce script — le test porte alors exactement sur le fichier qui vient d'être produit.

Chaque exécution laisse un rapport horodaté, signé, qui nomme la sauvegarde éprouvée et ce que chaque sonde a mesuré.

Ce que cette chaîne ne prouve pas

Elle prouve qu'une sauvegarde récente se restaure dans un PostgreSQL neuf et qu'elle contient les données attendues. Elle ne prouve pas que votre application fonctionne sur cette base : pour cela, il faut la démarrer contre le bac à sable et ajouter une sonde http. Elle ne dit rien non plus de ce qui a été créé depuis le dernier dump — cet écart, c'est votre RPO, et il se règle avec la fréquence des sauvegardes, pas avec les tests.

Éprouver ses sauvegardes sans y penser

RestoreProof rejoue ces étapes sur votre infrastructure, aussi souvent que vous le décidez : il récupère la sauvegarde, la restaure dans un conteneur jetable, pose les mêmes questions, détruit tout, et signe le résultat. Vos données ne sortent pas de votre réseau.

FAQ

Un job de sauvegarde en vert ne suffit-il pas ?

Il veut dire que le job s'est terminé sans signaler d'erreur. Il ne dit rien du fait que le fichier se rejoue. Un pg_dump | gzip sans pipefail rend un succès sur un fichier vide. Un dump pris par un rôle qui ne voit pas toutes les tables se restaure parfaitement avec la moitié des données. Une planification qui a cessé de déclencher laisse derrière elle un dump qui se restaure encore. Seule une restauration, suivie d'un comptage, distingue une sauvegarde qui existe d'une sauvegarde qui se restaure.

Qu'est-ce que pg_dump ne sauvegarde pas ?

Les rôles et leurs mots de passe, la configuration du serveur (postgresql.conf, pg_hba.conf) et les autres bases du même serveur. Les rôles se récupèrent séparément avec pg_dumpall --globals-only. Les extensions sont dans le dump sous la forme d'un CREATE EXTENSION, ce qui veut dire qu'elles doivent être installées sur le serveur où vous restaurez, sinon le dump s'arrête sur cette ligne.

À quelle fréquence tester la restauration d'une base de production ?

Aussi souvent que la sauvegarde est produite, si c'est tenable : un test nocturne révèle une sauvegarde cassée le lendemain matin, pas le jour où on en a besoin.

Comment détecter une sauvegarde qui a cessé d'être produite ?

Par son âge, pas par son contenu : un dump de mars se restaure parfaitement en septembre. Il faut donc une vérification qui échoue quand la sauvegarde la plus récente est trop vieille, et une requête sur la donnée la plus récente de la base restaurée.

Que demande un auditeur ISO 27001 ou NIS2 ?

La trace que la procédure de restauration a été exercée, pas seulement écrite : quand le test a tourné, sur quelle sauvegarde, ce qui a été vérifié et avec quel résultat. Un rapport daté qui nomme la sauvegarde restaurée et ce que chaque vérification a mesuré répond directement ; une capture d'un tableau de bord de sauvegarde en vert, non.