Restauration Point-In-Time d’un cluster Patroni avec pgBackRest

Dans un article précédent, nous avons vu comment mettre en place une réplication logique avec Patroni. Nous allons voir les méthodes de restauration à un instant donné (PITR - Point-In-Time Recovery) avec ce même outil.

Grâce à l’intégration de pgBackRest et Patroni, il est possible de reconstruire proprement un cluster en cas de suppression ou corruption de données — tout en remontant précisément à une date ou une transaction cible.

Prérequis pour la restauration PITR

Avant toute tentative de restauration temporelle, assurez-vous que :

• pgBackRest est correctement configuré ;
• L’archivage des WAL est activé ;
• Au moins une sauvegarde complète est disponible.

Mise en situation : suppression de données

Créons une base de test et insérons quelques lignes dans une table :

CREATE DATABASE pitr_test;
\c pitr_test
CREATE TABLE mytable (
  id INT,
  payload TEXT,
  inserted_at TIMESTAMP DEFAULT now()
);
INSERT INTO mytable SELECT n, 'text_' || n FROM generate_series(0,10) n;
SELECT * FROM mytable;

Résultat :

 id | payload |        inserted_at
----+---------+---------------------------
  0 | text_0  | 2025-07-21 09:14:15.78179
...
 10 | text_10 | 2025-07-21 09:14:15.78179

La transaction d’insertion est identifiable via le champ xmin :

SELECT xmin, xmax, * FROM mytable;

 xmin | xmax | id | payload | inserted_at
------+-------+----+---------+----------------------------
  752 |     0 |  0 | text_0  | 2025-07-21 09:14:15.78179
...

Supposons qu’une action destructive intervienne :

TRUNCATE mytable;

On force ensuite l’écriture des journaux de transaction pour garantir leur archivage :

SELECT pg_switch_wal();
SELECT now();

Étapes de restauration

Nous vous proposons deux méthodes afin de restaurer vos instances à la cible voulue.

Méthode 1 : restauration manuelle du primaire

1. Identifier le nœud primaire

Dans notre exemple, le primaire est pgdeb01.

patronictl -c /etc/patroni/config.yml topology
+ Cluster: loxodemo (7427142939826752414) ------+----+-----------+
| Member    | Host        | Role    | State     | TL | Lag in MB |
+-----------+-------------+---------+-----------+----+-----------+
| pgdeb01   | 10.200.0.11 | Leader  | running   |  3 |           |
| + pgdeb02 | 10.200.0.12 | Replica | streaming |  3 |         0 |
| + pgdeb03 | 10.200.0.13 | Replica | streaming |  3 |         0 |
+-----------+-------------+---------+-----------+----+-----------+

2. Stopper Patroni sur les réplicas

Dans notre exemple, sur pgdeb02 et pgdeb03.

systemctl stop patroni

3. Stopper Patroni sur le primaire

Une fois arrêté, le cluster doit apparaître vide :

patronictl -c /etc/patroni/config.yml topology
+ Cluster: loxodemo (7427142939826752414) ------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+------+------+-------+----+-----------+
+--------+------+------+-------+----+-----------+

4. Restaurer les données sur le futur primaire

On peut choisir n’importe quel nœud du cluster patroni pour effectuer cette opération.

pgbackrest --stanza=loxodemo --delta restore \
  --type=time \
  --target="2025-07-21 09:14:16.78179+00" \
  --target-action=promote

5. Lancer PostgreSQL localement (sans Patroni)

Cette opération s’effectue toujours sur le futur primaire.

su - postgres -c "/usr/lib/postgresql/16/bin/postgres -D /data/postgres/loxodemo ..."

6. Vérifier les logs de PostgreSQL

Extrait typique indiquant la fin de la restauration :

recovery stopping before commit of transaction 753
last completed transaction was at log time 2025-07-21 09:14:15.792863+00

7. Vérifier les données restaurées

Dans un autre terminal, lancez psql sur la base à vérifier.

pitr_test=# select * from mytable;
 id | payload |        inserted_at
----+---------+---------------------------
  0 | text_0  | 2025-07-21 09:14:15.78179
  1 | text_1  | 2025-07-21 09:14:15.78179
  2 | text_2  | 2025-07-21 09:14:15.78179
  3 | text_3  | 2025-07-21 09:14:15.78179
  4 | text_4  | 2025-07-21 09:14:15.78179
  5 | text_5  | 2025-07-21 09:14:15.78179
  6 | text_6  | 2025-07-21 09:14:15.78179
  7 | text_7  | 2025-07-21 09:14:15.78179
  8 | text_8  | 2025-07-21 09:14:15.78179
  9 | text_9  | 2025-07-21 09:14:15.78179
 10 | text_10 | 2025-07-21 09:14:15.78179
(11 rows)

Les données insérées à 09:14:15 sont bien présentes, la suppression ayant eu lieu après.

Forcer l’écriture des journaux de transaction.

select pg_switch_wal();
 pg_switch_wal
---------------
 0/100072D0

8. Stopper PostgreSQL localement

9. Redémarrer Patroni sur le primaire choisi

systemctl start patroni

10. Vérifier la promotion

patronictl -c /etc/patroni/config.yml topology
+ Cluster: loxodemo (7427142939826752414) -+----+-----------+
| Member  | Host        | Role   | State   | TL | Lag in MB |
+---------+-------------+--------+---------+----+-----------+
| pgdeb01 | 10.200.0.11 | Leader | running |  5 |           |
+---------+-------------+--------+---------+----+-----------+

11. Redémarrer Patroni sur chaque réplica

systemctl start patroni

patronictl -c /etc/patroni/config.yml topology
+ Cluster: loxodemo (7427142939826752414) ------+----+-----------+
| Member    | Host        | Role    | State     | TL | Lag in MB |
+-----------+-------------+---------+-----------+----+-----------+
| pgdeb01   | 10.200.0.11 | Leader  | running   |  5 |           |
| + pgdeb02 | 10.200.0.12 | Replica | streaming |  5 |         0 |
| + pgdeb03 | 10.200.0.13 | Replica | streaming |  5 |         0 |
+-----------+-------------+---------+-----------+----+-----------+

Méthode 2 : Bootstrap du cluster avec Patroni

Cette méthode est plus lourde, mais parfois nécessaire (ex. : restauration complète ou scriptée).

1. Connectez-vous sur une des machines qui sera le futur primaire.

2. Supprimer ou déplacer le répertoire de données de PostgreSQL

rm -rf /data/postgres/loxodemo

ou

mv /data/postgres/loxodemo /data/postgres/loxodemo_archive

3. Supprimer le cluster dans Etcd

patronictl -c /etc/patroni/config.yml remove loxodemo

Confirmation obligatoire :

Please confirm the cluster name to remove: loxodemo
You are about to remove all information ... please type: "Yes I am aware": Yes I am aware

4. Modifier la configuration de Patroni

Ajoutez à la section bootstrap :

bootstrap:
  method: pgbackrest
  pgbackrest:
    command: '/usr/bin/pgbackrest --stanza=loxodemo --delta restore'
    keep_existing_recovery_conf: false
    no_params: true
    recovery_conf:
      recovery_target_action: promote
      recovery_target_time: "2025-07-21 09:14:16.78179+00"
      restore_command: pgbackrest --stanza=loxodemo archive-get %f %p

5. Redémarrer Patroni sur le futur primaire

systemctl start patroni

Les logs doivent indiquer la séquence de bootstrap :

2025-07-21 12:59:23 INFO: Selected new etcd server http://10.200.0.12:2379
2025-07-21 12:59:23 INFO: No PostgreSQL configuration items changed, nothing to reload.
2025-07-21 12:59:23 INFO: Lock owner: None; I am pgdeb01
2025-07-21 12:59:23 INFO: trying to bootstrap a new cluster
2025-07-21 12:59:23 INFO: Running custom bootstrap script: /usr/bin/pgbackrest --stanza=loxodemo restore
2025-07-21 12:59:24 INFO: Lock owner: None; I am pgdeb01
2025-07-21 12:59:24 INFO: not healthy enough for leader race
2025-07-21 12:59:24 INFO: bootstrap in progress
2025-07-21 12:59:25 INFO: Lock owner: None; I am pgdeb01
2025-07-21 12:59:25 INFO: not healthy enough for leader race
2025-07-21 12:59:25 INFO: bootstrap in progress
2025-07-21 12:59:26 INFO: Lock owner: None; I am pgdeb01

Forcer l’écriture des journaux de transaction et par conséquent l’archivage.

select * from pg_switch_wal();
 pg_switch_wal
---------------
 0/9004A00
(1 row)

6. Redémarrer Patroni sur chaque réplica

systemctl start patroni

Contrôler l’état du cluster:

patronictl -c /etc/patroni/config.yml topology
+ Cluster: loxodemo (7427142939826752414) ------+-----+-----------+
| Member    | Host        | Role    | State     |  TL | Lag in MB |
+-----------+-------------+---------+-----------+-----+-----------+
| pgdeb01   | 10.200.0.11 | Leader  | running   |  10 |           |
| + pgdeb02 | 10.200.0.12 | Replica | streaming |  10 |         0 |
| + pgdeb03 | 10.200.0.13 | Replica | streaming |  10 |         0 |
+-----------+-------------+---------+-----------+-----+-----------+

Quelle méthode choisir ?

Conclusion

Grâce à pgBackRest et Patroni, il est possible de restaurer un cluster PostgreSQL à un instant précis, que ce soit pour corriger une erreur humaine ou revenir à un état stable après une défaillance. Selon les besoins (rapidité vs réinitialisation complète), l’une ou l’autre des méthodes peut être utilisée efficacement.