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;
