pg_cron
pg_cron copied to clipboard
citusdata
Multiple database
# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'
How can I use pg_cron for multiple database , Assume I have foo and bar databases
@sm2017 , hi ! From source code i can see that cron.database_name is for single database only:
https://github.com/citusdata/pg_cron/blob/master/src/pg_cron.c#L149
@sm2017 you can do Create extension pg_cron per logical database and it will work.
cron.database_name is used as databases for metadata tables:
https://github.com/citusdata/pg_cron#setting-up-pg_cron
By default, the pg_cron background worker expects its metadata tables to be created in the "postgres" database.
@anikin-aa
[2019-10-15 09:29:26] [P0001] ERROR: can only create extension in database postgres
[2019-10-15 09:29:26] Detail: Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database.
[2019-10-15 09:29:26] Hint: Add cron.database_name = 'postgres' in postgresql.conf to use the current database.
[2019-10-15 09:29:26] Where: PL/pgSQL function inline_code_block line 4 at RAISE
hm, that is strange, i thought that is possible to create pg_cron in multiple databases, but it's actually not.
I've discovered, that you can do queries on other databases with version 1.2., like in https://github.com/citusdata/pg_cron/tree/beta-2.0#advanced-usage
But you can still only use one scheduling table / one db with the cron.schedule() function
Actually if you want to schedule jobs in multiple databases here is simple setup:
CREATE DATABASE pg_cron
postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'pg_cron' # Use separate database for pg_cron
Suppose you have 2 separate databases db1 and db2, in database pg_cron in table cron.jobs there is a field database in which you can specify on which db is job executed:
But how do I submit a job as a user in db1 (e.g. public) using this setup?
While pg_cron is still managed though a single database, with 1.4 jobs can be scheduled to run in other databases via cron.schedule_in_database. Additionally, existing jobs can be changed to run in a different db with cron.alter_job.
I guess this issue can be closed now.
