PostgreSQL Backup & Restore : méthodes logiques et physiques avec PITR

Un runbook pratique et versionné pour sauvegarder et restaurer PostgreSQL avec pg_dump/pg_restore (logique) et pg_basebackup + WAL/PITR (physique), incluant commandes copiables, vérifications multicouches, diagnostics de panne et check-list d'exploitation reproductible.

Prerequisites & Assumptions

Avant toute opération, consolidez les faits suivants. Les exemples ciblent PostgreSQL 14–16 ; les différences majeures sont notées dans l'appendice.

OS et système de fichiers : Linux (ext4, XFS, ZFS) avec espace disque ≥ 1,5× à 2× la taille du cluster pour accueillir base backup, WAL archivés et restauration temporaire.
Synchronisation temps : NTP ou chrony actif sur tous les nœuds ; les horodatages PITR doivent être en UTC.
Outils clients : psql, pg_dump, pg_dumpall, pg_restore, pg_basebackup installés et alignés sur la version majeure du serveur (parité client/serveur recommandée).
Utilisateur de sauvegarde :
Physique (pg_basebackup) : rôle avec REPLICATION et LOGIN (souvent superutilisateur en pratique).
Logique : rôle avec CONNECT et SELECT sur les objets ciblés (principe du moindre privilège).
Authentification : .pgpass (mode 0600) ou variables d'environnement PGPASSWORD/PGSSLMODE ; pour services managés, IAM ou certificats selon le fournisseur.
Extensions : inventaire via SELECT extname, extversion FROM pg_extension; ; binaires correspondants présents sur la cible de restauration.
Espace et rétention : prévoir au minimum 3 cycles complets (base backup + WAL nécessaires) sur le stockage de destination.
☁️ MANAGED SERVICE : RDS, Cloud SQL, Azure Database limitent l'accès filesystem, interdisent pg_basebackup, restreignent wal_level et imposent leurs propres fenêtres de backup automatisé et PITR via console/API. La méthode logique reste la voie portable.

Architecture & Method Selection

Choisissez en fonction de RPO/RTO, volume, portabilité de version et contraintes de service managé.

Méthode — Cas d'usage principal — RPO / RTO typique — Commande de départ
Dump logique (pg_dump -Fc/-Fd) — Migrations cross-version, restauration sélective, schémas modérés — RPO = dernier dump réussi ; RTO proportionnel au volume — pg_dump -Fd -j 4 -f /backups/appdb_<DATE>_dir appdb
Globaux logiques (pg_dumpall -g) — Rôles, tablespaces, GRANT système — À rejouer avant les données — pg_dumpall -g > globals.sql
Physique + WAL (pg_basebackup + archivage) — Gros volumes (> 500 Go), PITR continu, seed standby — RPO secondes–minutes ; RTO rapide (copie fichiers) — pg_basebackup -D /backups/base_<DATE> -X stream --wal-method=stream --slot=backup_slot --create-slot --max-rate=100M

Règle pilote : validez d'abord sur une base/schéma réduit, confirmez la restauration, puis généralisez.

Security Hardening

Chiffrement au repos : chiffrez les dumps et archives WAL avec gpg --symmetric --cipher-algo AES256 ou age -e -p avant écriture sur le stockage durable. Stockez les mots de passe hors du serveur (coffre à secrets, HSM).
Chiffrement en transit : imposez TLS pour tout dump/base backup distant.

  pg_dump "postgresql://user@host:5432/db?sslmode=verify-full&sslrootcert=/path/ca.crt&sslcert=/path/client.crt&sslkey=/path/client.key"

pg_basebackup utilise la même chaîne de connexion libpq.

Gestion des identifiants : .pgpass (0600), variables d'environnement à durée de vie courte, rôles IAM pour services managés. Jamais de mots de passe en dur dans les scripts.
Permissions fichiers : répertoire de données 0700 (chmod 700 $PGDATA), fichiers de dump 0600, répertoire d'archive WAL 0700 appartenant à l'utilisateur postgres.
Secrets dans globals.sql : pg_dumpall -g exporte les définitions de rôles (y compris mots de passe hachés). Traitez ce fichier comme sensible ; chiffrez-le et limitez son audience.
🔒 SECURITY : archive_command avec cp (exemple local) n'offre aucune durabilité hors hôte. En production, utilisez pgBackRest, Barman, WAL-G ou un stockage objet cloud avec cohérence forte.

Logical Backup & Restore

Globaux (rôles, tablespaces, privilèges système)

pg_dumpall -g > globals_<DATE>.sql
# Vérification rapide
head -n 30 globals_<DATE>.sql

⚠️ WARNING : pg_dumpall -g n'inclut pas ALTER DEFAULT PRIVILEGES ni les event triggers. Si utilisés, dumpez-les séparément :

psql -d postgres -c "SELECT pg_catalog.pg_get_default_privileges();" > default_privs_<DATE>.sql
psql -d postgres -c "SELECT * FROM information_schema.event_triggers;" > event_triggers_<DATE>.sql

Base de données — format répertoire (dump parallèle)

pg_dump -Fd -j 4 -f /backups/appdb_<DATE>_dir -U appuser appdb
# Résultat attendu : répertoire non vide avec toc.dat et fichiers de données compressés
ls -lh /backups/appdb_<DATE>_dir/

Options utiles :

Schéma seul : -s
Données seules : -a
Exclure tables : -T 'temp_*'
Large objects : -b (par défaut depuis PG 14, explicite avant)

Restauration des globaux

psql -f globals_<DATE>.sql postgres

Création de la base cible

createdb -T template0 -E UTF8 --lc-collate=C --lc-ctype=C -O appuser appdb
psql -d postgres -c "\l+ appdb"

💡 TIP : template0 garantit un encodage/locale propre, évitant les collisions de collation.

Restauration parallèle depuis le format répertoire

pg_restore -d appdb -j 4 -v --clean --if-exists /backups/appdb_<DATE>_dir
# Résultat attendu : progression sans erreur fatale ; warnings acceptables (commentaires, statistiques)

Correctifs post-restauration fréquents

Extensions manquantes : installez les paquets (ex. postgresql-16-postgis-3) puis CREATE EXTENSION IF NOT EXISTS postgis VERSION '3.4.0'; sur la cible.
Privilèges par défaut : rejouez default_privs_<DATE>.sql si applicable.
Séquences : réalignez avec is_called = false pour éviter le décalage off-by-one.

   SELECT setval(
     pg_get_serial_sequence('public.orders', 'id'),
     COALESCE((SELECT MAX(id) FROM public.orders), 0) + 1,
     false
   );

📌 VERSION NOTE : is_called = false signifie « la prochaine valeur retournée par nextval sera celle-ci » ; true aurait déjà consommé la valeur.

Statistiques : VACUUM (ANALYZE); sur la base restaurée.

Physical Backup & PITR

Configuration de l'archivage WAL (production)

postgresql.conf :

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
# Alternative WAL-G : archive_command = 'wal-g wal-push %p'
# Alternative Barman : archive_command = 'barman-wal-archive main %p'
max_wal_senders = 10
wal_keep_size = 2GB
# Ou réplication slot dédié (recommandé pour pg_basebackup -X stream)

Créez le slot de réplication avant le premier base backup :

psql -d postgres -c "SELECT * FROM pg_create_physical_replication_slot('backup_slot');"

🔒 SECURITY : test ! -f ... && cp (exemple local) n'est pas atomique et ne protège pas contre la perte hôte. pgBackRest/WAL-G/Barman gèrent la rétention, la compression, le chiffrement et la validation d'intégrité.

Rechargez la configuration :

psql -d postgres -c "SELECT pg_reload_conf();"
psql -d postgres -c "SELECT pg_switch_wal();"
ls -l /var/lib/pgbackrest/archive/main/  # ou emplacement WAL-G/Barman

Base backup physique avec throttling et slot

sudo -u postgres pg_basebackup \
  -D /backups/base_<DATE> \
  -X stream \
  --wal-method=stream \
  --slot=backup_slot \
  --create-slot \
  --max-rate=100M \
  -c fast \
  -Fp \
  -v -P

Résultat attendu : répertoire base_<DATE> complet avec backup_label, backup_manifest (PG 13+), et fichiers de données. Le slot garantit la rétention des WAL pendant la copie.

Restauration PITR vers un nouveau data_dir (port alternatif)

# Arrêt de l'instance cible (si même hôte)
systemctl stop postgresql

# Préparation du répertoire de restauration
rm -rf /var/lib/pgsql/data_restore
mkdir -p /var/lib/pgsql/data_restore
chown postgres:postgres /var/lib/pgsql/data_restore
chmod 700 /var/lib/pgsql/data_restore

# Copie de la base backup
rsync -a --delete /backups/base_<DATE>/ /var/lib/pgsql/data_restore/
chown -R postgres:postgres /var/lib/pgsql/data_restore

# Configuration de la récupération
cat >> /var/lib/pgsql/data_restore/postgresql.conf <<'EOF'
restore_command = 'pgbackrest --stanza=main archive-get %f %p'
# Alternative WAL-G : restore_command = 'wal-g wal-fetch %f %p'
recovery_target_time = 'YYYY-MM-DD HH:MI:SS UTC'
recovery_target_action = 'pause'
# Pour promotion standby ultérieure (optionnel) :
# primary_conninfo = 'host=primary_host port=5432 user=repl password=secret'
EOF

touch /var/lib/pgsql/data_restore/recovery.signal

# Démarrage sur port alternatif
pg_ctl -D /var/lib/pgsql/data_restore -o "-p 5444" -l /var/lib/pgsql/data_restore.log start

# Suivi des logs
tail -f /var/lib/pgsql/data_restore.log

Validation et promotion

# Vérifier l'état de récupération
psql -p 5444 -d postgres -c "SELECT pg_is_in_recovery();"  -- attend 't'

# Inspecter les données
psql -p 5444 -d appdb -c "SELECT count(*) FROM public.orders;"
psql -p 5444 -d postgres -c "SELECT current_timestamp;"

# Promouvoir (fin de recovery, passage en primary)
psql -p 5444 -d postgres -c "SELECT pg_promote();"
psql -p 5444 -d postgres -c "SELECT pg_is_in_recovery();"  -- attend 'f'

📌 VERSION NOTE : pg_promote() existe depuis PG 12. Avant : pg_ctl promote -D ... ou fichier promote.signal (PG 12+) / trigger file (ancien).

Verification & Validation

Validez à cinq couches avant de déclarer une sauvegarde exploitable.

1. Fichiers & catalogue

# Logique : existence, taille, listage
ls -lh /backups/appdb_<DATE>_dir/
pg_restore -l /backups/appdb_<DATE>_dir/ | head -n 30

# Physique : manifeste, backup_label, permissions
cat /var/lib/pgsql/data_restore/backup_manifest  # PG 13+
ls -la /var/lib/pgsql/data_restore/backup_label

2. Restauration jetable (logique)

createdb -T template0 -E UTF8 -O appuser appdb_verify
pg_restore -d appdb_verify -j 4 /backups/appdb_<DATE>_dir

3. Contrôles de données (SQL pack réutilisable)

-- validation_pack.sql
-- 1. Comptes de tables
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY schemaname, relname;

-- 2. Séquences : last_value vs max réel
SELECT c.relname AS seq_name,
       s.last_value,
       (SELECT COALESCE(MAX(id),0) FROM public.orders) AS max_id
FROM pg_class c
JOIN pg_sequences s ON s.sequencename = c.relname
WHERE c.relkind = 'S';

-- 3. Taille TOAST
SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total,
       pg_size_pretty(pg_relation_size('public.orders')) AS main,
       pg_size_pretty(pg_total_relation_size('public.orders') - pg_relation_size('public.orders')) AS toast;

-- 4. Validité des index
SELECT indexrelname, indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT indisvalid;

-- 5. Nettoyage snapshot pour stats fraîches
SELECT pg_stat_clear_snapshot();

-- 6. Checksum applicatif (exemple)
-- SELECT md5(string_agg(id::text || '|' || data, '' ORDER BY id)) FROM public.orders;

Exécution :

psql -d appdb_verify -f validation_pack.sql

4. Test applicatif (smoke test)

Lancez la suite de tests en lecture seule de l'application contre appdb_verify (ou l'instance PITR sur port 5444).

5. Spécifique PITR

Surveillez les logs pour recovery stopping before... ou consistent recovery state reached.
Vérifiez pg_is_in_recovery() = true pendant la pause, false après promotion.
Confirmez que recovery_target_time correspond au besoin métier (UTC).

Failure Modes & Troubleshooting Decision Tree

Arbre de décision textuel : Échec restauration → Consulter logs → Identifier classe → Appliquer correctif → Re-valider

Classe de panne — Symptômes typiques — Logs / codes — Résolution
Version mismatch — pg_restore: error: unsupported version — pg_restore stderr — Utiliser client pg_dump/pg_restore ≥ version source ; privilégier logique pour migrations majeures.
Extension manquante — could not access file "$libdir/postgis-3" — psql / pg_restore — Installer paquet binaire cible ; CREATE EXTENSION IF NOT EXISTS ... VERSION 'x.y';
Encodage / Collation — ERROR: encoding mismatch ou tri inattendu — createdb / pg_restore — Créer base via template0 avec -E UTF8 --lc-collate=C --lc-ctype=C ; relancer restauration.
Droits / Propriétaires — permission denied for schema public — psql / application — Restaurer globals.sql d'abord ; rejouer default_privs.sql ; GRANT / ALTER DEFAULT PRIVILEGES ; aligner search_path.
Échec archive_command — WAL absents dans l'archive ; archive command failed dans log primaire — postgresql-<DATE>.log — Tester commande manuellement ; vérifier droits écriture destination ; surveiller pg_stat_archiver ; alerter sur failed_count > 0.
Base backup incomplet — Fichiers manquants, backup_label absent, taille anormale — ls -la $PGDATA — Relancer pg_basebackup -X stream avec slot ou wal_keep_size suffisant ; ne pas toucher au répertoire pendant la copie.
PITR over/under-shoot — Arrêt avant/après l'instant visé — Logs récupération : recovery stopping before... — Ajuster recovery_target_time ou utiliser recovery_target_lsn / recovery_target_name ; recovery_target_action = 'pause' pour inspection.
Restauration in-place risquée — Écrasement data_dir production sans filet — — — Toujours restaurer dans nouveau répertoire + port alternatif ; valider ; basculer (voir Rollback).
Permissions FS — could not open file ...: Permission denied — Démarrage instance — chown -R postgres:postgres $PGDATA && chmod 700 $PGDATA ; fichiers dump 0600.
Gros volumes / timeouts — pg_restore ou pg_dump dépasse fenêtres — Durée exécution — Paralléliser (-j N), format répertoire ; pour > 1 To, privilégier physique ; scinder par schéma si logique imposé.
Réseau instable (dump distant) — Connexion coupée, dump partiel — pg_dump stderr — Exécuter pg_dump localement sur hôte PG ; format répertoire plus reprenable ; tunnel SSH dédié.

Rollback & Cutover Procedures

Logical (swap bases)

Restaurez sur appdb_new (nom distinct).
Validez avec le pack de vérification + smoke test applicatif.
Bascule atomique :

   ALTER DATABASE appdb RENAME TO appdb_old;
   ALTER DATABASE appdb_new RENAME TO appdb;

Mettez à jour le pooler (PgBouncer) / DNS / configuration applicative.
Conservez appdb_old pendant une fenêtre de TTL (ex. 24–48 h) avant DROP DATABASE.

Physical / PITR (promotion sur nouveau port)

Après promotion sur port 5444, validez lectures/écritures.
Mettez à jour PgBouncer / HAProxy / DNS vers le nouveau port/hôte.
Arrêtez l'ancienne primaire proprement (pg_ctl stop -m fast).
Conservez l'ancien $PGDATA jusqu'à confirmation métier + TTL.
Optionnel : reconfigurez l'ancien nœud comme standby de la nouvelle primaire via pg_basebackup frais.

Automation, Scheduling & Retention

systemd timer (backup logique nocturne avec verrou)

/etc/systemd/system/pg-logical-backup.service :

[Unit]
Description=PostgreSQL logical backup (directory format)
Requires=postgresql.service
After=postgresql.service

[Service]
Type=oneshot
User=postgres
Environment=PGDATA=/var/lib/pgsql/data
ExecStart=/usr/bin/flock -n /var/lock/pg-backup.lock \
  /usr/bin/bash -c 'pg_dump -Fd -j 4 -f /backups/appdb_$(date -u +%F)_dir appdb && gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /run/secrets/backup_gpg_pass /backups/appdb_$(date -u +%F)_dir.tar'

/etc/systemd/system/pg-logical-backup.timer :

[Unit]
Description=Nightly logical backup

[Timer]
OnCalendar=*-*-* 02:30:00 UTC
Persistent=true
RandomizedDelaySec=15m

[Install]
WantedBy=timers.target

Activez : systemctl enable --now pg-logical-backup.timer.

Squelette de script de rétention (bash)

#!/usr/bin/env bash
set -euo pipefail
BACKUP_ROOT="/backups"
KEEP_FULL=3
STAMP=$(date -u +%F)

# 1. Lister les base backups complets triés par date
mapfile -t FULLS < <(find "$BACKUP_ROOT" -maxdepth 1 -name 'base_*' -type d | sort)

# 2. Garder les KEEP_FULL plus récents + leurs WAL
if (( ${#FULLS[@]} > KEEP_FULL )); then
  for OLD in "${FULLS[@]::${#FULLS[@]}-KEEP_FULL}"; do
    # Vérifier continuité WAL via pg_controldata / pg_waldump avant suppression
    echo "Candidate for removal: $OLD"
    # rm -rf "$OLD"  # décommenter après validation manuelle initiale
  done
fi

# 3. Nettoyer dumps logiques anciens (même logique)
find "$BACKUP_ROOT" -maxdepth 1 -name 'appdb_*_dir*' -type f -mtime +30 -print
# find ... -delete

⚠️ WARNING : testez le script en mode echo avant activation ; validez la continuité WAL avec pg_controldata (champ Latest checkpoint location) et pg_waldump sur les segments archivés.

Alerting

Exporteurs Prometheus : node_exporter (disque, durée job) + postgres_exporter (dernier backup réussi via pg_stat_archiver, pg_stat_replication).
Webhook simple en cas d'échec systemd (OnFailure=alert-webhook@%n.service).

Répétition de restauration (répétition de confiance)

Planifiez une restauration complète (logique ou PITR) mensuelle vers un environnement de staging.
Exécutez le pack de validation + smoke test applicatif.
Documentez durée, anomalies, correctifs.

Operations Checklist

Pré-vol

[ ] Espace disque ≥ 1,5× taille cluster (données + WAL + restauration temporaire)
[ ] Versions outils (pg_dump --version, pg_basebackup --version) alignées sur serveur
[ ] Identifiants backup testés (.pgpass, IAM, TLS)
[ ] RPO/RTO documentés et validés par les parties prenantes
[ ] Slot de réplication backup_slot créé (physique)

Répétition logique

[ ] pg_dumpall -g > globals_<DATE>.sql (chiffré, stocké hors site)
[ ] pg_dump -Fd -j 4 -f /backups/appdb_<DATE>_dir appdb
[ ] pg_restore -l /backups/appdb_<DATE>_dir/ → vérification sommaire
[ ] Restauration jetable + pack validation + VACUUM (ANALYZE) + smoke test

Répétition physique / PITR

[ ] wal_level = replica, archive_mode = on, archive_command opérationnel (test pg_switch_wal)
[ ] pg_basebackup -D /backups/base_<DATE> -X stream --wal-method=stream --slot=backup_slot --max-rate=100M
[ ] Restauration vers data_restore, restore_command, recovery_target_time, recovery.signal
[ ] Démarrage port 5444, validation données, pg_promote(), bascule pooler/DNS

Supervision & rétention

[ ] Métriques : durée, taille, succès/échec (Prometheus / webhook)
[ ] Rétention : 3 cycles complets + WAL requis (script ci-dessus)
[ ] Répétition de restauration mensuelle calendarisée

Tableau de synthèse commande / résultat

Tâche — Commande / Action — Résultat attendu
Dump globaux — pg_dumpall -g > globals.sql — Rôles, tablespaces capturés
Dump DB (répertoire) — pg_dump -Fd -j 4 -f /backups/appdb_<DATE>_dir appdb — Répertoire complet avec toc.dat
Restauration DB — pg_restore -d appdb -j 4 --clean --if-exists /backups/appdb_<DATE>_dir — Schéma + données restaurés sans erreur fatale
Switch WAL — SELECT pg_switch_wal(); — Nouveau segment WAL archivé
Base backup — pg_basebackup -D /backups/base_<DATE> -X stream --slot=backup_slot --max-rate=100M — Répertoire prêt avec backup_label / backup_manifest
Démarrer PITR — touch recovery.signal + restore_command + recovery_target_time — Relecture WAL jusqu'à cible, pause
Promouvoir — SELECT pg_promote(); — pg_is_in_recovery() = false

Version-Specific Notes Appendix

Version — Changement clé — Impact sur ce guide
12 — recovery.conf supprimé → recovery.signal + paramètres dans postgresql.conf ; pg_promote() introduit ; wal_level valeurs : replica (défaut) — Utilisez recovery.signal ; pg_promote() disponible
13 — backup_manifest généré par pg_basebackup ; pg_verifybackup pour validation hors-ligne — Vérifiez manifeste après base backup
14 — pg_dump inclut les large objects par défaut (-b implicite) ; pg_basebackup --manifest-force-encode — -b optionnel ; manifeste plus robuste
15 — pg_dump/pg_restore --no-sync pour performance ; wal_keep_size remplace wal_keep_segments — Utilisez wal_keep_size ; --no-sync si stockage fiable
16 — Parallélisme VACUUM amélioré ; pg_basebackup --compress=zstd — VACUUM (ANALYZE) plus rapide ; compression ZSTD pour base backup

Acronymes : RPO (Recovery Point Objective), RTO (Recovery Time Objective), PITR (Point-In-Time Recovery), WAL (Write-Ahead Log), LSN (Log Sequence Number).

Conclusion

Ce runbook fournit un parcours complet, versionné et testable pour la sauvegarde et la restauration PostgreSQL, couvrant les méthodes logiques et physiques avec PITR. Commencez par une répétition limitée sur un jeu de données réduit, validez à chaque couche — fichiers, catalogue, données, application — puis élargissez pour atteindre vos objectifs RPO/RTO. Maintenez une procédure de restauration séparée, documentée et exercée régulièrement ; la métrique de succès n'est pas l'achèvement du backup, mais la restauration vérifiée. Capitalisez sur chaque répétition, automatisez une fois les étapes fiables, et conservez toujours une fenêtre de retour arrière avant toute bascule en production.