Skip to main content

What is pg_cron? (Run periodic jobs in PostgreSQL)

 pg_cron is a simple cron-based job scheduler for PostgreSQL (10 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:

You can also install pg_cron by building it from source:
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# Ensure pg_config is in your path, e.g.
-bash-4.2$ export LD_LIBRARY_PATH=/u01/app/postgres/13.3/lib:$LD_LIBRARY_PATH
-bash-4.2$ export PATH=/u01/app/postgres/13.3/bin:$PATH
-bash-4.2$ cd pg_cron
-bash-4.2$ make && sudo PATH=$PATH make install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/13.3/include -I. -I./ -I/u01/app/postgres/13.3/include/server -I/u01/app/postgres/13.3/include/internal  -D_GNU_SOURCE   -c -o src/pg_cron.o src/pg_cron.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/13.3/include -I. -I./ -I/u01/app/postgres/13.3/include/server -I/u01/app/postgres/13.3/include/internal  -D_GNU_SOURCE   -c -o src/job_metadata.o src/job_metadata.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/13.3/include -I. -I./ -I/u01/app/postgres/13.3/include/server -I/u01/app/postgres/13.3/include/internal  -D_GNU_SOURCE   -c -o src/misc.o src/misc.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/13.3/include -I. -I./ -I/u01/app/postgres/13.3/include/server -I/u01/app/postgres/13.3/include/internal  -D_GNU_SOURCE   -c -o src/task_states.o src/task_states.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/13.3/include -I. -I./ -I/u01/app/postgres/13.3/include/server -I/u01/app/postgres/13.3/include/internal  -D_GNU_SOURCE   -c -o src/entry.o src/entry.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o pg_cron.so src/pg_cron.o src/job_metadata.o src/misc.o src/task_states.o src/entry.o -L/u01/app/postgres/13.3/lib    -Wl,--as-needed -Wl,-rpath,'/u01/app/postgres/13.3/lib',--enable-new-dtags  -L/u01/app/postgres/13.3/lib -lpq
cat pg_cron.sql > pg_cron--1.0.sql
/usr/bin/mkdir -p '/u01/app/postgres/13.3/lib'
/usr/bin/mkdir -p '/u01/app/postgres/13.3/share/extension'
/usr/bin/mkdir -p '/u01/app/postgres/13.3/share/extension'
/usr/bin/install -c -m 755  pg_cron.so '/u01/app/postgres/13.3/lib/pg_cron.so'
/usr/bin/install -c -m 644 .//pg_cron.control '/u01/app/postgres/13.3/share/extension/'
/usr/bin/install -c -m 644 .//pg_cron--1.3--1.4.sql .//pg_cron--1.2--1.3.sql .//pg_cron--1.0--1.1.sql .//pg_cron--1.4--1.4-1.sql .//pg_cron--1.1--1.2.sql pg_cron--1.0.sql '/u01/app/postgres/13.3/share/extension/'

make && sudo PATH=$PATH make install
-bash-4.2$ ./pg_ctl restart -D /u01/psql/DATA
pg_ctl: PID file "/u01/psql/DATA/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2022-07-15 16:53:02.272 IST [6254] LOG:  redirecting log output to logging collector process
2022-07-15 16:53:02.272 IST [6254] HINT:  Future log output will appear in directory "/u01/psql/DATA/log".
 done
server started
-bash-4.2$
-bash-4.2$ psql -h localhost -p 5433
psql (13.3)
Type "help" for help.

postgres=# CREATE EXTENSION pg_cron;
CREATE EXTENSION

postgres=# GRANT USAGE ON SCHEMA cron TO postgres;
GRANT
postgres=#
 
 
 
-- How to check job lists.
postgres=# SELECT * FROM cron.job;
 jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)
 
-- To schedule a VACUUM ANALYZE on each day at 11:00 AM
postgres=# SELECT cron.schedule('0 11 * * *', 'VACUUM ANALYZE');
 schedule
----------
        1
(1 row)
 
postgres=# SELECT * FROM cron.job;
 jobid |  schedule  |    command     | nodename  | nodeport | database | username | active | jobname
-------+------------+----------------+-----------+----------+----------+----------+--------+---------
     1 | 0 11 * * * | VACUUM ANALYZE | localhost |     5432 | postgres | postgres | t      |
(1 row)
 
postgres=#
 
postgres=#
postgres=#
postgres=# table cron.job;
 
 jobid |  schedule  |    command     | nodename  | nodeport | database | username | active | jobname
-------+------------+----------------+-----------+----------+----------+----------+--------+---------
     1 | 0 11 * * * | VACUUM ANALYZE | localhost |     5432 | postgres | postgres | t      |
(1 row)
 
 
Lets do some more tests, will now create a table with some test records and will try to delete them using pg_cron.
 
postgres=#
postgres=# CREATE TABLE article (
postgres(#     article_id bigserial primary key,
postgres(#     article_name varchar(20) NOT NULL,
postgres(#     article_desc text NOT NULL,
postgres(#     date_added timestamp default NULL
postgres(# );
CREATE TABLE
postgres=#
 
 
postgres=# insert into article (article_id, article_name, article_desc, date_added) values (1010,'vinyl','Beatles 1980 Vinyl',current_timestamp);
postgres=# insert into article (article_id, article_name, article_desc, date_added) values (1011,'Tape','Deftones 2015 Vinyl',current_timestamp);
postgres=# insert into article (article_id, article_name, article_desc, date_added) values (1019,'Tape','Deftones 2015 Vinyl',current_timestamp);
 
 
 
postgres=#
postgres=# select * from article;
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:03:30.514886
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:03:40.870081
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:03:52.046054
(3 rows)
 
postgres=#
 
 
postgres=# \! date
Sat Jul 24 12:20:40 EDT 2021
postgres=#
 
 
 
-- To delete entries those are older than 1 minute
postgres=# SELECT cron.schedule('23 12 * * *', $$DELETE FROM article WHERE date_added<now()- interval '1 Mins'$$);
 schedule
----------
       2
(1 row)
 
postgres=#
postgres=# SELECT * FROM cron.job;
 jobid |  schedule   |                            command                            | nodename  | nodeport | database | username | active | jobname
-------+-------------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+---------
     1 | */5 * * * * | VACUUM                                                        | localhost |     5432 | postgres | postgres | t      |
     2 | 23 12 * * * | DELETE FROM article WHERE date_added<now()- interval '1 Mins' | localhost |     5432 | postgres | postgres | t      |
(2 rows)
 
 
-- This is what it is before pg_cron executing the schedule.
postgres=#
postgres=#
postgres=# select * from article;
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)
 
 
-- Will do a watch on table records to see if pg_cron comes and delete records on specified time.
postgres=#
postgres=# \watch 5
                  Sat 24 Jul 2021 12:22:43 PM EDT (every 5s)
 
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)
 
                  Sat 24 Jul 2021 12:22:48 PM EDT (every 5s)
 
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)
 
                  Sat 24 Jul 2021 12:22:53 PM EDT (every 5s)
 
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)
 
                  Sat 24 Jul 2021 12:22:58 PM EDT (every 5s)
 
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)
 
                  Sat 24 Jul 2021 12:23:03 PM EDT (every 5s)
 
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
Yes, it did pretty smoothly on specified time! Let’s do some more fun.


postgres=#
postgres=# CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)
postgres-# AS $$ BEGIN
postgres$# RAISE NOTICE 'Procedure Parameter: %', msg ;
postgres$# END ;
postgres$# $$
postgres-# LANGUAGE plpgsql ;
CREATE PROCEDURE
postgres=#
postgres=# call display_message('This is my test case');
NOTICE:  Procedure Parameter: This is my test case
         msg
----------------------
 This is my test case
(1 row)
 
 
 
postgres=# SELECT cron.schedule('display_message', '* * * * *', 'CALL display_message()');
 schedule
----------
       14
(1 row)
 
postgres=# SELECT * FROM cron.job;
 jobid |  schedule   |                            command                            | nodename  | nodeport | database | username | active |     jobname
-------+-------------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+-----------------
     9 | */5 * * * * | VACUUM                                                        | localhost |     5432 | postgres | postgres | t      |
    12 | 20 12 * * * | DELETE FROM article WHERE date_added<now()- interval '1 Mins' | localhost |     5432 | postgres | postgres | t      |
    13 | 0 3 * * *   | VACUUM article                                                | localhost |     5432 | postgres | postgres | t      |
    14 | * * * * *   | CALL display_message()                                        | localhost |     5432 | postgres | postgres | t      | display_message
(4 rows)
Now, how to delete a single schedule or all/multiple.


postgres=#  SELECT cron.unschedule(2);
 unschedule
------------
 t
(1 row)
 
 
postgres=#
postgres=# SELECT cron.unschedule(jobid) FROM cron.job;
 unschedule
------------
 t
 t
 
(2 rows)
 
postgres=#
postgres=# SELECT * FROM cron.job;
 jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)
 
postgres=#


Comments

Post a Comment

Popular posts from this blog

PostgreSQL Vacuum and Vacuum full are not two different processes

  PostgreSQL’s   VACUUM   and   VACUUM FULL   are not separate processes but rather different operational modes of the same maintenance command. Here’s why: Core Implementation Both commands share the same underlying codebase and are executed through the  vacuum_rel()  function in PostgreSQL’s source code ( src/backend/commands/vacuum.c ). The key distinction lies in the  FULL  option, which triggers additional steps: Standard  VACUUM : Removes dead tuples (obsolete rows) and marks space reusable  within PostgreSQL Updates the visibility map to optimize future queries Runs concurrently with read/write operations VACUUM FULL : Rewrites the entire table into a new disk file, compressing it and reclaiming space for the  operating system Rebuilds all indexes and requires an  ACCESS EXCLUSIVE  lock, blocking other operations Key Differences in Behavior Aspect Standard VACUUM VACUUM FULL Space Reclamation Internal reuse onl...
1 comment
Read more

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 post...
Post a Comment
Read more

All about pg_hba.conf(authentication methods- Postgresql)

  pg_hba.conf is the PostgreSQL access policy configuration file, which is located in the /var/lib/pgsql/10/data/ directory (PostgreSQL10) by default. The configuration file has 5 parameters, namely: TYPE (host type), DATABASE (database name), USER (user name), ADDRESS (IP address and mask), METHOD (encryption method) host all all 192.168.109.103/22 md5 host dbName user 192.168.109.106/22 trust Modify the server-side pg_hba.conf file Make the shell can connect to the postgres database secretly: Modify the authentication file $PGDATA/pg_hba.conf, add the following lines, and reload to make the configuration take effect immediately. host pankajconnect postgresql 192.168.8.103/32 trust Reload to take effect: pg_ctl reload -D $PGDATA Examples: 1. Allow local login to the database using PGAdmin3, database address  localhost, user user1, database user1db: host user1db user1 127.0.0.1/32 md5 2. Allow 10.1.1.0~10.1.1.255 network segments to log in to the database: host all all 10.1.1....
Post a Comment
Read more