进阶数据库系列（二十二）：PostgreSQL 数据库作业调度工具 pgAgent
pgAgent 概述
pgAgent 是 Postgres 数据库的作业调度代理，能够运行多步批处理或 shell 脚本以及复杂调度的 SQL 任务。在 pgAdmin v1.9 之前，pgAgent作为 pgAdmin 的一部分提供，从 pgAdmin v1.9 开始，pgAgent作为单独的应用程序提供。
pgAgent 在 Unix 系统上作为守护进程运行，在 Windows 系统上作为服务运行。在大多数情况下，它将在数据库服务器上运行。
需要注意的是，pgAgent需要一些数据库表和其他对象的支持,因此需要先安装pgAgent数据库。
pgAgent 安装
环境
数据库服务器:Cenos7+PG13+pgAgent 客户端服务器:Win10+pgAdmin4
客户端安装
pgAdmin 安装包下载：https://www.pgadmin.org/download/pgadmin-4-windows/
Windows 图形化界面安装（前面章节介绍过），在此不再赘述。
pgAdmin 详情介绍可参考官网：https://www.pgadmin.org/docs/pgadmin4/development/index.html
服务器端安装
数据库安装可参考：，在此不再赘述。
源码安装时，当 PostgreSQL 版本高于9.1， pgAgent 版本高于v 3.4.0，即可以扩展形式进行安装，即源码编译安装完成后，使用下列语句创建扩展即可：
create extension pgagent;
rpm 安装及yum 安装均需要使用sql脚本初始化字典数据，生成名为 pgagent 的 schema，源码创建完扩展后会自动生成该 schema。源码安装对系统环境要求较高，可能需要更新 cmake、wxGTK、boost 等系统组件版本，耗时较长，建议优先选择 yum 或 rpm 安装。
pgAgent 采用 yum 安装方式，外网不通可选择 rpm 包下载：https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.8-x86_64/)安装，安装完成后配置同下。
配置 yum 源
yum 源 rpm 包下载：https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/
rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
搜索可用安装包
yum search pgagent -y
安装与数据库版本一致的安装包
yum install pgagent_13.x86_64 -y
创建扩展语言
创建扩展语言
create language plpgsql;
初始化数据库字典
su - postgres
\i /usr/local/share/pgagent.sql
pgAgent 配置
服务器端启动 pgAgent 守护进程
因守护进程通过 ps 命令可查询到相关命令，显示的数据库密码存在安全隐患，建议将密码写入密码文件中调用。
--创建密码文件
vi ~/.pgpass
localhost:5432:postgres:postgres:postgres
chmod 600 ~/.pgpass
--启动守护进程
pgagent_13 port=5432 dbname=postgres user=postgres
数据库中创建测试表
create table t1(id int,name varchar);
select * from t1;
客户端 pgAdmin 配置
连入 pgAdmin
创建 pgAgent 任务
host=192.168.100.115 port=5432 dbname=postgres connect_timeout=10 password=postgres
insert into t1 values(1,'a');
设置完成上述配置后，点击右下角保存即可。
查看定时运行情况
上述统计信息可查询运行次数及起始时间。
修改定时任务
属性进入编辑页面步骤同创建时一样，可自行修改。
卸载
客户端 pgAdmin 停止计划任务
上述修改定时任务中，选择停止后保存即可。
服务器端 kill 守护进程
kill -9 `ps -ef|grep pgagent|grep -v grep|awk '{print $2}'`
服务器端删除 schema，扩展
drop schema pgagent cascade;
drop extension plpgsql cascade;
移除 yum 包
yum remove -y pgagent_13.x86_64
pg_cron 概述
pg_cron 是一个简单的基于 cron 的 PostgreSQL(9.5或更高版本)作业调度程序，它在数据库中作为扩展运行。它使用与常规 cron 相同的语法，但它允许你直接从数据库调度 PostgreSQL 命令。
pg_cron 参数
postgres=# select name,setting from pg_settings where name like '%cron%';
name | setting
-----------------------------+-----------
cron.database_name | postgres
cron.enable_superuser_jobs | on
cron.host | localhost
cron.log_min_messages | warning
cron.log_run | on
cron.log_statement | on
cron.max_running_jobs | 32
cron.use_background_workers | off
(8 rows)
database_name：#定时任务所在数据库。
enable_superuser_jobs：#超级用户是否可启动定时任务。
host：#执行定时任务的主机名。
log_min_messages：#日志级别。
log_run：#定时任务执行信息是否记录到 job_run_details 表中。
log_statement：#执行任务前，是否将sql打印到日志。
max_running_jobs：#可以同时运行的job数量。
扩展安装后会自动创建名为 cron 的模式，并在该模式下生成 cron 相关的字典表。
--字典表 cron.job
Table "cron.job"
Column | Type | Default | Description
----------+---------+-------------------------------------+----------------------
jobid | bigint | nextval('cron.jobid_seq'::regclass) | 定时任务 ID
schedule | text | | 定时计划
command | text | | 定时任务作业内容
nodename | text | 'localhost'::text | 执行定时任务的主机名
nodeport | integer | inet_server_port() | 执行定时任务的端口号
database | text | current_database() | 执行定时任务的数据库
username | text | CURRENT_USER | 执行定时任务的用户
active | boolean | true | 定时任务状态
jobname | name | | 定时任务名字
--字典表 cron.job_run_details
Table "cron.job_run_details"
Column | Type | Default | Description
----------------+--------------------------+-------------------------------------+--------------------------
jobid | bigint | | 定时任务 ID
runid | bigint | nextval('cron.runid_seq'::regclass) | 定时计划运行 ID
job_pid | integer | | 定时任务 PID
database | text | | 运行定时任务的数据库名
username | text | | 运行定时任务的数据库用户
command | text | | 运行定任务的作业内容
status | text | | 定时任务的运行结果
return_message | text | | 定时任务返回信息
start_time | timestamp with time zone | | 定时任务开始时间
end_time | timestamp with time zone | | 定时任务结束时间
pg_cron 语法
schedule 创建定时任务
SELECT cron.schedule('<定时计划>', '<定时任务>');
SELECT cron.schedule('<定时任务名称>', '<定时计划>', '<定时任务>');
SELECT cron.schedule('<定时任务名称>', '<定时计划>', '<定时任务>', '<执行数据库>', '<执行用户>', '<任务是否启用>');
<定时计划> #定时任务的执行间隔时间，策略。
<定时任务> #定时任务的执行作业。
<执行数据库> #默认值为空，表示在postgres库执行。
<执行用户> #默认值为空，表示使用当前账号执行。
<任务是否启用> #默认值为true，表示启用该定时任务。
<定时计划>语法
使用标准的操作系统 cron 语法：
┌───────────── 分钟 (取值为0 - 59)
│ ┌────────────── 小时 (取值为0 - 23)
│ │ ┌─────────────── 日期 (取值为1 - 31)
│ │ │ ┌──────────────── 月份 (取值为1 - 12)
│ │ │ │ ┌───────────────── 一周中的一天 (取值为0 - 6，0表示周日)
│ │ │ │ │
│ │ │ │ │
│ │ │ │ │
* * * * *
* #表示任意时间都可以运行。
特定数字 #表示仅在这个时间运行。
, #表示分隔多个指定时间。
- #表示时间范围。
/ #表示范围间隔。
alter_job 修改定时任务
SELECT cron.alter_job('<定时任务ID>, '<定时计划>', '<定时任务>', '<执行数据库>', '<执行用户>', '<任务是否启用>');
删除定时任务
SELECT cron.unschedule(<定时任务ID>);
SELECT cron.unschedule('<定时任务名称>');
示例
下载二进制安装包编译安装
https://gitee.com/mirrors/pg_cron/releases ，上传数据库服务器，解压并编译安装。
mv pg_cron-v1.4.1.zip /home/postgres/
su - postgres
unzip pg_cron-v1.4.1.zip
cd pg_cron-v1.4.1
make && make install
数据库内创建扩展
alter system set shared_preload_libraries=pg_cron;
pg_ctl restart
show shared_preload_libraries;
create extension pg_cron;
启用定时任务
--配置本地免密登录（下列方式均可）
a. pg_hba.conf 中本地连接改为 trust。
b. 配置密码文件~/.pgpass。
c. 设置 PGPASSWORD 环境变量。
--构建测试表
create table t1(id serial primary key,name varchar(10));
--创建定时任务（每2分钟向t1表插入一条数据）
SELECT cron.schedule('每2分钟向t1表插入一条数据','*/2 * * * *', $$insert into public.t1(name) values(repeat(chr(int4(random()*26)+65),4))$$);
--查看定时任务
select * from cron.job;
select * from cron.job_run_details;
管理定时任务
--修改定时任务
SELECT cron.alter_job(1,'* */1 * * *',$$insert into public.t1(name) values('ssss')$$,null,'每1小时向t1表插入一条数据','f');
--停止定时任务
SELECT cron.alter_job(1,null,null,null,null,'f');