Sauvegarde et restauration – pg_dump, pg_dumpall et pg_restore

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

La sauvegarde et la restauration sont des opérations critiques pour toute base de données. PostgreSQL propose plusieurs outils pour réaliser des sauvegardes logiques : pg_dump, pg_dumpall et pg_restore. Ce cours vous présente ces outils, leurs options, et les bonnes pratiques pour une stratégie de sauvegarde efficace.

Types de sauvegardes dans PostgreSQL

Type	Description	Outils
Sauvegarde logique	Export des données et de la structure en format SQL ou binaire.	`pg_dump`, `pg_dumpall`
Sauvegarde physique	Copie des fichiers de données (binaires).	`pg_basebackup`, `rsync`
Archivage des WAL	Sauvegarde des journaux de transactions pour un PITR.	`archive_command`

Ce cours se concentre sur les sauvegardes logiques, les plus courantes pour les bases de données de taille moyenne.

1. pg_dump – Sauvegarde d’une base

Principe

pg_dump est l’outil principal pour sauvegarder une base de données spécifique. Il exporte la structure (schéma, tables, vues, fonctions) et les données.

Avantages :

Sauvegarde à chaud (peut être exécutée pendant que la base est utilisée).
Support de plusieurs formats.
Possibilité de parallélisation.
Peut exporter une partie de la base (tables, schémas).

Inconvénients :

Ne sauvegarde pas les rôles/utilisateurs.
Peut être lent pour les très grandes bases.

Syntaxe de base

pg_dump [options] [nom_base] > fichier.sql

Options principales

Option	Description	Exemple
`-d`	Base de données à sauvegarder	`-d magasin`
`-h`	Hôte du serveur	`-h localhost`
`-p`	Port du serveur	`-p 5432`
`-U`	Utilisateur	`-U postgres`
`-f`	Fichier de sortie	`-f backup.sql`
`-F`	Format de sortie (`c` = custom, `d` = directory, `t` = tar, `p` = plain text)	`-Fc`
`-j`	Nombre de threads pour la parallélisation	`-j 4`
`-z`	Niveau de compression (0-9)	`-z 6`
`-a`	Données uniquement (sans structure)	`-a`
`-s`	Structure uniquement (sans données)	`-s`
`-n`	Schéma spécifique à exporter	`-n public`
`-N`	Schéma à exclure	`-N reporting`
`-c`	Ajoute `CREATE DATABASE` dans le dump	`-c`
`-t`	Table spécifique à exporter	`-t clients`
`-T`	Table à exclure	`-T logs`
`-C`	Inclut `CREATE DATABASE` dans le dump	`-C`

Formats de sauvegarde

Format	Option `-F`	Description	Avantages	Inconvénients
Plain text	`p`	Fichier SQL lisible.	Lisible, modifiable, compatible avec `psql`.	Volumineux, pas de parallélisation.
Custom	`c`	Format binaire compressé.	Plus petit, parallélisable, permet `pg_restore`.	Non lisible.
Directory	`d`	Répertoire contenant plusieurs fichiers.	Parallélisable, permet `pg_restore`.	Nécessite un répertoire.
Tar	`t`	Archive tar avec les fichiers.	Similaire à custom.	Moins flexible que directory.

Exemples pratiques

Exemple 1 : Sauvegarde en plain text

# Sauvegarde simple
pg_dump -d magasin > magasin.sql

# Avec utilisateur et hôte
pg_dump -h localhost -U postgres -d magasin > magasin.sql

Exemple 2 : Sauvegarde en format custom (recommandé)

pg_dump -Fc -d magasin -f magasin.dump

Exemple 3 : Sauvegarde avec compression

# Compression avec le pipe
pg_dump -d magasin | gzip -c > magasin.sql.gz

# Ou avec l'option -z en format custom
pg_dump -Fc -z 9 -d magasin -f magasin.dump

Exemple 4 : Sauvegarde d’une table spécifique

pg_dump -d magasin -t clients -t commandes > clients_commandes.sql

Exemple 5 : Sauvegarde d’un schéma spécifique

pg_dump -d magasin -n public > public_schema.sql

Exemple 6 : Sauvegarde des données uniquement

pg_dump -d magasin -a -f magasin_data.sql

Exemple 7 : Sauvegarde de la structure uniquement

pg_dump -d magasin -s -f magasin_structure.sql

Exemple 8 : Sauvegarde avec parallélisation (format directory)

pg_dump -Fd -j 4 -d magasin -f /tmp/magasin_backup/

Exemple 9 : Exclure des tables volumineuses

pg_dump -d magasin -T logs_historique -T archives > magasin_sans_logs.sql

2. pg_dumpall – Sauvegarde de l’instance

Principe

pg_dumpall sauvegarde toute l’instance PostgreSQL : toutes les bases, les rôles/utilisateurs, les tablespaces, etc.

Avantages :

Sauvegarde complète de l’instance.
Inclut les rôles et les permissions.

Inconvénients :

Format plain text uniquement (SQL).
Pas de parallélisation.
Peut être volumineux.

Syntaxe

pg_dumpall [options] > fichier.sql

Options principales

Option	Description
`-h`	Hôte du serveur
`-p`	Port du serveur
`-U`	Utilisateur
`-f`	Fichier de sortie
`-s`	Structure uniquement
`-a`	Données uniquement
`-l`	Sauvegarde uniquement les rôles
`--if-exists`	Ajoute `IF EXISTS` dans les commandes DROP

Exemples pratiques

Exemple 1 : Sauvegarde complète de l’instance

pg_dumpall > instance_complete.sql

Exemple 2 : Sauvegarde avec fichier spécifié

pg_dumpall -f instance_complete.sql

Exemple 3 : Sauvegarde des rôles uniquement

pg_dumpall -l > roles.sql

Exemple 4 : Sauvegarde avec compression

pg_dumpall | gzip -c > instance_complete.sql.gz

Comparaison pg_dump vs pg_dumpall

Critère	pg_dump	pg_dumpall
Périmètre	Une base de données	Toute l’instance
Rôles/Users	Non	Oui
Tablespaces	Non	Oui
Formats	Plain, Custom, Directory, Tar	Plain uniquement
Parallélisation	Oui	Non
Sélectivité	Base, schéma, table	Instance entière

3. Restauration

Méthode selon le format de sauvegarde

Format de sauvegarde	Outil de restauration
Plain text (`.sql`)	`psql -f fichier.sql`
Custom (`.dump`)	`pg_restore -d base fichier.dump`
Directory	`pg_restore -d base /chemin/repertoire/`
Tar	`pg_restore -d base fichier.tar`

A. Restauration avec psql (plain text)

# Restaurer une sauvegarde en plain text
psql -f magasin.sql

# Restaurer dans une base spécifique
psql -d magasin -f magasin.sql

# Restaurer à partir d'une sauvegarde compressée
gunzip -c magasin.sql.gz | psql -d magasin

B. Restauration avec pg_restore (format custom/directory)

# Créer la base (si elle n'existe pas)
createdb magasin

# Restaurer
pg_restore -d magasin magasin.dump

# Restaurer avec nettoyage préalable (-c)
pg_restore -d magasin -c magasin.dump

# Restaurer uniquement les données
pg_restore -d magasin -a magasin.dump

# Restaurer uniquement la structure
pg_restore -d magasin -s magasin.dump

# Restaurer une seule table
pg_restore -d magasin -t clients magasin.dump

Options principales de pg_restore

Option	Description
`-d`	Base de destination
`-c`	Nettoie les objets avant de les recréer (`DROP ... CREATE`)
`-C`	Inclut `CREATE DATABASE` (nécessite une base de connexion)
`-a`	Restaure uniquement les données
`-s`	Restaure uniquement la structure
`-t`	Restaure une table spécifique
`-n`	Restaure un schéma spécifique
`-j`	Parallélisation (nombre de threads)
`--if-exists`	Ajoute `IF EXISTS` aux commandes DROP
`-v`	Mode verbeux

Exemples de restauration

Exemple 1 : Restauration simple

createdb magasin
pg_restore -d magasin -c magasin.dump

Exemple 2 : Restauration avec parallélisation

pg_restore -d magasin -j 4 -c magasin.dump

Exemple 3 : Restauration complète (avec CREATE DATABASE)

# Le dump a été créé avec -C
psql -f magasin.sql
# ou
pg_restore -C -d postgres magasin.dump

Exemple 4 : Restauration d’une table spécifique

pg_restore -d magasin -t clients magasin.dump

Exemple 5 : Restauration uniquement de la structure

pg_restore -d magasin -s magasin.dump

4. COPY – Export/Import rapide

Principe

COPY est une commande SQL qui permet d’exporter/importer des données en vrac, sans structure.

Export avec COPY

-- Exporter une table vers un fichier
COPY clients TO '/tmp/clients.csv' CSV HEADER;

-- Exporter une requête
COPY (SELECT * FROM clients WHERE actif = true) TO '/tmp/clients_actifs.csv' CSV HEADER;

-- Exporter vers une commande shell
COPY clients TO PROGRAM 'gzip > /tmp/clients.csv.gz';

Import avec COPY

-- Importer depuis un fichier CSV
COPY clients FROM '/tmp/clients.csv' CSV HEADER;

-- Importer depuis une commande shell
COPY clients FROM PROGRAM 'curl -s http://example.com/data.csv';

💡 Bon à savoir : COPY est beaucoup plus rapide que des INSERT individuels. Parfait pour les migrations ou les imports de données.

5. Stratégies de sauvegarde

Recommandations générales

Stratégie	Description
Sauvegarde quotidienne	Sauvegarde complète de toutes les bases chaque jour.
Sauvegarde hebdomadaire	Sauvegarde complète + archives WAL pour un PITR.
Sauvegarde différée	Sauvegarde sur un serveur distant ou un stockage cloud.
Rotation des sauvegardes	Conservez 30 jours de sauvegardes, puis supprimez les anciennes.

Exemple de planification

# Script de sauvegarde quotidien
#!/bin/bash
DATE=$(date +%Y-%m-%d)
BACKUP_DIR=/backup/postgresql

# Sauvegarde de chaque base
for DB in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1','postgres')"); do
    pg_dump -Fc -d $DB > $BACKUP_DIR/${DB}_${DATE}.dump
done

# Sauvegarde des rôles
pg_dumpall -l > $BACKUP_DIR/roles_${DATE}.sql

# Compression
gzip $BACKUP_DIR/*.dump

Rotation des sauvegardes avec cron

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

# Nettoyage des sauvegardes de plus de 30 jours
0 3 * * * find /backup/postgresql -name "*.gz" -mtime +30 -delete

6. Bonnes pratiques

Pratique	Description
Tester les restaurations	Faites des tests de restauration réguliers pour vérifier l’intégrité des backups.
Stockage externe	Ne stockez pas les sauvegardes sur le même serveur que la base de données.
Utiliser le format custom	Le format `-Fc` est plus petit, compressé et permet `pg_restore`.
Sauvegarder les rôles séparément	Utilisez `pg_dumpall -l` pour sauvegarder les rôles.
Documenter la procédure	Documentez les commandes de sauvegarde et de restauration.
Surveiller l’espace disque	Assurez-vous que l’espace disque est suffisant pour les sauvegardes.
Utiliser `--if-exists`	Pour les restaurations, cette option évite des erreurs inutiles.
Paralléliser les gros backups	Utilisez `-j` avec le format `-Fd` pour accélérer les sauvegardes volumineuses.

Tableau récapitulatif des commandes

Action	Commande
Sauvegarder une base (plain text)	`pg_dump -d base > base.sql`
Sauvegarder une base (custom)	`pg_dump -Fc -d base -f base.dump`
Sauvegarder une base (directory)	`pg_dump -Fd -j 4 -d base -f /dir/`
Sauvegarder toutes les bases	`pg_dumpall > all.sql`
Sauvegarder les rôles	`pg_dumpall -l > roles.sql`
Sauvegarder une table	`pg_dump -d base -t table > table.sql`
Sauvegarder la structure uniquement	`pg_dump -s -d base > structure.sql`
Sauvegarder les données uniquement	`pg_dump -a -d base > data.sql`
Restaurer (plain text)	`psql -f base.sql`
Restaurer (custom)	`pg_restore -d base -c base.dump`
Restaurer avec parallélisation	`pg_restore -d base -j 4 -c base.dump`
Exporter en CSV	`COPY table TO '/tmp/file.csv' CSV HEADER;`
Importer en CSV	`COPY table FROM '/tmp/file.csv' CSV HEADER;`

Prochain chapitre

Vous savez maintenant sauvegarder et restaurer vos bases de données. Dans le prochain cours, nous aborderons la réplication, pour assurer la haute disponibilité de votre infrastructure PostgreSQL.

👉 Cours 12 : Réplication physique

Junior TSAFACK – 20/08/2026