L'extension pg_cron pour PostgreSQL permet de planifier l'exécution automatique d'instructions SQL à des heures précises ou selon des intervalles définis, en utilisant la syntaxe cron standard. La planification s'effectue entièrement via des fonctions SQL, sans scripts externes ni fichiers de configuration.
Prérequis
Avant de commencer, assurez-vous que votre cluster respecte les exigences de version suivantes :
PolarDB for PostgreSQL (Compatible with Oracle) 2.0 — version de révision 2.0.14.1.0 ou ultérieure
PolarDB for PostgreSQL (Compatible with Oracle) 1.0 — version de révision 1.1.1 ou ultérieure
Pour vérifier la version de révision de votre cluster, exécutez la commande suivante :
SHOW polar_version;
Remarques d'utilisation
Si vous redémarrez un cluster exécutant une version mineure du moteur 2.0.14.16.0 ou antérieure, le port du cluster change et les tâches planifiées pg_cron existantes risquent d'échouer. Ce problème est corrigé dans la version mineure du moteur 2.0.14.16.1 et les versions ultérieures.
Fonctionnement
Maintenance des informations des tâches planifiées
La table cron.job stocke toutes les tâches planifiées. Au démarrage de la base de données, pg_cron construit deux listes internes, JOB LIST et TASK LIST, à partir de cette table. Le déclencheur cron.job_cache_invalidate synchronise ces listes avec cron.job en temps réel lors de l'ajout, de la modification ou de la suppression de tâches.
Exécution des tâches planifiées
Chaque tâche planifiée passe par les états suivants :
| État | Description |
|---|---|
| WAITING | État par défaut. Le planificateur ignore la tâche si les conditions ne sont pas remplies (tâche inactive ou heure planifiée non atteinte). Lorsque les conditions sont remplies, la tâche passe à l'état START. |
| START | Établit les informations de connexion et effectue un test de connexion. Passe à CONNECTING en cas de succès, ou à ERROR en cas d'échec. |
| CONNECTING | Vérifie si la tâche est active et si la connexion est valide. Passe à SENDING en cas de succès, ou à ERROR en cas d'échec. |
| SENDING | Envoie la tâche au serveur PolarDB for PostgreSQL (Compatible with Oracle). Passe à RUNNING en cas de succès, ou à ERROR en cas d'échec. |
| RUNNING | Reçoit le résultat de la tâche. Passe à DONE en cas de succès, ou à ERROR si la connexion est interrompue. |
| ERROR | La tâche a échoué. Réinitialise les informations de connexion et passe à DONE. |
| DONE | Tâche terminée. Réinitialise les informations de la tâche et retourne à WAITING. |
Fonctions
Seuls les comptes privilégiés peuvent appeler les fonctions suivantes pour créer ou modifier des entrées danscron.job. Les comptes standard disposent d'un accès en lecture seule àcron.job.
La base de données postgres stocke toutes les tâches planifiées. Connectez-vous à postgres pour gérer vos tâches planifiées.
Les tâches planifiées s'exécutent selon le fuseau horaire GMT (Greenwich Mean Time). Convertissez votre heure locale en GMT avant de définir une planification.
cron.schedule
Crée une tâche planifiée.
cron.schedule(
job_name TEXT,
schedule TEXT,
command TEXT
)
| Paramètre | Description |
|---|---|
job_name |
Nom de la tâche. Facultatif : laissez ce champ vide pour l'omettre. |
schedule |
Planification au format d'expression cron standard. |
command |
Instruction SQL à exécuter. |
cron.schedule_in_database
Crée une tâche planifiée dans une base de données spécifique.
cron.schedule_in_database(
job_name TEXT,
schedule TEXT,
command TEXT,
db_name TEXT
)
| Paramètre | Description |
|---|---|
job_name |
Nom de la tâche. |
schedule |
Planification au format d'expression cron standard. |
command |
Instruction SQL à exécuter. |
db_name |
Base de données dans laquelle la tâche s'exécute. |
cron.alter_job
Modifie une tâche planifiée existante. Passez la valeur NULL pour tout paramètre que vous ne souhaitez pas modifier.
cron.alter_job(
job_id BIGINT,
schedule TEXT DEFAULT NULL,
command TEXT DEFAULT NULL,
db_name TEXT DEFAULT NULL,
active BOOLEAN DEFAULT NULL
)
| Paramètre | Description |
|---|---|
job_id |
ID de la tâche à modifier. |
schedule |
Nouvelle expression cron. |
command |
Nouvelle instruction SQL. |
db_name |
Base de données dans laquelle la tâche s'exécutera après la modification. |
active |
true pour activer la tâche ; false pour la désactiver. |
cron.unschedule
Supprime une tâche planifiée par ID ou par nom.
cron.unschedule(job_id BIGINT);
cron.unschedule(job_name TEXT);
| Paramètre | Description |
|---|---|
job_id |
ID de la tâche à supprimer. |
job_name |
Nom de la tâche à supprimer. |
Gestion des tâches planifiées
Les opérations suivantes nécessitent un compte privilégié.
Création de l'extension pg_cron
Connectez-vous à la base de données et exécutez la commande suivante :
CREATE EXTENSION pg_cron;
Création d'une tâche planifiée
L'exemple suivant crée une tâche nommée task1 qui exécute SELECT 1 chaque minute dans la base de données db01. La fonction renvoie l'ID de la tâche.
SELECT cron.schedule_in_database('task1', '* * * * *', 'SELECT 1', 'db01');
Résultat :
schedule_in_database
----------------------
1
(1 row)
Consultation des tâches planifiées
SELECT * FROM cron.job;
Résultat :
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-----------+----------+----------+----------+----------+----------+--------+---------
1 | * * * * * | SELECT 1 | /tmp | 39361 | db01 | u1 | t | task1
(1 row)
Consultation de l'historique d'exécution des tâches
Pour consulter tous les enregistrements d'exécution :
SELECT * FROM cron.job_run_details;
Résultat :
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 4152316 | db01 | u1 | SELECT 1 | succeeded | 1 row | 2023-10-19 03:55:00.020442+00 | 2023-10-19 03:55:00.021512+00
1 | 2 | 4152438 | db01 | u1 | SELECT 1 | succeeded | 1 row | 2023-10-19 03:56:00.006468+00 | 2023-10-19 03:56:00.006822+00
(2 rows)
Pour consulter uniquement les exécutions ayant échoué :
SELECT * FROM cron.job_run_details WHERE status = 'failed';
Suppression d'une tâche planifiée
SELECT cron.unschedule('task1');
Résultat :
unschedule
------------
t
(1 row)
Suppression de l'extension pg_cron
DROP EXTENSION pg_cron;
Référence de la syntaxe cron
pg_cron utilise la syntaxe cron standard :
┌───────────── Minute (0–59)
│ ┌────────────── Hour (0–23)
│ │ ┌─────────────── Day of month (1–31)
│ │ │ ┌──────────────── Month (1–12)
│ │ │ │ ┌───────────────── Day of week (0–6, where 0 and 7 = Sunday)
│ │ │ │ │
* * * * *
Caractères spéciaux
| Caractère | Signification | Exemple |
|---|---|---|
* |
Toutes les valeurs du champ | * * * * * — chaque minute |
, |
Liste de valeurs spécifiques | 1,3,5 pour le jour de la semaine — lundi, mercredi, vendredi |
- |
Plage de valeurs | 10-12 pour l'heure — 10 h, 11 h et 12 h |
/ |
Valeurs de pas | */15 pour les minutes — toutes les 15 minutes |
Exemples de planification
Les exemples suivants couvrent des tâches courantes d'administration de bases de données. Toutes les heures sont exprimées en GMT.
Supprimer les données expirées chaque samedi à 03:30 :
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);
Exécuter une tâche tous les jours à 10:00 :
SELECT cron.schedule('0 10 * * *', 'VACUUM;');
Exécuter une tâche chaque minute :
SELECT cron.schedule('* * * * *', 'SELECT 1;');
Exécuter une tâche à la 23e minute de chaque heure :
SELECT cron.schedule('23 * * * *', 'SELECT 1;');
Exécuter une tâche le 4e jour de chaque mois :
SELECT cron.schedule('* * 4 * *', 'SELECT 1;');