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

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

Dans le monde de la gestion de bases de données, des stratégies robustes de sauvegarde et de restauration ne sont pas seulement recommandées ; elles sont absolument critiques. La perte de données peut provenir de diverses sources, notamment des pannes matérielles, des erreurs humaines ou des attaques malveillantes. PostgreSQL, une base de données relationnelle open-source puissante, fournit des utilitaires en ligne de commande essentiels, pg_dump et pg_restore, pour assurer l'intégrité et la récupérabilité de vos données. Ce tutoriel vous guidera à travers les meilleures pratiques pour utiliser ces outils afin de créer des sauvegardes fiables et d'effectuer des restaurations sécurisées de vos bases de données PostgreSQL.

Comprendre ces utilitaires est fondamental pour tout administrateur ou développeur PostgreSQL responsable de la protection des données. pg_dump est utilisé pour extraire une base de données PostgreSQL dans un fichier de script ou un autre fichier d'archive, tandis que pg_restore peut interpréter de tels fichiers pour reconstruire la base de données. Les maîtriser vous permet de protéger vos informations précieuses et de maintenir la continuité de votre activité.

Comprendre pg_dump

pg_dump est un utilitaire permettant de créer des sauvegardes d'une base de données PostgreSQL. Il génère un fichier contenant des commandes SQL qui peuvent être utilisées pour recréer les objets de la base de données (tables, fonctions, index, etc.) et les remplir avec des données. pg_dump fonctionne en se connectant à la base de données, puis en générant la sortie. Il peut générer une sortie dans plusieurs formats, chacun ayant ses propres avantages.

Formats de sortie

pg_dump prend en charge plusieurs formats de sortie :

• Script SQL en texte brut (-Fp ou par défaut) : C'est le format le plus simple. Il génère un fichier contenant une série de commandes SQL. Ce format est lisible par l'homme et facilement scriptable, mais il peut être volumineux et plus lent à restaurer pour de très grandes bases de données.
• Archive personnalisée (-Fc) : Ce format crée un fichier d'archive compressé et personnalisé. C'est généralement le format recommandé pour les sauvegardes car il est compressé par défaut, prend en charge la restauration parallèle (pg_restore) et est généralement plus flexible.
• Format de répertoire (-Fd) : Cela crée un répertoire contenant plusieurs fichiers, un pour chaque table et autres objets. Il prend également en charge la restauration parallèle et est utile pour les très grandes bases de données.
• Format Tar (-Ft) : Cela crée une archive tar. Il est similaire au format de répertoire mais est un fichier unique. Il est utile pour la compatibilité avec d'autres outils qui gèrent les archives tar.

Options courantes de pg_dump

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

• -h <hostname> : Spécifie le nom d'hôte de la machine sur laquelle le serveur est en cours d'exécution.
• -p <port> : Spécifie le port TCP sur lequel le serveur écoute les connexions.
• -U <username> : Se connecter en tant qu'utilisateur spécifié.
• -W : Force pg_dump à demander un mot de passe avant de se connecter.
• -f <filename> : Spécifie le nom du fichier de sortie. S'il est omis, pg_dump écrit sur la sortie standard.
• -F <format> : Spécifie le format du fichier de sortie (p, c, d, t).
• -Z <0-9> : Spécifie le niveau de compression pour les formats compressés (par exemple, Fc).
• --exclude-table=<table_name> : Exclut la table spécifiée du dump.
• --jobs=<number> : Nombre de jobs parallèles à utiliser lors du dump (uniquement pour le format de répertoire).
• --clean : Inclut des commandes pour supprimer les objets de la base de données avant de les créer. Ceci est utile pour les restaurations complètes afin d'assurer un état propre.
• --create : Inclut une commande pour créer la base de données elle-même. Utile lors de la restauration vers un nouveau serveur.

Exemple : Sauvegarde basique 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 la sauvegarder 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

Cette commande demandera le mot de passe de myuser.

Exemple : Sauvegarde au format personnalisé (recommandé)

L'utilisation du format d'archive personnalisé est généralement préférée pour sa flexibilité et sa compression. Pour créer une sauvegarde personnalisée compressée de mydatabase :

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

Cela créera un fichier nommé mydatabase_backup.dump qui est compressé et dans un format adapté à pg_restore.

Exemple : Sauvegarde au format de répertoire avec des jobs parallèles

Pour les très grandes bases de données, le dump parallèle peut accélérer considérablement le processus de sauvegarde. Cela nécessite le format de répertoire.

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

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

Comprendre pg_restore

pg_restore est un utilitaire permettant de restaurer une base de données PostgreSQL à partir d'une archive créée par pg_dump dans l'un des formats personnalisé, répertoire ou tar. Il n'est pas utilisé pour les dumps SQL en texte brut (qui sont restaurés à l'aide de la commande psql).

Options courantes de pg_restore

• -h <hostname> : Spécifie le nom d'hôte de la machine sur laquelle le serveur est en cours d'exécution.
• -p <port> : Spécifie le port TCP sur lequel le serveur écoute les connexions.
• -U <username> : Se connecter en tant qu'utilisateur spécifié.
• -W : Force pg_restore à demander un mot de passe avant de se connecter.
• -d <database> : Spécifie le nom de la base de données à restaurer. Ceci est obligatoire.
• -v : Mode verbeux.
• -c ou --clean : Nettoyer (supprimer) les objets de la base de données avant de les recréer.
• --create : Créer la base de données avant d'y restaurer. Nécessite de se connecter initialement à une base de données différente (par exemple, postgres).
• -j <number> : Nombre de jobs parallèles à utiliser pour la restauration. Cela accélère considérablement la restauration, en particulier pour les formats personnalisés et de répertoire.
• -F <format> : Spécifie le format de l'archive (c, d, t). Généralement, pg_restore peut le détecter automatiquement.

Restauration à partir de sauvegardes SQL en texte brut

Si vous avez créé une sauvegarde SQL en texte brut à l'aide de pg_dump (par exemple, mydatabase_backup.sql), vous la restaurez à l'aide de psql :

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

Si le fichier de sauvegarde inclut des commandes CREATE DATABASE et \connect, vous pourriez avoir besoin de vous connecter à une base de données différente (comme postgres) et d'omettre l'argument -d mydatabase :

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 la base de données cible restored_db n'existe pas, vous pouvez utiliser l'option --create. Dans ce cas, vous devez vous connecter à une base de données différente (comme postgres), car la base de données cible sera créée.

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

Cette commande créera la base de données restored_db, puis y restaurera les données.

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 tentera de restaurer la base de données en utilisant 4 jobs parallèles.

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

Pour restaurer une sauvegarde créée au format de 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.

Bonnes pratiques pour les sauvegardes

• Planification régulière : Automatisez vos sauvegardes à l'aide de cron (Linux/macOS) ou du Planificateur de tâches (Windows) pour qu'elles s'exécutent à intervalles réguliers (par exemple, quotidiennement, toutes les heures).
• Choisir le bon format : Pour la plupart des cas d'utilisation, le format personnalisé (-Fc) est recommandé en raison de sa compression et de sa compatibilité avec les fonctionnalités parallèles de pg_restore. Utilisez le format de répertoire (-Fd) pour les bases de données extrêmement volumineuses où le dump/restauration parallèle est crucial.
• Testez vos sauvegardes : Une sauvegarde n'est utile que si elle peut être restaurée. Testez régulièrement votre processus de restauration en restaurant vers un environnement de staging ou de développement pour assurer l'intégrité des données et la validité de vos fichiers de sauvegarde.
• Stockez les sauvegardes hors site : Conservez des copies de vos sauvegardes dans un emplacement physique distinct de votre serveur de base de données principal pour vous protéger contre les catastrophes à l'échelle du site.
• Surveillez les tâches de sauvegarde : Mettez en œuvre une surveillance pour vous assurer que vos tâches de sauvegarde planifiées s'exécutent avec succès et pour vous alerter en cas d'échec.
• Envisagez la compression : Utilisez les options de compression de pg_dump ou dirigez la sortie vers des outils de compression externes (gzip, zstd) si vous n'utilisez pas de formats qui compressent automatiquement.
• Utilisez --clean et --create avec discernement : Lors de l'exécution d'une restauration complète vers un environnement existant, --clean peut être très utile. Lors du déplacement vers un nouvel environnement ou pour assurer un état complètement neuf, --create est essentiel. Soyez prudent avec ces options sur les systèmes de production.
• Gestion des versions des sauvegardes : Mettez en œuvre une stratégie de gestion de la rétention des sauvegardes, y compris la durée de conservation des sauvegardes et la manière dont les anciennes sont purgées.

Conclusion

pg_dump et pg_restore sont des outils indispensables pour protéger vos données PostgreSQL. En comprenant leurs diverses options, formats de sortie, et en mettant en œuvre une stratégie de sauvegarde et de restauration cohérente avec des tests réguliers, vous pouvez considérablement atténuer le risque de perte de données et assurer la résilience de vos systèmes de bases de données. Faites de ces utilitaires un pilier de vos pratiques d'administration de bases de données.