PostgreSQL : sauvegarde et restauration

Une sauvegarde n’a de valeur que testée : restaurer régulièrement sur une machine d’essai. Les exemples utilisent la base boutique.

Sauvegarde logique d’une base : `pg_dump`

pg_dump -h 127.0.0.1 -U app boutique > boutique.sql                  # texte SQL
pg_dump -h 127.0.0.1 -U app -Fc boutique -f boutique.dump            # format « custom » (compressé)
pg_dump -h 127.0.0.1 -U app -Fd -j 4 boutique -f boutique_dir        # format répertoire, 4 tâches en parallèle
sudo -u postgres pg_dump boutique -Fc -f /var/backups/boutique.dump  # local, via le compte système

Format	Option	Restauration
Texte SQL	`-Fp` (défaut)	`psql -f`
Custom	`-Fc`	`pg_restore` (sélection possible des objets)
Répertoire	`-Fd`	`pg_restore`, avec parallélisme

Le format custom est le plus pratique : compressé et restaurable table par table.

Options utiles : --schema-only (structure), --data-only (données), -t clients (une table), -n public (un schéma), --no-owner (ignore les propriétaires, utile pour restaurer sous un autre rôle).

Compresser le format texte

pg_dump -h 127.0.0.1 -U app boutique | gzip > boutique-$(date +%F).sql.gz

Restaurer

# Format texte
createdb -h 127.0.0.1 -U admin boutique_test
psql -h 127.0.0.1 -U admin -d boutique_test -f boutique.sql
gunzip -c boutique-2026-10-05.sql.gz | psql -h 127.0.0.1 -U admin -d boutique_test

# Format custom ou répertoire
pg_restore -h 127.0.0.1 -U admin -d boutique_test boutique.dump
pg_restore -h 127.0.0.1 -U admin -d boutique_test -j 4 boutique_dir    # en parallèle
pg_restore -l boutique.dump                                            # liste le contenu de l'archive
pg_restore -h 127.0.0.1 -U admin -d boutique_test -t clients boutique.dump   # une seule table

Pour remplacer une base existante : pg_restore --clean --if-exists -d boutique boutique.dump.

Rôles et droits : `pg_dumpall`

pg_dump ne sauvegarde pas les rôles, qui sont globaux à l’instance.

sudo -u postgres pg_dumpall --globals-only > globals.sql       # rôles et tablespaces
sudo -u postgres psql -f globals.sql                           # restauration
sudo -u postgres pg_dumpall > tout.sql                         # toutes les bases et les rôles

Vérifier la sauvegarde

ls -lh boutique.dump
pg_restore -l boutique.dump | head -20          # contenu lisible : l'archive n'est pas corrompue

Le test définitif est la restauration complète dans une base d’essai.

Planifier avec cron

Identifiants dans ~/.pgpass (droits 600), puis le script /usr/local/bin/sauvegarde-postgres.sh :

#!/usr/bin/env bash
set -euo pipefail

DEST="/var/backups/postgres"
mkdir -p "$DEST"

pg_dump -h 127.0.0.1 -U app -Fc boutique -f "$DEST/boutique-$(date +%F_%H%M).dump"

# Conserve 14 jours
find "$DEST" -name "boutique-*.dump" -mtime +14 -delete

sudo chmod 750 /usr/local/bin/sauvegarde-postgres.sh
crontab -e

30 2 * * * /usr/local/bin/sauvegarde-postgres.sh >> /var/log/sauvegarde-postgres.log 2>&1

Copier ensuite les sauvegardes hors du serveur.

Sauvegarde physique et restauration à un instant précis

Méthode	Usage
`pg_basebackup`	Copie complète du cluster en fonctionnement (même version de PostgreSQL pour la restauration)
Archivage WAL (`archive_mode`, `archive_command`)	Combiné à `pg_basebackup` : restauration à un instant précis (PITR)
Outils dédiés (pgBackRest, Barman)	Sauvegardes incrémentales, rétention et restauration automatisées pour la production

sudo -u postgres pg_basebackup -D /var/backups/base -Ft -z -P

Dépannage

Symptôme	Piste
`pg_dump: error: aborting because of server version mismatch`	Le client `pg_dump` est plus ancien que le serveur : installer `postgresql-client-18`
`permission denied for table` pendant `pg_dump`	Le rôle doit pouvoir lire toutes les tables : `GRANT SELECT …` ou utiliser un rôle propriétaire
`pg_restore: error: role "x" does not exist`	Restaurer d’abord `globals.sql`, ou ajouter `--no-owner`
Restauration très lente	Utiliser le format répertoire avec `-j`, ou augmenter `maintenance_work_mem`

Pour aller plus loin

Manuel : Backup and Restore
Planification : Bonnes pratiques Bash