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!

Coycoyle

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.

Coycoyle

Hi Martina. Please have a look at github.com/citusdata/pg_cron/issues/66. My case is almost similar to yours but I am trying to use password (md5) auth. I wouldn't like to ask a new question. Tia – Showcase

Answer 2

Two things:

• check the regular pg log (configured in postgresql.conf)
• run select * from cron.job_run_details

Octane

Answer 3

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.

Litter

Thanks a lot, yes we have full control of the Linux System and this is a working solution. It would still be useful to be able to schedule jobs from the db server, so we are also waiting for other suggestions. – Coycoyle