Pg_cron crontab log issue

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.

The 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 the Postgres service, we did not track log for pg_cron.

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

As work around, we use this in root's crontab:

su -c "Sql_statement" postgres

but this require full control of the Linux system.

It would be useful to be able to schedule jobs from the db server.

Can someone help us?

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.