Laboratoire de sauvegarde et de récupération PostgreSQL 18 sur Ubuntu 24.04 — pg_dump, pg_basebackup et pgBackRest

Si vous travaillez avec PostgreSQL en production, les sauvegardes ne sont pas facultatives.

Dans ce laboratoire, j'ai mis en place un environnement complet de sauvegarde et de restauration PostgreSQL 18 sur Ubuntu 24.04 et testé les trois principales approches de sauvegarde :

• sauvegardes logiques avec pg_dump
• sauvegardes physiques avec pg_basebackup
• sauvegardes de niveau production avec pgBackRest, y compris l'archivage des WAL et la récupération à un instant T (PITR)

Tout a été testé sur une vraie VM avec des exercices de récupération réels — pas des simulations.

L'environnement

Installation de PostgreSQL 18

J'ai utilisé le dépôt officiel PGDG plutôt que le paquet par défaut d'Ubuntu, qui contient une version plus ancienne.

Installer l'assistant de dépôt :

sudo apt install -y postgresql-common

Exécutez le script de configuration PGDG :

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Installer PostgreSQL 18 :

sudo apt install -y postgresql-18

Vérifiez que le cluster est en cours d'exécution.

pg_lsclusters

Résultat attendu :

Ver Cluster Port Status Owner    Répertoire de données
18  principal 5432 en ligne postgres /var/lib/postgresql/18/main

Création de la base de données du laboratoire

Créer l'utilisateur de l'application :

sudo -u postgres psql -c "CREATE USER banking WITH PASSWORD 'banking';"

Créer la base de données :

sudo -u postgres psql -c "CREATE DATABASE bankingdb OWNER banking;"

Connectez-vous en tant que l'utilisateur bancaire et créez le schéma :

psql -h localhost -U banking -d bankingdb -c "CREATE SCHEMA banking AUTHORIZATION banking;"

Création des tables

J'ai créé cinq tables représentant un petit schéma bancaire : branches, employees, customers, accounts et transactions. La chaîne de clés étrangères entre elles exerce les contraintes qui importent dans les scénarios de restauration réels.

-- Connected as banking@bankingdb
CREATE TABLE banking.branches (
  branch_id    INTEGER PRIMARY KEY,
  branch_name  VARCHAR(100) NOT NULL,
  city         VARCHAR(50),
  country      VARCHAR(50),
  opened_date  DATE
);

CREATE TABLE banking.employees (
  employee_id  INTEGER PRIMARY KEY,
  branch_id    INTEGER NOT NULL REFERENCES banking.branches(branch_id),
  full_name    VARCHAR(100) NOT NULL,
  role         VARCHAR(50),
  hire_date    DATE
);

CREATE TABLE banking.customers (
  customer_id  INTEGER PRIMARY KEY,
  full_name    VARCHAR(100) NOT NULL,
  email        VARCHAR(100),
  country      VARCHAR(50),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE banking.accounts (
  account_id    INTEGER PRIMARY KEY,
  customer_id   INTEGER NOT NULL REFERENCES banking.customers(customer_id),
  branch_id     INTEGER NOT NULL REFERENCES banking.branches(branch_id),
  account_type  VARCHAR(20),
  balance       NUMERIC(15,2) DEFAULT 0,
  opened_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE banking.transactions (
  txn_id       INTEGER PRIMARY KEY,
  account_id   INTEGER NOT NULL REFERENCES banking.accounts(account_id),
  employee_id  INTEGER REFERENCES banking.employees(employee_id),
  txn_type     VARCHAR(20),
  amount       NUMERIC(15,2) NOT NULL,
  txn_date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  description  VARCHAR(200)
);

J'ai inséré 18 lignes sur les cinq tables — trois branches, quatre employés, trois clients, quatre comptes, quatre transactions. C'est l'état de base que chaque sauvegarde en laboratoire doit capturer et que chaque restauration doit reproduire.

Création de répertoires de sauvegarde

# pg_dump output — runs as the fernando OS user
mkdir -p /home/fernando/backups/pg-dump

# pg_basebackup output — runs as postgres
sudo mkdir -p /var/lib/postgresql/backups/pg-basebackup
sudo chown -R postgres:postgres /var/lib/postgresql/backups
sudo chmod -R 750 /var/lib/postgresql/backups

Le répertoire du dépôt pgBackRest est créé séparément dans la partie 3.

Partie 1 - pg_dump

pg_dump crée une exportation logique d'une base de données — des instructions SQL ou un format binaire compressé qui peut être rejoué dans n'importe quelle instance PostgreSQL compatible.

Meilleur pour :

• migrations inter-versions
• restauration d'une seule base de données ou d'une seule table
• exportations de schéma uniquement pour la documentation ou la gestion des versions DDL

Ne convient pas pour les sauvegardes PITR ou les bases de données volumineuses où le temps de restauration doit être mesuré en minutes plutôt qu'en heures.

Sauvegarde complète de la base de données

J'ai utilisé le format personnalisé (-F c) — binaire comprimé, restaurable uniquement avec pg_restore, prend en charge la restauration parallèle et les options de sélection de table.

PGPASSWORD=banking pg_dump \
  -h localhost \
  -U banking \
  -F c \
  -d bankingdb \
  -f /home/fernando/backups/pg-dump/bankingdb.dump

Inspecter le contenu de la sauvegarde sans restaurer :

pg_restore -l /home/fernando/backups/pg-dump/bankingdb.dump | head -30

La table des matières présentait les schémas, les tables, les séquences, les contraintes et les données de table pour les cinq tables.

Sauvegarde uniquement du schéma

PGPASSWORD=banking pg_dump \
  -h localhost \
  -U banking \
  -F c \
  --schema-only \
  -d bankingdb \
  -f /home/fernando/backups/pg-dump/bankingdb-schema.dump

Utile pour les migrations, le versionnement DDL et la recréation d'environnements cibles vides avant un chargement de données.

Sauvegarde d'une seule table

PGPASSWORD=banking pg_dump \
  -h localhost \
  -U banking \
  -F c \
  -t banking.transactions \
  -d bankingdb \
  -f /home/fernando/backups/pg-dump/transactions.dump

La sauvegarde ne contient que la table des transactions et ses contraintes — pas les tables parentes. Sa restauration nécessite que les tables parentes existent déjà dans la cible.

Test de restauration complète

J'ai créé une nouvelle base de données vide et l'ai restaurée avec la sauvegarde complète :

sudo -u postgres psql -c "CREATE DATABASE bankingdb_restore OWNER banking;"

PGPASSWORD=banking pg_restore \
  -h localhost \
  -U banking \
  -d bankingdb_restore \
  /home/fernando/backups/pg-dump/bankingdb.dump

Vérifier que les décomptes de lignes correspondent à la source dans les cinq tables :

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb_restore -c "
SELECT 'branches'     AS table_name, COUNT(*) FROM banking.branches
UNION ALL SELECT 'employees',    COUNT(*) FROM banking.employees
UNION ALL SELECT 'customers',    COUNT(*) FROM banking.customers
UNION ALL SELECT 'accounts',     COUNT(*) FROM banking.accounts
UNION ALL SELECT 'transactions', COUNT(*) FROM banking.transactions;"

Ensuite, abandonner la base de données de test :

sudo -u postgres psql -c "DROP DATABASE bankingdb_restore;"

Exercice de reprise après sinistre — Restauration d'une table

Simulation d'une suppression accidentelle de table :

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
DROP TABLE banking.transactions CASCADE;" 

Restauré uniquement cette table du vidage de transactions dédié :

PGPASSWORD=banking pg_restore \
  -h localhost \
  -U banking \
  -d bankingdb \
  -t transactions \
  /home/fernando/backups/pg-dump/transactions.dump

Vérifié que les quatre rangées étaient de retour. C'est là que pg_dump brille vraiment — récupération granulaire au niveau de l'objet sans toucher le reste de la base de données.

Partie 2 — pg_basebackup

pg_basebackup effectue une copie binaire au niveau du système de fichiers de l'ensemble du cluster PostgreSQL. Il ne comprend ni les bases de données ni les tables — il copie des fichiers.

Meilleur pour :

• amorçage des répliques en continu
• instantanés de récupération d'urgence d'un cluster entier
• migration de cluster vers un nouveau serveur

Ne convient pas à la récupération au niveau de la table ou de la base de données — la restauration est tout ou rien au niveau du cluster.

Vérification de la configuration de WAL

sudo -u postgres psql -c "SHOW wal_level; SHOW max_wal_senders;"

Résultat attendu :

 wal_level
-----------
 réplication

 max_wal_senders
-----------------
 10

Les deux sont correctement configurés par défaut sur PostgreSQL 18.

Création de la sauvegarde physique

J'ai utilisé le format tar-F t) — produit compressé base.tar.gz et pg_wal.tar.gz archives plutôt qu'un arborescence de répertoires non compressée.

sudo -u postgres pg_basebackup \
  -D /var/lib/postgresql/backups/pg-basebackup/cluster \
  -F t \
  -z \
  -P \
  -c fast

-c rapide émet un point de contrôle au début de la sauvegarde au lieu d'attendre le prochain programmé — cela ajoute une brève pointe d'E/S mais est le bon choix pour un laboratoire interactif.

Vérification de la sauvegarde

ls -lh /var/lib/postgresql/backups/pg-basebackup/cluster/

Fichiers attendus :

Vérifiez l'intégrité avant de vous fier à la sauvegarde :

sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup --no-parse-wal \
  /var/lib/postgresql/backups/pg-basebackup/cluster

Une sauvegarde que vous n'avez jamais vérifiée est une sauvegarde à laquelle vous ne pouvez pas faire confiance.

Exercice de restauration physique complète

Arrêter PostgreSQL :

sudo systemctl stop postgresql

Mettez de côté le répertoire de données existant :

sudo mv /var/lib/postgresql/18/main /var/lib/postgresql/18/main.before-restore
sudo mkdir -p /var/lib/postgresql/18/main
sudo chown postgres:postgres /var/lib/postgresql/18/main
sudo chmod 700 /var/lib/postgresql/18/main

Extraire l'archive de base :

sudo -u postgres tar -xzf \
  /var/lib/postgresql/backups/pg-basebackup/cluster/base.tar.gz \
  -C /var/lib/postgresql/18/main

Extraire l'archive WAL :

sudo -u postgres mkdir -p /var/lib/postgresql/18/main/pg_wal
sudo -u postgres tar -xzf \
  /var/lib/postgresql/backups/pg-basebackup/cluster/pg_wal.tar.gz \
  -C /var/lib/postgresql/18/main/pg_wal

Démarrer PostgreSQL

sudo systemctl start postgresql
sudo systemctl status postgresql@18-main

Vérifier que les décomptes de lignes correspondent à la référence dans les cinq tables.

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
SELECT 'branches'     AS table_name, COUNT(*) FROM banking.branches
UNION ALL SELECT 'employees',    COUNT(*) FROM banking.employees
UNION ALL SELECT 'customers',    COUNT(*) FROM banking.customers
UNION ALL SELECT 'accounts',     COUNT(*) FROM banking.accounts
UNION ALL SELECT 'transactions', COUNT(*) FROM banking.transactions;"

Supprimez le répertoire mis de côté :

sudo rm -rf /var/lib/postgresql/18/main.before-restore

Partie 3 — pgBackRest

pgBackRest est la solution de sauvegarde de niveau production pour PostgreSQL. Il combine la sauvegarde physique avec l'archivage WAL intégré, les types de sauvegarde incrémentielle et différentielle, la gestion de la rétention, les E/S parallèles et la récupération à un instant T — le tout dans un seul outil.

Installation de pgBackRest

sudo apt install -y pgbackrest

Créer le répertoire du dépôt

pgBackRest ne crée pas automatiquement le répertoire du dépôt — il doit exister et être la propriété de PostgreSQL avant que la strophe ne soit créée.

sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest

Configuration de pgBackRest

sudo mkdir -p /etc/pgbackrest
sudo tee /etc/pgbackrest/pgbackrest.conf > /dev/null << 'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
log-level-console=info
log-level-file=detail

[main]
pg1-path=/var/lib/postgresql/18/main
EOF

Le nom du strophe principal correspond au nom du cluster PostgreSQL d'Ubuntu, celui par défaut sur toute installation PostgreSQL d'Ubuntu.

Configuration de l'archivage WAL

Trois paramètres sont nécessaires pour /etc/postgresql/18/main/postgresql.conf:

mode d'archivage nécessite un redémarrage complet de PostgreSQL — il ne s'agit pas d'un paramètre rechargeable.

sudo systemctl restart postgresql

Vérifier qu'ils ont pris effet :

sudo -u postgres psql -c "SHOW archive_mode; SHOW archive_command; SHOW wal_level;"

Création de la strophe et exécution de la vérification

Une stanza est la configuration nommée de pgBackRest pour un cluster PostgreSQL. créer-une-strophe initialise la structure de répertoire du dépôt — exécutez-la une seule fois.

sudo -u postgres pgbackrest --stanza=main stanza-create

Puis exécutez Vérifier — pgBackRest force un basculement WAL, archive le segment et le relit :

sudo -u postgres pgbackrest --stanza=main check

C'est l'étape de vérification préalable à la sauvegarde la plus importante. Si Vérifier échoue, les sauvegardes ne seront pas récupérables.

Sauvegarde complète

sudo -u postgres pgbackrest --stanza=main --type=full backup

Sauvegarde différentielle

Après avoir inséré une nouvelle ligne de transaction, j'ai effectué une sauvegarde différentielle :

sudo -u postgres pgbackrest --stanza=main --type=diff backup

La taille de la sauvegarde représentait une petite fraction du total — seuls les blocs modifiés ont été stockés.

Sauvegarde incrémentielle

Après avoir inséré une autre ligne :

sudo -u postgres pgbackrest --stanza=main --type=incr backup

La chaîne de sauvegarde est devenue : complète → différentielle → incrémentielle.

Inspecter le catalogue complet :

sudo -u postgres pgbackrest info

Restauration à un instant T

C'était la partie la plus importante du laboratoire.

Capture l'horodatage actuel avant la catastrophe :

RECOVERY_TARGET=$(sudo -u postgres psql -d bankingdb -Atc "SELECT now()::text;")
echo "Recovery target: $RECOVERY_TARGET"
sleep 2

CIBLE_DE_RECUPERATION est une variable de shell bash. Elle stocke l'horodatage PostgreSQL exact capturé avant la catastrophe.

Toutes les étapes PITR s'exécutent dans la même session de terminal, de sorte que la variable reste active entre les commandes.

Il est passé directement à pgBackRest --objectif paramètre, qui lui indique de rejouer les WAL jusqu'à ce moment précis et de s'arrêter.

Simuler la catastrophe — supprimer la table des transactions :

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
DROP TABLE banking.transactions CASCADE;" 

Arrêter PostgreSQL :

sudo systemctl stop postgresql

Restaurer à l'horodatage avant la baisse :

sudo -u postgres pgbackrest --stanza=main \
  --type=time \
  "--target=$RECOVERY_TARGET" \
  --target-action=promote \
  --delta \
  restore

--delta compare le répertoire de données existant avec la sauvegarde et remplace uniquement les fichiers modifiés — beaucoup plus rapide qu'une ré-extraction complète lorsque la plupart des fichiers sont inchangés.

Démarrez PostgreSQL — il entre en mode de récupération, rejoue les WAL jusqu'à l'horodatage cible, puis est promu en lecture/écriture :

sudo systemctl start postgresql
sudo tail -20 /var/log/postgresql/postgresql-18-main.log

Vérifiez que la table est de retour :

PGPASSWORD=banking psql -h localhost -U banking -d bankingdb -c "
SELECT COUNT(*) FROM banking.transactions;" 

Attendu : 6 lignes — 4 de base plus les 2 insérées pendant les étapes différentielle et incrémentielle. PITR a fonctionné.

Résumé

Règles de décision pratiques :

• Utilisation pg_dump pour les migrations inter-versions, les sauvegardes sélectives par table et les instantanés ad hoc.
• Utilisation pg_basebackup principalement pour amorcer des répliques en flux.
• Utilisez pgBackRest pour tout en production — incrémentiels quotidiens, PITR, rétention, archivage WAL, le tout dans un seul outil.

Dans des environnements réels, vous les combinez : pgBackRest pour la protection continue, pg_dump pour les migrations et la récupération au niveau de l'objet.

Réflexions finales

Une sauvegarde n'est utile que si la restauration fonctionne réellement.

La discipline la plus importante dans ce laboratoire n'était pas l'exécution des commandes de sauvegarde, mais l'exécution des exercices de restauration et la vérification des comptes de lignes ensuite.

La restauration des tests n'est pas facultative.

Si vous prévoyez une migration d'Oracle vers PostgreSQL et que vous avez besoin d'aide pour concevoir la stratégie de sauvegarde et de restauration de l'environnement cible, voir mes services →