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.
- 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'
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
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 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!