Accueil » Formations » Fondamentaux en informatique » Bases de données et SQL avec PostgreSQL » TP07 – Sauvegarder et restaurer avec pg_dump et pg_restore

TP07 – Sauvegarder et restaurer avec pg_dump et pg_restore

← Bases de données et SQL avec PostgreSQL

// travaux pratiques · Fondamentaux en informatique

TP07 - Sauvegarder et restaurer avec pg_dump et pg_restore

  • TP 7 / 7
  • 7 étapes
  • ≈ 55 min
  • Accès libre

Objectif : sauvegarder la base parc et les rôles de l’instance, prouver que la sauvegarde est restaurable dans une base de test, restaurer une seule table après une suppression accidentelle, puis automatiser une sauvegarde quotidienne avec rotation.

Contexte

Une sauvegarde qui n’a jamais été restaurée n’est qu’une hypothèse. Les incidents qui imposent une restauration sont variés : suppression de données par erreur, mise à jour applicative qui corrompt une table, rançongiciel qui chiffre le serveur. Dans une petite structure, une sauvegarde logique quotidienne avec pg_dump, copiée hors du serveur, couvre souvent le besoin ; dans un grand groupe, on y ajoute des sauvegardes physiques avec archivage continu des journaux de transactions pour restaurer à la minute près, mais les sauvegardes logiques restent utiles pour extraire ou migrer une base précise.

À la fin de ce TP, vous saurez produire une sauvegarde au format personnalisé, en lire le contenu, la restaurer entièrement ou partiellement, et planifier son exécution.

Prérequis : TP06 terminé (base parc, rôles parc_admin, parc_lecture, lecteur_bi, app_parc). Commandes à exécuter sur pg01.

Durée estimée : 55 minutes.

Actions à réaliser

1. Préparer un répertoire de sauvegarde protégé

Les sauvegardes contiennent toutes les données de la base : leur répertoire doit appartenir au compte postgres et n’être lisible par personne d’autre. install -d crée le répertoire et fixe propriétaire et droits en une seule commande.

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
ls -ld /var/backups/postgresql

Résultat attendu : une ligne commençant par drwx------ avec postgres postgres comme propriétaire et groupe.

2. Sauvegarder la base au format personnalisé

pg_dump produit une image cohérente de la base au moment où il démarre, sans bloquer les lectures ni les écritures. Le format personnalisé (-Fc) est compressé et permet une restauration sélective avec pg_restore ; le format texte (-Fp, par défaut) produit un script SQL qui se rejoue avec psql, mais sans sélection fine.

sudo -u postgres pg_dump -Fc -d parc -f /var/backups/postgresql/parc-$(date +%F).dump
sudo ls -lh /var/backups/postgresql

Résultat attendu : un fichier parc-AAAA-MM-JJ.dump de quelques mégaoctets, nettement plus petit que la base sur disque grâce à la compression et parce que les index ne sont pas copiés : seule leur définition est sauvegardée, ils sont reconstruits à la restauration.

3. Lire la table des matières de la sauvegarde

pg_restore -l liste le contenu d’une archive sans rien restaurer. C’est le moyen rapide de vérifier qu’une sauvegarde contient bien ce qu’on attend, et la base de toute restauration partielle.

sudo -u postgres pg_restore -l /var/backups/postgresql/parc-$(date +%F).dump | grep -E 'TABLE|VIEW|INDEX|ACL'

Résultat attendu : des lignes TABLE public sites, equipements et interventions, une ligne VIEW pour v_interventions, des lignes INDEX pour les deux index du TP05, des lignes TABLE DATA pour les données et des lignes ACL pour les droits accordés au TP06.

4. Sauvegarder les rôles de l’instance

pg_dump ne sauvegarde ni les rôles ni les tablespaces, qui appartiennent à l’instance et non à une base. Sans eux, une restauration sur un serveur neuf échoue sur chaque GRANT et chaque changement de propriétaire. pg_dumpall --globals-only les exporte sous forme de script SQL.

sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals-$(date +%F).sql
sudo grep -E '^(CREATE|ALTER) ROLE' /var/backups/postgresql/globals-$(date +%F).sql

Résultat attendu : des instructions CREATE ROLE et ALTER ROLE pour postgres, parc_admin, parc_lecture, lecteur_bi et app_parc, ces dernières contenant l’empreinte SCRAM des mots de passe (chaîne commençant par SCRAM-SHA-256$) : ce fichier est sensible.

5. Restaurer dans une base de test

On ne restaure jamais pour essayer sur la base de production : on crée une base vide et on y restaure l’archive. On compare ensuite un indicateur simple entre la base d’origine et la copie.

sudo -u postgres createdb -O parc_admin parc_restore
sudo -u postgres pg_restore -d parc_restore /var/backups/postgresql/parc-$(date +%F).dump
sudo -u postgres psql -d parc -At -c "SELECT count(*) FROM interventions;"
sudo -u postgres psql -d parc_restore -At -c "SELECT count(*) FROM interventions;"

Résultat attendu : pg_restore se termine sans message d’erreur, et les deux comptages sont identiques (300 013 si vous avez réalisé l’insertion de test du TP06). \dp dans parc_restore montre les mêmes droits que dans parc, puisque les rôles existent déjà sur cette instance.

6. Restaurer une seule table après une erreur

Cas fréquent : un utilisateur a vidé une table, le reste de la base est sain. On simule l’incident sur la base de test, puis on ne restaure que les données de cette table avec -t (table) et --data-only (données sans recréer la structure).

sudo -u postgres psql -d parc_restore -c "TRUNCATE interventions;"
sudo -u postgres pg_restore -d parc_restore --data-only -t interventions /var/backups/postgresql/parc-$(date +%F).dump
sudo -u postgres psql -d parc_restore -At -c "SELECT count(*) FROM interventions;"

Résultat attendu : après le TRUNCATE, la table est vide ; après la restauration partielle, le comptage retrouve la valeur de l’étape 5. Les autres tables n’ont pas été touchées. Supprimez ensuite la base de test : sudo -u postgres dropdb parc_restore.

7. Automatiser la sauvegarde quotidienne

Un script sauvegarde chaque base autorisée aux connexions (hors modèles et base postgres), puis les rôles, et supprime les fichiers de plus de 14 jours. set -eu arrête le script à la première erreur ou variable non définie. Créez le fichier /usr/local/sbin/pg-sauvegarde.sh avec le contenu suivant :

#!/bin/sh
set -eu
DEST=/var/backups/postgresql
JOUR=$(date +%F)
for BASE in $(psql -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate AND datname <> 'postgres'"); do
  pg_dump -Fc -d "$BASE" -f "$DEST/$BASE-$JOUR.dump"
done
pg_dumpall --globals-only -f "$DEST/globals-$JOUR.sql"
find "$DEST" -type f -mtime +14 -delete

Rendez-le exécutable, testez-le en tant que postgres, puis planifiez-le chaque nuit à 1 h 30 avec un fichier /etc/cron.d/pg-sauvegarde (le sixième champ indique le compte d’exécution) :

sudo chmod 755 /usr/local/sbin/pg-sauvegarde.sh
sudo -u postgres /usr/local/sbin/pg-sauvegarde.sh
echo '30 1 * * * postgres /usr/local/sbin/pg-sauvegarde.sh' | sudo tee /etc/cron.d/pg-sauvegarde

Résultat attendu : le test manuel se termine sans message ; sudo ls -lh /var/backups/postgresql montre un fichier parc-AAAA-MM-JJ.dump et un fichier globals-AAAA-MM-JJ.sql à la date du jour (le fichier du jour est simplement réécrit).

Vérification

Le TP est réussi si l’archive liste les tables, la vue, les index et les droits ; si le fichier des rôles contient les cinq rôles ; si la restauration complète donne le même comptage que la base d’origine ; si la restauration partielle a reconstitué la seule table vidée ; et si le script, lancé à la main, produit les fichiers attendus.

Problèmes fréquents

  • « pg_restore: error: input file appears to be a text format dump. Please use psql. » : la sauvegarde a été faite sans -Fc. Restaurez-la avec psql -d base -f fichier.sql, ou refaites-la au format personnalisé.
  • « role “parc_lecture” does not exist » lors d’une restauration sur un autre serveur : les rôles n’ont pas été recréés. Rejouez d’abord le fichier globals avec psql, puis lancez pg_restore.
  • « could not open output file … Permission denied » : le répertoire de destination n’appartient pas à postgres, ou la commande a été lancée avec votre compte au lieu de sudo -u postgres.
  • Le script fonctionne à la main mais pas depuis cron : le fichier /etc/cron.d ne doit contenir ni point dans son nom ni droits d’écriture pour le groupe ou les autres ; consultez le journal avec journalctl -u cron.

Bonnes pratiques et sécurité

  • Appliquez la règle 3-2-1 : copiez chaque nuit les sauvegardes hors du serveur, dont une copie hors ligne ou immuable, inaccessible à un attaquant qui compromettrait pg01.
  • Programmez un test de restauration régulier (mensuel au minimum) sur une machine séparée, et conservez la preuve du résultat.
  • Traitez le fichier globals comme un secret : il contient les empreintes de mots de passe de tous les rôles.
  • Supervisez l’exécution : un script qui échoue en silence pendant des semaines est l’incident le plus courant. Une alerte sur l’absence de fichier récent est simple à mettre en place.

Questions de contrôle

1. Pourquoi pg_dump seul ne suffit-il pas pour reconstruire l’instance sur un serveur neuf ?

Voir la réponse

Il ne sauvegarde pas les objets globaux de l’instance (rôles, tablespaces). Il faut aussi pg_dumpall --globals-only, rejoué avant la restauration des bases.

2. Quel avantage le format personnalisé (-Fc) a-t-il sur le format texte ?

Voir la réponse

Il est compressé, se lit avec pg_restore -l et permet une restauration sélective (une table, ses seules données) ; le format texte ne se rejoue qu’en bloc avec psql.

3. Une sauvegarde logique nocturne permet-elle de revenir à l’état de 15 h 42 ?

Voir la réponse

Non : elle ramène à l’état du début de la sauvegarde. La restauration à un instant précis exige une sauvegarde physique (pg_basebackup) et l’archivage continu des journaux de transactions (WAL).

Pour aller plus loin

Pour externaliser et chiffrer les fichiers produits, suivez le cours Sauvegardes 3-2-1 avec Restic ou notre article Kopia : sauvegardes chiffrées, dédupliquées et immuables. Sur une instance volumineuse, pg_dump -Fd -j 4 (format répertoire) parallélise la sauvegarde sur quatre processus. Le laboratoire peut être conservé pour vos essais ou supprimé : ce TP clôt le cours.

// formation sur mesure

Former votre équipe sur ce sujet ?

Azur Secure anime des sessions intra-entreprise, à distance ou dans vos locaux sur la Côte d'Azur, adaptées au niveau de vos équipes.

Demander un programme
Défiler vers le haut