瀚高数据库安全版4.5.8系列使用pg_cron定时任务

文章目录环境文档用途详细信息环境系统平台Linux x86-64 Red Hat Enterprise Linux 7版本4.5.8文档用途本文档主要介绍瀚高数据库安全版中如何使用pg_cron插件适用版本是4.5.8系列pg_cron版本是1.3。详细信息pg_cron是瀚高数据库中基于CRON的一个简单的作业调度程序可以实现一些定时任务例如定时执行vacuum、批处理、刷新物化视图等同时支持跨库执行定时任务。下面介绍其详细的功能和使用。一、参数介绍参数名功能默认值生效机制cron.database_name此参数指定定时任务存储于哪个库中pg_cron支持跨库执行定时任务所以可以统一使用highgo库管理多个业务库的定时任务。highgo重启生效cron.use_background_workers控制执行定时任务的方式包括后台模式(on)和非后台模式(off)。后台模式是使用数据库内核提供的接口创建一个后台进程完成查询执行的操作这种方式不需要传入用户密码即可执行sql语句非后台模式是开启一个进程使用libpq提供的c函数连接到数据库执行sql语句此种方式需要指定用户名、密码、数据库。on重启生效cron.timezone时区参数可以自定义时区建议调整为PRC即东八区北京时间。PRC重启生效cron.host连接的主机名后台模式下该参数无效。localhost重启生效cron.max_running_jobs允许并行运行的最大定时任务数不能超过数据库参数max_worker_processes的值值得范围是0~7。5重启生效cron.log_run在cron.job_run_details表中记录任务运行的详细信息。on重启生效cron.log_statement用日志记录运行的sql语句。on重启生效二、安装和配置1.创建pg_cron之前postgresql.auto.conf文件中配置如下参数[rootlocalhost ~]# vi $PGDATA/postgresql.auto.confshared_preload_libraries 已配置的库文件, pg_cron # pg_cron依赖的预加载库必须配置 cron.database_name dbname # 配置创建pg_cron插件的数据库例如highgo、testdb cron.use_background_workers on cron.timezone PRC cron.max_running_jobs 5 # 默认5按需设定2.系统用户root下重启数据库生效[rootlocalhost ~]# pg_ctl restart3.创建pg_cron插件创建pg_cron插件需要关闭三权详细如下1syssso登录数据库关闭三权 highgoselectshow_secure_param();--如果hg_sepofpowers是off表示三权已关闭只执行第3步即可highgoselectset_secure_param(hg_sepofpowers,off);2系统用户root下重启数据库[rootlocalhost~]# pg_ctl restart3sysdba创建pg_cron插件 cron_dbcreateextension pg_cron;4syssso开启三权 highgoselectset_secure_param(hg_sepofpowers,on);5系统用户root下重启数据库[rootlocalhost~]# pg_ctl restart6syssso确认三权已开启 highgoselectshow_secure_param();--hg_sepofpowers变成on即可4.sysdba把cron模式的usage权限赋给普通用户cron_db grant usage on schema cron to 普通用户;表的属主是普通用户时对表的一些定时操作需要使用普通用户创建定时任务因为sysdba用户对表没有读写权限至此pg_cron的配置和创建完成。三、功能介绍1.创建定时任务pg_cron支持两种创建定时任务的方式1使用时间间隔的方式创建selectcron.job_create(任务名称,定时任务,时间间隔,开始时间);selectcron.job_create(定时任务,时间间隔,开始时间);selectcron.job_create(定时任务,时间间隔);注①如果任务名称已经创建过那么会修改已有的任务而不创建新的。需要特别注意如果是想创建新的定时任务则一定要保证任务名称从未创建过否则会覆盖已有任务。注②创建定时任务时如果不设定开始时间默认立刻执行一次建议设定一个开始时间以避开业务时间或者其他定时任务。2使用cron语法设定定时计划的方式创建selectcron.schedule(任务名称,定时计划,定时任务);selectcron.schedule(定时计划,定时任务);注如果任务名称已经创建过那么会修改已有的任务而不创建新的。需要特别注意如果是想创建新的定时任务则一定要保证任务名称从未创建过否则会覆盖已有任务。pg_cron的定时计划schedule的设置规则如下其中pg_cron计划使用标准的CRON语法*表示每个该时间运行指定数字则表示仅在这个时间运行如下所示。-- ┌───────────── 分钟 (0 - 59) -- │ ┌────────────── 小时 (0 - 23) -- │ │ ┌─────────────── 日期 (1 - 31) -- │ │ │ ┌──────────────── 月份 (1 - 12) -- │ │ │ │ ┌───────────────── 一周中的某一天 (0 - 6) (0 到 6 是 周日 到 周六或者 使用名称; 7 也是周日) -- │ │ │ │ │ -- * * * * *示例--每分钟执行一次插入selectcron.schedule(每分钟执行一次插入,* * * * *,insert into public.t1(v) values(test || clock_timestamp()::timestamp(0)::text););-- 每天上午9点40对表执行一次vacuumselectcron.schedule(每天上午9点40对表执行一次vacuum,40 09 * * *,vacuum analyze public.t1;);--每周六的晚上23点执行一次vacuumselectcron.schedule(每周六的晚上23点执行一次vacuum,00 23 * * 6,vacuum analyze public.t1;);--一周自动执行一次vacuumselectcron.job_create(一周执行一次vacuum操作,vacuum;,1 week,now());--每隔5秒向表t1中插入一条记录selectcron.job_create(每隔5秒向表t1中插入一条记录,insert into public.t1(v) values(test || clock_timestamp()::timestamp(0)::text);,5s,now());--每隔5分钟执行一次vacuum并设定开始时间为当天的晚上22点selectcron.job_create(vacuum analyze;,5 min,2025-08-07 22:00:00);2.查看定时任务查询cron.job表记录了所有的定时任务。示例cron_dbselect*fromcron.job;jobid|nodeport|is_hgjob|schedule|command|first_run_time|nodename|database|username|interval|nex t_run_time|active|jobname----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1|5866|f|*****|insertintopublic.t1(v)values(test||clock_timestamp()::timestamp(0)::text);||localhost|cron_db|cron_user|||t|每分钟执行一次插入2|5866|f|4009***|vacuumanalyzepublic.t1;||localhost|cron_db|cron_user|||t|每天上午9点40对表执行一次vacuum3|5866|f|0023**6|vacuumanalyzepublic.t1;||localhost|cron_db|cron_user|||t|每周六的晚上23点执行一次vacuum4|5866|t||vacuum;|2025-08-0714:34:46.56445108|localhost|cron_db|cron_user|7days|2025-08-1414:34:47.30032308|t|一周执行一次vacuum操作5|5866|t||insertintopublic.t1(v)values(test||clock_timestamp()::timestamp(0)::text);|2025-08-0714:34:53.68329708|localhost|cron_db|cron_user|00:00:05|2025-08-0714:36:34.73725708|t|每隔5秒向表t1中插入一条记录(5行记录)3.修改定时任务pg_cron 1.3版本没有提供修改定时任务的函数可以使用创建定时任务的函数通过传入相同的任务名称实现修改功能。但是必须使用执行该定时任务的用户去修改即cron.job表中的username。示例--根据任务名称查询出任务信息cron_dbselect*fromcron.jobwherejobname每天上午9点40对表执行一次vacuum;-[RECORD1]----------------------------------jobid|2nodeport|5866is_hgjob|f schedule|4009***command|vacuumanalyzepublic.t1;first_run_time|nodename|localhostdatabase|cron_db username|cron_userinterval|next_run_time|active|t jobname|每天上午9点40对表执行一次vacuum--修改定时计划为每天晚上22点30cron_dbselectcron.schedule(每天上午9点40对表执行一次vacuum,30 22 * * *,vacuum analyze public.t1;);-[RECORD1]schedule|2--查看cron.job表定时计划更新了cron_dbselect*fromcron.jobwherejobname每天上午9点40对表执行一次vacuum;-[RECORD1]----------------------------------jobid|2nodeport|5866is_hgjob|f schedule|3022***command|vacuumanalyzepublic.t1;first_run_time|nodename|localhostdatabase|cron_db username|cron_userinterval|next_run_time|active|t jobname|每天上午9点40对表执行一次vacuum4.查看定时任务执行情况cron.job_run_details表记录了定时任务的执行情况包括开始时间、结束时间、执行结果等。示例cron_dbselect*fromcron.job_run_details;-[RECORD1]------------------------------------------------------------------------------------jobid|1runid|22job_pid|3425database|cron_db username|cron_user command|insertintopublic.t1(v)values(test||clock_timestamp()::timestamp(0)::text);status|succeeded return_message|INSERT01start_time|2025-08-0714:30:00.82377508end_time|2025-08-0714:30:00.83079085.删除定时任务使用cron.unschedule函数支持按照jobid或者job名称删除。按照job名称删除时需要使用执行定时任务的用户删除否则报错而jobid可以使用sysdba用户删除因有时也存在job名称为空的情况所以推荐按照jobid删除。示例cron_dbselectcron.unschedule(10);unschedule------------t(1行记录)cron_dbselectcron.unschedule(每隔5秒向表t1中插入一条记录);unschedule------------t(1行记录)6.跨库创建定时任务pg_cron 的1.3版本没有提供跨库创建定时任务的函数但是可以通过更新cron.job表中的database和username实现此功能。需要使用sysdba用户执行。示例--更新定时任务的数据库和用户cron_db# update cron.job set databasecron_db_02,usernamecron_user_02 where jobid3;UPDATE1--查看修改后的定时任务cron_db# select * from cron.job where jobid3;-[RECORD1]---------------------------------jobid|3nodeport|5866is_hgjob|f schedule|*****command|vacuumanalyzepublic.t1;first_run_time|nodename|localhostdatabase|cron_db_02 username|cron_user_02interval|next_run_time|active|t jobname|每周六的晚上23点执行一次vacuum--查看定时任务执行结果跨库执行成功cron_db# select * from cron.job_run_details where jobid3 order by end_time desc;-[RECORD1]------------------------------------jobid|3runid|780job_pid|19554database|cron_db_02 username|cron_user_02 command|vacuumanalyzepublic.t1;status|succeeded return_message|VACUUM start_time|2025-08-0809:38:00.24230708end_time|2025-08-0809:38:00.26024408