Sauvegarde et restauration PostgreSQL avec pg_dump et pg_restore

Pour une sauvegarde logique PostgreSQL, exportez une base avec pg_dump, puis restaurez une archive au format custom avec pg_restore dans une base distincte. Vérifiez le code de sortie, examinez les erreurs et testez les données restaurées avant de compter sur la sauvegarde. Un export terminé ne prouve pas, à lui seul, que l'application peut reprendre.

Ce guide s'adresse aux développeurs et administrateurs qui souhaitent un exercice reproductible de sauvegarde et de restauration PostgreSQL. Il couvre une archive logique complète, les objets qu'elle ne contient pas et les vérifications à effectuer. Il ne configure pas de service de sauvegarde de production ni de restauration à un instant donné.

Choisir la méthode selon le besoin de récupération

PostgreSQL décrit trois approches : exports logiques, sauvegardes du système de fichiers et archivage continu. Un export logique convient à un transfert de base ou à un exercice de restauration contrôlé. Les grandes bases de production ou celles qui changent fréquemment demandent souvent une autre méthode principale.

La restauration à un instant donné exige une sauvegarde de base adaptée et la séquence nécessaire de journaux de transactions archivés, ou WAL. Une archive pg_dump quotidienne ne permet pas de rejouer les transactions jusqu'à une heure quelconque entre deux exports. Consultez la documentation sur l'archivage continu si votre besoin de récupération l'exige.

Une copie ordinaire du répertoire de données d'un serveur PostgreSQL actif n'est pas une sauvegarde cohérente. Les sauvegardes du système de fichiers imposent des conditions de cohérence, par exemple des instantanés coordonnés ou l'arrêt du serveur pour une simple copie. pg_basebackup offre une autre voie pour une sauvegarde physique de base, avec ses propres prérequis.

Vérifier les versions PostgreSQL et les serveurs

Notez les versions des serveurs source et cible, les extensions installées, les paramètres régionaux, la taille de la base et les utilitaires clients employés. Commencez par vérifier ces clients :

pg_dump --version
pg_restore --version
psql --version

Un client pg_dump ne peut pas exporter un serveur dont la version majeure est plus récente que la sienne. Ne supposez pas non plus qu'une archive pourra être chargée dans une version majeure plus ancienne. Pour cet exercice, utilisez des clients à jour de la même version majeure que les serveurs source et cible ; traitez une montée de version comme un changement distinct à tester. La référence pg_dump décrit la compatibilité et les formats d'archive.

Les exemples utilisent des hôtes fictifs, une base source appdb et un serveur de restauration séparé. Remplacez chaque valeur avec attention. Vérifiez que la cible ne peut pas envoyer des emails de production, consommer des files actives ou déclencher des tâches externes. Exécutez les commandes une par une et arrêtez-vous après tout échec.

Préparer les identifiants et une destination protégée

Le rôle backup_reader doit pouvoir lire toutes les données nécessaires. La sécurité au niveau des lignes, les tables restreintes ou les objets d'extensions peuvent demander une préparation supplémentaire. Ne répondez pas à une erreur en accordant systématiquement les droits de superutilisateur. Sur le serveur isolé, restore_owner doit pouvoir créer la base de test et ses objets.

Configurez une authentification approuvée avant les commandes. Pour une authentification par mot de passe, un fichier pgpass protégé peut servir aux tâches sans intervention. Sous Unix, il doit interdire l'accès au groupe et aux autres utilisateurs ; sous Windows, son répertoire doit être protégé. Ne placez pas de mots de passe dans les commandes ni dans les scripts versionnés. --no-password fait échouer la commande si un mot de passe nécessaire n'est pas disponible ; cette option ne désactive pas l'authentification. Omettez-la pour permettre une saisie interactive.

Utilisez un nouveau répertoire restreint, suffisamment d'espace libre, un nom de fichier unique et des paramètres de transport approuvés. L'archive peut contenir les mêmes données sensibles que la source. La compression ne chiffre pas les données. Prévoyez chiffrement du stockage, droits d'accès, conservation et copie séparée ; notre guide de la sauvegarde cloud couvre cette organisation.

Créer et inspecter une archive au format custom

Exportez la base dans une archive au format custom :

pg_dump --host=source-db.example.invalid --port=5432 --username=backup_reader --dbname=appdb --format=custom --file=appdb-2026-10-30.dump --verbose --no-password

Conservez la sortie d'erreur et le code de sortie du processus dans le compte rendu. Vérifiez le code immédiatement, par exemple avec $? sous Bash ou $LASTEXITCODE sous PowerShell. Ne poursuivez pas après un code non nul ou un avertissement inexpliqué. Un fichier peut exister alors que la tâche a échoué.

Listez le contenu de l'archive sans connexion à une base :

pg_restore --list --file=appdb-2026-10-30.contents.txt appdb-2026-10-30.dump

Vérifiez la présence des schémas, tables, séquences et autres objets attendus. Une liste lisible confirme seulement que l'en-tête et le catalogue de l'archive peuvent être lus. Elle ne prouve pas que tous les blocs de données pourront être restaurés.

Un export logique représente un instantané cohérent de la base, pas les changements validés pendant toute sa durée. Si la source continue à évoluer, ses nombres de lignes ultérieurs peuvent différer de ceux de la copie restaurée. Pour le premier exercice, utilisez un petit jeu de données connu, suspendez ses écritures et notez les résultats attendus avant l'export.

Prendre en compte les objets extérieurs à la base

Une archive pg_dump ne couvre pas l'ensemble du cluster. Les rôles partagés et les définitions des tablespaces demandent un traitement distinct. pg_dumpall --globals-only peut exporter ces définitions globales selon les droits du rôle utilisé. Relisez ce SQL avant de l'appliquer ailleurs et protégez les éventuelles informations d'authentification qu'il contient.

Notez aussi les extensions et leurs versions disponibles, la configuration serveur, les certificats, les tâches planifiées et les fichiers externes. Une base restaurée qui dépend d'une extension absente ou d'un service indisponible peut rester inutilisable malgré la présence de ses tables ordinaires.

Restaurer dans une nouvelle base cible isolée

Utilisez une archive de confiance. La restauration peut exécuter du code défini par les superutilisateurs de la source ; sélectionner une seule table ne supprime pas ce risque. Consultez l'avertissement de la documentation pg_restore avant de restaurer une archive inconnue.

Créez une nouvelle cible à partir de template0, puis restaurez l'archive. L'utilitaire createdb permet de préciser l'hôte et le modèle. Si ce nom de base existe déjà, arrêtez-vous et choisissez-en un autre au lieu de supprimer la base existante.

createdb --host=restore-db.example.invalid --port=5432 --username=restore_owner --template=template0 --no-password appdb_restore_20261030
pg_restore --host=restore-db.example.invalid --port=5432 --username=restore_owner --dbname=appdb_restore_20261030 --no-owner --no-privileges --no-tablespaces --single-transaction --exit-on-error --verbose --no-password appdb-2026-10-30.dump

Ces options adaptent volontairement la restauration au test : --no-owner attribue les objets au rôle qui restaure ; --no-privileges omet les droits accordés ; --no-tablespaces ignore le placement initial. Cela ne reproduit pas les contrôles d'accès de production. Des noms de rôles intégrés à des politiques ou à des fonctions peuvent encore demander une intervention.

--single-transaction applique la restauration entièrement ou pas du tout et implique l'arrêt sur erreur. Cette option est incompatible avec les tâches parallèles ; une grande restauration peut aussi dépasser les limites pratiques de transaction ou de verrous. N'ajoutez pas --clean ou --create à cet exercice : ces options changent la destination et peuvent affecter des objets existants ou la base dont le nom figure dans l'archive.

Vérifier la base restaurée au-delà du succès des commandes

Enregistrez ce contrôle initial dans verify-restore.sql. Il identifie la connexion et liste les tables applicatives visibles :

SELECT current_database(), current_user, version();

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Exécutez-le sur la nouvelle base :

psql --host=restore-db.example.invalid --port=5432 --username=restore_owner --dbname=appdb_restore_20261030 --no-psqlrc --set=ON_ERROR_STOP=on --no-password --file=verify-restore.sql

Le paramètre d'arrêt sur erreur de psql interrompt le script en cas d'erreur, tandis que --no-psqlrc évite les personnalisations locales de démarrage. Aucun ne transforme les résultats des requêtes en assertions. Ajoutez des contrôles propres à vos données et faites explicitement échouer le test lorsqu'un résultat attendu diffère.

• Comparez les nombres de lignes et certaines valeurs connues du jeu de test, y compris les tables vides et le texte non ASCII.
• Vérifiez les clés primaires et étrangères, index, séquences, vues, fonctions et extensions nécessaires.
• Testez une insertion pour repérer les problèmes de séquence ou de valeur par défaut, uniquement avec des données de test jetables.
• Testez les lectures et écritures applicatives avec les rôles prévus, après avoir préparé les droits adaptés.
• Mesurez le délai complet de récupération : obtention de l'archive, préparation des dépendances, restauration et contrôle de l'application.

Actualisez si nécessaire les statistiques de planification avec ANALYZE avant de juger les performances. Une liste de tables, un nombre de lignes ou une somme de contrôle identique ne valide qu'un aspect de la restauration. Notre guide des tests de récupérabilité aide à définir les critères d'acceptation.

Les fichiers SQL texte se restaurent avec psql

Pour un script SQL texte, choisissez --format=plain et chargez le fichier avec psql. Il s'agit d'une autre méthode, pas d'une étape supplémentaire sur la restauration précédente. Créez d'abord une base neuve appdb_sql_restore_20261030 sur le serveur isolé, avec des paramètres compatibles et les rôles nécessaires au script :

pg_dump --host=source-db.example.invalid --port=5432 --username=backup_reader --dbname=appdb --format=plain --file=appdb-2026-10-30.sql --no-password
psql --host=restore-db.example.invalid --port=5432 --username=restore_owner --dbname=appdb_sql_restore_20261030 --no-psqlrc --set=ON_ERROR_STOP=on --no-password --file=appdb-2026-10-30.sql

L'option --file indique explicitement le chemin de sortie et évite les différences de comportement des redirections selon le shell. Cet exemple SQL texte conserve les propriétaires et droits d'origine ; ON_ERROR_STOP n'annule pas les instructions déjà validées. Gardez une cible jetable et examinez la première erreur avant de réessayer.

Maintenir une procédure de récupération

Consignez l'identifiant de l'archive, la version source, les heures de début et de fin d'export, les codes de sortie, la cible, les contrôles effectués et les différences non résolues. Planifiez les prochaines exécutions selon la perte de données acceptable, puis vérifiez que les sauvegardes effectivement terminées respectent ce besoin.

Déclenchez des alertes pour les exports manqués ou échoués, les transferts défaillants, les erreurs de conservation et les tests de restauration en échec. Gardez une copie protégée indépendamment et indiquez qui peut la récupérer. Répétez l'exercice après un changement de schéma, d'extension, de rôle ou de version PostgreSQL.

Les options de commande de ce guide ont été vérifiées dans la documentation PostgreSQL. Elles n'ont pas été exécutées sur un serveur PostgreSQL ici. Adaptez et testez cette procédure avant de l'utiliser, en gardant l'autorisation de récupération en production distincte de l'exercice.