Comment sauvegarder et restaurer des bases de données avec pg_dump et pg_restore

Vous avez besoin d'une sauvegarde PostgreSQL que vous pouvez restaurer, pas seulement d'un fichier qui ressemble à une sauvegarde. pg_dump et pg_restore sont les outils standard pour les sauvegardes logiques : ils copient les objets de la base de données et les données dans un vidage portable, puis les reconstruisent plus tard sur le même serveur, un serveur de test ou une nouvelle instance PostgreSQL.

Ce guide se concentre sur les sauvegardes logiques d'une seule base de données. Pour la reprise après sinistre de tout un cluster, la récupération à un instant précis ou les très grandes installations, associez cela à des sauvegardes physiques et à l'archivage WAL.

Comprendre pg_dump

pg_dump se connecte à une base de données et exporte son schéma et ses données. Il ne vide pas les rôles, les tablespaces ou les objets globaux du cluster ; utilisez pg_dumpall --globals-only lorsque vous en avez besoin.

Formats de sortie

pg_dump prend en charge plusieurs formats de sortie :

Texte brut (-Fp, par défaut) : Écrit du SQL que vous restaurez avec psql. Il est facile à inspecter et à modifier, mais les grandes restaurations peuvent être plus lentes et moins flexibles.
Archive personnalisée (-Fc) : Écrit une archive compressée pour pg_restore. Elle prend en charge la restauration sélective et la restauration parallèle.
Format répertoire (-Fd) : Écrit un répertoire de fichiers. Il prend en charge le vidage parallèle et la restauration parallèle, ce qui est utile pour les grandes bases de données.
Format tar (-Ft) : Écrit une archive tar pour pg_restore. C'est un fichier unique, mais il ne prend pas en charge la compression à l'intérieur du format de la même manière que les archives personnalisées.

Options courantes de pg_dump

Voici quelques-unes des options les plus fréquemment utilisées pour pg_dump :

-h <nom_hôte> : Se connecter à un hôte.
-p <port> : Se connecter à un port.
-U <nom_utilisateur> : Se connecter en tant qu'utilisateur.
-W : Forcer une demande de mot de passe avant la connexion.
-f <nom_fichier> : Écrire dans un fichier ou un répertoire au lieu de la sortie standard.
-F <format> : Choisir p, c, d ou t.
-Z <niveau> : Définir le niveau de compression pour les formats qui prennent en charge la compression.
--exclude-table=<modèle> : Exclure les tables correspondantes.
-j <nombre> ou --jobs=<nombre> : Utiliser des travaux parallèles lors du vidage au format répertoire.
--clean : Inclure les commandes de suppression avant les commandes de création dans la sortie SQL brut.
--create : Inclure les commandes pour créer la base de données elle-même.

Exemple : Sauvegarde de base en texte brut

Pour créer une sauvegarde en texte brut d'une base de données nommée mydatabase appartenant à l'utilisateur myuser et l'enregistrer dans mydatabase_backup.sql :

pg_dump -U myuser -f mydatabase_backup.sql mydatabase

Si vous devez spécifier un hôte et un port :

pg_dump -h localhost -p 5432 -U myuser -f mydatabase_backup.sql mydatabase

Selon votre méthode d'authentification, cela peut demander le mot de passe de myuser.

Exemple : Sauvegarde au format personnalisé (recommandé)

Le format personnalisé est un bon choix par défaut pour de nombreuses sauvegardes de production car il fonctionne avec pg_restore et prend en charge la restauration sélective.

pg_dump -U myuser -Fc -f mydatabase_backup.dump mydatabase

Cela crée mydatabase_backup.dump dans un format adapté à pg_restore.

Exemple : Sauvegarde au format répertoire avec travaux parallèles

Pour les grandes bases de données, le vidage parallèle peut réduire le temps de sauvegarde. Il nécessite le format répertoire :

pg_dump -U myuser -Fd -j 4 -f mydatabase_backup_dir mydatabase

Cette commande crée un répertoire nommé mydatabase_backup_dir contenant plusieurs fichiers, utilisant 4 travaux parallèles.

Comprendre pg_restore

pg_restore restaure les archives personnalisées, de répertoire ou tar créées par pg_dump. Les fichiers SQL bruts sont restaurés avec psql, pas avec pg_restore.

Options courantes de pg_restore

-h <nom_hôte> : Se connecter à un hôte.
-p <port> : Se connecter à un port.
-U <nom_utilisateur> : Se connecter en tant qu'utilisateur.
-W : Forcer une demande de mot de passe avant la connexion.
-d <base_de_données> : Se connecter à cette base de données pour la restauration.
-v : Afficher la sortie détaillée de la restauration.
-c ou --clean : Supprimer les objets de la base de données avant de les recréer.
--create : Créer la base de données vidée avant de la restaurer. Connectez-vous avec -d à une base de données de maintenance existante, généralement postgres.
-j <nombre> : Utiliser des travaux parallèles pour la restauration lorsque c'est pris en charge.
-F <format> : Spécifier le format de l'archive si la détection automatique ne suffit pas.

Restauration à partir de sauvegardes SQL en texte brut

Si vous avez créé une sauvegarde SQL en texte brut avec pg_dump (par exemple, mydatabase_backup.sql), vous la restaurez en utilisant psql :

psql -U myuser -d mydatabase -f mydatabase_backup.sql

Si le fichier de sauvegarde inclut les commandes CREATE DATABASE et \connect de pg_dump --create, connectez-vous à une base de données de maintenance telle que postgres :

psql -U myuser -f mydatabase_backup.sql postgres

Exemple : Restauration à partir d'une sauvegarde au format personnalisé

Pour restaurer la sauvegarde au format personnalisé mydatabase_backup.dump dans une base de données nommée restored_db :

pg_restore -U myuser -d restored_db mydatabase_backup.dump

Si l'archive a été créée avec pg_dump --create, vous pouvez demander à pg_restore de créer la base de données vidée. La valeur de -d doit être une base de données existante utilisée pour la connexion initiale, pas la nouvelle base de données en cours de création :

pg_restore -U myuser --create -d postgres mydatabase_backup.dump

Cela crée la base de données nommée dans le vidage et restaure dedans. Si vous souhaitez restaurer dans une base de données nommée différemment, créez d'abord cette base de données et restaurez sans --create :

createdb -U myuser restored_db
pg_restore -U myuser -d restored_db mydatabase_backup.dump

Exemple : Restauration parallèle

Pour une restauration plus rapide, surtout avec de grands ensembles de données, utilisez l'option -j :

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup.dump

Cela restaure avec 4 travaux de travailleurs. La restauration parallèle est plus utile lorsque le vidage contient plusieurs objets indépendants à charger et à indexer.

Exemple : Restauration à partir d'une sauvegarde au format répertoire

Pour restaurer une sauvegarde créée au format répertoire (par exemple, mydatabase_backup_dir) :

pg_restore -U myuser -d restored_db -j 4 mydatabase_backup_dir

Notez que le dernier argument est le chemin du répertoire.

Meilleures pratiques pour les sauvegardes

Planifiez les sauvegardes : Utilisez cron, les minuteries systemd ou votre plateforme d'orchestration. Choisissez un calendrier en fonction de la quantité de données que vous pouvez vous permettre de perdre.
Utilisez le bon format : Le format personnalisé est un bon choix par défaut. Le format répertoire est meilleur lorsque vous avez besoin d'un vidage parallèle pour une grande base de données.
Testez les restaurations : Restaurez dans une base de données de test selon un calendrier. Une sauvegarde que vous ne testez jamais n'est qu'une supposition.
Capturez les objets globaux : Videz les rôles et les tablespaces séparément avec pg_dumpall --globals-only lors de la reconstruction d'un serveur à partir de zéro.
Stockez des copies hors site : Gardez les sauvegardes loin du serveur principal et protégez-les avec un chiffrement et des contrôles d'accès.
Surveillez les travaux : Alertez en cas d'échec de vidage, de fichiers de sauvegarde anormalement petits et d'horodatages de sauvegarde anciens.
Planifiez la rétention : Décidez combien de sauvegardes horaires, quotidiennes, hebdomadaires et mensuelles vous conservez, puis automatisez le nettoyage.
Soyez prudent avec --clean : Cela supprime les objets dans la base de données cible. Utilisez-le uniquement lorsque vous avez l'intention de remplacer des objets existants.

À retenir

Utilisez pg_dump -Fc pour une sauvegarde flexible d'un seul fichier, pg_dump -Fd -j lorsque vous avez besoin d'un vidage parallèle, psql pour les restaurations SQL brutes et pg_restore pour les restaurations d'archives. Ensuite, prouvez le processus en restaurant dans une base de données de test avant d'en avoir besoin en cas d'urgence.