La sauvegarde et la restauration PostgreSQL constituent le socle de tout plan de reprise après sinistre. Que vous protégiez une seule base applicative ou gériez des centaines d’instances, maîtriser pg_dump et pg_restore vous garantit de pouvoir récupérer après une panne matérielle, une suppression accidentelle ou une migration ratée. Ce guide couvre tous les aspects pratiques — des dumps basiques jusqu’à la planification automatisée, la restauration sélective, et l’étape critique que la plupart des équipes négligent : tester réellement que les sauvegardes peuvent être restaurées.
Prérequis
- PostgreSQL 12 ou version ultérieure installé et en cours d’exécution
- Accès à une base de données PostgreSQL avec des privilèges suffisants (
pg_dumprequiert au minimum un accès en lecture sur tous les objets) - Accès
sudoou à l’utilisateurpostgressur le serveur de base de données - Connaissance de base de SQL et des concepts PostgreSQL (bases de données, schémas, tables)
Créer des sauvegardes avec pg_dump
pg_dump produit des sauvegardes logiques — une représentation de votre base de données sous forme d’instructions SQL ou d’une archive compressée. Contrairement aux copies au niveau du système de fichiers, ces sauvegardes sont portables entre les versions de PostgreSQL et les plateformes.
Dump SQL brut (format par défaut)
La sauvegarde la plus simple crée un fichier .sql contenant toutes les commandes SQL nécessaires pour recréer la base :
# Exporter une seule base de données vers un fichier SQL
pg_dump -U postgres -h localhost mydb > mydb_backup.sql
# Inclure l'instruction CREATE DATABASE
pg_dump -U postgres -h localhost -C mydb > mydb_backup_with_create.sql
# Dump avec horodatage dans le nom de fichier
pg_dump -U postgres mydb > "mydb_$(date +%Y%m%d_%H%M%S).sql"Le format SQL brut est lisible par un humain et peut être restauré avec psql. L’inconvénient : il ne prend pas en charge la restauration parallèle ni la restauration sélective de tables.
Format personnalisé (-Fc) — Recommandé en production
Le format personnalisé crée une archive compressée et non textuelle qui offre les options de restauration les plus flexibles :
# Sauvegarde au format personnalisé (compressée par défaut)
pg_dump -U postgres -Fc mydb > mydb_backup.dump
# Format personnalisé avec compression maximale
pg_dump -U postgres -Fc -Z 9 mydb > mydb_backup.dump
# Format personnalisé avec dump parallèle (4 workers)
pg_dump -U postgres -Fc -j 4 mydb > mydb_backup.dumpPourquoi le format personnalisé est préféré :
- Compression automatique (généralement 5 à 10 fois plus petit qu’un dump SQL brut)
- Restauration parallèle possible avec
pg_restore -j - Restauration sélective (tables, schémas ou données spécifiques)
- Réordonnancement des éléments lors de la restauration pour optimiser la vitesse de chargement
Format répertoire (-Fd) — Idéal pour les grandes bases
Le format répertoire crée un dossier contenant un fichier par table, ce qui permet de véritables dumps parallèles :
# Format répertoire avec dump parallèle (8 workers)
pg_dump -U postgres -Fd -j 8 -f /backup/mydb_dir mydbCela crée un répertoire contenant un fichier toc.dat (table des matières) et un fichier compressé par table. Le dump parallèle réduit considérablement le temps de sauvegarde pour les grandes bases.
Sauvegarde de toutes les bases (pg_dumpall)
Pour sauvegarder toutes les bases du cluster PostgreSQL, ainsi que les objets globaux (rôles, tablespaces) :
# Exporter toutes les bases et les objets globaux
pg_dumpall -U postgres > all_databases.sql
# Exporter uniquement les objets globaux (rôles, tablespaces)
pg_dumpall -U postgres --globals-only > globals.sqlpg_dumpall produit toujours du SQL brut. Pour les clusters de grande taille, exportez les objets globaux séparément et utilisez pg_dump par base de données en format personnalisé.
Dumps sélectifs
# Exporter une seule table
pg_dump -U postgres -t users mydb > users_table.sql
# Exporter plusieurs tables spécifiques
pg_dump -U postgres -t users -t orders -t products mydb > selected_tables.sql
# Exporter un schéma spécifique
pg_dump -U postgres -n public mydb > public_schema.sql
# Exporter le schéma uniquement (sans les données)
pg_dump -U postgres -s mydb > schema_only.sql
# Exporter les données uniquement (sans le schéma)
pg_dump -U postgres -a mydb > data_only.sql
# Exclure une grande table du dump
pg_dump -U postgres -T audit_log mydb > mydb_no_audit.sqlRestaurer des sauvegardes
Restauration depuis un dump SQL brut
# Restaurer dans une base existante
psql -U postgres -h localhost mydb < mydb_backup.sql
# Créer la base et restaurer (si le dump inclut l'option -C)
psql -U postgres -h localhost < mydb_backup_with_create.sql
# Restaurer avec sortie verbeuse
psql -U postgres -v ON_ERROR_STOP=1 mydb < mydb_backup.sqlL’option ON_ERROR_STOP=1 force psql à s’arrêter à la première erreur au lieu de continuer silencieusement. Utilisez toujours cette option lors de la restauration de bases de production.
Restauration depuis un format personnalisé
# Restauration basique
pg_restore -U postgres -d mydb mydb_backup.dump
# Restauration parallèle (8 workers — nettement plus rapide pour les grandes bases)
pg_restore -U postgres -d mydb -j 8 mydb_backup.dump
# Restaurer dans une nouvelle base
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored mydb_backup.dump
# Supprimer les objets existants avant la restauration
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dumpRestauration de tables spécifiques
# Restaurer une seule table depuis un dump au format personnalisé
pg_restore -U postgres -d mydb -t users mydb_backup.dump
# Restaurer à partir d'un fichier liste (contrôle fin)
pg_restore -l mydb_backup.dump > restore_list.txt
# Éditez restore_list.txt — commentez les éléments à exclure
pg_restore -U postgres -d mydb -L restore_list.txt mydb_backup.dumpRestauration d’un schéma spécifique
# Restaurer uniquement le schéma 'public'
pg_restore -U postgres -d mydb -n public mydb_backup.dump
# Restaurer les données uniquement (le schéma existe déjà)
pg_restore -U postgres -d mydb -a mydb_backup.dumpComparaison des méthodes de sauvegarde PostgreSQL
| Fonctionnalité | pg_dump (logique) | pg_basebackup (physique) | Snapshot système de fichiers |
|---|---|---|---|
| Périmètre | Base individuelle | Cluster complet | Cluster complet |
| Restauration cross-version | Oui | Non (même version majeure) | Non |
| Restauration sélective | Oui (option -t) | Non | Non |
| Récupération ponctuelle (PITR) | Non | Oui (avec WAL) | Oui (avec WAL) |
| Sauvegarde à chaud | Oui (snapshot cohérent) | Oui | Nécessite fsync/freeze |
| Taille de la sauvegarde | Petite (données compressées) | Grande (répertoire de données complet) | Très grande (disque complet) |
| Vitesse de sauvegarde | Lente (lecture via SQL) | Rapide (streaming) | Très rapide (niveau bloc) |
| Idéal pour | Bases individuelles, migrations | DR cluster complet, PITR | Snapshots VM/cloud |
Utilisez pg_dump pour les sauvegardes quotidiennes de bases individuelles et pour les migrations entre versions de PostgreSQL. Utilisez pg_basebackup lorsque vous avez besoin d’une récupération ponctuelle ou que vous mettez en place une réplication en streaming. Utilisez les snapshots système de fichiers en complément, pas en remplacement — ils sont rapides mais nécessitent une gestion rigoureuse des fichiers WAL.
Automatiser les sauvegardes avec cron
Script de sauvegarde
#!/bin/bash
# /usr/local/bin/pg_backup.sh
# Script de sauvegarde PostgreSQL automatisé
set -euo pipefail
# Configuration
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=30
PG_USER="postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"
# Créer le répertoire de sauvegarde s'il n'existe pas
mkdir -p "$BACKUP_DIR"
# Fonction de journalisation
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}
log "Démarrage de la sauvegarde PostgreSQL"
# Exporter les objets globaux (rôles, tablespaces)
pg_dumpall -U "$PG_USER" --globals-only > "$BACKUP_DIR/globals_${TIMESTAMP}.sql" 2>> "$LOG_FILE"
log "Objets globaux exportés"
# Exporter chaque base en format personnalisé
for DB in $(psql -U "$PG_USER" -At -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
DUMP_FILE="$BACKUP_DIR/${DB}_${TIMESTAMP}.dump"
pg_dump -U "$PG_USER" -Fc -Z 6 "$DB" > "$DUMP_FILE" 2>> "$LOG_FILE"
SIZE=$(du -h "$DUMP_FILE" | cut -f1)
log "Base $DB exportée ($SIZE)"
done
# Supprimer les sauvegardes plus anciennes que la durée de rétention
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete
find "$BACKUP_DIR" -name "globals_*.sql" -mtime +${RETENTION_DAYS} -delete
log "Nettoyage des sauvegardes de plus de $RETENTION_DAYS jours effectué"
log "Sauvegarde terminée"# Rendre le script exécutable
chmod +x /usr/local/bin/pg_backup.sh
# Tester manuellement d'abord
sudo -u postgres /usr/local/bin/pg_backup.shPlanification cron
# Éditer la crontab de l'utilisateur postgres
sudo crontab -u postgres -e# Sauvegarde quotidienne à 2h00
0 2 * * * /usr/local/bin/pg_backup.sh
# Sauvegarde complète hebdomadaire le dimanche à 1h00 (conservation plus longue)
0 1 * * 0 /usr/local/bin/pg_backup.shAuthentification sans mot de passe avec .pgpass
Pour les tâches cron, vous avez besoin d’une authentification sans mot de passe. Créez un fichier .pgpass :
# Créer .pgpass pour l'utilisateur postgres
sudo -u postgres bash -c 'cat > ~/.pgpass << EOF
localhost:5432:*:postgres:your_secure_password
EOF'
# Définir les permissions requises (pg_dump refuse d'utiliser un .pgpass lisible par tous)
sudo -u postgres chmod 600 ~/.pgpassFormat : hostname:port:database:username:password. Utilisez * comme joker pour le champ base de données afin de couvrir toutes les bases.
Tester l’intégrité des sauvegardes
Une sauvegarde qui ne peut pas être restaurée n’est pas une sauvegarde. Planifiez des tests de restauration réguliers :
#!/bin/bash
# /usr/local/bin/pg_restore_test.sh
# Vérifier l'intégrité d'une sauvegarde en la restaurant dans une base temporaire
set -euo pipefail
BACKUP_FILE="$1"
TEST_DB="restore_test_$(date +%s)"
PG_USER="postgres"
# Créer la base temporaire
createdb -U "$PG_USER" "$TEST_DB"
# Tenter la restauration
if pg_restore -U "$PG_USER" -d "$TEST_DB" "$BACKUP_FILE" 2>/dev/null; then
# Vérifier les données
TABLES=$(psql -U "$PG_USER" -At -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'" "$TEST_DB")
ROWS=$(psql -U "$PG_USER" -At -c "SELECT sum(n_live_tup) FROM pg_stat_user_tables" "$TEST_DB")
echo "SUCCÈS : $TABLES tables restaurées avec $ROWS lignes au total"
else
echo "ÉCHEC : La restauration a rencontré des erreurs"
fi
# Nettoyage
dropdb -U "$PG_USER" "$TEST_DB"# Tester la sauvegarde la plus récente
sudo -u postgres /usr/local/bin/pg_restore_test.sh /var/backups/postgresql/mydb_latest.dumpRésolution de Problèmes Courants
”pg_dump: error: connection to server failed"
# Vérifier que PostgreSQL est en cours d'exécution
sudo systemctl status postgresql
# Vérifier que la connexion fonctionne
psql -U postgres -h localhost -l
# Consulter pg_hba.conf pour les règles d'authentification
sudo cat /etc/postgresql/16/main/pg_hba.conf | grep -v '^#' | grep -v '^$'"pg_restore: error: could not execute query: ERROR: relation already exists”
La base cible contient déjà des objets. Utilisez --clean --if-exists pour supprimer les objets existants avant la restauration :
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dumpOu restaurez dans une base vide fraîchement créée :
dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb mydb_backup.dumpLa sauvegarde est trop lente
# Utiliser le dump parallèle (format custom ou directory uniquement)
pg_dump -U postgres -Fd -j $(nproc) -f /backup/mydb_dir mydb
# Exclure les grandes tables régénérables
pg_dump -U postgres -Fc -T large_cache_table -T session_data mydb > mydb.dump
# Réduire la compression pour gagner en vitesse
pg_dump -U postgres -Fc -Z 1 mydb > mydb_fast.dumpLa restauration est trop lente
# Utiliser la restauration parallèle (format custom ou directory)
pg_restore -U postgres -d mydb -j $(nproc) mydb_backup.dump
# Désactiver les triggers pendant le chargement des données (gain de vitesse significatif)
pg_restore -U postgres -d mydb --disable-triggers mydb_backup.dump
# Augmenter maintenance_work_mem pour la session de restauration
psql -U postgres -d mydb -c "SET maintenance_work_mem = '1GB';"
pg_restore -U postgres -d mydb mydb_backup.dumpLe fichier .pgpass est ignoré
# Vérifier les permissions (doivent être 600)
ls -la ~/.pgpass
# Corriger les permissions
chmod 600 ~/.pgpass
# Vérifier le format (pas d'espaces superflus)
cat ~/.pgpass
# Doit être : hostname:port:database:username:passwordCas particuliers et pièges à éviter
Grands objets (BLOBs) : pg_dump inclut les grands objets par défaut, mais pg_restore -t ne les restaure PAS. Utilisez pg_restore -L avec une liste personnalisée pour inclure les grands objets lors des restaurations sélectives.
Extensions : pg_dump inclut les instructions CREATE EXTENSION, mais la restauration échoue si l’extension n’est pas installée sur le serveur cible. Installez les extensions avant de restaurer.
Propriété et permissions : pg_dump enregistre la propriété des objets. Si le rôle propriétaire n’existe pas sur le serveur cible, la restauration échoue. Utilisez --no-owner pour ignorer les assignations de propriété, ou créez les rôles au préalable à l’aide du dump des objets globaux.
Accès concurrent pendant le dump : pg_dump prend un snapshot au départ et lit de façon cohérente depuis celui-ci. Les autres transactions continuent normalement. Cependant, les opérations DDL (ALTER TABLE, DROP) maintenant un AccessExclusiveLock bloquent le dump. Planifiez les sauvegardes pendant les périodes de faible activité.
Incompatibilité d’encodage : Si les bases source et cible utilisent des encodages différents, des corruptions de données peuvent survenir. Vérifiez toujours que les encodages correspondent :
psql -U postgres -c "SHOW server_encoding;"Résumé
- pg_dump avec le format personnalisé (-Fc) est l’approche recommandée pour les sauvegardes en production — il compresse les données, prend en charge la restauration parallèle et permet la récupération sélective de tables
- pg_dumpall capture les objets globaux (rôles, tablespaces) que pg_dump ne prend pas en charge — exportez toujours les objets globaux séparément en complément de vos sauvegardes de bases
- Automatisez avec cron et un script de sauvegarde incluant rotation, journalisation et gestion des erreurs — ne comptez jamais sur des procédures manuelles
- Testez vos restaurations régulièrement en restaurant vers une base temporaire et en validant les comptages de lignes — une sauvegarde jamais testée est une sauvegarde susceptible d’échouer au moment critique
- Utilisez .pgpass pour l’authentification sans mot de passe dans les tâches cron avec des permissions
chmod 600— pg_dump refuse de lire les fichiers d’identifiants lisibles par tous - Le dump et la restauration parallèles (option
-j) réduisent considérablement le temps pour les grandes bases — utilisez le format répertoire (-Fd) pour de meilleures performances parallèles