PITR et archivage des WAL – Restauration à un instant précis

Bonne lecture et bon apprentissage !
Junior TSAFACK – 20/08/2026
⏱️ Temps de lecture estimé : 12 minutes

---

Les sauvegardes logiques (pg_dump) sont utiles, mais elles présentent une limite majeure : elles ne permettent de restaurer qu’à l’instant de la dernière sauvegarde. Que se passe-t-il si une table est supprimée accidentellement à 14h32, alors que la dernière sauvegarde date de 2h du matin ? Vous perdez toutes les données de la journée.

Le PITR (Point-In-Time Recovery) résout ce problème. Il permet de restaurer une base de données à n’importe quel instant dans le passé, à la seconde près, en combinant une sauvegarde physique (copie des fichiers de données) et l’archivage continu des WAL (journaux de transactions).

---

Qu’est-ce que le PITR ?

Le PITR (Point-In-Time Recovery) est une technique de restauration qui permet de rejouer les journaux de transactions (WAL) à partir d’une sauvegarde physique jusqu’à un instant précis.

Comment ça fonctionne ?

┌─────────────────────────────────────────────────────────────────────┐
│                    PRINCIPE DU PITR                                 │
│                                                                     │
│  ┌─────────────────────┐    ┌──────────────────────────────────┐   │
│  │  Sauvegarde physique │    │     Archivage continu des WAL    │   │
│  │  (pg_basebackup)     │    │  ┌──────┐ ┌──────┐ ┌──────────┐ │   │
│  │  ┌─────────────────┐ │    │  │WAL 1 │ │WAL 2 │ │WAL 3 ... │ │   │
│  │  │ Données à 02:00 │ │    │  └──────┘ └──────┘ └──────────┘ │   │
│  │  └─────────────────┘ │    └──────────────────────────────────┘   │
│  └─────────────────────┘                     │                      │
│              │                                │                      │
│              ▼                                ▼                      │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │                    RESTAURATION PITR                         │   │
│  │                                                              │   │
│  │   1. Restaurer la sauvegarde physique (02:00)               │   │
│  │   2. Rejouer les WAL jusqu'à l'instant cible (14:31:59)    │   │
│  │   3. Base restaurée à l'instant T                          │   │
│  │                                                              │   │
│  └──────────────────────────────────────────────────────────────┘   │
│                                                                     │
│  ⏱️  Instant cible : 2026-08-20 14:31:59                           │
└─────────────────────────────────────────────────────────────────────┘

Avantages du PITR

Avantage	Description
Restauration granulaire	Retour à n’importe quel instant (à la seconde près).
Protection contre les erreurs humaines	Annulation d’un DROP TABLE ou d’un DELETE accidentel.
Sauvegarde à chaud	La sauvegarde physique peut être prise pendant que la base tourne.
Efficace pour les grandes bases	Pas besoin de sauvegardes logiques quotidiennes longues.

Inconvénients

Inconvénient	Description
Complexité	Plus complexe à mettre en œuvre qu’un simple pg_dump.
Espace de stockage	Les WAL archivés peuvent occuper beaucoup d’espace.
Temps de restauration	La restauration peut prendre du temps selon la quantité de WAL à rejouer.

---

Composants du PITR

1. Les WAL (Write-Ahead Logs)

Les WAL sont des journaux de transactions qui enregistrent chaque modification apportée à la base de données. Ils sont stockés dans le répertoire pg_wal/ (ou pg_xlog/ avant PostgreSQL 10).

Caractéristiques :

• Chaque fichier WAL fait 16 Mo par défaut.
• Les fichiers sont nommés numériquement (ex : 000000010000000000000001).
• Sans archivage, les fichiers WAL sont réutilisés en boucle.

💡 Bon à savoir : Les WAL servent également à la crash recovery : en cas d’arrêt brutal, PostgreSQL rejoue les WAL pour rétablir la cohérence des données.

2. L’archivage continu des WAL

L’archivage continu consiste à copier chaque fichier WAL vers un emplacement sécurisé (disque, serveur distant, S3) avant qu’il ne soit réutilisé par le système.

3. La sauvegarde physique (Base Backup)

La sauvegarde physique est une copie brute de l’ensemble des fichiers de données du cluster PostgreSQL. Elle est généralement réalisée avec pg_basebackup.

---

Configuration de l’archivage des WAL

Étape 1 : Créer le répertoire d’archivage

# Créer le répertoire d'archivage
sudo mkdir -p /var/lib/postgresql/wal_archive
sudo chown postgres:postgres /var/lib/postgresql/wal_archive

Étape 2 : Configurer postgresql.conf

/etc/postgresql/16/main/postgresql.conf

# Activer l'archivage des WAL
wal_level = replica                    # Nécessaire pour l'archivage
archive_mode = on                      # Active l'archivage
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

Explication des paramètres :

Paramètre	Description
wal_level	Niveau de journalisation. replica est le minimum pour l’archivage.
archive_mode	Active l’archivage des WAL.
archive_command	Commande shell exécutée pour archiver chaque fichier WAL.
%p	Chemin complet du fichier WAL à archiver.
%f	Nom du fichier WAL.

💡 Bon à savoir : test ! -f vérifie que le fichier n’existe pas déjà dans le répertoire d’archivage, évitant ainsi les écrasements.

Étape 3 : Options d’archivage avancées

Archivage avec compression :

archive_command = 'gzip < %p > /var/lib/postgresql/wal_archive/%f.gz'

Archivage vers un serveur distant (rsync) :

archive_command = 'rsync -a %p backup-server:/wal_archive/%f'

Archivage vers S3 (avec AWS CLI) :

archive_command = 'aws s3 cp %p s3://my-bucket/wal-archive/%f'

Archivage avec WAL-G (recommandé pour le cloud) :

archive_command = 'wal-g wal-push %p'

Étape 4 : Redémarrer PostgreSQL

sudo systemctl restart postgresql

Étape 5 : Vérifier l’archivage

-- Vérifier le statut de l'archivage
SELECT * FROM pg_stat_archiver;

-- Forcer un switch de WAL pour tester l'archivage
SELECT pg_switch_wal();

-- Vérifier que les fichiers apparaissent dans le répertoire
\! ls -la /var/lib/postgresql/wal_archive/

---

Sauvegarde physique avec pg_basebackup

Qu’est-ce que pg_basebackup ?

pg_basebackup est l’outil officiel de PostgreSQL pour réaliser des sauvegardes physiques à chaud (sans arrêter la base).

Syntaxe de base

pg_basebackup [options] -D <répertoire_destination>

Options principales

Option	Description	Exemple
-h	Hôte du serveur	-h localhost
-U	Utilisateur	-U backup
-D	Répertoire de destination	-D /backup/base/
-Ft	Format tar (fichiers compressés)	-Ft
-z	Compression gzip	-z
-X stream	Inclut les WAL pendant la sauvegarde	-X stream
--checkpoint=fast	Force un checkpoint immédiat	--checkpoint=fast
-P	Affiche la progression	-P

Exemple complet

# Créer un utilisateur pour les sauvegardes
psql -c "CREATE USER backup REPLICATION LOGIN ENCRYPTED PASSWORD 'backup_password';"

# Autoriser dans pg_hba.conf
# host replication backup 10.0.0.20/32 scram-sha-256

# Prendre une sauvegarde
pg_basebackup \
    -h localhost \
    -U backup \
    -D /backup/base/$(date +%Y%m%d_%H%M) \
    -Ft \
    -z \
    -X stream \
    --checkpoint=fast \
    -P

Résultat :

ls -la /backup/base/20260820_0200/
# base.tar.gz   (fichiers de données compressés)
# pg_wal.tar.gz (WAL de la sauvegarde)

💡 Bon à savoir : L’option -X stream garantit qu’aucun WAL n’est perdu entre le début et la fin de la sauvegarde.

---

Restauration PITR

Scénario : Suppression accidentelle à 14:32:00

Imaginons qu’un utilisateur exécute DELETE FROM commandes WHERE date < '2024-01-01'; à 14:32:00. Vous voulez restaurer la base à l’état qu’elle avait à 14:31:59.

Étape 1 : Arrêter PostgreSQL sur le serveur cible

sudo systemctl stop postgresql

Étape 2 : Nettoyer le répertoire de données

sudo rm -rf /var/lib/postgresql/16/main/*

Étape 3 : Restaurer la sauvegarde physique

# Restaurer les fichiers de données
sudo tar -xzf /backup/base/20260820_0200/base.tar.gz -C /var/lib/postgresql/16/main/

# Restaurer les WAL (si présents dans la sauvegarde)
sudo tar -xzf /backup/base/20260820_0200/pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal/

Étape 4 : Configurer la restauration

Méthode 1 : PostgreSQL 12+ (recommandée)

Créer le fichier standby.signal :

sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

Configurer les paramètres de restauration dans postgresql.conf (ou postgresql.auto.conf) :

/var/lib/postgresql/16/main/postgresql.conf

# Commande pour restaurer les WAL depuis l'archive
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'

# Point de restauration : temps précis
recovery_target_time = '2026-08-20 14:31:59 UTC'

# Récupérer sur la timeline la plus récente
recovery_target_timeline = 'latest'

Méthode 2 : PostgreSQL 11 et antérieurs

Créer le fichier recovery.conf :

sudo -u postgres vim /var/lib/postgresql/16/main/recovery.conf

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-08-20 14:31:59 UTC'
recovery_target_timeline = 'latest'

Étape 5 : Démarrer PostgreSQL

sudo systemctl start postgresql

PostgreSQL démarre en mode récupération : il restaure la sauvegarde, puis rejoue les WAL archivés jusqu’à l’instant cible.

Étape 6 : Vérifier la restauration

-- Vérifier que la base est en récupération
SELECT pg_is_in_recovery();
-- Résultat : true (tant que la récupération n'est pas terminée)

-- Vérifier les données restaurées
SELECT COUNT(*) FROM commandes WHERE date < '2024-01-01';
-- Résultat : les données supprimées sont de retour

-- Une fois satisfait, arrêter le mode récupération
-- (soit en promouvant, soit en arrêtant et en supprimant le fichier)

Étape 7 : Promouvoir la base (si nécessaire)

Si la restauration est réussie et que vous voulez utiliser cette base en production :

# Promouvoir la base en master
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main/

---

Options de ciblage pour la restauration

PostgreSQL offre plusieurs façons de spécifier l’instant de restauration :

Option	Description	Exemple
recovery_target_time	Restauration à une date/heure précise	'2026-08-20 14:31:59 UTC'
recovery_target_lsn	Restauration à un LSN (Log Sequence Number) spécifique	'3/72658818'
recovery_target_xid	Restauration à un ID de transaction	'1234567'
recovery_target_name	Restauration à un point nommé (créé avec pg_create_restore_point)	'before_deletion'
recovery_target_timeline	Timeline de récupération (généralement 'latest')	'latest'

Exemple avec LSN

-- Trouver le LSN avant la suppression
SELECT pg_current_wal_lsn();

-- Dans recovery.conf
recovery_target_lsn = '3/72658818'

Exemple avec point de restauration nommé

-- Créer un point de restauration avant une opération risquée
SELECT pg_create_restore_point('before_migration');

-- Dans recovery.conf
recovery_target_name = 'before_migration'

---

Planification des sauvegardes

Stratégie recommandée

Fréquence	Type	Description
Quotidienne	Sauvegarde physique	pg_basebackup à 2h00 du matin.
Continue	Archivage WAL	Tous les fichiers WAL sont archivés en temps réel.
Hebdomadaire	Rotation	Nettoyage des sauvegardes de plus de 30 jours.
Mensuelle	Test de restauration	Vérification de l’intégrité des sauvegardes.

Script de sauvegarde automatisé

/usr/local/bin/backup_postgresql.sh

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M)
BACKUP_DIR="/backup/base"
WAL_ARCHIVE="/var/lib/postgresql/wal_archive"
LOG_FILE="/var/log/postgresql/backup.log"

# Nettoyer les WAL archivés de plus de 7 jours
find $WAL_ARCHIVE -name "*.gz" -mtime +7 -delete

# Prendre la sauvegarde physique
echo "$(date): Début de la sauvegarde" >> $LOG_FILE
pg_basebackup \
    -h localhost \
    -U backup \
    -D $BACKUP_DIR/$DATE \
    -Ft -z \
    -X stream \
    --checkpoint=fast \
    -P >> $LOG_FILE 2>&1

# Vérifier le succès
if [ $? -eq 0 ]; then
    echo "$(date): Sauvegarde réussie" >> $LOG_FILE

    # Supprimer les sauvegardes de plus de 30 jours
    find $BACKUP_DIR -name "20*" -type d -mtime +30 -exec rm -rf {} \;
else
    echo "$(date): ERREUR lors de la sauvegarde" >> $LOG_FILE
    exit 1
fi

Planification avec cron

# Sauvegarde quotidienne à 2h00
0 2 * * * /usr/local/bin/backup_postgresql.sh

# Nettoyage des WAL archivés (déjà dans le script)

---

Outils avancés pour le PITR

Outil	Description
pgBackRest	Solution de sauvegarde complète avec compression, parallélisation et support S3.
WAL-G	Outil moderne pour l’archivage WAL et les sauvegardes vers le cloud (S3, GCS, Azure).
Barman	Outil de backup et recovery développé par EnterpriseDB.
Pigsty	Solution intégrée avec pgBackRest pour la gestion des sauvegardes.

Exemple avec WAL-G

# Installation
curl -L https://github.com/wal-g/wal-g/releases/latest/download/wal-g-pg-ubuntu-20.04-amd64.tar.gz | tar xz
sudo mv wal-g /usr/local/bin/

# Configuration
export WALG_S3_PREFIX=s3://my-backup-bucket/postgresql/
export AWS_ACCESS_KEY_ID=your_key
export AWS_SECRET_ACCESS_KEY=your_secret

# Archive_command avec WAL-G
archive_command = 'wal-g wal-push %p'

# Sauvegarde avec WAL-G
wal-g backup-push /var/lib/postgresql/16/main/

# Restauration avec WAL-G
wal-g backup-fetch /var/lib/postgresql/16/main/ LATEST

---

Bonnes pratiques

Pratique	Description
Tester les restaurations	Effectuez des tests de restauration au moins une fois par mois. Une sauvegarde non testée n’est pas une sauvegarde.
Stockage externe	Ne stockez pas les sauvegardes sur le même serveur que la base.
Surveiller l’espace disque	Les WAL archivés peuvent rapidement saturer le disque. Mettez en place une rotation.
Utiliser un format compressé	Utilisez -Ft -z avec pg_basebackup pour réduire la taille.
Documenter la procédure	Documentez les commandes de restauration et les points de contact.
Automatiser les sauvegardes	Utilisez cron ou des outils comme pgBackRest.
Vérifier les logs	Consultez régulièrement les logs de l’archivage (pg_stat_archiver).
Utiliser un outil dédié	Pour les environnements de production, privilégiez pgBackRest ou WAL-G.

---

Commandes récapitulatives

Action	Commande
Créer un répertoire d’archivage	mkdir -p /var/lib/postgresql/wal_archive
Configurer l’archivage	Modifier wal_level, archive_mode, archive_command
Redémarrer PostgreSQL	sudo systemctl restart postgresql
Vérifier l’archivage	SELECT * FROM pg_stat_archiver;
Forcer un switch WAL	SELECT pg_switch_wal();
Prendre une sauvegarde physique	pg_basebackup -Ft -z -X stream -D /backup/base/
Restaurer une sauvegarde	tar -xzf base.tar.gz -C /var/lib/postgresql/16/main/
Configurer la restauration	restore_command = 'cp /wal_archive/%f %p'
Cibler un temps	recovery_target_time = '2026-08-20 14:31:59 UTC'
Démarrer en récupération	sudo systemctl start postgresql
Promouvoir la base	pg_ctl promote -D /var/lib/postgresql/16/main/
Vérifier l’état de récupération	SELECT pg_is_in_recovery();

---

Prochain chapitre

Vous savez maintenant configurer l’archivage des WAL et effectuer une restauration PITR. Ces techniques sont essentielles pour protéger vos données contre les erreurs humaines et les sinistres.

---

Junior TSAFACK – 20/08/2026