PostgreSQL : planifier une tâche avec pg_cron

Hello

nous avons vu auparavant, dans un précédent article comment migrer une base Oracle vers PostgreSQL via un outil nommé ora2pg.
Au cours de cette migration, nous avons réalisé que certains packages Oracle ne pouvaient être retranscrits pour PostgreSQL.
Par exemple, le package Oracle DBMS_JOB n’a pu être migré sur l’instance PostgreSQL de destination. Il s’agit d’un package Oracle permettant de planifier des tâches (procédures, ordres SQL, stats ….) qui seront exécutées par le moteur Oracle.

Il existe, pour PostgreSQL, une extension qui permet de faire ce travail au sein d’une instance PostgreSQL.
L’extension se nomme “pg_cron”.

Cet article suit donc les étapes de configuration qui permettront d’utiliser cette extension. Nous partirons d’un serveur Linux Debian avec une instance PostgreSQL 10.5.

 

Les extensions, qu’est ce donc ?

Une extension est un module complémentaire proposant un ensemble de procédures/fonctions et tables permettant la gestion d’opérations  supplémentaires (ex pg_stat_statements, pg_cron, pgpsql….) en base.

Ces modules, ou “plug-ins”,  sont disponibles dans les « contribs » PostgreSQL (si installation depuis les dépôts systèmes « yum » ou « aptitude ».
Ou bien ces derniers seront à télécharger sur le site postgresql :

https://www.postgresql.org/download/products/6-postgresql-extensions/

Certaines extensions sont disponibles sur des sites communautaires  comme Github , ex pour pg_cron :

https://github.com/citusdata/pg_cron

 

Une extension : pg_cron

Cette extension, proposée par Citusdata, permet de planifier des tâches dans une instance PostgreSQL (le processus PostgreSQL s’appuie sur la Crontab du serveur).
Nous pourrons aller télécharger les sources depuis le site « github » par exemple.

 

Il faudra ensuite dezipper le fichier sur un emplacement de destination sur le serveur.

La plupart des extensions PostgreSQL sont écrites en C, il est donc impératif de disposer d’un compilateur C (gcc)

Installation

Le contenu du zip pour cette extension, est le suivant. Attention, vérifier qu’il s’agit bien de la version 1.2 ou <, car celle ci est bien plus complète que les anciennes.

 

postgres@:~/ora2pg/REF/sources/pg_cron_master$ pg_cron-master]$ ls -l
drwxr-xr-x. 2 postgres postgres   95 Aug 28 12:19 src
drwxr-xr-x. 2 postgres postgres   30 Aug 28 12:19 sql
-rw-r--r--. 1 postgres postgres 6293 Aug 28 12:19 README.md
-rw-r--r--. 1 postgres postgres 2034 Aug 28 12:19 pg_cron.sql
-rw-r--r--. 1 postgres postgres  121 Aug 28 12:19 pg_cron.control
-rw-r--r--. 1 postgres postgres  155 Aug 28 12:19 pg_cron--1.1--1.2.sql
-rw-r--r--. 1 postgres postgres  101 Aug 28 12:19 pg_cron--1.0--1.1.sql
-rw-r--r--. 1 postgres postgres 1335 Aug 28 12:19 META.json
-rw-r--r--. 1 postgres postgres  812 Aug 28 12:19 Makefile
-rw-r--r--. 1 postgres postgres  918 Aug 28 12:19 LICENSE
drwxr-xr-x. 2 postgres postgres  136 Aug 28 12:19 include
-rw-r--r--. 1 postgres postgres 4139 Aug 28 12:19 github-banner.png
drwxr-xr-x. 2 postgres postgres   30 Aug 28 12:19 expected
-rw-r--r--. 1 postgres postgres 1632 Aug 28 12:19 CHANGELOG.md

 

Il s’agira maintenant de compiler nos sources.

postgres@:~/ora2pg/REF/sources/pg_cron_master$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security 
-fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -g -pipe -Wall 
-Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches 
-specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables 
-fstack-clash-protection -fcf-protection -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-maybe-uninitialized 
-I/usr/include -I. -I./ -I/usr/include/pgsql/server -I/usr/include/pgsql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   
-c -o src/pg_cron.o src/pg_cron.c ………….

Dès à présent, nous voyons notre librairie “pg_cron.so” ainsi construite sur le répertoire courant.

postgres@:~/ora2pg/REF/sources/pg_cron_master $ ls -l
-rw-r--r--. 1 postgres postgres   1632 Aug 28 12:19 CHANGELOG.md
drwxr-xr-x. 2 postgres postgres     30 Aug 28 12:19 expected
-rw-r--r--. 1 postgres postgres   4139 Aug 28 12:19 github-banner.png
drwxr-xr-x. 2 postgres postgres    136 Aug 28 12:19 include
-rw-r--r--. 1 postgres postgres    918 Aug 28 12:19 LICENSE
-rw-r--r--. 1 postgres postgres    812 Aug 28 12:19 Makefile
-rw-r--r--. 1 postgres postgres   1335 Aug 28 12:19 META.json
-rw-r--r--. 1 postgres postgres    101 Aug 28 12:19 pg_cron--1.0--1.1.sql
-rw-r--r--. 1 postgres postgres   2034 Sep 16 15:18 pg_cron--1.0.sql
-rw-r--r--. 1 postgres postgres    155 Aug 28 12:19 pg_cron--1.1--1.2.sql
-rw-r--r--. 1 postgres postgres    121 Aug 28 12:19 pg_cron.control
-rwxr-xr-x. 1 postgres postgres 223096 Sep 16 15:18 pg_cron.so
-rw-r--r--. 1 postgres postgres   2034 Aug 28 12:19 pg_cron.sql
-rw-r--r--. 1 postgres postgres   6293 Aug 28 12:19 README.md
drwxr-xr-x. 2 postgres postgres     30 Aug 28 12:19 sql
drwxr-xr-x. 2 postgres postgres    184 Sep 16 15:18 src

Cette nouvelle librairie devra être propagée aux librairies partagées du serveur auxquelles PostgreSQL sait accéder.
Seul le user “root” est habilité à effectuer cette modification et écrire dans le répertoire /usr.

root@:~/ora2pg/REF/sources/pg_cron_master # make install
usr/bin/mkdir -p '/usr/lib64/pgsql'
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/install -c -m 755  pg_cron.so '/usr/lib64/pgsql/pg_cron.so'
/usr/bin/install -c -m 644 .//pg_cron.control '/usr/share/pgsql/extension/'
/usr/bin/install -c -m 644 .//pg_cron--1.0--1.1.sql .//pg_cron--1.1--1.2.sql pg_cron--1.0.sql '/usr/share/pgsql/extension/'

Notre librairie “pg_cron” est donc installée sur le répertoire “/usr/lib64/pgsql/”. Les fichiers SQL relatifs à cette extension sont présents dans “/usr/share/pgsql/extension”.
Les noms de ces répertoires peuvent variés en fonction de votre distribution Linux. L’installation de votre instance PostgreSQL aura également une influence sur le nom de ces répertoires, selon que vous ayez utilisé les dépôts OS ou bien les sources du site “postgresql.org”.

 

Configuration en base

Une fois installée, partie OS, il faudra modifier les paramètres base de données dans PostgreSQL afin de pouvoir utiliser cette extension.
Le fichier “postgresql.conf” devra indiquer les éléments suivants pour permettre la création de cette extension en base

• share_preload_libraries
• cron.database_name.

postgres@:~/ora2pg/REF/sources/pg_cron_master $ cd $PGDATA
postgres@:/data $ vi postgresql.conf
....
shared_preload_libraries = 'pg_cron'          # (change requires restart)
cron.database_name = 'capdata'
....

Le paramètre “shared_preload_libraries” peut contenir plusieurs entrées (si plusieurs extensions utilisées).

Il faudra redémarrer l’instance PostgreSQL pour la prise en compte des modifications. Une fois redémarrée, la log PostgreSQL doit indiquer les informations suivantes

2019-09-16 15:32:24.410 UTC [2262] LOG:  last completed transaction was at log time 2019-09-16 15:29:49.397743+00
2019-09-16 15:32:24.430 UTC [2260] LOG:  database system is ready to accept connections
2019-09-16 15:32:24.433 UTC [2268] LOG:  pg_cron scheduler started

Notre process pg_cron scheduler est UP. Il reste à déclarer l’extension sur notre base “capdata”.

postgresql@:~/ora2pg/REF $ psql -U capdata -d capdata

>  select name, setting, sourcefile from pg_settings where name ='shared_preload_libraries';
           name           |             setting             |                sourcefile
--------------------------+---------------------------------+-------------------------------------------
 shared_preload_libraries | pg_cron		            | /data/postgresql/10/main2/postgresql.conf

> create extension pg_cron ;
CREATE EXTENSION


> select * from pg_extension;
 extname            | extowner | extnamespace | extrelocatable | extversion |   extconfig   | extcondition
--------------------+----------+--------------+----------------+------------+---------------+--------------
 plpgsql            |       10 |           11 | f              | 1.0        | NULL          | NULL
 pg_stat_statements |       10 |         2200 | t              | 1.6        | NULL          | NULL
 pgcrypto           |       10 |         2200 | t              | 1.3        | NULL          | NULL
 pg_cron            |       10 |         2200 | f              | 1.2        | {24695,24693} | {"",""}

L’ordre de création de l’extension va déclarer un nouveau schéma nommé “cron” puis jouer les scripts SQL de création des objets relatifs à son utilisation.

 

L’utilisation

Afin de programmer une tâche , nous utiliserons la procédure “schedule” présente dans le schéma “cron” de notre base “capdata”.

Le modèle de planification d’une tâche est comme celui d’une crontab Linux :

 

L’exemple ci dessous crée une tâche qui va insérer une ligne dans la table “employes” chaque minute.

$ psql -U capdata -d capdata

> select * from employes;
 nom | poste | date_entree | societe
-----+-------+-------------+---------
(0 rows)

> select * from cron.schedule('* * * * *',$$insert into employes values ('Manu','DBA',now(),'Capdata')$$);
 schedule
----------
        3
(1 row)

> select * from cron.job;
 jobid | schedule  |                          command                           | nodename  | nodeport | database | username | active
-------+-----------+------------------------------------------------------------+-----------+----------+----------+----------+--------
     3 | * * * * * | insert into employes values ('Manu','DBA',now(),'Capdata') | localhost |     5432 | capdata  | capdata  | t
(1 row)

La table “job” que l’on interroge donne les renseignements sur les jobs en cours sur cette base, avec notamment les informations de connexions utilisées par le processus de scheduling (host, port, database). On s’assure que notre table “employes” est bien alimentée.

$ psql -U capdata -d capdata
> select now();
              now
-------------------------------
 2019-09-18 15:22:03.419635+00
(1 row)

> select * from employes;
 nom  | poste | date_entree | societe
------+-------+-------------+---------
 Manu | DBA   | 2019-09-18  | Capdata
 Manu | DBA   | 2019-09-18  | Capdata
 Manu | DBA   | 2019-09-18  | Capdata
 Manu | DBA   | 2019-09-18  | Capdata
 Manu | DBA   | 2019-09-18  | Capdata
(5 rows)

On pourra également créer une tâche qui supprime les lignes sur cette table

> select * from cron.schedule('* * * * *',$$delete from employes where nom='Manu'$$);
 schedule
----------
        4
(1 row)

> select * from cron.job;
 jobid | schedule  |                          command                           | nodename  | nodeport | database | username | active
-------+-----------+------------------------------------------------------------+-----------+----------+----------+----------+--------
     3 | * * * * * | insert into employes values ('Manu','DBA',now(),'Capdata') | localhost |     5432 | capdata  | capdata  | t
     4 | * * * * * | delete from employes where nom='Manu'                      | localhost |     5432 | capdata  | capdata  | t
(2 rows)

On peut tout aussi bien supprimer ces tâches via la procédure “unschedule”

> select * from cron.unschedule(3);

> select * from cron.unschedule(4);

> select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active
-------+----------+---------+----------+----------+----------+----------+--------
(0 rows)

Il est également possible de mettre en “pause” le process en allant modifier son état dans la table “job”. S’il l’on reprend notre ancien job de suppression de lignes

> select * from cron.job;
 jobid | schedule  |                command                | nodename  | nodeport | database | username | active
-------+-----------+---------------------------------------+-----------+----------+----------+----------+--------
     4 | * * * * * | delete from employes where nom='Manu' | localhost |     5432 | capdata  | capdata  | t
(1 row)

On voit que la colonne “active” est en fait un booléen qui définit si ce job est actif, ou non. Il s’agira alors de passer à FALSE ce job afin qu’il ne soit plus effectué, tout en conservant son utilisation si besoin par la suite.

> select * from cron.job;
jobid | schedule  |                 command               | nodename  | nodeport | database | username | active
------+-----------+---------------------------------------+-----------+----------+----------+----------+--------
    4 | * * * * * | delete from employes where nom='Manu' | localhost |     5432 | capdata  | capdata  | t
(1 row)


(postgres@[local]:5432) [capdata] > update cron.job set active='f' where jobid=4;
UPDATE 1

(postgres@[local]:5432) [capdata] > select * from cron.job;
jobid  | schedule  |               command                 | nodename  | nodeport | database | username | active
-------+-----------+---------------------------------------+-----------+----------+----------+----------+--------
     4 | * * * * * | delete from employes where nom='Manu' | localhost |   5432   | capdata  | capdata  | f
(1 row)

 

On s’assure par la suite que la table “employes” ne subit plus de suppression.

Afin d’avoir un visu sur tous les objets liés à cette extension, on peut interroger psql

 > \dx+ pg_cron
Objets dans l'extension « pg_cron »
Description d'objet
--------------------------------------
fonction cron.job_cache_invalidate()
fonction cron.schedule(text,text)
fonction cron.unschedule(bigint)
schéma cron
séquence cron.jobid_seq
table cron.job
(6 lignes)

 

🙂

Emmanuel RAMI

Continuez votre lecture sur le blog :

• Containeriser PostgreSQL avec Docker ! (Emmanuel RAMI) [ContainerPostgreSQL]
• pg_recursively_delete : Simplifier les suppressions récursives (Sarah FAVEERE) [PostgreSQL]
• La migration Oracle vers PostgreSQL avec ora2pg (Emmanuel RAMI) [OraclePostgreSQL]
• PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
• Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]