Many databases, possibility to have different cron schémas?

[mattbunter]

We have several databases in the same Postgres instance and we would like to 'split' them across crondb schemas, instead of having them all in the 'cron' schema. We tried creating a cron_test schema in crondb and using select * from cron_test.schedule but it didn't work.

ERROR: function cron_test.schedule(unknown, unknown, unknown) does not exist LINE 1: select cron_test.schedule('tester test schedule',' * * * * *...

Is there a quick and dirty work-around to this? We are doing to it 'secure' cron info between databases.

[SebastienCaunes]

+1 this is a must have

[zilti]

We have several databases as well, is there a reason you must have separate cronjob tables?

In any case, here is our approach. We have this as part of our postgresql init script for the postgres database:

CREATE SCHEMA extensions;
CREATE EXTENSION dblink SCHEMA extensions;

CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO "${pg_user}";
GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO "${pg_user}";
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO "${pg_user}";
CREATE OR REPLACE FUNCTION cron.our_cron_schedule(job_name name, schedule text, command text, database_name text) RETURNS bigint
AS
$$
DECLARE
    job_id bigint;
BEGIN
    SELECT cron.schedule(job_name, schedule, command) INTO job_id;
    UPDATE cron.job SET database = database_name WHERE jobid = job_id;
    RETURN job_id;
END;
$$ LANGUAGE plpgsql;

In the individual databases we then just have a scheduling function that accesses this "superfunction":

CREATE OR REPLACE FUNCTION public.pg_cron_schedule(job_name name, schedule text, command text)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
  DECLARE
    xsql TEXT;
    xsql_set_user TEXT;
    job_id BIGINT;
    dbname NAME;
    crondbname NAME;
    cronresult BIGINT;
  BEGIN
    crondbname := 'dbname=postgres user=' || current_role || ' port=' || inet_server_port();
    PERFORM dblink_connect_u('cronconn',crondbname);
    SELECT current_database() INTO dbname;
    xsql := format('SELECT cron.our_cron_schedule(%L,%L,%L,%L);',job_name,schedule,command,dbname);
    EXECUTE 'SELECT * FROM dblink($1,$2,true) f(col1 BIGINT);'
      USING 'cronconn',xsql
      INTO cronresult;
    PERFORM dblink_disconnect('cronconn');
    RETURN cronresult;
  END
$function$
;

[citizenfish]

Worth noting that the function schedule_in_database in version 1.4 will remove the need for the update

Also, how are you authenticating with dblink_connect_u without a password?