A new release of the famous pg_cron (v1.3.0) came out on the 6th of October with a lot of cool new features, and a few bug fixes as well. The PostgreSQL 13 support might be the most important one, however, some other very cool stuff was implemented as well. Especially the audit table support might a great one for a lot of people out there.
- PostgreSQL 13 support
- Background worker support
- Audit table support
- Schedule jobs by name
- Fixes a bug that could cause cron.schedule to crash with long schedules
- Fixes a bug that could cause cron.schedule to get into an infinite loop
- Fixes a bug that caused overlapping runs not to start
For more information, check out pg_cron on github.
https://github.com/citusdata/pg_cron
A feature I was looking for was pg_cron support for multiple databases, which is not the case with pg_cron v1.3.0. What I mean, is that the parameter cron.database_name still supports one database only. So the scheduling framework is attached to one database only.
cron.database_name = 'pg_cron'
So the question is, how do I schedule jobs with pg_cron in multiple databases? And does it work at all? The short answer is yes. With a little workaround. 😉
Let’s say you have two databases “ptdb” and “slvdb” and you want to schedule jobs in both of them. The trick is to create a third database “pg_cron”, and to adjust the job metadata manually with some update commands.
But let’s go through it step by step. First of all, lets create our environment. A few users and a few databases.
(postgres@[local]:55001)[postgres] create user cron_admin password 'cron_admin'; CREATE ROLE (postgres@[local]:55001)[postgres] create user ptdb_admin password 'ptdb_admin'; CREATE ROLE (postgres@[local]:55001)[postgres] create user slvdb_admin password 'slvdb_admin'; CREATE ROLE (postgres@[local]:55001)[postgres] create database pg_cron owner=cron_admin; CREATE DATABASE (postgres@[local]:55001)[postgres] create database ptdb owner=ptdb_admin; CREATE DATABASE (postgres@[local]:55001)[postgres] create database slvdb owner=slvdb_admin; CREATE DATABASE
Afterwards we compile the new pg_cron software against PostgreSQL 13.1. Simply by running make and make install.
[postgres@ocm199 1.3.0]$ unzip pg_cron-main.1.3.0.zip Archive: pg_cron-main.1.3.0.zip 4fdb4676781c56ea837058d336287f41909e56c6 creating: pg_cron-main/ inflating: pg_cron-main/.gitignore inflating: pg_cron-main/CHANGELOG.md inflating: pg_cron-main/LICENSE inflating: pg_cron-main/META.json inflating: pg_cron-main/Makefile ... ... inflating: pg_cron-main/src/entry.c inflating: pg_cron-main/src/job_metadata.c inflating: pg_cron-main/src/misc.c inflating: pg_cron-main/src/pg_cron.c inflating: pg_cron-main/src/task_states.c [postgres@ocm199 1.3.0]$ cd pg_cron-main/ [postgres@ocm199 pg_cron-main]$ [postgres@ocm199 pg_cron-main]$ make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/pgproduct/pg-13.1/include -I. -I./ -I/u01/app/postgres/pgproduct/pg-13.1/include/server -I/u01/app/postgres/pgproduct/pg-13.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o src/pg_cron.o src/pg_cron.c ... ... /usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/pgproduct/pg-13.1/include -I. -I./ -I/u01/app/postgres/pgproduct/pg-13.1/include/server -I/u01/app/postgres/pgproduct/pg-13.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -flto=thin -emit-llvm -c -o src/entry.bc src/entry.c [postgres@ocm199 pg_cron-main]$ make install /usr/bin/mkdir -p '/u01/app/postgres/pgproduct/pg-13.1/lib' /usr/bin/mkdir -p '/u01/app/postgres/pgproduct/pg-13.1/share/extension' /usr/bin/mkdir -p '/u01/app/postgres/pgproduct/pg-13.1/share/extension' /usr/bin/install -c -m 755 pg_cron.so '/u01/app/postgres/pgproduct/pg-13.1/lib/pg_cron.so' /usr/bin/install -c -m 644 .//pg_cron.control '/u01/app/postgres/pgproduct/pg-13.1/share/extension/' /usr/bin/install -c -m 644 .//pg_cron--1.2--1.3.sql .//pg_cron--1.0--1.1.sql .//pg_cron--1.1--1.2.sql pg_cron--1.0.sql '/u01/app/postgres/pgproduct/pg-13.1/share/extension/' /usr/bin/mkdir -p '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode/pg_cron' /usr/bin/mkdir -p '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode'/pg_cron/src/ /usr/bin/install -c -m 644 src/pg_cron.bc '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode'/pg_cron/src/ /usr/bin/install -c -m 644 src/job_metadata.bc '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode'/pg_cron/src/ /usr/bin/install -c -m 644 src/misc.bc '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode'/pg_cron/src/ /usr/bin/install -c -m 644 src/task_states.bc '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode'/pg_cron/src/ /usr/bin/install -c -m 644 src/entry.bc '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode'/pg_cron/src/ cd '/u01/app/postgres/pgproduct/pg-13.1/lib/bitcode' && /usr/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_cron.index.bc pg_cron/src/pg_cron.bc pg_cron/src/job_metadata.bc pg_cron/src/misc.bc pg_cron/src/task_states.bc pg_cron/src/entry.bc [postgres@ocm199 pg_cron-main]$
After a successful compilation, you should see a few pg_cron files in the $PGHOME/share/extension directory.
[postgres@ocm199 pg_cron-main]$ cd $PGHOME/share/extension [postgres@ocm199 extension]$ [postgres@ocm199 extension]$ ls -l | grep cron -rw-r--r--. 1 postgres postgres 101 Nov 13 03:17 pg_cron--1.0--1.1.sql -rw-r--r--. 1 postgres postgres 2034 Nov 13 03:17 pg_cron--1.0.sql -rw-r--r--. 1 postgres postgres 155 Nov 13 03:17 pg_cron--1.1--1.2.sql -rw-r--r--. 1 postgres postgres 1407 Nov 13 03:17 pg_cron--1.2--1.3.sql -rw-r--r--. 1 postgres postgres 121 Nov 13 03:17 pg_cron.control
After we have compiled pg_cron successfully, we need to adjust the postgresql.conf and load the extension into the pg_cron database.
$ cat postgresql.conf ... ... shared_preload_libraries = 'pg_cron' # pg_cron extension cron.database_name='pg_cron'
[postgres@ocm199 PTDB01A]$ psql
psql (13.1 - Pt(78)DB PostgreSQL 13.1 JIT Build)
Type "help" for help.
(postgres@[local]:55001)[postgres]> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------------+----------+-------------+-------------+-----------------------
pg_cron | cron_admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ptdb | ptdb_admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
slvdb | slvdb_admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
(postgres@[local]:55001)[postgres]> \c pg_cron
You are now connected to database "pg_cron" as user "postgres".
(postgres@[local]:55001)[pg_cron]>
(postgres@[local]:55001)[pg_cron]> create extension pg_cron;
CREATE EXTENSION
(postgres@[local]:55001)[pg_cron]>
After creating the extension, you should see a new schema called cron and few new objects in it.
(postgres@[local]:55001)[pg_cron]> \dn
List of schemas
Name | Owner
--------+----------
cron | postgres
public | postgres
(2 rows)
(postgres@[local]:55001)[pg_cron]> set search_path=cron;
SET
(postgres@[local]:55001)[pg_cron]> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-----------------+-------+----------+-------------+------------+-------------
cron | job | table | postgres | permanent | 8192 bytes |
cron | job_run_details | table | postgres | permanent | 8192 bytes |
(2 rows)
Change the ownership of the cron objects to user cron_admin
(postgres@[local]:55001)[postgres]> \c pg_cron
You are now connected to database "pg_cron" as user "postgres".
(postgres@[local]:55001)[pg_cron]> set search_path=cron;
SET
(postgres@[local]:55001)[pg_cron]> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-----------------+-------+----------+-------------+------------+-------------
cron | job | table | postgres | permanent | 8192 bytes |
cron | job_run_details | table | postgres | permanent | 8192 bytes |
(2 rows)
(postgres@[local]:55001)[pg_cron]> GRANT USAGE ON SCHEMA cron TO cron_admin;
GRANT
(postgres@[local]:55001)[pg_cron]> ALTER TABLE cron.job OWNER TO cron_admin;
ALTER TABLE
(postgres@[local]:55001)[pg_cron]> ALTER TABLE cron.job_run_details OWNER TO cron_admin;
ALTER TABLE
(postgres@[local]:55001)[pg_cron]> ALTER SEQUENCE cron.jobid_seq OWNER TO cron_admin;
ALTER SEQUENCE
(postgres@[local]:55001)[pg_cron]> ALTER SEQUENCE cron.runid_seq OWNER TO cron_admin;
ALTER SEQUENCE
(postgres@[local]:55001)[pg_cron]> ALTER FUNCTION cron.job_cache_invalidate() OWNER TO cron_admin;
ALTER FUNCTION
(postgres@[local]:55001)[pg_cron]> ALTER FUNCTION cron.schedule (job_name name, schedule text, command text) OWNER TO cron_admin;
ALTER FUNCTION
(postgres@[local]:55001)[pg_cron]> ALTER FUNCTION cron.schedule (schedule text, command text) OWNER TO cron_admin;
ALTER FUNCTION
(postgres@[local]:55001)[pg_cron]> ALTER FUNCTION cron.unschedule (job_id bigint) OWNER TO cron_admin;
ALTER FUNCTION
(postgres@[local]:55001)[pg_cron]> ALTER FUNCTION cron.unschedule (job_name name) OWNER TO cron_admin;
ALTER FUNCTION
Now we create a big table in each database. This has nothing to do with pg_cron. This is only to have some data to play with.
[postgres@ocm199 PTDB01A]$ psql --username=ptdb_admin --dbname=ptdb
Password for user ptdb_admin:
psql (13.1 - Pt(78)DB PostgreSQL 13.1 JIT Build)
Type "help" for help.
(ptdb_admin@[local]:55001)[ptdb]> create schema ptdb;
CREATE SCHEMA
(ptdb_admin@[local]:55001)[ptdb]> set search_path=ptdb;
SET
(ptdb_admin@[local]:55001)[ptdb]> CREATE TABLE ptdb_big (n1 bigint, n2 double precision, n3 double precision);
CREATE TABLE
(ptdb_admin@[local]:55001)[ptdb]> INSERT INTO ptdb_big (n1, n2, n3)
> SELECT round(random()*10), random(), random()*142
> FROM generate_series(1, 20000000) s(i);
INSERT 0 20000000
(ptdb_admin@[local]:55001)[ptdb]> CREATE INDEX i1 ON ptdb_big (n1);
CREATE INDEX
(ptdb_admin@[local]:55001)[ptdb]> ANALYZE VERBOSE ptdb_big;
INFO: analyzing "ptdb.ptdb_big"
INFO: "ptdb_big": scanned 30000 of 127389 pages, containing 4710000 live rows and 0 dead rows; 30000 rows in sample, 20000073 estimated total rows
ANALYZE
(ptdb_admin@[local]:55001)[ptdb]> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+----------+-------+------------+-------------+--------+-------------
ptdb | ptdb_big | table | ptdb_admin | permanent | 996 MB |
(1 row)
(ptdb_admin@[local]:55001)[ptdb]>
Do the same for database slvdb
[postgres@ocm199 PTDB01A]$ psql --username=slvdb_admin --dbname=slvdb
Password for user slvdb_admin:
psql (13.1 - Pt(78)DB PostgreSQL 13.1 JIT Build)
Type "help" for help.
(slvdb_admin@[local]:55001)[slvdb]> create schema slvdb;
CREATE SCHEMA
(slvdb_admin@[local]:55001)[slvdb]> set search_path=slvdb;
SET
(slvdb_admin@[local]:55001)[slvdb]> CREATE TABLE slvdb_big (n1 bigint, n2 double precision, n3 double precision);
CREATE TABLE
(slvdb_admin@[local]:55001)[slvdb]> INSERT INTO slvdb_big (n1, n2, n3)
> SELECT round(random()*10), random(), random()*142
> FROM generate_series(1, 20000000) s(i);
INSERT 0 20000000
(slvdb_admin@[local]:55001)[slvdb]> CREATE INDEX i1 ON slvdb_big (n1);
CREATE INDEX
(slvdb_admin@[local]:55001)[slvdb]> ANALYZE VERBOSE slvdb_big;
INFO: analyzing "slvdb.slvdb_big"
INFO: "slvdb_big": scanned 30000 of 127389 pages, containing 4710000 live rows and 0 dead rows; 30000 rows in sample, 20000073 estimated total rows
ANALYZE
(slvdb_admin@[local]:55001)[slvdb]> \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-----------+-------+-------------+-------------+--------+-------------
slvdb | slvdb_big | table | slvdb_admin | permanent | 996 MB |
(1 row)
(slvdb_admin@[local]:55001)[slvdb]>
Now we can start scheduling jobs as user cron_admin for database ptdb
(cron_admin@[local]:55001)[pg_cron] SELECT cron.schedule('ptdb-minute-analyze', '* * * * *', 'ANALYZE ptdb.ptdb_big');
schedule
----------
1
(1 row)
From the alert.log
2020-11-14 03:06:33.399 EST [11014] LOG: pg_cron scheduler started 2020-11-14 03:09:00.005 EST [11014] LOG: cron job 1 starting: ANALYZE ANALYZE ptdb.ptdb_big 2020-11-14 03:09:00.033 EST [11014] LOG: cron job 1 connection failed
As you might notice, the wrong database and username are in the job table
(cron_admin@[local]:55001)[pg_cron]> select jobid, command, nodename, nodeport, database, username from job;
jobid | command | nodename | nodeport | database | username
-------+-----------------------+-----------+----------+----------+------------
2 | ANALYZE ptdb.ptdb_big | localhost | 55001 | pg_cron | cron_admin
(1 row)
Looks much better
(cron_admin@[local]:55001)[pg_cron]> update job set database='ptdb' where jobid = 2;
UPDATE 1
(cron_admin@[local]:55001)[pg_cron]> update job set username='ptdb_admin' where jobid = 2;
UPDATE 1
(cron_admin@[local]:55001)[pg_cron]> select jobid, command, nodename, nodeport, database, username from job;
jobid | command | nodename | nodeport | database | username
-------+-----------------------+-----------+----------+----------+------------
2 | ANALYZE ptdb.ptdb_big | localhost | 55001 | ptdb | ptdb_admin
(1 row)
The job still failed
2020-11-14 03:19:00.003 EST [11014] LOG: cron job 2 starting: ANALYZE ptdb.ptdb_big 2020-11-14 03:19:00.007 EST [11014] LOG: cron job 2 connection failed
Adjust the .pgpass file
[postgres@ocm199 ~]$ cat .pgpass #hostname:port:database:username:password localhost:55001:ptdb:ptdb_admin:ptdb_admin
Test if it works
[postgres@ocm199 ~]$ psql --username=ptdb_admin --dbname=ptdb psql (13.1 - Pt(78)DB PostgreSQL 13.1 JIT Build) Type "help" for help. (ptdb_admin@[local]:55001)[ptdb] exit [postgres@ocm199 ~]$
From the alert.log
2020-11-14 03:20:00.003 EST [11014] LOG: cron job 2 starting: ANALYZE ptdb.ptdb_big 2020-11-14 03:20:05.678 EST [11014] LOG: cron job 2 COMMAND completed: ANALYZE
Cool. It worked. Now we can repeat the same steps for the slvdb and start scheduling jobs as user cron_admin for database slvdb
[postgres@ocm199 ~]$ cat .pgpass #hostname:port:database:username:password localhost:55001:ptdb:ptdb_admin:ptdb_admin localhost:55001:slvdb:slvdb_admin:slvdb_admin
(cron_admin@[local]:55001)[pg_cron]> SELECT cron.schedule('slvdb-minute-analyze', '* * * * *', 'ANALYZE slvdb.slvdb_big');
schedule
----------
3
(1 row)
(cron_admin@[local]:55001)[pg_cron]> select jobid, command, nodename, nodeport, database, username from job;
jobid | command | nodename | nodeport | database | username
-------+-------------------------+-----------+----------+----------+------------
2 | ANALYZE ptdb.ptdb_big | localhost | 55001 | ptdb | ptdb_admin
3 | ANALYZE slvdb.slvdb_big | localhost | 55001 | pg_cron | cron_admin
(2 rows)
(cron_admin@[local]:55001)[pg_cron]> update job set database='slvdb' where jobid = 3;
UPDATE 1
(cron_admin@[local]:55001)[pg_cron]> update job set username='slvdb_admin' where jobid = 3;
UPDATE 1
(cron_admin@[local]:55001)[pg_cron]> select jobid, command, nodename, nodeport, database, username from job;
jobid | command | nodename | nodeport | database | username
-------+-------------------------+-----------+----------+----------+-------------
2 | ANALYZE ptdb.ptdb_big | localhost | 55001 | ptdb | ptdb_admin
3 | ANALYZE slvdb.slvdb_big | localhost | 55001 | slvdb | slvdb_admin
(2 rows)
(cron_admin@[local]:55001)[pg_cron]>
From the alert.log
2020-11-14 03:23:00.002 EST [11014] LOG: cron job 2 starting: ANALYZE ptdb.ptdb_big 2020-11-14 03:23:00.006 EST [11014] LOG: cron job 3 starting: ANALYZE slvdb.slvdb_big 2020-11-14 03:23:00.439 EST [11014] LOG: cron job 2 COMMAND completed: ANALYZE 2020-11-14 03:23:05.886 EST [11014] LOG: cron job 3 COMMAND completed: ANALYZE
Ready.
Conclusion
With a few workarounds, you can schedule jobs for different databases and different users with pg_cron. The trick essentially is to create a separate pg_cron database which holds only the pg_cron extension. Besides that, you are very flexible as well. For example, if you need to drop one of the databases, the pg_cron jobs for all others still work.