Pg_cron crontab log

5
votes

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

Citusdata pg_cron project

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!

2
postgresqlcronscheduled-tasks

2 Answers

3
votes

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.

0
votes

Assuming you have full control of the Linux system, you can use this in root's crontab:

su -c "YourqueryHere" postgres

So you don't need pg_cron unless you need to be able to schedule jobs right from the db server.