MySQL vs PostgreSQL : exercices PITR — testez la restauration avant d’en avoir besoin

PITR est un exercice, pas une case à cocher

La PITR est simple en concept : prendre une sauvegarde de base, puis rejouer le journal des transactions jusqu’à un temps cible, un ID de transaction ou une position de journal. La base de données réapparaît, légèrement contrariée, au moment juste avant l’incident.

En pratique, la PITR est une chaîne d’hypothèses fragiles : synchronisation temporelle, rétention des journaux, configuration correcte, clés de chiffrement disponibles, permissions, et bande passante I/O suffisante pour effectuer le replay avant que votre entreprise ne manque la paie. Les exercices PITR sont la façon de trouver le maillon faible quand les enjeux sont faibles.

Voici la vérité opérationnelle : une sauvegarde que vous n’avez pas restaurée n’est qu’un tas compressé d’espoir.

Une citation à garder sur un post-it (idée paraphrasée) : « L’espoir n’est pas une stratégie » — souvent attribuée dans les cercles opérationnels au général Gordon R. Sullivan.
Paraphrase mise à part, le sentiment est terriblement exact.

Blague #1 : La seule chose plus optimiste qu’une restauration non testée est la croyance que l’incident attendra les heures de bureau.

Faits et petite histoire importants en production

Les détails derrière les binlogs MySQL et le WAL PostgreSQL ne sont pas de la trivia. Ils expliquent pourquoi certaines pratiques PITR fonctionnent — et pourquoi d’autres pourrissent tranquillement jusqu’à un incident.

1. PostgreSQL a introduit le WAL dans la version 7.1 (2001), permettant la récupération après crash et plus tard la réplication en streaming. La PITR s’appuie sur le même mécanisme.
2. Le journal binaire de MySQL est devenu un élément central de la réplication tôt, et la PITR dans MySQL revient essentiellement à « restaurer la sauvegarde + appliquer les binlogs ». Opérationnellement, réplication et PITR partagent les mêmes modes de défaillance.
3. Le concept de « timeline » dans PostgreSQL existe parce que la récupération peut bifurquer l’historique. Si vous restaurez et promouvez, vous créez une nouvelle timeline ; l’ignorer, c’est rejouer le mauvais futur.
4. MySQL supporte les formats binlog statement-based, row-based et mixed. La fiabilité et la déterminisme de la PITR changent radicalement selon lequel vous utilisez.
5. L’archivage WAL basé sur archive_command de PostgreSQL précède le stockage objet en cloud généralisé, ce qui explique pourquoi de nombreux environnements emballent encore des scripts shell autour, et pourquoi ces scripts échouent de façons créatives.
6. Les GTID de MySQL (identifiants globaux de transaction) ont été introduits pour rendre la réplication et le basculement plus robustes ; ils améliorent aussi le raisonnement autour de la PITR quand ils sont utilisés de façon cohérente.
7. PostgreSQL a ajouté les mécanismes backup_label et recovery.signal (remplaçant l’ancien recovery.conf) pour rendre l’état de récupération plus explicite. Les runbooks anciens qui référencent recovery.conf existent toujours, et ils causent encore des confusions nocturnes.
8. Les défauts et changements de binlog_row_image à travers les variantes MySQL influencent la quantité de données dans les binlogs. Cela affecte le temps de replay, le coût de stockage, et la capacité à reconstruire proprement certaines lignes.

MySQL vs PostgreSQL PITR : ce qui change vraiment

Ce que « rejouer le journal » signifie dans chaque système

La PITR MySQL rejoue des journaux binaires : changements logiques au niveau des instructions SQL (statement-based), modifications au niveau des lignes (row-based), ou un mélange. L’outil de replay est typiquement mysqlbinlog.

La PITR PostgreSQL rejoue le WAL (Write-Ahead Log) : des informations de redo plutôt physiques, appliquées par le serveur pendant la récupération. Vous n’« appliquez pas le WAL » avec un outil client ; vous configurez la récupération, fournissez des segments WAL (depuis l’archive ou le streaming), et PostgreSQL effectue le replay.

Attentes pour la sauvegarde de base

Les sauvegardes de base MySQL varient largement : snapshots de système de fichiers (LVM/ZFS), xtrabackup, ou dumps logiques. La PITR avec binlogs dépend fortement d’une sauvegarde de base cohérente qui s’aligne sur les coordonnées du binlog.

PostgreSQL a une histoire plus standardisée : un pg_basebackup (ou un snapshot de système de fichiers pris correctement) plus des WAL archivés est un chemin courant et bien éprouvé.

Le temps est un menteur (à moins que vous ne le maîtrisiez)

La PITR MySQL utilise souvent des positions de binlog, des noms de fichiers, des horodatages intégrés aux événements, ou des GTID. Si les horloges système divergent entre les machines, « restaurer à 14:03:00 » devient une danse d’interprétation.

PostgreSQL dispose de recovery_target_time, mais il dépend aussi de la gestion des fuseaux horaires du cluster et de la timeline WAL sur laquelle vous vous trouvez. Si vous restaurez à un point qui n’existe pas sur la timeline choisie, vous échouerez ou arriverez quelque part de surprenant.

Observabilité pendant la récupération

PostgreSQL expose l’état de récupération via des vues comme pg_stat_wal_receiver, et les logs indiquent quel WAL est appliqué. C’est assez introspectible.

Le replay MySQL via mysqlbinlog | mysql est transparent dans le sens « on peut voir le pipe », mais mauvais dans le sens « quel est exactement mon progrès » à moins que vous ne l’entouriez d’une instrumentation personnelle.

Une préférence opérationnelle (opinion)

Si vous exécutez MySQL et que vous vous souciez de la PITR, privilégiez le binlog en mode row-based sauf raison spécifique contraire. Les binlogs basés sur les instructions et les fonctions non déterministes sont la retraite dorée pour un avocat en divorce.

Calculs RPO/RTO que vous ne pouvez pas bâcler

Les exercices PITR ne sont pas seulement « est-ce que ça restaure ? » Ils sont « est-ce que ça restaure assez vite et assez précisément ? »

• RPO (Recovery Point Objective) : combien de données vous pouvez perdre. La PITR vise généralement proche de zéro, mais seulement si les journaux sont complets et disponibles.
• RTO (Recovery Time Objective) : combien de temps vous pouvez être indisponible. C’est dominé par le temps de restauration de la base + le temps de replay des journaux + le temps de validation.

Le tueur de RTO est presque toujours l’I/O : décompression des sauvegardes, écriture de téraoctets sur disque, et replay de journaux qui n’ont jamais été conçus pour être lus « rapidement ». Si vous faites des exercices PITR sur une petite VM de test avec stockage lent, vous paniquerez pour rien ou, pire, croirez à un RTO qui ne tiendra pas en production.

Mesurez le taux de replay. Mesurez le débit de restauration. Mesurez le temps nécessaire pour exécuter les vérifications d’intégrité et quelques requêtes applicatives. Ensuite, décidez si votre fenêtre de rétention actuelle et votre niveau de stockage sont compatibles avec vos engagements.

Concevoir un exercice PITR qui découvre de vrais bugs

Choisissez un incident cible que vous pouvez simuler

Ne faites pas d’exercices abstraits. Simulez une défaillance réaliste :

• Un DELETE accidentel sans WHERE (classique, indémodable).
• Une mauvaise migration qui supprime un index et fait tout timeouter.
• Un bug applicatif qui écrit le mauvais tenant ID pendant 15 minutes.
• Erreur opérateur : exécution d’un script « sûr » sur le mauvais cluster.

Définissez les critères de succès à l’avance

Le succès n’est pas « la base a démarré ». Le succès, c’est :

• Restauration au bon point (vérifié par des requêtes marqueurs connues).
• Tables critiques pour l’application présentes et cohérentes.
• RTO et RPO mesurés et enregistrés.
• Runbook mis à jour en fonction de ce qui a cassé.

Incluez toujours une étape « qui a les clés ? »

Si les sauvegardes sont chiffrées (et elles devraient l’être), votre exercice doit inclure la récupération de la clé de déchiffrement depuis l’endroit où votre organisation la cache derrière une file de tickets. Si votre restauration nécessite un rôle IAM spécifique ou une permission KMS, testez cela aussi.

Conservez une chose ennuyeuse : nommage et métadonnées

Chaque artefact de sauvegarde doit inclure : ID du cluster, heure de début/fin, étiquette de la sauvegarde de base, position de départ binlog/WAL, et version du logiciel. Les métadonnées « évidentes » aujourd’hui deviennent de l’archéologie dans six mois.

Tâches pratiques : commandes, sorties, décisions

Les commandes ci-dessous ne sont pas décoratives. Ce sont le genre de réflexes que vous voulez avant un incident. Chaque tâche inclut : une commande, ce que signifie sa sortie, et la décision que vous prenez.

Task 1 — Vérifier que le binlog MySQL est activé et sain

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'binlog_row_image';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

Signification : La PITR dépend des binlogs ; si log_bin=OFF, vous ne pouvez rien rejouer. binlog_format=ROW est la valeur par défaut plus sûre pour la PITR.

Décision : Si les binlogs sont désactivés ou en mode statement, considérez la PITR comme « effort maximal » et corrigez la configuration avant d’annoncer un faible RPO.

Task 2 — Vérifier la rétention des binlogs MySQL (et si elle ment)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; SHOW VARIABLES LIKE 'expire_logs_days';"
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| binlog_expire_logs_seconds | 259200 |
+--------------------------+--------+
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| expire_logs_days| 0     |
+-----------------+-------+

Signification : Les binlogs expirent après 3 jours ici. Si votre conformité exige 14 jours de PITR, vous n’êtes pas conforme ; vous faites seulement profil bas.

Décision : Définissez la rétention selon les besoins métier et la réalité du stockage. Si vous expédiez les binlogs ailleurs, la rétention sur le primaire peut être plus courte, mais seulement si l’expédition est vérifiée.

Task 3 — Lister les fichiers binlog MySQL et choisir une fenêtre de replay

cr0x@server:~$ mysql -e "SHOW BINARY LOGS;"
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000421 |  52428800 |
| mysql-bin.000422 |  52428800 |
| mysql-bin.000423 |   8388608 |
+------------------+-----------+

Signification : Vous avez des segments de binlog présents sur le serveur. Vous ne savez toujours pas s’ils couvrent le temps de l’incident.

Décision : Identifiez quel binlog contient la fenêtre de l’incident en utilisant les horodatages de mysqlbinlog (tâche suivante) ou les GTID.

Task 4 — Inspecter les horodatages du binlog MySQL autour d’un incident

cr0x@server:~$ mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-datetime="2025-12-30 13:55:00" --stop-datetime="2025-12-30 14:10:00" /var/lib/mysql/mysql-bin.000423 | head -n 25
# at 4
#251230 13:55:02 server id 1  end_log_pos 123 CRC32 0x4f2b1a77  Start: binlog v 4, server v 8.0.36 created 251230 13:55:02
# at 123
#251230 14:02:11 server id 1  end_log_pos 456 CRC32 0x9c4d6e10  Query   thread_id=812 exec_time=0 error_code=0
SET TIMESTAMP=1735567331/*!*/;
BEGIN
# at 456
#251230 14:02:11 server id 1  end_log_pos 892 CRC32 0x19a0f2cb  Table_map: `app`.`users` mapped to number 108

Signification : Vous pouvez localiser la fenêtre temporelle et voir quelles tables ont été touchées. Cela vous aide à définir un point d’arrêt précis.

Décision : Choisissez --stop-datetime juste avant l’instruction dommageable, ou trouvez la position/GTID exacte pour un contrôle précis.

Task 5 — Extraire les événements binlog MySQL dans un fichier (ne pas pipeliner aveuglément)

cr0x@server:~$ mysqlbinlog --start-datetime="2025-12-30 13:55:00" --stop-datetime="2025-12-30 14:02:10" /var/lib/mysql/mysql-bin.000423 > /tmp/pitr_replay.sql
cr0x@server:~$ tail -n 5 /tmp/pitr_replay.sql
# at 3321
#251230 14:02:10 server id 1  end_log_pos 3456 CRC32 0x2f1b8c3a  Xid = 991228
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*!*/;
DELIMITER ;
# End of log file

Signification : Vous avez un script de replay déterministe pour cette fenêtre. C’est auditable et cela vous permet de rechercher « DROP » avant d’appuyer sur Entrée.

Décision : Passez en revue les instructions dangereuses et confirmez le point d’arrêt. Lors de restaurations à enjeux élevés, « faire confiance au pipe » est ce que vous expliquerez plus tard.

Task 6 — Restaurer la sauvegarde de base MySQL dans un datadir de staging

cr0x@server:~$ sudo systemctl stop mysql
cr0x@server:~$ sudo rsync -aH --delete /backups/mysql/base/2025-12-30_1200/ /var/lib/mysql/
cr0x@server:~$ sudo chown -R mysql:mysql /var/lib/mysql
cr0x@server:~$ sudo systemctl start mysql
cr0x@server:~$ mysqladmin ping
mysqld is alive

Signification : La restauration de base a réussi et MySQL démarre. Cela ne dit rien sur si les données correspondent au point de restauration souhaité.

Décision : Capturez les coordonnées binlog ou l’état GTID depuis les métadonnées de la sauvegarde, puis appliquez les binlogs depuis là jusqu’au temps cible.

Task 7 — Appliquer le script de replay MySQL et surveiller les erreurs

cr0x@server:~$ mysql --show-warnings < /tmp/pitr_replay.sql
Warning (Code 1287): 'SET @@SESSION.GTID_NEXT' is deprecated and will be removed in a future release.

Signification : Les avertissements ne sont pas fatals, mais les erreurs le sont. Une erreur fatale courante est l’absence de tables parce que la sauvegarde de base ne correspond pas au point de départ du replay.

Décision : Si vous voyez des erreurs comme « Table doesn’t exist », arrêtez. Votre sauvegarde de base + plage de binlogs est incohérente ; corrigez les coordonnées de départ.

Task 8 — Vérifier l’état GTID MySQL après la PITR (si vous utilisez les GTID)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'gtid_mode'; SELECT @@global.gtid_executed\G"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
*************************** 1. row ***************************
@@global.gtid_executed: 8b3f9b75-9c67-11ee-9b7d-0242ac120002:1-889122

Signification : Les GTID montrent quelles transactions existent. Après une PITR, cela importe si vous avez l’intention de répliquer depuis/vers cette instance.

Décision : Si vous allez réintégrer la réplication, assurez-vous que les ensembles GTID s’alignent avec votre plan topologique. Sinon, vous créerez une confusion de réplication qui ressemble à de la corruption de données.

Task 9 — Vérifier que l’archivage PostgreSQL est configuré et réellement opérationnel

cr0x@server:~$ psql -d postgres -c "SHOW wal_level; SHOW archive_mode; SHOW archive_command; SHOW archive_timeout;"
 wal_level 
-----------
 replica
(1 row)

 archive_mode 
--------------
 on
(1 row)

                     archive_command                     
---------------------------------------------------------
 test ! -f /wal_archive/%f && cp %p /wal_archive/%f
(1 row)

 archive_timeout 
-----------------
 60s
(1 row)

Signification : L’archivage est activé et la commande copie le WAL vers un répertoire d’archive local. C’est un début, pas une preuve.

Décision : Confirmez que les fichiers WAL apparaissent réellement et que les échecs sont alertés. « Configuré » n’est pas « fonctionnel ».

Task 10 — Vérifier le backlog d’archive PostgreSQL et l’activité WAL récente

cr0x@server:~$ ls -lh /wal_archive | tail -n 5
-rw------- 1 postgres postgres 16M Dec 30 14:00 000000010000002A0000009F
-rw------- 1 postgres postgres 16M Dec 30 14:01 000000A0
-rw------- 1 postgres postgres 16M Dec 30 14:02 000000A1
-rw------- 1 postgres postgres 16M Dec 30 14:03 000000A2
-rw------- 1 postgres postgres 16M Dec 30 14:04 000000A3

Signification : Les segments WAL sont archivés, les noms sont séquentiels, les horodatages semblent sains.

Décision : Si l’archive est obsolète, cessez de prétendre que la PITR existe. Corrigez l’archivage d’abord ; les sauvegardes de base seules ne suffisent pas pour la PITR.

Task 11 — Créer une sauvegarde de base PostgreSQL que vous pouvez réellement restaurer

cr0x@server:~$ sudo -u postgres pg_basebackup -D /backups/pg/base/2025-12-30_1200 -Fp -Xs -P -R
waiting for checkpoint
22994/22994 kB (100%), 1/1 tablespace
22994/22994 kB (100%), 1/1 tablespace

Signification : Vous avez pris une sauvegarde physique de base et inclus le streaming WAL (-Xs), plus écrit la configuration de réplication (-R). Pour la PITR, vous avez toujours besoin des WAL archivés ou de la disponibilité du WAL stream.

Décision : Enregistrez l’étiquette de la sauvegarde et l’horodatage. Assurez-vous que la plage d’archive WAL correspondante existe et est conservée.

Task 12 — Restaurer la sauvegarde de base PostgreSQL dans un nouveau datadir

cr0x@server:~$ sudo systemctl stop postgresql
cr0x@server:~$ sudo rm -rf /var/lib/postgresql/16/main
cr0x@server:~$ sudo rsync -aH /backups/pg/base/2025-12-30_1200/ /var/lib/postgresql/16/main/
cr0x@server:~$ sudo chown -R postgres:postgres /var/lib/postgresql/16/main

Signification : Les fichiers du cluster sont en place. Maintenant vous décidez si vous faites une PITR (récupération) ou simplement un clone.

Décision : Pour la PITR, configurez les cibles de récupération et le restore_command ; ne le démarrez pas comme primaire normal sauf si vous avez l’intention de bifurquer la timeline.

Task 13 — Configurer la PITR PostgreSQL : restore_command et temps cible

cr0x@server:~$ sudo -u postgres bash -c 'cat >> /var/lib/postgresql/16/main/postgresql.conf <<EOF
restore_command = '\''cp /wal_archive/%f %p'\''
recovery_target_time = '\''2025-12-30 14:02:10+00'\''
recovery_target_action = '\''pause'\''
EOF'
cr0x@server:~$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
cr0x@server:~$ sudo systemctl start postgresql

Signification : PostgreSQL démarrera en mode récupération, récupérera le WAL depuis l’archive, rejouera jusqu’au temps cible, puis mettra en pause.

Décision : La mise en pause est volontaire : vous inspectez les données avant promotion. Si vous promouvez automatiquement, vous pouvez consigner accidentellement le mauvais point dans le temps comme « vérité ».

Task 14 — Observer la progression de la récupération PostgreSQL et confirmer l’atteinte du cible

cr0x@server:~$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery 
-------------------
 t
(1 row)

cr0x@server:~$ sudo -u postgres psql -c "SELECT now(), pg_last_wal_replay_lsn();"
              now              | pg_last_wal_replay_lsn 
------------------------------+-------------------------
 2025-12-30 14:02:12.123+00   | 2A/A100F2B0
(1 row)

Signification : Vous êtes en récupération et avez un LSN de replay. Les logs indiqueront généralement que vous avez atteint recovery_target_time et que la pause est effective.

Décision : Exécutez les requêtes de validation maintenant. Si c’est correct, promouvez ; sinon, ajustez la cible et relancez la récupération.

Task 15 — Promouvoir PostgreSQL après validation

cr0x@server:~$ sudo -u postgres psql -c "SELECT pg_promote(wait_seconds => 60);"
 pg_promote 
------------
 t
(1 row)

cr0x@server:~$ sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery 
-------------------
 f
(1 row)

Signification : Le serveur est maintenant en écriture et une nouvelle timeline existe.

Décision : Décidez comment cette instance réintègre la topologie. Une restauration PITR promue n’est pas automatiquement sûre à reconnecter comme si de rien n’était.

Task 16 — Valider les données à la frontière applicative (pas seulement « comptage de lignes »)

cr0x@server:~$ psql -d appdb -c "SELECT COUNT(*) FROM users; SELECT MAX(updated_at) FROM users;"
  count  
---------
 1284932
(1 row)

          max          
-----------------------
 2025-12-30 14:02:05+00
(1 row)

Signification : Les comptages et les horodatages donnent une saine vérification rapide. Le timestamp max étant avant l’incident est un bon signe.

Décision : Si les données semblent incorrectes, ne promouvez pas (PostgreSQL) et ne basculez pas (MySQL). Ajustez le point de restauration et relancez.

Task 17 — Mesurer les goulots I/O de restauration (bases Linux qui décident votre RTO)

cr0x@server:~$ iostat -xm 2 3
Linux 6.5.0 (server) 	12/31/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.10    0.00    6.20   38.70    0.00   42.00

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz  aqu-sz  %util
nvme0n1        1200.0  98000.0     0.0    0.0    1.2    81.7   2200.0 180000.0     0.0    0.0    6.8    81.8   16.2   99.0

Signification : %util proche de 99% et un iowait élevé signifient que le stockage est le goulot d’étranglement. Votre restauration ne s’accélérera pas en ajoutant du CPU.

Décision : Déplacez les restaurations sur des volumes plus rapides, utilisez un scratch local NVMe, parallélisez la décompression, ou réduisez le replay en prenant des sauvegardes de base plus fréquentes.

Task 18 — Confirmer la synchronisation temporelle avant une PITR basée sur le temps

cr0x@server:~$ timedatectl
               Local time: Wed 2025-12-31 10:12:03 UTC
           Universal time: Wed 2025-12-31 10:12:03 UTC
                 RTC time: Wed 2025-12-31 10:12:03
                Time zone: Etc/UTC (UTC, +0000)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no

Signification : Votre horloge système est synchronisée. Sans cela, « restaurer à 14:02 » devient de la devinette.

Décision : Si les horloges ne sont pas synchronisées, préférez la récupération basée sur la position (MySQL) ou le LSN (PostgreSQL) et corrigez NTP avant le prochain exercice.

Trois micro-histoires tirées de la vie en entreprise

Micro-histoire 1 : L’incident causé par une mauvaise hypothèse

Une entreprise SaaS de taille moyenne exécutait MySQL sur des VMs managées. Ils « avaient la PITR » parce que des sauvegardes de base nocturnes existaient et que le binlog était activé. L’hypothèse : les binlogs étaient copiés vers un stockage objet par un agent, et la rétention dans le bucket était « à peu près éternelle ».

Un développeur a poussé une migration qui a reconstruit une grosse table de manière incorrecte. Cela n’a pas cassé l’application immédiatement ; ça a juste commencé à écrire des données subtilement incorrectes. Quatre heures plus tard, un client s’en est rendu compte. Le chef d’incident a demandé la PITR à « 30 minutes avant le déploiement ».

La restauration a commencé en douceur : sauvegarde de base restaurée, MySQL démarré, et l’équipe a commencé à appliquer les binlogs. Puis le replay a heurté un trou. Le fichier binlog pour une fenêtre de 40 minutes manquait. Pas corrompu. Manquant.

La cause racine n’était pas exotique. L’agent de copie échouait silencieusement sur des erreurs de permission après un changement de politique de bucket. Les binlogs sur le primaire avaient déjà expiré à cause d’une rétention trop courte. Leur « PITR » avait un trou de quatre heures au milieu de la journée, comme une mauvaise mémoire.

La correction n’a pas été un nouveau fournisseur. Elle a été ennuyeuse : alerter sur les échecs d’archivage, étendre la rétention jusqu’à ce que l’expédition soit prouvée fiable, et ajouter une étape d’exercice : « lister les binlogs dans l’archive pour la fenêtre d’incident ».

Micro-histoire 2 : L’optimisation qui s’est retournée contre eux

Une autre organisation exécutait PostgreSQL avec archivage WAL. Les exercices de restauration étaient « trop lents », alors quelqu’un a optimisé en compressant fortement le WAL et en le poussant via un pipeline mono-thread d’encryption+upload sur le nœud de base de données. Le CPU est monté, mais la taille de l’archive a baissé. Tout le monde a applaudi.

La célébration a duré jusqu’au premier vrai essai PITR. Le cluster de restauration pouvait récupérer le WAL, mais la décompression est devenue le goulot, et le pipeline mono-thread signifiait que l’archive accusait du retard pendant les périodes d’écriture de pic. Pendant l’incident, les segments WAL les plus récents n’avaient pas encore été archivés. La récupération s’est arrêtée avant le temps cible.

L’équipe a essayé de « juste attendre que l’archive rattrape son retard ». Ce n’est pas un RTO ; c’est une prière avec une invitation calendrier.

Après l’incident, la correction a été contre-intuitive : réduire le niveau de compression, déplacer le chiffrement/upload hors du primaire, paralléliser le pipeline, et fixer des SLO explicites pour « WAL archivé dans X secondes ». Ils ont accepté un coût de stockage plus élevé et ont retrouvé de la prévisibilité.

Micro-histoire 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Une entreprise liée aux finances faisait un exercice PITR hebdomadaire. Ce n’était pas glamour. C’était une checklist, un ticket et un court rapport. Ça agaçait tout le monde juste assez pour être efficace.

Lors d’un exercice routinier, l’ingénieur a remarqué que la restauration fonctionnait mais arrivait toujours cinq à sept minutes après l’horodatage demandé. Les données « semblaient correctes », mais le décalage le gênait. Il a creusé et a trouvé une confusion de fuseau horaire : le runbook utilisait l’heure locale, mais recovery_target_time attendait l’UTC. Ils ont corrigé le runbook, standardisé sur l’UTC, et ajouté une table marqueur avec une insertion horodatée lors des déploiements.

Deux mois plus tard, un incident de production a eu lieu : un job batch a mis à jour la mauvaise partition pendant environ huit minutes. L’équipe a restauré juste avant le marqueur et a validé rapidement. Ils ont respecté leur RTO parce qu’ils avaient déjà débattu du détail idiot du fuseau horaire lors d’un exercice sans enjeu.

Blague #2 : Les fuseaux horaires sont comme la latence de réplication — tout le monde admet leur existence, puis ils vous gâchent l’après-midi.

Playbook de diagnostic rapide

Les restaurations échouent ou traînent pour quelques raisons prévisibles. L’astuce est d’identifier laquelle en minutes, pas en heures. Voici l’ordre que j’utilise quand quelqu’un dit « la PITR est lente/cassée ».

Premier : manquez-vous des journaux requis ?

• MySQL : confirmez la couverture des binlogs pour le temps cible. Avez-vous les fichiers binlog localement ou dans l’archive pour toute la fenêtre ?
• PostgreSQL : confirmez la couverture de l’archive WAL, et que restore_command peut récupérer le segment suivant requis.

Si des journaux manquent, arrêtez d’optimiser. Vous n’avez pas un problème de performance ; vous avez une frontière de perte de données.

Second : la sauvegarde de base est-elle cohérente avec le début du replay ?

• MySQL : la sauvegarde de base doit correspondre aux coordonnées binlog/ensemble GTID depuis lesquelles vous démarrez. Un décalage provoque des erreurs « table manquante » ou des doublons lors du replay.
• PostgreSQL : la sauvegarde de base doit être complète et inclure les fichiers nécessaires ; le WAL requis depuis le début de la sauvegarde doit exister.

Troisième : est-ce I/O, CPU ou réseau ?

• I/O lié : iowait élevé, disques à forte utilisation, débit de restauration plafonné indépendamment du CPU.
• CPU lié : compression/décompression/chiffrement saturant les cœurs, disques non pleinement utilisés.
• Réseau lié : récupération lente depuis l’emplacement d’archive, latence élevée, plafonnement du débit.

Quatrième : avez-vous choisi la bonne cible et la bonne timeline ?

• PostgreSQL : les décalages de timeline après promotion sont classiques. Restaurer depuis la mauvaise timeline donne « requested timeline does not contain minimum recovery point » ou aboutit à un point inattendu.
• MySQL : le replay basé sur le temps est vulnérable au skew d’horloge et à la confusion des fuseaux ; la position/GTID est plus sûre quand c’est possible.

Cinquième : validez-vous la bonne chose ?

« La base a démarré » n’est pas une validation. Validez un petit ensemble de requêtes métier, de contraintes et de flux applicatifs. Si vous ne pouvez pas définir ceux-ci, votre métrique de succès de restauration repose surtout sur l’intuition.

Erreurs courantes : symptômes → cause racine → correction

1) « La PITR s’arrête tôt et n’atteint pas le temps cible »

Symptômes : la récupération PostgreSQL se met en pause avant la cible ; les logs mentionnent un WAL manquant. Le replay MySQL se termine sans atteindre le temps de l’incident.

Cause racine : trou dans l’archive WAL/binlog, rétention trop courte, ou échecs d’expédition.

Correction : Alertez sur les échecs d’archive_command, vérifiez quotidiennement l’exhaustivité de l’archive, étendez la rétention, et stockez les journaux indépendamment du primaire.

2) « Le replay binlog MySQL lance des erreurs de table manquante »

Symptômes : ERROR 1146 (42S02): Table '...' doesn't exist pendant le replay.

Cause racine : La sauvegarde de base a été restaurée depuis le temps T, mais le replay a démarré depuis des binlogs générés avant que la table n’existe (ou après qu’elle ait été supprimée/recréée).

Correction : Démarrez le replay depuis la position/GTID exacte enregistrée avec la sauvegarde de base ; ne devinez pas uniquement avec des horodatages.

3) « La récupération PostgreSQL boucle sur le même segment WAL »

Symptômes : les logs montrent des tentatives répétées pour restaurer le même %f.

Cause racine : le restore_command retourne un succès mais ne place pas réellement le fichier (ou place un fichier tronqué). Classique quand les scripts avalent les erreurs.

Correction : Faites en sorte que le restore_command échoue bruyamment. Vérifiez la taille et le checksum du fichier. Évitez les wrappers « exit 0 ».

4) « La restauration est douloureusement lente, mais le CPU est bas »

Symptômes : temps de restauration longs ; iostat montre des disques saturés ; CPU principalement inactif.

Cause racine : plafond de débit/IOPS du stockage ; trop d’écritures aléatoires pendant le replay ; WAL/binlog sur des médias lents.

Correction : Utilisez des volumes scratch plus rapides pour la restauration, séparez les données des journaux, envisagez des sauvegardes de base plus fréquentes, et assurez-vous que le chemin de récupération de l’archive n’est pas bridé.

5) « La restauration est rapide, puis échoue à la fin avec des plaintes de corruption »

Symptômes : PostgreSQL se plaint d’un enregistrement de checkpoint invalide ; MySQL ne démarre pas à cause de problèmes de logs InnoDB.

Cause racine : sauvegarde de base incomplète/corrompue, ou snapshot de système de fichiers pris sans quiescence appropriée.

Correction : Utilisez des outils conçus pour des sauvegardes cohérentes (pg_basebackup, procédures de snapshot testées, xtrabackup), et vérifiez l’intégrité des sauvegardes pendant l’exercice.

6) « La PITR aboutit au mauvais moment »

Symptômes : l’état restauré inclut des changements qui auraient dû être exclus, ou manque des changements qui auraient dû être inclus.

Cause racine : décalage de fuseau horaire, skew d’horloge, ou mauvaise compréhension des bornes inclusives/exclusives durant le replay.

Correction : Standardisez sur l’UTC pour les horodatages d’incident, intégrez une transaction marqueur, et préférez les cibles position/LSN/GTID quand c’est possible.

7) « Tout se restaure, mais la réplication après est le chaos »

Symptômes : les replicas PostgreSQL ne peuvent plus suivre ; erreurs de réplication MySQL sur des GTID exécutés ou des entrées en double.

Cause racine : vous avez promu une instance restaurée sans planifier la nouvelle topologie, la timeline ou l’alignement des ensembles GTID.

Correction : Traitez la restauration PITR comme une bifurcation : décidez du nœud faisant autorité, reconstruisez les replicas à partir de celui-ci, et documentez la procédure de réintégration.

Checklists / plan étape par étape

Exercice PITR hebdomadaire (90 minutes, ennuyeux volontairement)

1. Choisir un scénario : choisissez une fenêtre de « mauvais changement » connue de la semaine passée (ou créez une transaction marqueur inoffensive).
2. Choisir un point cible : définissez le point de restauration en UTC et enregistrez-le.
3. Confirmer la couverture des journaux : vérifiez que les binlogs/WAL existent pour toute la fenêtre.
4. Restaurer la sauvegarde de base : sur une infrastructure isolée avec une classe de stockage similaire à la production.
5. Rejouer les journaux jusqu’à la cible : MySQL via mysqlbinlog ; PostgreSQL via configuration de récupération.
6. Valider : 5–10 requêtes critiques, un test smoke applicatif, et une vérification d’intégrité.
7. Enregistrer les durées : temps de restauration de la base, temps de replay, temps de validation.
8. Rédiger : ce qui a échoué, ce qui a été lent, ce qui a changé dans le runbook.

Avant de prétendre « nous avons la PITR » (barre minimale)

• Les sauvegardes de base sont cohérentes et restaurables sans héros.
• L’archivage des journaux est complet, monitoré et conservé selon les exigences.
• Le runbook de restauration inclut les étapes IAM/KMS/accès aux clés.
• Les chiffres RTO/RPO sont mesurés, pas souhaités.
• Des requêtes de validation existent et sont pilotées par une personne responsable.

Étapes spécifiques au drill MySQL

1. Enregistrer le fichier/position binlog ou l’ensemble GTID de la sauvegarde de base.
2. Confirmer binlog_format et la rétention.
3. Extraire le SQL de replay dans un fichier et vérifier les instructions dangereuses.
4. Appliquer le replay sur l’instance restaurée ; arrêter au premier erreur et corriger le décalage de coordonnées.
5. Décider du plan topologique : nouveau primaire, clone pour analytics, ou source pour reconstruire les replicas ?

Étapes spécifiques au drill PostgreSQL

1. Confirmer l’archivage WAL et la fraîcheur de l’archive.
2. Restaurer la sauvegarde de base ; assurer recovery.signal et un restore_command correct.
3. Utiliser recovery_target_action='pause' pour les exercices afin de valider avant promotion.
4. Après promotion, noter la nouvelle timeline et planifier la reconstruction des replicas en conséquence.
5. Vérifier que votre archive contient des WAL couvrant les changements de timeline (un piège courant à long terme).

FAQ

1) La PITR est-elle la même chose que la haute disponibilité ?

Non. La HA vous maintient en fonctionnement lors de pannes de nœuds. La PITR récupère des désastres logiques : mauvais déploiements, suppressions, corruptions causées par des humains. Vous voulez les deux.

2) Lequel est « plus facile » pour la PITR : MySQL ou PostgreSQL ?

Le workflow PITR de PostgreSQL est plus standardisé : sauvegarde de base + archive WAL + cibles de récupération. La PITR MySQL peut être propre, mais elle varie plus selon la méthode de sauvegarde et le format du binlog.

3) Dois-je utiliser des cibles basées sur le temps ou sur la position ?

Les cibles basées sur la position (position de binlog/GTID MySQL, LSN PostgreSQL) sont généralement plus déterministes. Les cibles basées sur le temps sont conviviales pour les humains mais fragiles à moins que les horloges et fuseaux soient disciplinés.

4) À quelle fréquence dois-je prendre des sauvegardes de base si j’ai des journaux ?

Aussi souvent que le tolère votre temps de replay. Des écarts plus longs signifient plus de replay de journaux. Si votre replay WAL/binlog pour une journée prend six heures, votre RTO est déjà décidé pour vous.

5) Puis-je faire la PITR à partir de sauvegardes logiques (mysqldump/pg_dump) ?

Pas de façon fiable au sens strict de PITR. Les dumps logiques capturent un point, mais rejouer jusqu’à un temps exact est fastidieux et lent. Utilisez-les pour la portabilité ; utilisez des sauvegardes physiques plus les journaux pour la PITR.

6) Quelle est la façon la plus propre de valider une restauration PITR ?

Un petit ensemble d’invariants métier : comptages clés, marqueurs last-updated, vérifications d’intégrité référentielle, et un test smoke applicatif léger. Si votre validation se limite à « SELECT 1 », vous pratiquez le déni.

7) Qu’en est-il du chiffrement et des clés ?

Si les sauvegardes ou archives sont chiffrées, votre exercice doit inclure la récupération des clés et des permissions. La défaillance la plus courante « on ne peut pas restaurer » est l’accès, pas les données.

8) La PITR peut-elle restaurer au travers de mises à niveau majeures de version ?

Typiquement, non en place pour des sauvegardes physiques. Les changements de version majeure nécessitent souvent une migration logique ou des outils de mise à niveau. Votre plan PITR doit supposer « restauration sur la même version majeure », puis mise à niveau après si nécessaire.

9) Comment empêcher que l’instance restaurée ne se reconnecte accidentellement à la production ?

Placez les restaurations dans des réseaux isolés, changez les identifiants, et désactivez explicitement la connectivité sortante vers les services de production. Aussi : renommez le cluster et ajoutez des bannières visibles dans la supervision.

10) Quel est l’indicateur unique que je souhaite que chaque équipe suive ?

« Temps depuis le début de l’incident jusqu’aux données restaurées et validées ». Pas « sauvegarde réussie ». Pas « WAL archivé ». Le résultat de bout en bout est ce que l’entreprise expérimente.

Prochaines étapes qui réduisent vraiment le risque

Si vous ne faites rien d’autre ce trimestre, faites ceci : planifiez un exercice PITR, exécutez-le sur une infrastructure qui ressemble à la production, et notez exactement où vous vous êtes embrouillés. Cette confusion est votre futur incident.

• Choisissez une base de données (celle qui fait le plus mal) et effectuez une restauration vers un temps marqueur connu.
• Instrumentez la chaîne : alertez sur les échecs d’expédition binlog/WAL et la fraîcheur des archives.
• Standardisez les cibles : horodatages UTC plus une position/LSN/GTID quand c’est possible.
• Mesurez le RTO et décidez s’il faut investir dans du stockage plus rapide, des sauvegardes de base plus fréquentes ou des fenêtres de replay plus petites.
• Mettez à jour le runbook immédiatement après l’exercice, tant que la douleur est fraîche et que les leçons sont honnêtes.

La PITR n’est pas une fonctionnalité que vous activez. C’est une compétence que vous entraînez. La restauration se moque de la confiance que vous affichez en réunion.