pg_cron的一点优化

pg_cron简介

pg_cron 是一个简单的基于 cron 的 PostgreSQL（9.5 或更高版本）作业调度程序，作为扩展在数据库内部运行。它使用与常规 cron 相同的语法，但它允许您直接从数据库调度 PostgreSQL 命令。

是一个Hook插件，在postmaster启动时，通过 process_shared_preload_libraries()函数根据配置的插件名找到对应的lib，然后运行里边的pg_init()函数，做一些初始化。加载cron相关的配置参数，并注册bgwork，在maybe_start_bgworkers()时启动为pg_cron launcher bgworker。

在数据库里create extension pg_cron创建扩展，通过一些内置函数来操作job

通过cron.schedule()函数将job写入cron.job表中。在触发job时，通过读取cron.job的元数据，以libpq的方式和数据库server建立连接，执行job中的command。

以下我在testdb里创建一个定时任务，定时对表做vacuum

testdb=> SELECT cron.schedule('*/1 * * * *', $$vacuum analyze tbl_test$$);
 schedule 
----------
        1
(1 row)

testdb=> select * from cron.job;
 jobid |  schedule   |         command         | nodename  | nodeport | database | username | active | jobname 
-------+-------------+-------------------------+-----------+----------+----------+----------+--------+---------
     1 | */1 * * * * | vacuum analyze tbl_test | localhost |     5142 | testdb   | admin    | t      | 
(1 row)

存在的缺陷

这里其实是有缺陷的，执行job是通过获取cron.job元数据来连接数据库。
1、 那么当实例发生HA或者其他场景，实例实际的物理port发生变化，那就无法连接实例执行job了；
2、 如果实例物理port发生变化，元数据记录的port刚好对应同主机上其他实例，并且可以连通，那有误操作其他实例的风险；

手动模拟HA，物理端口发生变化场景：
这里备库的物理端口为5413，元数据记录的端口为5412

testdb=> select * from cron.job;
 jobid |  schedule   |         command         | nodename  | nodeport | database | username | active | jobname 
-------+-------------+-------------------------+-----------+----------+----------+----------+--------+---------
     1 | */1 * * * * | vacuum analyze tbl_test | localhost |     5142 | testdb   | admin    | t      | 
(1 row)

testdb=> select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

testdb=> show port;
 port 
------
 5143
(1 row)

当执行promote后，job开始执行，并报错无法连接实例。

postgres@NickCentos:pg14.2:5143 /data/pg14-2debug/standby]$pg_ctl promote -D /data/pg14-2debug/standby
waiting for server to promote.... done
server promoted
[postgres@NickCentos:pg14.2:5143 /data/pg14-2debug/standby]$cd pg_log
[postgres@NickCentos:pg14.2:5143 /data/pg14-2debug/standby/pg_log]$tail -10f postgresql-09.csv
2022-06-09 16:49:24.334 CST,,,21304,,62a18723.5338,10,,2022-06-09 13:37:39 CST,1/0,0,LOG,00000,"selected new timeline ID: 2",,,,,,,,,"","startup",,0
2022-06-09 16:49:24.383 CST,,,21304,,62a18723.5338,11,,2022-06-09 13:37:39 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,"","startup",,0
2022-06-09 16:49:24.400 CST,,,21301,,62a18723.5335,6,,2022-06-09 13:37:39 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,"","postmaster",,0
2022-06-09 16:51:00.003 CST,,,22075,,62a1b414.563b,12,,2022-06-09 16:49:24 CST,2/0,0,LOG,00000,"cron job 1 starting: vacuum analyze tbl_test",,,,,,,,,"","pg_cron launcher",,0
2022-06-09 16:51:00.006 CST,,,22075,,62a1b414.563b,13,,2022-06-09 16:49:24 CST,2/0,0,LOG,00000,"cron job 1 connection failed",,,,,,,,,"","pg_cron launcher",,0

那这里看起来，获取port的方式需要调整下，直接获取物理port去连接，而并非从元数据中获取。

优化方案

在cron_schedule创建job时，ScheduleCronJob()中从全局变量PostPortNumber获取端口号，写入cron.job；执行job时，加载cron.job表元数据从中获取port信息，这里个人觉得不怎么合理，直接从全局变量获取就行了。

PostPortNumber对应的就是guc参数port

在LoadCronJobList()时，TupleToCronJob()中修改job->nodePort = Int32GetDatum(PostPortNumber);

重新编译插件替换so并重启，验证成功

2022-06-09 21:46:21.393 CST,,,7107,,62a1f9ad.1bc3,1,,2022-06-09 21:46:21 CST,2/0,0,LOG,00000,"pg_cron scheduler started",,,,,,,,,"","pg_cron launcher",,0
2022-06-09 21:47:00.003 CST,,,7107,,62a1f9ad.1bc3,2,,2022-06-09 21:46:21 CST,2/0,0,LOG,00000,"cron job 1 starting: vacuum analyze tbl_test",,,,,,,,,"","pg_cron launcher",,0
2022-06-09 21:47:00.025 CST,,,7107,,62a1f9ad.1bc3,3,,2022-06-09 21:46:21 CST,2/0,0,LOG,00000,"cron job 1 COMMAND completed: VACUUM ",,,,,,,,,"","pg_cron launcher",,0