Aller au contenu

Sauvegarder PostgreSQL vers S3 et vérifier la restauration chaque nuit

Cette page monte la chaîne entière : ce qu'une sauvegarde PostgreSQL doit contenir, le script qui la produit et l'envoie sur S3, la restauration faite une fois à la main, puis la même vérification rejouée chaque nuit sans personne.

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

Format Commande Ce qu'il permet
SQL brut pg_dump se lit, se corrige à la main, se rejoue avec psql
custom pg_dump -Fc déjà compressé, restauration sélective, table par table
directory pg_dump -Fd -j 4 le seul qui se produise et se restaure en parallèle

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 :

À la main Dans le plan
aws s3 cp depuis le bucket fetch, qui prend le fichier le plus récent
gunzip -c unpack
docker run postgres:17-alpine start_sandbox
psql -v ON_ERROR_STOP=1 restore_postgres
les quatre requêtes une sonde postgres par question
docker rm -f le nettoyage, toujours exécuté

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.

L'écran de fin d'essai : chaque sonde avec la valeur mesurée et le seuil proposé cinq pour cent en dessous

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é.

Le détail d'une exécution : la signature Ed25519, le résultat de chaque sonde avec ses mesures, et le journal

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.

Le plan complet

À coller dans l'éditeur de plan, vue JSON (expert), en remplaçant l'identifiant de source et le nom de la table.

{
  "steps": [
    {
      "type": "fetch",
      "config": {
        "source": "source_profile",
        "source_id": "<identifiant de votre source>",
        "max_age": "26h",
        "output": "dump.sql.gz"
      }
    },
    {
      "type": "unpack",
      "config": { "format": "gzip", "input": "dump.sql.gz", "output": "dump.sql" }
    },
    {
      "type": "start_sandbox",
      "config": { "image": "postgres:17-alpine", "env": { "POSTGRES_DB": "restoreproof" } }
    },
    {
      "type": "restore_postgres",
      "config": { "database": "restoreproof", "input": "dump.sql", "format": "auto" }
    }
  ],
  "probes": [
    {
      "name": "schema",
      "type": "postgres",
      "config": { "RESTOREPROOF_PG_ASSERT": "gte", "RESTOREPROOF_PG_EXPECTED_ROWS": "1" },
      "timeout": 60
    },
    {
      "name": "factures",
      "type": "postgres",
      "config": {
        "RESTOREPROOF_PG_TABLE": "factures",
        "RESTOREPROOF_PG_ASSERT": "gte",
        "RESTOREPROOF_PG_EXPECTED_ROWS": "1"
      },
      "timeout": 60
    }
  ]
}

Et ensuite