PostgreSQL Job scheduler “pg_cron”

What is pg_cron ?

pg_cron is a simple, cron-based job scheduler for PostgreSQL that runs inside the database as an extension. A background worker initiates commands according to their schedule by connecting to the local database as the user that scheduled the job. pg_cron supports PostgreSQL (9.5 or higher version).

Why We need it ?

Running periodic maintenance jobs or removing old data is a common requirement in PostgreSQL. A simple way to achieve this is to configure cron or another external daemon to periodically connect to the database and run a command.

Installation of pg_cron

Assuming PostgreSQL server is already installed

On Ubuntu

sudo apt-get -y install postgresql-10-cron

OR you can install it from source code as well -

Step 1 : Download source code from git pg_cron

export PATH=/usr/local/pgsql/bin:$PATH

wget https://github.com/citusdata/pg_cron/archive/master.zip

unzip master

cd pg_cron-master/

make

make install

Step 2 : Make changes in .conf files as below 

To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to shared_preload_libraries in postgresql.conf. Note that pg_cron does not run any jobs as a long a server is in hot standby mode, but it automatically starts when the server is promoted.

postgresql.conf

shared_preload_libraries = 'pg_cron'             (-- Need server restart)

cron.database_name = 'postgres'

Important: Internally, pg_cron uses libpq to open a new connection to the local database. It may be necessary to enable trust authentication for connections coming from localhost in pg_hba.conf for the user running the cron job. Alternatively, you can add the password to a .pgpass file, which libpq will use when opening a connection.

pg_hba.conf

host    postgres  postgres          trust

.pgpass

localhost:5433:postgres:postgres:**********

Step 3 : Create pg_cron extension and grant permission 

-- run as superuser:

CREATE EXTENSION pg_cron;

-- optionally, grant usage to regular users:

GRANT USAGE ON SCHEMA cron TO postgres;

Step 4 : Try scheduling cron in database

      A ] Schedule cron to run function every 2 minutes 

            SELECT cron.schedule('*/2 * * * *', $$select insert_itn_count()$$);

      B ] Schedule cron to run Vacuum every day at 10:00am

            SELECT cron.schedule('0 10 * * *', 'VACUUM');

      C ] You can see scheduled crons as below 

postgres=# select * from cron.job;

 jobid |  schedule   |          command          | nodename  | nodeport |    database    |    username    | active 

-------+-------------+---------------------------+-----------+----------+----------------+----------------+--------

     1 | */2 * * * * | select insert_itn_count() | localhost |     5433 | postgres | postgres | t

     2 | 0 10 * * *  | VACUUM                    | localhost |     5433 | postgres | postgres       | t

(2 rows)

    D ] Stop scheduled job 

          SELECT cron.unschedule(2);

      ************************************************ THANK YOU***************************************************

        

Do You Like This Blog ?