pg_cron setup in PostgreSQL

pg_cron is a PostgreSQL extension that lets you run scheduled jobs (like Linux cron) directly inside PostgreSQL.

1.RHEL / CentOS / Rocky Linux install

sudo yum install pg_cron_15

2. Enable pg_cron in PostgreSQL

/var/lib/pgsql/15/data/postgresql.conf

Add:

shared_preload_libraries = 'pg_cron'

cron.database_name = 'postgres'

3. Restart PostgreSQL

pg_ctl

4. Create the Extension

psql -U postgres

Create extension:

CREATE EXTENSION pg_cron;

Verify:

SELECT * FROM pg_extension WHERE extname = 'pg_cron';

5. Schedule Jobs

CREATE EXTENSION pg_cron;

PostgreSQL creates a schema named cron inside the database where the extension was installed (commonly the postgres database).

objects like:

cron.job

cron.job_run_details

cron.schedule()

cron.unschedule()

select * from  cron.job;

select * from  cron.job_run_details;

select * from cron.schedule();

select * from cron.unschedule();

Important detail:

pg_cron is installed per database, not cluster-wide.

If you created the extension in postgres DB:

Check extension location:

SELECT extname, extnamespace::regnamespace

FROM pg_extension

WHERE extname='pg_cron';

postgres=# select version();

                                                 version

---------------------------------------------------------------------------------------------------------

 PostgreSQL 15.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit

(1 row)

postgres=# CREATE EXTENSION pg_cron;

CREATE EXTENSION

postgres=# SELECT * FROM pg_extension WHERE extname = 'pg_cron';

  oid  | extname | extowner | extnamespace | extrelocatable | extversion |         extconfig         | extcondition

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

 24853 | pg_cron |       10 |           11 | f              | 1.6        | {24856,24855,24875,24874} | {"","","",""}

(1 row)

postgres=# SELECT extname, extnamespace::regnamespace

postgres-# FROM pg_extension

postgres-# WHERE extname='pg_cron';

 extname | extnamespace

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

 pg_cron | pg_catalog

(1 row)

postgres=# \dn

      List of schemas

  Name  |       Owner

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

 admin  | postgres

 cron   | postgres

 public | pg_database_owner

(3 rows)

postgres=#

SELECT * FROM pg_available_extensions

postgres-# WHERE name = 'pg_cron';

  name   | default_version | installed_version |           comment

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

 pg_cron | 1.6             | 1.6               | Job scheduler for PostgreSQL

(1 row)

Find job ID & name:

SELECT jobid, jobname

FROM cron.job;

 SELECT jobid, jobname

postgres-# FROM cron.job;

 jobid |      jobname

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

     1 | db-size-collector

(1 row)

Remove:

SELECT cron.unschedule('db-size-collector');

Check Execution History

SELECT *

FROM cron.job_run_details

ORDER BY start_time DESC

LIMIT 10;

Verify Job Created

SELECT * FROM cron.job;