Job scheduler for PostgreSQL "pg_cron"

What is pg_cron  : - 

pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database .

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.

Let's see how it's works 

Step 1 :- 

For implementing/Installation of pg_cron you need to download source code from git

Dowload link 

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 : -

To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to shared_preload_libraries in postgresql.conf and restart PostgreSQL. 

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.

Add below line in postgresql.conf

shared_preload_libraries = 'pg_cron'

Step 3 : -

Create extension pg_cron ,After creation of extension it will create one schema having one table "job"

create extension pg_cron ;

Step 4 : - 

Now try to setup your job inside the DB .

A) 

-- Vacuum Analyze every day at 11:00am SELECT cron.schedule('0 11 * * *', 'VACUUM ANALYZE');

B) To run SQL statement in scheduler.

-- Delete old data on Saturday at 3:30 SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

C) Stop/unscheduled 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":

 ┌───────────── min (0 - 59)
 │ ┌────────────── hour (0 - 23)
 │ │ ┌─────────────── day of month (1 - 31)
 │ │ │ ┌──────────────── month (1 - 12)
 │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
 │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
 │ │ │ │ │
 │ │ │ │ │
 * * * * * 

How to run SQL job against remote server :-







If you are superuser, then you can manually modify the cron.job table and use custom values for nodename and nodeport to connect to a different machine:




INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username) VALUES ('0 11 * * *', 'VACUUM ANALYZE', 'postgresql-pgcron', 5432, 'postgres', 'tushar');

You can use .pgpass to allow pg_cron to authenticate with the remote server .  

Background process : - 

Process "bgworker: pg_cron_scheduler" get executed in back-end to run scheduled  jobs .

Please refer below snip .