定时任务插件pg_cron多数据库支持方法

默认情况下pg_cron将定时任务相关的元数据存储在cron.database_name设置的数据库里，通常是postgres数据库。

在postgres数据库创建扩展之后，赋予用户访问cron模式的权限，普通用户dk即可在postgres数据库使用job。

create extension pg_cron;
GRANT USAGE ON SCHEMA cron TO dk;

例如添加每分钟插入一条随机数据到test表

create table test (id int,info text);

SELECT cron.schedule('* * * * *', $$insert into test values((random()*100)::int,'test');$$);

但是如果在其它database下使用job会遇到一些问题：

既然在PostgreSQL数据库里不能进行跨库访问，那我们是否可以在其它database下创建pg_cron扩展，然后再使用job呢？

答案是否定的，从前面全局的配置参数cron.database_name中可以看出端倪，下面的报错也能证明这一点：

mydb1=# create extension pg_cron;
ERROR:  can only create extension in database postgres
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 = 'mydb1' in postgresql.conf to use the current database.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE

这个问题社区有不少人提过issue，所幸从v1.4.0的更新日志中我们看到了新的接口方法：

Adds a cron.schedule_in_database function to schedule in a custom database, by @bdrouvotAWS

那从v1.4.0版本开始，我们可以使用schedule_in_database接口函数来添加多个数据库的支持，测试如下：

SELECT cron.schedule_in_database(
'mydb1_job1',
'* * * * *',
$$insert into test values((random()*100)::int,'test');$$,
'mydb1',
'dk'
);

再数据库mydb1添加任务，同理其它数据库类似。

新版本里可以通过新接口函数来处理，在issue也发现有人提供了另外一种解决方案：基于dblink的使用方式。

元数据库配置

首先元数据库需要配置cron模式及job表的update权限

GRANT update on cron.job to dk;

然后封装一个带database的schedule函数

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;

其它数据库配置

在用户自定义数据库里创建dblink扩展及权限设置

CREATE EXTENSION dblink SCHEMA public;
GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO dk;
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dk;

接下来在用户数据库mydb1、mydb2下创建pg_cron_schedule函数来访问元数据库里的our_cron_schedule函数

CREATE OR REPLACE FUNCTION public.pg_cron_schedule(job_name name, schedule text, command text)
RETURNS bigint
AS $$
DECLARE
    xsql TEXT;
    xsql_set_user TEXT;
    job_id BIGINT;
    dbname NAME;
    crondbname NAME;
    cronresult BIGINT;
  BEGIN
    crondbname := 'dbname=postgres user=dk port=1402';
    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;
$$ LANGUAGE plpgsql;

添加定时任务

最后在各自的用户数据库下使用自定义pg_cron_schedule函数添加任务：

SELECT pg_cron_schedule(
'mydb1_job1',
 '* * * * *', 
$$insert into test values((random()*100)::int,'test');$$
);

总结

1. 从v1.4.0版本开始可以使用schedule_in_database函数来支持多数据库任务。
2. 低版本可以借助dblink方案来实现。

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。

如果群二维码失效可以加我微信。