PostgreSQL 定时任务

露秋

好奇者号

参考

在 PostgreSQL 的基本安装中并没有定时任务这一项，必须和 PostGis 一样通过组件的方式安装，该组件的名字叫 PgAgent，安装了此组件才能在 PostgreSQL 中创建定时任务。

PostgreSQL 版本为 9.6 ，PgAgent 版本为 3.4 。

安装 PgAgent

在网上可以看到大部分文章都是说通过安装 PostgreSQL 的 StackBuilder 来安装 PgAgent。但是不幸的是当打开 StackBuilder 时，在工具列表中并没有发现 PgAgent 工具

在网上也没有找到解释为什么没有这个工具，而且官方的安装说明还比较有误导性。在官网的 PgAgent 工具下载列表中并没有给出单独的下载地址，必须要通过 StackBuilder 工具来安装，这就成了个死循环。参考 Unable to install pgagent with application stack builder 中第一个回答给出的下载地址，可以下载到单独的安装文件，下载下来安装即可。

第一步选择安装路径，并确定是否使用升级模式安装，如果是首次安装可以不用勾选，如果是安装新版本可以选择升级安装模式

第二步验证 PostgreSQL 的相关安装信息，按照安装 PostgreSQL 的相关配置填写即可

然后下一步，如果之前安装过 PgAgent 并卸载了，卸载的过程中并不会把安装 PgAgent 时创建的库删除，因此在此安装的时候会提示，如果安装的版本一致，直接复用先前的库即可

 A 'pgagent' schema of the correct version was found in the 'postgres'database and will be reused by this installation.

在之后填入 postgres 账户的密码，之后进入安装过程，等待安装完成即可。

接下来进行 PgAgent 服务的安装过程，此过程会在 postgres 中创建一个 pgagent 数据库。

有了 pg就可以做定时任务。

PgAgent原理

此部分查看 pgAgent (PostgreSQL定时任务) 文章的原理部分，此处只做摘录

原理图

流程：
1、创建进程，创建pgagent进程以及一个session连接
2、完整性检查，通过连接去查询pgagent扩展是否有、版本号等是否符合要求
3、将session backendpid和主机名写入表pga_jobagent
4、检查job，jobnextrun<=now()，找到要执行的job
5、创建job session，pga_job表写入session pid, joblastrun
6、查询step，得到jstcode
7、创建step session，执行jstcode sql或者shelll
8、执行完，更新pga_job，触发器更新jobnextrun
9、循环 4~8步

PgAgent 的使用

安装完成 PgAgent 之后，打开 PgAdmin之后就可以看到 pgAgent Jobs 选项，之后的任务就要在此下面创建

这里以常见一个每分钟查询当前时间的定时任务。

第一步：创建 Job

在 pgAgent jobs 上右键，创建一个 job，打开创建 job 的对话框

基本信息 general 包含了任务的基本信息，其中包括

Name ：定时任务的名字
Enable? ：是否启动定时任务，可以用于定时任务的开启和关闭
Job Class ：定时任务的类别，包含 Routine Maintenance 、 Data Import 、 Data Export 、 Data Summarisation 、 Miscellaneous 等几种，这里选择默认的 Routing Maintenance
Host Agent ：运行 PgAgent 服务的主机，如果为空，表示任何机器都可以运行此 job
Comment ：当前定时任务的描述

第二步：设置 Steps

在 Steps 页签中可以设置任务的执行步骤。在右上角的页签中可添加步骤，点击添加，设置步骤的名字、是否启动、步骤的类型（SQL/Batch）、连接数据库的方式（Local/Remote）、错误时如何处理（Fail/Success/Ignore）。点击步骤左侧的编辑按钮可以对不走进行具体的编辑，也可以进行删除。

设置步骤的基本信息，就可以对步骤的详情进行编辑，设置具体的信息。编辑页面含有两个页签：

General ：步骤的基本信息，与上面的类似，但多了 Database 连接数据的选项，此处表示要连接的数据库，Job会在此数据库上运行， Connection string 表示通过字符串的方式连接到数据库，改选项对应了 Connection Type 中的 Remote 选项。

这里需要注意的是，使用 local 方式连接数库的时候有可能会出现 couldn't get a connection to the database 的情况，这里还是推荐使用 Remote 的方式连接，即设置连接字符串如下：
user=postgres host=localhost port=5432 dbname=hbyh password=<my password>

当我们把 Steps 中的连接方式改为 Local 选择数据库之后，就会出现数据库无法连接的情况

Code ：设置完成 General 就可以开始设置 Code 选项，这个就是我们定时任务要执行的语句，这个语句可以是非常简单的，想我们的查询当前时间 select current_timestamp ；也可以是非常复杂的SQL，如定时汇总数据并插入到另一张表。

第三步：设置 Schedules

设置好了具体的任务信息之后，就可以设置任务的定时调度。 Schedules 页签与 Steps 页签类似，首先添加一个定时调度，设置任务的名字，是否启用，开始时间，结束时间等。结束时间不设置表示一直执行

接下来就是编辑具体的信息，含有三个页签， General 填写基本信息与上面类似

最为主要的还是 Repeat 页签，这里具体设置定期执行的时间。在其中的描述中可看到是类似 cron 的风格

Schedules are specified using a cron-style format.
For each selected time or date element, the schedule will execute. e.g. To execute at 5 minutes past every hour, simply select ‘05’ in the Minutes list box.
Values from more than one field may be specified in order to further control the schedule. e.g. To execute at 12:05 and 14:05 every Monday and Thursday, you would click minute 05, hours 12 and 14, and weekdays Monday and Thursday.
For additional flexibility, the Month Days check list includes an extra Last Day option. This matches the last day of the month, whether it happens to be the 28th, 29th, 30th or 31st.

里面包含两大部分，每个部分包含子项：

Days

Week Days：表示每周几执行
Month Days：表示每月的几号执行
Month：表示那几个月执行

Times

Hours：表示一天中的那几个小时执行
Minutes：表示每个小时的那几分钟执行

默认情况下全选，如这里我们要每分钟执行，那么就需要吧 Minutes 的所有项都都选上，其他的默认，这样就表示每分钟执行一次。

第三个页签 Exceptions 表示排除那些时间不执行任务，设置了之后在这些时间点就不会执行。这里不做设置。

第四步：查看配置 SQL

设置好上面的任务之后，在 SQL 中就会自动生成上面的相关配置

 DO $$
 DECLARE
     jid integer;
     scid integer;
 BEGIN
 -- Creating a new job
 INSERT INTO pgagent.pga_job(
     jobjclid, jobname, jobdesc, jobhostagent, jobenabled
 ) VALUES (
     1::integer, 'query_current_time'::text, '定时查询当前时间'::text, ''::text, true
 ) RETURNING jobid INTO jid;
 -- Steps
 -- Inserting a step (jobid: NULL)
 INSERT INTO pgagent.pga_jobstep (
     jstjobid, jstname, jstenabled, jstkind,
     jstconnstr, jstdbname, jstonerror,
     jstcode, jstdesc
 ) VALUES (
     jid, 'query'::text, true, 's'::character(1),
     'user=postgres host=localhost port=5432 dbname=hbyh password=123456'::text, ''::name, 'f'::character(1),
 SELECT CURRENT_TIMESTAMP;
 '::text, '执行查询'::text
 -- Schedules
 -- Inserting a schedule
 INSERT INTO pgagent.pga_schedule(
     jscjobid, jscname, jscdesc, jscenabled,
     jscstart,     jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
 ) VALUES (
     jid, 'very minutes'::text, '每分钟执行一次'::text, true,
     '2020-06-02T15:47:42+08:00'::timestamp with time zone, 
     -- Minutes
     ARRAY[true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true]::boolean[],
     -- Hours
     ARRAY[false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false]::boolean[],
     -- Week days
     ARRAY[false,false,false,false,false,false,false]::boolean[],
     -- Month days