Introducing pg_cron – The Automation task master

The pg_cron extension is a PostgreSQL extension that allows you to schedule regular database tasks using the familiar cron syntax. Any tasks that need to be done at regular intervals, such as data archiving, partitioning, creating or deleting backup sets, etc. can be automated using pg_cron.

Installation the pg_cron extension:

Install the pg_cron extension dependencies: The pg_cron extension depends on the libpq and libcron libraries. Install these dependencies using the package manager of your operating system.

Modify PostgreSQL configuration: Open the postgresql.conf file of your PostgreSQL installation and add the following line to enable the pg_cron extension:

postgres=# alter system set shared_preload_libraries = 'pg_cron';

postgres=# Alter system set cron.databasename = ‘postgres’;

Restart the PostgreSQL service to apply the configuration changes.

Systemctl restart postgresql.service

Create the pg_cron extension:

postgres=# create extension pg_cron;

Verifying pg_cron installation

Below is an example to schedule a vacuum task. The expression used for the scheduler is similar to any other scheduled cron job on the linux operating system.

postgres=# SELECT cron.schedule('2 * * * *', 'vacuum');

schedule 
----------
        2

We can check the output of the schedule 2 as following in the log

2023-05-16 21:32:00.015 IST [60387] LOG:  cron job 2 starting: vacuum
2023-05-16 21:32:00.064 IST [60387] LOG:  cron job 2 COMMAND completed: VACUUM
2023-05-16 21:33:26.019 IST [60381] LOG:  checkpoint starting: time

2023-05-16 21:33:28.333 IST [60381] LOG:  checkpoint complete: wrote 23 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=2.308 s, sync=0.002 s, total=2.315 s; sync files=10, longest=0.001 s, average=0.001 s; distance=107 kB, estimate=464 kB

We can check the history in the cron.job_run_details table as follows.

postgres=# select * from cron.job_run_details ;

jobid | runid | job_pid | database | username |      command       |  status   |  return_message   | 

——-+——-+———+———-+———-+——————–+———–+——————-+-

     3 |    28 |   60541 | postgres | postgres | vacuum             | succeeded | VACUUM           

|      1 |     1 |         | postgres | postgres | select * from emp; | failed    | connection failed |  

We can check all the scheduled cron jobs as following command

postgres=# select * from cron.job;

To unschedule a job using the job id

Conclusion:

You have successfully installed the pg_cron extension in your PostgreSQL database. You can now start scheduling tasks using pg_cron to automate your routine database activities on Postgres by following the examples above. Using pg_cron can help save on some of the developers and DBAs time, which could then be used for something more critical or productive,  and also add a layer of efficiency to the business processes by reducing human dependency.