Sauvegarde logique et restauration PostgreSQL (pg_dump / pg_restore)

Sauvegarde logique et restauration avec pg_dump, pg_dumpall et pg_restore au niveau d'une table, d'un schéma, d'une base ou de tout le cluster ; structure seule (DDL) ou données seules, migration vers un autre serveur ou une autre version, restauration d'une seule table depuis un dump, et sauvegarde planifiée, vérifiée et avec durée de rétention via systemd/cron. Chaque procédure se termine par une vérification qui compare les nombres de lignes. Avec la streaming replication et les clusters Patroni, le dump est pris sur une replica (port de lecture HAProxy ou standby) ; la restauration se fait uniquement sur le primary/leader.

Une sauvegarde logique ramène à l'instant du dump et ne permet pas la restauration à un instant donné (PITR) ; en production, la sauvegarde principale doit être physique : pgBackRest (recommandé) ou, sans outil supplémentaire, pg_basebackup + archivage WAL. Pour migrer une grosse base vers une nouvelle version avec quelques secondes d'interruption, utilisez la logical replication ; pour une mise à niveau sur place sur le même serveur, pg_upgrade.

Topologie: 1 serveur, streaming replication (primary + standby) ou cluster Patroni ; pour une migration, source + cible ; pour une sauvegarde planifiée distante, un serveur de sauvegarde en plus

Scénarios

• Export / import d'une ou plusieurs tables — La structure et les données des tables choisies (index, contraintes, triggers compris) sont extraites puis chargées dans une autre base.
• Export / import d'un schéma — Tous les objets du schéma (tables, vues, fonctions, séquences) sont extraits.
• Sauvegarde / restauration d'une base — Une base complète (structure + données) est extraite dans un seul dump puis restaurée dans la même base ou dans une autre. Les rôles (globals) ne sont pas inclus.
• Cluster complet (rôles + tablespaces + chaque base) — Les globals avec pg_dumpall -g, un pg_dump distinct par base ; la restauration est répétée sur un cluster de test temporaire.
• Structure du schéma seule (DDL) — Définitions des tables, index, vues et fonctions sans les données ; pour revue, gestion de versions ou création d'un environnement vide.
• Données seules — Seules les lignes (COPY) sont transférées, la structure étant déjà en place sur la cible.
• Migration vers un autre serveur / une autre version — Sur le serveur cible, le pg_dump de la nouvelle version se connecte à la source par le réseau ; le transfert se fait par pipe ou par fichier.
• Restaurer une seule table / un seul objet depuis un dump — À partir d'un dump custom/directory existant, seule une table avec ses index, contraintes et données est restaurée à l'aide d'un fichier liste (-L).
• Sauvegarde planifiée (systemd timer / cron) — Sauvegarde quotidienne des globals et des bases, contrôle de lisibilité, durée de rétention, journalisation et notification d'échec.

Avant de commencer

• …Le cluster Patroni fonctionne ; les outils client PostgreSQL … (…) sont installés sur ce serveur et les ports d'écriture (…)… et de lecture (…)… de HAProxy/VIP (…) sont accessibles. Vous disposez du mot de passe du rôle postgres (avec Patroni, PATRONI_SUPERUSER_PASSWORD dans /etc/patroni/patroni.env).…PostgreSQL … tourne sur le primary et l'utilisateur postgres peut se connecter en local (peer) ; le standby … est à l'état streaming et son port … est accessible depuis le primary. Comme le dump se connecte au standby par le réseau, le rôle postgres doit avoir un mot de passe (sinon, \password postgres sur le primary ; le mot de passe arrive sur le standby par le WAL).…PostgreSQL … tourne et l'utilisateur système postgres peut se connecter en local (peer). Si l'installation a suivi le guide DBAtlas Installation PostgreSQL sur serveur unique, les chemins sont les mêmes.…
• …Le cluster est sain : un Leader, au moins une replica en streaming et un retard faible. Vérifiez-le sur l'un des nodes Patroni.…Le standby … est connecté au primary et son retard est faible (le dump prend l'image du standby à cet instant).…
• Le répertoire de sauvegarde … se trouve sur un disque distinct du disque de données et dispose d'assez d'espace libre : un dump custom/directory compressé représente généralement 10–30 % de la taille de la base (les index n'entrent dans le dump que sous forme de définitions) ; le format plain et les dumps non compressés sont plus volumineux.
• La version majeure de pg_dump est identique ou plus récente que celle du serveur sauvegardé (un pg_dump plus récent peut lire un serveur plus ancien ; l'inverse ne fonctionne pas). pg_restore doit être de la même version que le pg_dump qui a créé le dump, ou plus récent.
• pg_dump/pg_restore sur la cible (…) sont de la même version majeure que le serveur source (…) ou plus récents : la migration se fait avec les outils plus récents de la cible.
• Les logiciels des extensions utilisées sur la source (postgis, pgvector, pg_partman…) sont aussi installés sur le serveur cible. Une sauvegarde logique ne transporte que la ligne CREATE EXTENSION, pas l'extension elle-même.
• S'il existe des tablespaces définis par l'utilisateur (la requête ci-dessous renvoie des lignes), il faut préparer les mêmes répertoires sur la cible, ou restaurer dans le tablespace par défaut avec --no-tablespaces.
• PostgreSQL … est installé, démarré et vide sur le serveur cible … (seulement postgres, template0, template1). La cible peut joindre le port … de la source.
• Le serveur de sauvegarde … peut joindre …le port … de l'adresse HAProxy/VIP ……le port … du standby ……le port … du serveur de base de données… et accède au dépôt PGDG (ou à un miroir interne).
• Le serveur peut envoyer des e-mails : le MTA local (postfix) relaie vers le relais SMTP de l'entreprise.
• Une fenêtre de maintenance est prévue pour une grosse restauration ou une migration ; si la cible doit être écrasée, une sauvegarde/un snapshot de la cible a été pris.

Phases du guide

• Préparation — Créer le répertoire de sauvegarde · Enregistrer le mot de passe de connexion dans .pgpass · Autoriser l'accès dans pg_hba.conf sur le standby · Vérifier sur quel rôle arrivent les connexions · Vérifier la taille des bases et les transactions longues
• Restauration (import) — Préparer la base cible · Préparer la cible : extensions, schémas et fonctions trigger · Restaurer · Restaurer en écrasant la cible · Collecter les statistiques (ANALYZE) · Comparer les nombres de lignes
• Structure du schéma seule (DDL) — Prendre le dump DDL · Créer la base cible · Appliquer le DDL sur la cible · Vérifier que les structures sont identiques
• Données seules — Prendre le dump des données · Créer la base cible · Créer la structure des tables sur la cible · Vider les tables cibles · Charger les données · Collecter les statistiques et comparer les nombres de lignes
• Sauvegarde du cluster complet — Sauvegarder les globals et toutes les bases · Vérifier que la sauvegarde est lisible
• Répétition de restauration (cluster de test temporaire) — Préparer le répertoire de test · Créer et démarrer le cluster temporaire · Charger les globals · Charger les bases · ANALYZE et comparaison des nombres de lignes · Arrêter et supprimer le cluster temporaire
• Autorisation d'accès sur le serveur source — Autoriser le serveur cible dans pg_hba.conf · Autoriser le serveur cible dans pg_hba (Patroni) · Ouvrir le port à la cible dans le pare-feu · Arrêter l'application (point de bascule)
• Transfert sur le serveur cible — Enregistrer le mot de passe de connexion à la source dans .pgpass · Vérifier la compatibilité des versions et des extensions · Migrer les rôles (globals) · Transférer les bases (par fichiers, en parallèle) · Transférer les bases (par pipe) · Collecter les statistiques (ANALYZE) …
• Après la bascule — Retirer l'accès temporaire sur la source · Retirer l'accès temporaire sur la source (Patroni) · Rediriger l'application vers la cible
• Restaurer une seule table depuis un dump — Examiner le contenu du dump · Créer le fichier liste (-L) · Préparer la base cible · Restaurer la table · Restaurer la table dans la base d'origine · Comparer le nombre de lignes …
• Serveur de sauvegarde et rôle de sauvegarde — Créer le rôle de sauvegarde · Donner l'accès en lecture aux large objects · Définir un mot de passe pour le rôle de sauvegarde · Autoriser le serveur de sauvegarde dans pg_hba.conf · Autoriser le serveur de sauvegarde dans pg_hba (Patroni) · Installer les outils client PostgreSQL (serveur de sauvegarde) …
• Script de sauvegarde — Vérifier les chemins complets des outils · Installer l'outil de notification · Écrire le script de sauvegarde · Rendre le script exécutable · Faire la première sauvegarde à la main
• Planification (systemd timer) — Écrire le service systemd · Écrire le timer systemd · Activer le timer et déclencher le service à la main
• Planification (cron) — Préparer le service cron · Écrire la tâche cron · Régler les droits du fichier cron et tester dans un environnement cron · Vérifier que cron exécute la tâche
• Vérification et supervision — Restaurer la dernière sauvegarde dans une base de test · Surveiller l'âge de la dernière sauvegarde
• Étape suivante : sauvegarde physique — Combler les limites de la sauvegarde logique