TL;DR — Résumé Rapide

Sauvegardes PostgreSQL en production : pg_dump, pg_dumpall, pg_basebackup, archivage WAL, PITR et scripts automatisés avec rotation et envoi vers S3.

PostgreSQL est la colonne vertébrale d’innombrables applications en production — perdre des données sans plan de restauration testé est catastrophique. Ce guide couvre toutes les principales stratégies de sauvegarde et restauration PostgreSQL : sauvegardes logiques avec pg_dump et pg_dumpall, sauvegardes physiques avec pg_basebackup, archivage WAL continu pour la récupération à un instant précis (PITR), scripts automatisés, chiffrement et envoi vers le cloud.

Prérequis

  • PostgreSQL 14 ou supérieur (les commandes fonctionnent depuis la version 12).
  • L’utilisateur postgres ou un rôle avec les privilèges pg_read_all_data / SUPERUSER.
  • Espace disque suffisant (au moins 1,5× la taille de vos bases de données).
  • aws-cli ou rclone installé si vous utilisez un stockage cloud.
  • gpg installé pour les sauvegardes chiffrées.

Sauvegardes Logiques avec pg_dump

pg_dump exporte une seule base de données en SQL ou en format binaire personnalisé — la méthode de sauvegarde la plus portable.

Sauvegarde Complète de la Base de Données

# Format personnalisé (recommandé) — compressé, restauration sélective possible
pg_dump -U postgres -Fc -d madb -f /backup/madb_$(date +%Y%m%d).pgdump

# SQL simple — lisible, restauration avec psql
pg_dump -U postgres -Fp -d madb -f /backup/madb_$(date +%Y%m%d).sql

Sauvegarde d’une Seule Table

pg_dump -U postgres -Fc -d madb -t public.commandes -f /backup/commandes_$(date +%Y%m%d).pgdump

Format Personnalisé vs SQL Simple

FonctionnalitéFormat Personnalisé (-Fc)SQL Simple (-Fp)
CompressionIntégrée (zlib)Non (utiliser gzip séparément)
Restauration sélectiveOui (-t, -n, -T)Non
Restauration parallèleOui (pg_restore -j)Non
Lisible par l’humainNonOui
Outil de restaurationpg_restorepsql

Utilisez le format personnalisé pour les sauvegardes en production.

Sauvegardes de Cluster avec pg_dumpall

pg_dumpall sauvegarde le cluster PostgreSQL complet : toutes les bases, rôles et tablespaces.

pg_dumpall -U postgres -f /backup/cluster_$(date +%Y%m%d).sql
pg_dumpall -U postgres --globals-only -f /backup/globals_$(date +%Y%m%d).sql

Restauration :

psql -U postgres -f /backup/cluster_20260322.sql

Sauvegardes Physiques avec pg_basebackup

pg_basebackup crée une copie binaire du répertoire de données PostgreSQL — la base pour le PITR et les serveurs standby.

pg_basebackup -U replicator -h localhost -D /backup/base \
  -Ft -z -P --wal-method=stream

Prérequis :

  • Rôle avec le privilège REPLICATION : CREATE ROLE replicator REPLICATION LOGIN PASSWORD '...';
  • max_wal_senders >= 2 dans postgresql.conf.
  • Entrée dans pg_hba.conf : host replication replicator 127.0.0.1/32 md5

Archivage WAL et Récupération à un Instant Précis

L’archivage WAL combiné à une sauvegarde de base permet le PITR — restaurer vers n’importe quel moment passé.

Activer l’Archivage WAL

Éditez /etc/postgresql/16/main/postgresql.conf :

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'
archive_timeout = 300

Pour S3 :

archive_command = 'aws s3 cp %p s3://mes-sauvegardes-pg/wal/%f'
sudo systemctl reload postgresql

Vérifier que l’Archivage Fonctionne

SELECT last_archived_wal, last_archived_time, last_failed_wal
FROM pg_stat_archiver;

Récupération vers un Point Spécifique

  1. Arrêtez PostgreSQL et remplacez le répertoire de données par la sauvegarde de base.
  2. Créez recovery.signal dans le répertoire de données (PostgreSQL 12+).
  3. Configurez les paramètres de récupération dans postgresql.conf :
restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-03-22 14:30:00'
recovery_target_action = 'promote'
  1. Démarrez PostgreSQL — il rejouera le WAL jusqu’au moment spécifié.

Restauration à partir des Sauvegardes

Restaurer avec pg_restore (Format Personnalisé)

createdb -U postgres madb_restauree
pg_restore -U postgres -d madb_restauree /backup/madb_20260322.pgdump

# Restauration parallèle
pg_restore -U postgres -j 4 -d madb_restauree /backup/madb_20260322.pgdump

# Restaurer une seule table
pg_restore -U postgres -d madb_restauree -t public.commandes /backup/madb_20260322.pgdump

Restaurer avec psql (SQL Simple)

psql -U postgres -d madb_restauree -f /backup/madb_20260322.sql

Voir le Contenu d’une Sauvegarde

pg_restore --list /backup/madb_20260322.pgdump | grep TABLE

Scripts de Sauvegarde Automatisés

Script Quotidien avec Rotation

#!/bin/bash
# /usr/local/bin/pg-backup.sh

set -euo pipefail

BACKUP_DIR="/backup/postgresql"
JOURS_A_CONSERVER=7
PG_USER="postgres"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR/daily"

for DB in $(psql -U "$PG_USER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    DB=$(echo "$DB" | xargs)
    pg_dump -U "$PG_USER" -Fc -d "$DB" \
        -f "$BACKUP_DIR/daily/${DB}_${DATE}.pgdump"
done

pg_dumpall -U "$PG_USER" --globals-only \
    -f "$BACKUP_DIR/daily/globals_${DATE}.sql"

find "$BACKUP_DIR/daily" -type f -mtime +"$JOURS_A_CONSERVER" -delete
0 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Sécurité des Sauvegardes — Compression, Chiffrement et S3

Compression avec Gzip

pg_dump -U postgres -Fp -d madb | gzip > /backup/madb_$(date +%Y%m%d).sql.gz
gunzip -c /backup/madb_20260322.sql.gz | psql -U postgres -d madb_restauree

Chiffrement avec GPG

pg_dump -U postgres -Fc -d madb | \
    gpg --encrypt --recipient admin@exemple.com \
    -o /backup/madb_$(date +%Y%m%d).pgdump.gpg

gpg --decrypt /backup/madb_20260322.pgdump.gpg | \
    pg_restore -U postgres -d madb_restauree

Envoi vers S3

aws s3 cp /backup/madb_20260322.pgdump \
    s3://mes-sauvegardes-pg/daily/ --storage-class STANDARD_IA

aws s3 sync /backup/postgresql/ s3://mes-sauvegardes-pg/ --storage-class STANDARD_IA

Comparatif des Méthodes de Sauvegarde

MéthodeVitesseTailleRestauration SélectiveSupport PITRCas d’Usage
pg_dump (personnalisé)MoyennePetiteOuiNonSauvegarde quotidienne par base
pg_dump (SQL simple)MoyenneGrandeNonNonPetites bases, portabilité
pg_dumpallLenteGrandeNonNonCluster complet + rôles
pg_basebackupRapideRép. completNonOui (avec WAL)Base PITR, standby
Archivage WALContinuVariableNonOuiProtection continue

Scénario Réel : Récupération après Sinistre

Situation : Votre serveur PostgreSQL 16 en production tombe en panne à 15h47 un mardi. Le dernier pg_basebackup a été exécuté dimanche à 01h00. L’archivage WAL vers S3 a fonctionné continuellement.

Objectif : Restaurer à l’état de 15h45.

Étape 1 — Provisionner un Nouveau Serveur

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

Étape 2 — Restaurer la Sauvegarde de Base

cd /var/lib/postgresql/16/main
sudo -u postgres tar -xzf /backup/base/20260320/base.tar.gz .
sudo -u postgres tar -xzf /backup/base/20260320/pg_wal.tar.gz pg_wal/

Étape 3 — Configurer la Récupération

restore_command = 'aws s3 cp s3://mes-sauvegardes-pg/wal/%f %p'
recovery_target_time = '2026-03-22 15:45:00'
recovery_target_action = 'promote'
sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl start postgresql
sudo tail -f /var/log/postgresql/postgresql-16-main.log

Étape 4 — Vérifier les Données et Reprendre les Opérations

SELECT COUNT(*) FROM commandes WHERE created_at < '2026-03-22 15:46:00';

Cas Particuliers et Pièges Courants

  • Grandes bases (>100 Go) : Utilisez pg_dump -j et pg_restore -j pour des dumps et restaurations parallèles.
  • Verrous pendant la sauvegarde : pg_dump prend un verrou ACCESS SHARE. Exécutez les sauvegardes pendant les fenêtres de faible activité.
  • Différences d’encodage : Créez la base cible avec le même encodage et la même locale que la source.
  • Séquences : Le format personnalisé capture les valeurs de séquence au moment du dump.
  • Extensions : Assurez-vous que les mêmes extensions sont disponibles sur le serveur de restauration.
  • Tablespaces personnalisés : Ils doivent exister avec les bons chemins avant d’exécuter pg_restore.

Dépannage

ProblèmeCause ProbableSolution
ERROR: permission denied dans pg_dumpLe rôle manque de droits de lectureAccordez pg_read_all_data ou utilisez le superutilisateur postgres
ERROR: relation already exists dans pg_restoreRestauration dans une base non videSupprimez et recréez la base cible ou utilisez le flag --clean
archive_command échoue silencieusementChemin ou permissions incorrectsVérifiez pg_stat_archiver.last_failed_wal et testez la commande manuellement
PITR bloqué à rejouer le WALrecovery_target_time dans le futur ou fuseau horaire incorrectUtilisez des horodatages UTC ; vérifiez show timezone dans psql
pg_basebackup: error: could not connectPas d’entrée dans pg_hba.confAjoutez host replication replicator 127.0.0.1/32 md5 à pg_hba.conf
Restauration très lenteRestauration mono-thread d’un grand dumpUtilisez pg_restore -j N où N = nombre de cœurs CPU

Résumé

  • Utilisez pg_dump (format personnalisé) quotidiennement pour des sauvegardes logiques avec restauration sélective.
  • Utilisez pg_dumpall —globals-only quotidiennement pour capturer les rôles et tablespaces.
  • Utilisez pg_basebackup hebdomadairement comme base binaire pour une restauration complète rapide.
  • Activez l’archivage WAL pour une protection continue et le PITR.
  • Chiffrez les sauvegardes avec GPG et stockez-les hors site sur S3.
  • Testez les restaurations régulièrement — une sauvegarde non testée n’est pas une sauvegarde.
  • Utilisez des scripts cron avec rotation pour automatiser et supprimer les anciennes sauvegardes.

Articles Connexes