Comment planifier des sauvegardes automatiques pour PostgreSQL avec pgAgent dans pgAdmin

Introduction

pgAgent est un outil utilisé pour planifier des tâches pour les bases de données PostgreSQL. Il dispose également de capacités de planification plus puissantes que le cron souvent utilisé car il est spécifiquement conçu pour gérer les tâches de Postgres. Par exemple, pgAgent peut planifier plusieurs étapes sans script batch ou sans répéter la commande. Il est important de noter que même si vous avez pgAdmin installé, pgAgent doit être téléchargé indépendamment, spécifiquement en tant qu’extension de pgAdmin.

Dans ce tutoriel, vous installerez pgAgent, configurerez votre base de données pour utiliser pgAgent sur l’interface utilisateur graphique (GUI) de pgAdmin, configurerez pgAgent en tant que démon, puis utiliserez pgAgent pour planifier une tâche qui sauvegardera votre base de données.

Prérequis

Pour suivre ce tutoriel, vous aurez besoin de :

• D’un serveur Ubuntu 22.04 configuré avec un utilisateur non-root sudo activé et un pare-feu. Vous pouvez le faire en lisant notre guide de configuration initiale du serveur
• PostgreSQL est installé sur votre serveur. Vous pouvez le configurer en suivant notre guide sur Comment Installer et Utiliser PostgreSQL sur Ubuntu 22.04. En suivant ce guide, assurez-vous de créer un nouveau rôle et une nouvelle base de données, car vous aurez besoin des deux pour connecter pgAdmin à votre instance PostgreSQL
• pgAdmin est installé avec un compte configuré. Vous pouvez le faire en suivant notre tutoriel sur Comment Installer et Configurer pgAdmin4 en Mode Serveur sur Ubuntu 22.04

Étape 1 — Installation de pgAgent

Comme mentionné précédemment, pgAgent n’est pas configuré automatiquement lors de l’installation de pgAdmin. Vous pouvez installer pgAgent depuis votre terminal en exécutant apt install et le nom du paquet pgagent comme dans la commande suivante :

sudo apt install pgagent

Après avoir installé pgAgent, passez à l’étape suivante pour configurer votre base de données afin d’utiliser pgAgent dans pgAdmin.

Étape 2 — Configuration de votre Base de Données pour pgAgent

Ayant suivi les prérequis, pgAdmin est configuré et prêt à être utilisé. Vous pouvez configurer votre base de données pour utiliser pgAgent via pgAdmin. Ouvrez votre navigateur Web et accédez à l’application pgAdmin à l’adresse http://votre_domaine. Une fois connecté à votre compte, accédez au panneau de contrôle à gauche. Localisez la base de données que vous avez créée appelée sammy et développez la liste. Dans cette liste, il y aura une option appelée Extensions. Une fois que vous l’avez trouvée, faites un clic droit et choisissez l’option Query Tool:

pgAgent nécessite une extension à charger dans votre base de données avant de pouvoir être utilisé dans pgAdmin. Pour ce faire, écrivez la requête suivante et cliquez sur la flèche latérale ▶ signifiant Execute pour exécuter la commande:

CREATE EXTENSION pgagent;

Cette étape avec la fonction Query Tool est représentée dans l’exemple suivant:

Sous l’onglet Messages, il y aura une sortie qui retourne Query returned successfully in 300 msec. Cela confirme que l’extension pgAgent a été créée avec succès.

Output
ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
SQL state: 42704

CREATE LANGUAGE plpgsql;

Après avoir exécuté ces requêtes, sous Extensions, il y aura deux éléments répertoriés pour pgagent et plpgsql:

A new item in the tree control on the left-hand side will appear called pgAgent Jobs. This signifies that pgAgent was successfully installed on your pgAdmin account. Next, you will set up pgAgent as a daemon so that it can run your jobs successfully.

Étape 3 — Configuration de pgAgent en tant que démon

Maintenant que vous avez installé pgAgent et configuré pour votre base de données via pgAdmin, vous devez maintenant configurer pgAgent en tant que démon sur votre serveur. Un démon est un programme qui s’exécute en continu en arrière-plan et exécute des tâches de service. pgAgent fonctionne en tant que démon sur les systèmes Unix et généralement sur le serveur de base de données lui-même.

Pour configurer pgAgent en tant que démon, vous avez besoin d’une chaîne de connexion PostgreSQL afin que lorsque vous planifiez votre tâche, le processus s’exécute correctement. Dans ce cas, vous configurez une chaîne de connexion libpq PostgreSQL, ce qui signifie une chaîne spécifique à l’utilisateur et se connecte à certains paramètres que vous définissez. Vous pouvez en savoir plus sur les fonctions de connexion libpq PostgreSQL dans la documentation PostgreSQL.

Votre chaîne de connexion fournira les informations d’identification de votre hôte, le nom de la base de données et le nom d’utilisateur. Dans notre exemple, l’hôte utilisera un socket de domaine Unix, le nom de la base de données est sammy et l’utilisateur est sammy. Cette chaîne sera ajoutée à une commande pgagent pour démarrer le démon. Dans votre terminal, exécutez le code suivant :

pgagent host=/var/run/postgresql dbname=sammy user=sammy

Si rien n’apparaît dans votre sortie et que vous ne recevez pas de message d’erreur de connexion, la configuration de la chaîne de connexion a réussi.

Une fois que vous avez créé la chaîne de connexion, vous êtes prêt à planifier une tâche avec pgAgent.

Étape 4 — Planification d’une tâche avec pgAgent

pgAgent sert d’agent de planification capable d’exécuter et de gérer des tâches, et peut créer des tâches d’une ou plusieurs étapes ou horaires. Par exemple, une étape peut consister en plusieurs instructions SQL dans un script shell et est exécutée consécutivement après les autres. Dans l’ensemble, vous pouvez utiliser pgAgent pour planifier, gérer, modifier ou désactiver vos tâches.

Pour les besoins de ce tutoriel, vous utiliserez pgAgent pour créer une tâche qui sauvegardera votre base de données sammy toutes les minutes chaque jour de la semaine. Vous pouvez commencer en faisant un clic droit sur pgAgent Jobs et en sélectionnant Create puis pgAgent Job… comme suit :

Une fois que vous avez fait cela, une invite intitulée Créer – pgAgent Job apparaîtra, et vous pourrez commencer à remplir les informations nécessaires dans l’onglet Général. Dans cet exemple, nous utiliserons le nom sammy_backup et ne spécifierons pas un Agent hôte car nous voulons pouvoir exécuter cette tâche sur n’importe quel hôte. De plus, nous laisserons la Classe de tâche comme Maintenance ordinaire. Si vous souhaitez inclure d’autres commentaires, n’hésitez pas à le faire dans la section Commentaire:

Ensuite, accédez à l’onglet Étapes. Cliquez sur le symbole + dans le coin supérieur droit pour créer une étape. Dans cet exemple, nous nommerons cette étape étape1. Ensuite, pour élargir vos options, cliquez sur le crayon à gauche de l’icône de corbeille. Le bouton Activé? est activé par défaut, ce qui signifie que cette étape sera incluse lorsque cette tâche sera exécutée.

Pour l’option Kind, vous pouvez sélectionner soit SQL soit Batch, ici nous avons choisi Batch. La raison pour laquelle vous voulez choisir Batch dans cet exemple est parce que c’est ce qui exécutera les commandes PostgreSQL appropriées que vous définirez pour les sauvegardes que vous souhaitez planifier pour votre base de données. L’option SQL est disponible pour planifier une tâche pour exécuter du SQL brut. Dans ce cas, nous avons sélectionné Local pour le Type de connexion afin que l’étape soit exécutée sur le serveur local, mais si vous préférez, vous pouvez également choisir Remote pour un hôte distant de votre choix. Si vous préférez le faire sur un hôte distant, vous devez spécifier ce critère dans le champ Chaîne de connexion. Si vous avez suivi Étape 1, votre chaîne de connexion est déjà configurée et connectée.

Pour le champ Base de données, assurez-vous d’avoir sélectionné la base de données correcte, ici nous avons spécifié sammy. Avec l’option En cas d’erreur, vous pouvez personnaliser la réponse de pgAgent s’il y a une erreur lors de l’exécution d’une étape. Dans ce cas, nous avons sélectionné Échec pour nous notifier s’il y a une erreur lorsqu’une étape est en cours de traitement. Encore une fois, si vous souhaitez ajouter des notes supplémentaires, vous pouvez les ajouter dans la zone de Commentaire.

Dans l’onglet Étapes, il y a aussi un onglet Code. Si vous avez sélectionné Batch comme nous l’avons fait dans cet exemple, accédez à cet onglet Code. Une fois dans cet onglet, il y a une ligne vide pour insérer votre commande PostgreSQL. Vous pouvez substituer votre propre commande de sauvegarde ici avec votre ensemble personnalisé d’options. Toute commande valide est acceptable.

Ce tutoriel utilisera la commande pg_dump pour sauvegarder votre base de données Postgres sammy. Dans cette commande, incluez votre nom d’utilisateur spécifique, le nom de la base de données et le drapeau --clean, qui aide avec pg_dump en supprimant ou « nettoyant » les objets de base de données avant de produire les commandes qui sont créées. Pour le drapeau --file, vous spécifiez l’emplacement exact où les fichiers de sauvegarde seront enregistrés. La dernière partie de cette déclaration date +%Y-%m-%d-%H-%M-%S est pour générer dynamiquement une date et plusieurs fichiers pour chaque sauvegarde. Sinon, le fichier de sauvegarde remplacera constamment et sauvegardera sur celui existant. De cette façon, vous pouvez suivre chacun de vos fichiers de sauvegarde pour une heure ou une date spécifiée que vous avez programmée. Votre commande complète sera la suivante :

pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql

Une fois que vous avez ajouté votre commande de sauvegarde, vous pouvez naviguer vers l’onglet intitulé Programmes. Tout comme lors de la configuration des Étapes, cliquez sur le symbole + pour ajouter un programme, puis fournissez le nom de votre choix, et cliquez sur l’icône de crayon à côté de l’icône de la corbeille pour développer vos options. Sous l’onglet Général, il y aura le Nom que vous avez écrit, dans cet exemple, c’est programme1. Encore une fois, pour Activé, cela est par défaut sur la position activée pour garantir que le programme est exécuté correctement. Pour les options Début et Fin, spécifiez le jour et l’heure de début et de fin pour votre tâche programmée. Comme vous allez tester votre tâche programmée, assurez-vous que l’heure actuelle se situe dans la plage de Début et Fin. Ajoutez une note dans Commentaire si vous le souhaitez :

Bien, veuillez procéder à l’onglet Répéter maintenant. Ici, vous pouvez personnaliser la fréquence d’exécution de cette tâche planifiée. Vous pouvez être aussi précis que possible avec la semaine, le mois, la date, les heures ou les minutes. Veuillez noter que si vous ne faites pas de sélection, c’est la même chose que de choisir Tout Sélectionner. Par conséquent, si vous laissez les Jours de la semaine vides, votre programme tiendra compte de tous les jours de la semaine. De même, avec les Heures, vous pouvez laisser les heures ou les minutes vides, et c’est la même chose que de Tout Sélectionner. Gardez à l’esprit que les heures sont au format cron, donc, pour cet exemple, pour générer une sauvegarde toutes les minutes, vous devez sélectionner chaque minute dans une heure (00 à 59). Pour illustrer cela, nous avons choisi Tout Sélectionner pour les minutes. Toutes les minutes sont répertoriées, mais les laisser vides donnera également les mêmes résultats :

. Si vous ne souhaitez pas exécuter une tâche à certains jours ou heures, vous pouvez créer un horaire plus détaillé, ou vous pouvez le définir en accédant à l’onglet Exceptions :

Lorsque vous avez terminé de configurer et de personnaliser l’horaire que vous souhaitez exécuter, appuyez sur le bouton Enregistrer. Un nouveau job pgAgent apparaîtra dans l’arborescence du côté gauche avec le nom de votre job. Pour cet exemple, sammy_backup apparaît avec les Programmes et Étapes répertoriées en dessous :

Maintenant que vous avez créé avec succès un job pgAgent, à l’étape suivante, vous apprendrez comment vérifier si votre job pgAgent s’exécute avec succès.

Étape 5 — Vérification de votre job pgAgent

Vous pouvez vérifier si votre tâche planifiée pour créer un fichier de sauvegarde de votre base de données toutes les minutes fonctionne de plusieurs manières. Dans pgAdmin, vous pouvez naviguer jusqu’à l’arborescence du côté gauche et cliquer sur sammy_backup. Ensuite, accédez à l’onglet intitulé Statistiques. La page des Statistiques répertoriera chaque instance de votre tâche planifiée comme suit :

Veuillez noter que les statistiques peuvent ne pas apparaître ou se rafraîchir immédiatement, vous devrez peut-être naviguer ailleurs ou actualiser le navigateur. N’oubliez pas que votre tâche est programmée pour s’exécuter à des intervalles définis, gardez cela à l’esprit si vous définissez une date ou une heure périodique ou d’une durée plus longue.

Si vous préférez vérifier que votre travail fonctionne depuis la ligne de commande, vous pouvez exécuter la commande ls ~ pour répertorier les fichiers de votre répertoire personnel. Dans ce tutoriel, vous configurez l’emplacement exact vers ce répertoire personnel lors de l’écriture de la commande pg_dump dans l’étape 4 : Étape 4 :

ls ~

La sortie répertoriera chaque instance des fichiers de sauvegarde enregistrés.

Étape 6 — Modification, Désactivation, Suppression et Exécution Manuelle des Tâches pgAgent (Optionnel)

pgAgent offre une flexibilité en ce qui concerne la modification ou la désactivation d’une tâche. En utilisant pgAdmin, vous pouvez accéder à la tâche pgAgent spécifique, dans ce cas, sammy_backup. Ensuite, faites un clic droit et choisissez l’option Propriétés dans la liste. À partir de là, vous pouvez appuyer sur l’icône du crayon en haut pour apporter des modifications à votre tâche :

Si vous êtes dans Propriétés et que vous appuyez sur l’icône du crayon, vous pouvez accéder à l’onglet Calendriers pour désactiver votre tâche comme dans l’exemple suivant :

De plus, à partir du contrôle de l’arborescence situé du côté gauche, si vous faites un clic droit sur la tâche pgAgent, vous pouvez sélectionner la sélection DELETE/DROP pour supprimer complètement la tâche. Si vous souhaitez exécuter une tâche manuellement, vous pouvez répéter la même étape, mais cette fois, choisissez la sélection RUN NOW dans la liste :

Bien que ce ne soient que quelques fonctions pour utiliser pgAgent afin de planifier des tâches, il existe de nombreuses autres possibilités. Par exemple, vous pouvez planifier une tâche qui crée une sauvegarde pour tous les utilisateurs et rôles de votre base de données, en utilisant spécifiquement la commande pg_dumpall --globals only. Vous pouvez même utiliser des scripts plus complexes avec des tâches de lot, dont vous pouvez en apprendre davantage sur la page wiki de PostgreSQL.

Conclusion

pgAgent est utile pour planifier diverses tâches qui peuvent aider à réduire certaines de vos tâches quotidiennes plus banales mais nécessaires, telles que la sauvegarde régulière de votre base de données. Une fois que vous êtes plus à l’aise avec cet outil, vous pouvez même essayer de créer une tâche à plusieurs étapes ou des tâches SQL qui nécessitent une planification complexe. Si vous souhaitez en savoir plus, vous pouvez lire la documentation officielle de pgAgent sur le site web de pgAdmin.