Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to schedule cron jobs in multiple Azure Database for PostgreSQL databases?
The microservices I am working on use multiple PostgreSQL databases on the same instance. Some of them require some cron jobs. I have decided to use the pg_cron PostgreSQL extension, but when trying to enable the extension on more than one database I have received the error:
ERROR: can only create extension in database postgres SQL state: P0001 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. Hint: Add cron.database_name = 'custom-db' in postgresql.conf to use the current database. Context: PL/pgSQL function inline_code_block line 4 at RAISE
I see that cron.database_name server parameter indeed indicates postgres. I can change this to point to my database, but this means that I will not be able to run jobs on other database. I fail to understand how this is supposed to work in a multi database server configuration.
Some suggest to use dblink, but this involves more setup than I think this should involve.
What is the proper way to use pg_cron extension in Azure Database for PostgreSQL instance?
1 answer
Azure Database for PostgreSQL by design limits the number of databases containing pg_cron jobs metadata to only one. This is can be specified in the cron.database_name server parameter (the default is postgres).
PostgreSQL has a pretty good isolation between the databases on the same instances, so running cross-database queries is not trivial (as opposed to SQL Server).
When enabling pg_cron a custom function allows to specify the database the job non-query runs against. Example:
SELECT cron.schedule_in_database('JobName','*/10 * * *
*','CALL custom_procedure();','custom-db');
0 comment threads
0 comment threads