Pg_cron crontab log

Question

We're trying to configure periodic jobs in Postgresql. To do this, we have installed on linux machine, with postgres 9.6 running, the citusdata pg_cron project.

System information

• OS: Linux pg 4.4.0-72-generic #93-Ubuntu SMP
• PG: Postgres 9.6.3 installed from repo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main'

Citusdata pg_cron project

• https://github.com/citusdata/pg_cron

Following the instructions in the pg_cron repository, we set in postgresql.conf the configuration below

shared_preload_libraries = 'pg_cron'   
cron.database_name = 'our db_name'

Then, on db_name, we created the EXTENSION pg_cron

CREATE EXTENSION pg_cron;

and we scheduled our first postgres job:

SELECT cron.schedule('45 12 * * *', $$CREATE TABLE testCron AS Select 'Test Cron' as Cron$$);

So, jobid 1 is created and listed in table cron.job.

We expect that at 12:45 the command of the scheduled job will be launched.

But nothing happens.

testCron table is not created and we have no trace in any logs.

We have also defined LOG_FILE in /usr/src/pg_cron/include/pathnames.h to enable logging.

But, after re-compiling the project and restarting postgres service, we did not track log for pg_cron.

Can someone help us?

How can we enable logs for pg_cron to check scheduling result?

Thanks in advance!

Answer 1

To schedule jobs from the db server we'll need to enable trust authentication in pg_hba.conf for the user running the cron job. We'll also need to either run UPDATE cron.job SET nodename = '' to make pg_cron connect via a local (unix domain) socket or add host all all 127.0.0.1/32 in pg_hba.conf to allow access to the pg_cron background worker via a local TCP connection.

As a basic sanity check to see if logging is enabled, we run SELECT cron.schedule('* * * * *', 'SELECT 1') which will run SELECT 1 at the start of every minute and should show up in the regular postgres log.