How to manage jobs using pg_cron

The purpose of this article is to provide steps on how one can install pg_cron as well as schedule and drop automatic jobs using pg_cron.

pg_cron is a simple cron-based job scheduler for PostgreSQL that runs inside the database as an extension. It allows you to schedule commands directly from the Postgres database and can run multiple jobs in parallel. The pg_cron extension can only be installed in a single database on a given server. This should be noted when adding a part for the CREATE EXTENSION pg_cron command. By default jobs are run by establishing a new connection to the database they are scheduled to run on which may need some pg_hba.conf changes or password files in place; however this can be configured to use background process instead, in which case max_worker_processes may need to be increased (which requires a server restart).

Now let us take a look at how we can setup pg_cron using the following steps:

• Install pg_cron. Note that, the package name should match the server version and type as shown below:

  • edb-asXX-pg-cron1 -> EPAS XX
  • edb-pgXX-pg-cron1 -> Community Postgres XX
  • edb-postgresextendedXX-pg-cron1 -> EDB PGExtended XX

Installed:
  edb-as15-pg-cron1-1.5.2-1.el8.x86_64

• Add pg_cron to any present entries in the shared_preload_libraries and set shared_preload_libraries in your postgresql.conf file (Note that this requires a server restart to take effect).

shared_preload_libraries = 'pg_cron, pg_stat_statements'
cron.database_name = 'edb'

• Create pg_cron extension on the database specified on the cron.database_name

create extension pg_cron;
CREATE EXTENSION

• Create a cron job. The schedule uses the standard cron syntax, in which * means “run every time period”, and a specific number means “but only at this time”.

SELECT cron.schedule('1 * * * *', 'VACUUM');
 schedule_in_database
----------------------
                    1
(1 row)

SELECT cron.schedule('1 * * * *', 'ANALYZE');
 schedule
----------
        2
(1 row)

The above examples will accomplish the following:

• The first command runs VACUUM every minute
• The second schedule runs ANALYZE to run every minute for the sake of this test scenario

Note that all jobs must be scheduled from the database mentioned in the cron.database_name, however if you need to be run on a different database cron.schedule_on_database() can be used instead of cron.schedule(). Also, regular users can be allowed to schedule jobs as well, they will need USAGE permission on the cron schema and their jobs are run with their user.

• Then proceed to verify that the jobs are now active by examining the cron.job table.

SELECT * FROM cron.job;

 jobid | schedule  |           command            | nodename  | nodeport | database |   username   | active | jobname
-------+-----------+------------------------------+-----------+----------+----------+--------------+--------+---------
     1 | 1 * * * * | VACUUM                       | localhost |     5444 | edb      | enterprisedb | t      |
     2 | 1 * * * * | ANALYZE                      | localhost |     5444 | edb      | enterprisedb | t      |

• You can see the status of jobs that have run by examining the cron.job_run_details table

SELECT * from cron.job_run_details ;

-[ RECORD 1 ]--+-------------------------------------
jobid          | 1
runid          | 2
job_pid        | 40688
database       | edb
username       | enterprisedb
command        | VACUUM
status         | succeeded
return_message | VACUUM
start_time     | 22-APR-24 19:01:00.055761 -04:00
end_time       | 22-APR-24 19:01:00.101602 -04:00
-[ RECORD 2 ]--+-------------------------------------
jobid          | 2
runid          | 1
job_pid        | 40687
database       | edb
username       | enterprisedb
command        | ANALYZE
status         | succeeded
return_message | ANALYZE
start_time     | 22-APR-24 19:01:00.059002 -04:00
end_time       | 22-APR-24 19:01:00.29276 -04:00

Now let's try to delete an existing job using cron.unschedule as shown below

SELECT cron.unschedule('1');
 unschedule
------------
 t
(1 row)

SELECT * FROM cron.job;

 jobid | schedule  | command | nodename  | nodeport | database |   username   | active | jobname
-------+-----------+---------+-----------+----------+----------+--------------+--------+---------
     2 | 1 * * * * | ANALYZE | localhost |     5444 | edb      | enterprisedb |  t     |
(1 row)