Sauvegarde et restauration d’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 des bases de données. Dans cet article, vous allez apprendre à 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. Si ce n’est pas le cas, lisez nos articles suivants pour installer PostgreSQL sur votre distribution Linux.

Commençons...

Sauvegarde d’une seule base de données PostgreSQL

PostgreSQL fournit l’utilitaire pg_dump pour vous aider à sauvegarder des bases de données. Il génère un fichier de base de données avec des commandes SQL dans un format qui peut être facilement restauré à l’avenir.

Pour sauvegarder une base de données PostgreSQL, commencez par vous connecter à votre serveur de base de données, puis basculez vers le 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 sont adaptés à la saisie dans pg_restore.

Par exemple:

$ pg_dump -F c tecmintdb > tecmintdb.dump
OR
pg_dump -F t tecmintdb > tecmintdb.tar

Pour vider la sortie dans le 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 vidage à l’aide de 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 non texte brut (personnalisé, tar ou répertoire).

Voici un exemple de restauration d’un vidage de fichier en 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 compressée de la base de données PostgreSQL

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 exécuter un dump compressé où 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 vidage en parallèle en vidant number_of_jobs tables simultanément à l’aide de l’indicateur -j, comme illustré.

$ pg_dump -F d -j 5 -f tecmintdumpdir

Il est important de noter que l’option de vidage parallèle réduit la durée du vidage, mais d’un autre côté, elle augmente également la charge sur le serveur de base de données.

Sauvegarde des bases de données PostgreSQL distantes

pg_dump s’agit d’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 devez contacter, utilisez les options de ligne de commande -h pour spécifier l’hôte distant et -p pour spécifier le port distant sur lequel le serveur de base de données écoute. En outre, utilisez l’indicateur -U pour spécifier le nom du rôle de base de données sous lequel se 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 similaire à celle illustrée dans la capture d’écran suivante.

Il est également possible de vider une base de données directement d’un serveur à un autre, en utilisant 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 divers 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 le 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 l’un 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 cette nouvelle tâche sans redémarrage. Et ce job 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