Comment sauvegarder et restaurer une base de données PostgreSQL
Dans un environnement de production, quelle que soit la taille de votre base de données PostgreSQL, une sauvegarde régulière est un aspect essentiel de la gestion de la base de données. Dans cet article, vous apprendrez comment sauvegarder et restaurer une base de données PostgreSQL.
Nous supposons que vous disposez déjà d’une installation fonctionnelle du système de base de données PostgreSQL. Sinon, lisez nos articles suivants pour installer PostgreSQL sur votre distribution Linux.
Commençons…
Sauvegarder une seule base de données PostgreSQL
PostgreSQL fournit l'utilitaire pg_dump pour vous aider à sauvegarder les bases de données. Il génère un fichier de base de données avec des commandes SQL dans un format qui pourra être facilement restauré ultérieurement.
Pour sauvegarder une base de données PostgreSQL, commencez par vous connecter à votre serveur de base de données, puis passez au compte utilisateur Postgres et exécutez pg_dump comme suit (remplacez tecmintdb par le nom de la base de données que vous souhaitez sauvegarder). Par défaut, le format de sortie est un fichier de script SQL en texte brut.
pg_dump tecmintdb > tecmintdb.sql
Le pg_dump prend également en charge d'autres formats de sortie. Vous pouvez spécifier le format de sortie à l'aide de l'option -F, où c signifie fichier d'archive au format personnalisé, d signifie archive au format de répertoire et t signifie fichier d'archive au format tar : tous les formats peuvent être saisis dans pg_restore.
Par exemple:
pg_dump -F c tecmintdb > tecmintdb.dump
OR
pg_dump -F t tecmintdb > tecmintdb.tar
Pour vider la sortie au format de sortie du répertoire, utilisez l'indicateur -f (qui est utilisé pour spécifier le fichier de sortie) pour spécifier le répertoire cible au lieu d'un fichier. Le répertoire qui sera créé par pg_dump ne doit pas exister.
pg_dump -F d tecmintdb -f tecmintdumpdir
Pour sauvegarder toutes les bases de données PostgreSQL, utilisez l'outil pg_dumpall comme indiqué.
pg_dumpall > all_pg_dbs.sql
Vous pouvez restaurer le dump en utilisant psql comme indiqué.
psql -f all_pg_dbs.sql postgres
Restauration d'une base de données PostgreSQL
Pour restaurer une base de données PostgreSQL, vous pouvez utiliser les utilitaires psql ou pg_restore. psql est utilisé pour restaurer des fichiers texte créés par pg_dump tandis que pg_restore est utilisé pour restaurer une base de données PostgreSQL à partir d'une archive créée par pg_dump< dans l'un des formats autres que du texte brut (personnalisé, tar ou répertoire).
Voici un exemple de restauration d'un dump de fichier texte brut :
psql tecmintdb < tecmintdb.sql
Comme mentionné ci-dessus, un dump au format personnalisé n'est pas un script pour psql, il doit donc être restauré avec pg_restore comme indiqué.
pg_restore -d tecmintdb tecmintdb.dump
OR
pg_restore -d tecmintdb tecmintdb.tar
OR
pg_restore -d tecmintdb tecmintdumpdir
Sauvegarde de base de données PostgreSQL compressée
Si la base de données que vous sauvegardez est volumineuse et que vous souhaitez générer un fichier de sortie assez petit, vous pouvez alors exécuter un dump compressé dans lequel vous devez filtrer la sortie de pg_dump via un outil de compression tel que gzip. ou l'un de vos favoris :
pg_dump tecmintdb | gzip > tecmintdb.gz
Si la base de données est extrêmement volumineuse, vous pouvez effectuer un dump en parallèle en dumpant simultanément les tables number_of_jobs à l'aide de l'indicateur -j, comme indiqué.
pg_dump -F d -j 5 -f tecmintdumpdir
Il est important de noter que l'option de vidage parallèle réduit le temps de vidage, mais d'un autre côté, elle augmente également la charge sur le serveur de base de données.
Sauvegarder les bases de données PostgreSQL distantes
pg_dump est un outil client PostgreSQL standard, il prend en charge les opérations sur des serveurs de bases de données distants. Pour spécifier le serveur de base de données distant que pg_dump doit contacter, utilisez les options de ligne de commande -h pour spécifier l'hôte distant et -p spécifie l'hôte distant. port sur lequel le serveur de base de données écoute. De plus, utilisez l'indicateur -U pour spécifier le nom du rôle de base de données sous lequel vous connecter.
N'oubliez pas de remplacer 10.10.20.10 et 5432 et tecmintdb par l'adresse IP ou le nom d'hôte de votre hôte distant, le port de la base de données et le nom de la base de données, respectivement.
pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql
Assurez-vous que l'utilisateur qui se connecte à distance dispose des privilèges requis pour accéder à la base de données et que la méthode d'authentification de base de données appropriée est configurée sur le serveur de base de données, sinon vous obtiendrez une erreur comme celle affichée dans la capture d'écran suivante.
Il est également possible de vider une base de données directement d'un serveur à un autre, utilisez les utilitaires pg_dump et psql comme indiqué.
pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdb
Sauvegarde automatique de la base de données PostgreSQL à l'aide d'une tâche Cron
Vous pouvez effectuer des sauvegardes à intervalles réguliers à l'aide de tâches cron. Les tâches Cron sont un moyen couramment utilisé pour planifier différents types de tâches à exécuter sur un serveur.
Vous pouvez configurer une tâche cron pour automatiser la sauvegarde de la base de données PostgreSQL comme suit. Notez que vous devez exécuter les commandes suivantes en tant que superutilisateur PostgreSQL :
mkdir -p /srv/backups/databases
Ensuite, exécutez la commande suivante pour modifier la crontab afin d'ajouter une nouvelle tâche cron.
crontab -e
Copiez et collez la ligne suivante à la fin de la crontab. Vous pouvez utiliser n'importe lequel des formats de vidage expliqués ci-dessus.
0 0 * * * pg_dump -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql
Enregistrez le fichier et quittez.
Le service cron commencera automatiquement à exécuter ce nouveau travail sans redémarrage. Et cette tâche cron s'exécutera tous les jours à minuit, c'est une solution minimale à la tâche de sauvegarde.
Pour plus d'informations sur la planification des tâches cron, consultez : Comment créer et gérer des tâches cron sous Linux
C'est tout pour le moment! C'est une bonne idée d'intégrer la sauvegarde des données à votre routine de gestion de base de données. Pour nous contacter pour toute question ou commentaire, utilisez le formulaire de commentaires ci-dessous. Pour plus d'informations, consultez les pages de référence pg_dump et pg_restore.