Rappelez-vous, il y a quelques années, j’avais parlé de la migration de la base de mon jeu AIM sur PostgreSQL. C’était une bonne idée, mais je n’avais rien automatisé au niveau des sauvegardes. Donc je faisais régulièrement mes sauvegardes à la main, je coupais le jeu et hop, un bon dump qui prenait trois heures.
Puis j’ai fait le calcul du nombre de jours de données que je perdrais si le disque lâchait maintenant. Et j’ai arrêté de trouver ça drôle. Parce que perdre 15 jours de jeu, ça peut faire pleurer.
Voilà donc le récit de comment je suis passée du pg_dump artisanal à une vraie automatisation avec pgBackRest, ce que ça m’a appris sur les enjeux réels d’une sauvegarde, et surtout ce que tout ça coûte (ou pas) aux performances du SGBD.
Le problème du dump manuel : ce n’est pas une sauvegarde, c’est un souvenir
Commençons par le mythe à démonter. Beaucoup de gens (moi la première) pensent avoir une sauvegarde parce qu’ils ont ça quelque part :
pg_dump -U postgres mabase > /backup/mabase.sql
C’est un fichier. Ce n’est pas une stratégie. Voici pourquoi.
1. Le RPO est catastrophique
Le RPO (Recovery Point Objective), c’est la quantité de données que tu acceptes de perdre. Avec un dump manuel fait « de temps en temps », ton RPO c’est : depuis la dernière fois que j’y ai pensé. Ça peut être trois semaines. Et tu ne le sauras qu’au moment où ça comptera vraiment.
2. Un dump de 77 Go, ce n’est pas un fichier, c’est un événement
Ma base de prod fait environ 77 Go. Un pg_dump là-dessus, ce n’est pas une commande qu’on lance négligemment en journée : c’est une longue lecture logique de toute la base, un CPU qui part en compression, et un fichier énorme à écrire. Je vais y revenir dans la partie perfs, mais retenez déjà ceci : plus la base grossit, moins tu as envie de lancer ta sauvegarde à la main. Et donc moins tu la lances. Le manuel se sabote tout seul.
3. Tu ne peux pas remonter à hier 14h37
Le dump est une photo. Si ta dernière photo date de dimanche et que la bêtise a eu lieu mardi à 14h37, tu reviens à dimanche. Point. Toutes les données de lundi et mardi : perdues. Alors que PostgreSQL sait faire infiniment mieux — il écrit en permanence un journal de toutes ses modifications, les WAL (Write-Ahead Logs). Si on archive ces journaux, on peut rejouer l’histoire jusqu’à la minute près. C’est le PITR (Point-In-Time Recovery). Le dump manuel jette cette possibilité à la poubelle.
4. Une sauvegarde jamais restaurée n’existe pas
C’est le point qui fait le plus mal. Un dump dans un coin, dont tu n’as jamais vérifié qu’il se restaure, c’est un pari, pas une garantie. L’automatisation force à structurer ça : un dépôt, un inventaire, une commande de vérification.
Ce que j’ai mis en place : pgBackRest + archivage des WAL
J’ai choisi pgBackRest. Pas par snobisme d’outil, mais parce qu’il fait nativement les trois choses que je voulais : sauvegarde physique, archivage WAL, et rétention automatique.
Étape 1 — Autoriser l’archivage côté PostgreSQL
Dans postgresql.conf :
wal_level = replica archive_mode = on archive_command = 'pgbackrest --stanza=mabase archive-push %p' max_wal_senders = 3
Le point important ici : wal_level = replica est le minimum requis. Si vous laissez minimal, PostgreSQL refusera carrément de démarrer avec archive_mode = on. J’ai eu droit à ce joli message, un soir :
FATAL: WAL archival cannot be enabled when wal_level is "minimal"
Ça a le mérite d’être clair.
Étape 2 — Configurer la stanza
Une stanza, chez pgBackRest, c’est simplement le nom qu’on donne à un cluster sauvegardé. La mienne s’appelle mabase.
Dans /etc/pgbackrest.conf :
[global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 compress-type=lz4 process-max=4 [mabase] pg1-path=/var/lib/postgresql/16/main
Trois choix à souligner :
- compress-type=lz4 : nettement plus rapide que gzip pour un taux de compression très honnête. Sur 77 Go, la différence n’est pas cosmétique.
- process-max=4 : pgBackRest parallélise. C’est un de ses gros avantages sur pg_dump, qui reste largement mono-thread sur ce type de travail.
- repo1-retention-full=2 : je garde deux sauvegardes complètes, les vieilles s’effacent toutes seules. La rétention automatique, c’est la moitié de l’intérêt de l’automatisation. Sans elle, on remplace « oublier de sauvegarder » par « saturer le disque de sauvegardes ». J’ai vu des serveurs mourir de ça.
Étape 3 — Initialiser et vérifier
sudo -u postgres pgbackrest --stanza=mabase stanza-create sudo -u postgres pgbackrest --stanza=mabase check
Le check est votre ami : il valide que l’archivage WAL fonctionne réellement de bout en bout. Ne sautez pas cette étape.
Étape 4 — Le cron (le cœur du sujet)
crontab -u postgres -e
0 2 * * 2 nice -n 10 pgbackrest --stanza=mabase --type=full backup
Traduction : tous les mardis à 2h du matin, sauvegarde complète, en priorité CPU basse.
Deux détails qui comptent :
- Le cron appartient à l’utilisateur postgres, pas à root. C’est le bon niveau de privilège pour ce travail — inutile de faire tourner en root quelque chose qui n’en a pas besoin.
- nice -n 10 : la sauvegarde s’exécute en priorité réduite. Si le serveur a autre chose à faire à 2h du matin, la sauvegarde s’efface poliment. On y revient tout de suite.
Et pour vérifier au réveil que tout s’est bien passé :
sudo -u postgres pgbackrest --stanza=mabase info
Et les performances du SGBD dans tout ça ?
C’est LA question qui m’a fait traîner des mois. « Si j’automatise, est-ce que je vais plomber ma base ? »
Réponse courte : le manuel coûte plus cher que l’automatique. Voici pourquoi.
pg_dump est une lecture logique, pgBackRest une copie physique
pg_dump ne copie pas des fichiers : il reconstruit la base en la relisant intégralement et en générant du SQL. Ça veut dire un parcours complet, du CPU en pagaille, et surtout une transaction très longue ouverte pendant toute l’opération. Or une transaction longue empêche l’autovacuum de nettoyer les lignes mortes derrière elle. Sur une base à fortes écritures, un dump interminable en pleine journée, c’est du bloat qui s’accumule et des perfs qui se dégradent — bien après la fin du dump.
pgBackRest, lui, copie les fichiers du cluster. C’est plus proche du disque, ça se parallélise, et ça n’a pas ce coût transactionnel.
Le vrai chiffre
Ma première sauvegarde complète des 77 Go, avec lz4 et nice -n 10, a pris 22 minutes. Vingt-deux minutes, à 2h du matin, en priorité basse, sans que personne ne s’en aperçoive. À comparer avec un dump équivalent lancé un mardi à 15h « parce que j’y ai pensé ».
Le coût continu de l’archivage WAL
L’archivage n’est pas gratuit : chaque segment WAL rempli déclenche un archive-push. Le coût unitaire est faible, mais il y a un piège à connaître : si l’archive_command est plus lente que le rythme de production des WAL, les segments s’accumulent dans pg_wal/ et le disque se remplit. Surveillez cet espace disque. C’est le principal effet de bord d’une config PITR, et il est silencieux jusqu’au moment où il ne l’est plus.
Un mot sur synchronous_commit
Sur des charges à écritures très fréquentes, synchronous_commit = off donne un gain net : PostgreSQL n’attend plus la confirmation d’écriture du WAL sur disque pour valider la transaction. Mais soyons précises sur ce qu’on échange : en cas de crash brutal, vous perdez les quelques dernières centaines de millisecondes de transactions validées. La base reste cohérente (ce n’est pas fsync = off, qui lui est un vrai danger en prod), mais elle n’est pas complète.
C’est un arbitrage à faire en conscience, selon la valeur de vos dernières millisecondes.
Le jour où ça plante : restaurer à mardi 14h37
Tout ce qui précède ne sert à rien si on ne sait pas s’en servir le jour J. Alors imaginons le scénario. Mardi, 14h37, quelqu’un lance un DELETE sans WHERE sur une table centrale. À 14h50, on s’en aperçoit. Objectif : remettre la base exactement dans l’état de 14h36.
0. Ne rien faire de brutal
Le premier réflexe est le plus important : on ne redémarre pas PostgreSQL, on ne bricole rien. Si la base tourne encore, on l’arrête proprement et on réfléchit. Les WAL déjà archivés sont votre matière première : ne les touchez pas.
1. Regarder ce dont on dispose
sudo -u postgres pgbackrest --stanza=mabase info
Vous obtenez la liste des sauvegardes complètes avec leurs dates de début et de fin. Le point à vérifier absolument : votre cible (mardi 14h36) doit être postérieure à la fin d’une sauvegarde complète, et tous les WAL entre cette sauvegarde et la cible doivent être présents dans l’archive. Dans mon cas, avec un full le mardi à 2h, une cible à 14h36 le même mardi est parfaitement atteignable : pgBackRest repart du backup de 2h et rejoue ~12h de WAL.
2. Arrêter PostgreSQL
sudo systemctl stop postgresql3. Restaurer avec une cible temporelle
C’est ici que le PITR prend tout son sens :
sudo -u postgres pgbackrest \ --stanza=mabase \ --delta \ --type=time \ --target="2026-07-14 14:36:00+02" \ --target-action=promote \ restore
Décortiquons, parce que chaque option compte :
- –type=time + –target : la date et l’heure exactes où l’on veut atterrir. Précisez le fuseau horaire (+02 ici) — sans lui, PostgreSQL interprète selon le timezone du serveur, et se tromper d’une heure sur un rollback, c’est ballot.
- –delta : pgBackRest compare les fichiers existants et ne réécrit que ce qui diffère. C’est plus rapide, et surtout ça vous évite d’avoir à vider le répertoire de données à la main. Parce que oui, la méthode « officielle » sans –delta exige un data directory vide, et ça veut dire taper un rm -rf /var/lib/postgresql/16/main/* en tremblant à 15h un mardi. Utilisez –delta.
- –target-action=promote : une fois la cible atteinte, PostgreSQL sort du mode recovery et redevient ouvert en écriture. L’alternative, –target-action=pause, arrête la relecture et attend : la base est lisible mais figée, ce qui permet de vérifier qu’on est bien au bon endroit avant de valider. Sur une restauration où j’ai un doute sur l’heure exacte, je commence par pause.
pgBackRest choisit tout seul la bonne sauvegarde complète à utiliser en fonction de la cible. Il écrit également le recovery.signal et le restore_command dans postgresql.auto.conf — inutile de les créer à la main.
4. Redémarrer et regarder la base rejouer l’histoire
sudo systemctl start postgresql sudo journalctl -u postgresql -f
C’est le moment le plus étrange de l’opération : PostgreSQL rejoue les WAL un par un, et vous voyez défiler dans les logs les heures de votre journée à l’envers. Vous cherchez la ligne qui confirme que le point de recovery a été atteint (recovery stopping before commit of transaction…, puis database system is ready to accept connections).
Tant que le recovery tourne, la base est en lecture seule. C’est normal, laissez-la finir.
5. Vérifier avant de crier victoire
La donnée la plus récente doit dater de ~14h36. Si oui : c’est gagné, et vous venez de récupérer 12h de travail qu’un pg_dump du dimanche vous aurait fait perdre.
SELECT COUNT(*) FROM ma_table_massacree; SELECT MAX(created_at) FROM ma_table_massacree;
Pour conclure
Voilà tout tourne en tâche de fond, et les joueurs et moi on n’a plus à s’en soucier et au lieu de se préoccuper des dumps on peut ainsi écouter Radio Chirac, un son qui déchire pas mal.