Viswamitra's blog

pg_cron is like Linux cron scheduler but this helps the users to schedule the database jobs from database itself. pg_cron follows the same syntax as the Linux cron. I have done the installation for PostgreSQL 12 in Centos7.

Examples from documentation

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

-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');
 schedule
----------
       43

-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');
 schedule
----------
       43

-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
 unschedule 
------------
 t
(1 row)

SELECT cron.unschedule(42);
 unschedule
------------
          t

git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/pgsql-12/bin:$PATH
make && sudo PATH=$PATH make install

Add below configuration parameters to postgresql.auto.conf

# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

Restart PostgreSQL cluster

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data restart


Login to postgres database and create pg_cron extension

-bash-4.2$ psql
psql (12.5)
Type "help" for help.

postgres=# CREATE EXTENSION pg_cron;
CREATE EXTENSION
postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 cron   | postgres
 public | postgres
(2 rows)

postgres=# \dt cron.job
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 cron   | job  | table | postgres
(1 row)

Issues faced

[root@localhost pg_cron]# export PATH=/usr/pgsql-12/bin:$PATH
[root@localhost pg_cron]# make && sudo PATH=$PATH make install
Makefile:23: /usr/pgsql-12/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target `/usr/pgsql-12/lib/pgxs/src/makefiles/pgxs.mk'. Stop.

This requires postgresql12-devel to be installed, so started installing it

yum install postgresql12-devel
.......
.......
--> Finished Dependency Resolution
Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (pgdg12)
           Requires: llvm5.0-devel >= 5.0
Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (pgdg12)
           Requires: llvm-toolset-7-clang >= 4.0.1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Failed again, this requires epel, centos-release-scl to be installed

yum install epel
yum install centos-release-scl