PostgreSQL 实现定时job执行(pgAgent)

PostgreSQL中定时job执行

业务分析

近期项目需要定期清理数据库中的多余数据，即每月1号删除指定表中一年以上的数据。

初步分析这种定时job可以使用一下两种技术实现：

Linux的crontab功能

pgadmin的pgAgent功能

为了避免数据库系统和操作系统的绑定，最后决定在数据库层面实现自动清理功能，即使用pgAgent功能

介绍

pgAgent是pgAdmin III工具中的一个插件，它在PgAdmin III v1.4版本中引用。

主要用于PostgreSQL的作业调度代理，能够在复杂的时间表上运行多步批处理shell和SQL任务。

需要注意的是，pgAgent需要一些数据库表和其他对象的支持,因此需要先安装pgAgent数据库。

环境

PostgreSQL:

操作系统：CentOS Linux release 7.3.1611 (Core)数据库系统： PostgreSQL 9.5.8IP： 192.168.230.134port: 5432

安装

安装pgAdmin III

yum install pgadmin3_95.x86_64 

输入：

[root@localhost ~]# yum install pgadmin3_95.x86_64 Loaded plugins: fastestmirror, langpacksbase                               | 3.6 kB 00:00:00   EPEL/x86_64/metalink                       | 6.4 kB 00:00:00   extras                              | 3.4 kB 00:00:00   pgdg95                              | 4.1 kB 00:00:00   updates                             | 3.4 kB 00:00:00   Loading mirror sPEeds From cached hostfile * base: mirrors.163.COM * epel: mirrors.tuna.tsinghua.edu.cn * extras: mirrors.163.com * updates: mirrors.163.comResolving Dependencies-->
     Running transaction check--->
     Package pgadmin3_95.x86_64 0:1.22.1-1.rhel7 will be installed-->
     PRocessing Dependency: wxGTK for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_xrc-2.8.so.0(WXU_2.8)(64bIT) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_stc-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_htML-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_core-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_aui-2.8.so.0(WXU_2.8.5)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_aui-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_adv-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu_XMl-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu_net-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu-2.8.so.0(WXU_2.8.5)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu-2.8.so.0(WXU_2.8)(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_xrc-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_stc-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_html-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_core-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_aui-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_gtk2u_adv-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu_xml-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu_net-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Processing Dependency: libwx_baseu-2.8.so.0()(64bit) for package: pgadmin3_95-1.22.1-1.rhel7.x86_64-->
     Running transaction check--->
     Package wxBase.x86_64 0:2.8.12-20.el7 will be installed--->
     Package wxGTK.x86_64 0:2.8.12-20.el7 will be installed-->
     Processing Dependency: libSDL-1.2.so.0()(64bit) for package: wxGTK-2.8.12-20.el7.x86_64-->
     Running transaction check--->
     Package SDL.x86_64 0:1.2.15-14.el7 will be installed-->
     Finished Dependency ResolutionDependencies Resolved======================================================================================== Package        Arch       Version         Repository    Size========================================================================================Installing: pgadmin3_95      x86_64      1.22.1-1.rhel7      pgdg95      3.2 MInstalling for dependencies: SDL          x86_64      1.2.15-14.el7      base       204 k wxBase        x86_64      2.8.12-20.el7      epel       588 k wxGTK         x86_64      2.8.12-20.el7      epel       2.9 MTransaction Summary========================================================================================Install 1 Package (+3 Dependent packages)total download Size: 6.9 MInstalled size: 27 MIs this ok [y/d/N]: yDownloading packages:(1/4): SDL-1.2.15-14.el7.x86_64.rpm               | 204 kB 00:00:00   (2/4): wxBase-2.8.12-20.el7.x86_64.rpm              | 588 kB 00:00:00   (3/4): wxGTK-2.8.12-20.el7.x86_64.rpm              | 2.9 MB 00:00:01   (4/4): pgadmin3_95-1.22.1-1.rhel7.x86_64.rpm           | 3.2 MB 00:00:48   ----------------------------------------------------------------------------------------Total                           147 kB/s | 6.9 MB 00:48   Running transaction checkRunning transaction testTransaction test succeededRunning transaction Installing : wxBase-2.8.12-20.el7.x86_64                     1/4  Installing : SDL-1.2.15-14.el7.x86_64                       2/4  Installing : wxGTK-2.8.12-20.el7.x86_64                      3/4  Installing : pgadmin3_95-1.22.1-1.rhel7.x86_64                  4/4  Verifying : SDL-1.2.15-14.el7.x86_64                       1/4  Verifying : wxGTK-2.8.12-20.el7.x86_64                      2/4  Verifying : wxBase-2.8.12-20.el7.x86_64                     3/4  Verifying : pgadmin3_95-1.22.1-1.rhel7.x86_64                  4/4 Installed: pgadmin3_95.x86_64 0:1.22.1-1.rhel7                          Dependency Installed: SDL.x86_64 0:1.2.15-14.el7 wxBase.x86_64 0:2.8.12-20.el7 wxGTK.x86_64 0:2.8.12-20.el7complete!

安装pgAgent

yum install pgagent_95

输入：

[root@localhost ~]# yum install pgagent_95Loaded plugins: fastestmirror, langpacksLoading mirror speeds from cached hostfile * base: mirrors.163.com * epel: mirrors.ustc.edu.cn * extras: mirrors.163.com * updates: mirrors.163.comResolving Dependencies-->
     Running transaction check--->
     Package pgagent_95.x86_64 0:3.4.0-9.rhel7 will be installed-->
     Finished Dependency ResolutionDependencies Resolved======================================================================================== Package       Arch       Version          Repository    Size========================================================================================Installing: pgagent_95      x86_64      3.4.0-9.rhel7       pgdg95      42 kTransaction Summary========================================================================================Install 1 PackageTotal download size: 42 kInstalled size: 151 kIs this ok [y/d/N]: yDownloading packages:pgagent_95-3.4.0-9.rhel7.x86_64.rpm               | 42 kB 00:00:02   Running transaction checkRunning transaction testTransaction test succeededRunning transaction Installing : pgagent_95-3.4.0-9.rhel7.x86_64                   1/1  Verifying : pgagent_95-3.4.0-9.rhel7.x86_64                   1/1 Installed: pgagent_95.x86_64 0:3.4.0-9.rhel7                           Complete!

在数据库上配置pgAgent

找到pgagent.sql和pgagent_upgrade.sql文件路径，并在需要job功能的数据库上执行

将会在数据库中创建一个pgagent模式，并创建相应的功能表和函数

psql -U postgrespostgres=# \i /usr/share/pgagent_95-3.4.0/pgagent.sqlpostgres=# \i /usr/share/pgagent_95-3.4.0/pgagent_upgrade.sqlpostgres=# \q

输入：

[root@localhost pgagent_95-3.4.0]# psql -U postgrespsql (9.5.9)Type "help" for help.postgres=# \i /usr/share/pgagent_95-3.4.0/pgagent.sqlBEGINCREATE SCHEMACOMMENTCREATE TABLECOMMENTCREATE TABLECREATE INDEXCOMMENTINSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1INSERT 0 1CREATE TABLECOMMENTCOMMENTCREATE TABLECREATE INDEXCOMMENTCOMMENTCOMMENTCREATE TABLECREATE INDEXCOMMENTCREATE TABLECREATE INDEXCREATE INDEXCOMMENTCREATE TABLECREATE INDEXCOMMENTCOMMENTCREATE TABLECREATE INDEXCOMMENTCOMMENTCOMMENTCREATE FUNCTIONCREATE FUNCTIONCOMMENTCREATE FUNCTIONCOMMENTCREATE FUNCTIONCOMMENTCREATE TRIGGERCOMMENTCREATE FUNCTIONCOMMENTCREATE TRIGGERCOMMENTCREATE FUNCTIONCOMMENTCREATE TRIGGERCOMMENTCOMMITpostgres=# \i /usr/share/pgagent_95-3.4.0/pgagent_upgrade.sqlCREATE FUNCTIONCREATE FUNCTIONCOMMENTpsql:/usr/share/pgagent_95-3.4.0/pgagent_upgrade.sql:49: ERROR: column "jstconnstr" of relation "pga_jobstep" already existsALTER TABLEALTER TABLEpostgres=# \q

如果数据库满足以下条件：

* PostgreSQL 版本高于9.1

* pgAgent 版本高于3.4.0

可使用简易安装模式：

[root@localhost pgagent_95-3.4.0]# psql -U postgrespsql (9.5.9)Type "help" for help.postgres=# CREATE EXTENSION pgagent;
    

效果相同

启动pgAgent守护程序

语法：

pgagent_95 [options] connect-string>
    options:-f run in the foreground (do not detach from the terminal)-t poll time interval in seconds (default 10)>
    -r retry period after connection abort in seconds (>
    =10, default 30)>
    -s LOG file (messages are logged to STDOUT if not specified>
    -l logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>
    

实例：

[root@localhost pgagent_95-3.4.0]# pgagent_95 hostaddr=192.168.230.134 dbname=postgres user=postgres password=oracle

执行计划创建

使用pgadmin III工具连接目标库（可以使用其他机器远程连接目标库）

配置如下：

进入pgadmin后可看到作业状态，如下图所示：

创建作业date_delete_new

然后在date_delete_new作业中添加执行计划和步骤

创建计划delete_date_new

计划其实就是作业的执行时间表，可以在其中设置作业的运行时间，最小单位为min，设置方式与linux的cron控件类似

本次的业务要求是每月1号执行删除操作，顾只需设置每月一日执行即可：

新建计划：

设置日期为每月的1号

设置时间为00点00分

创建步骤delete_date_new

步骤是可以执行的SQL脚本或shell脚本，当计划条件触发时，作业的每个步骤将依次以字母数字名称顺序运行

本次的业务要求是删除表中一年以前的数据，以test表为例

新建步骤：

需要指定操作的数据库（postgres）

步骤1

在定义中写入所要执行的脚本，如：

DELETE FROM "test"."test" where time  current_DATE-('1 year')::INTERVAL;
    

步骤2

到此执行计划创建完成

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• PostgreSQL查看正在执行的任务并强制结束的操作方法
• 解决PostgreSQL 执行超时的情况
• 在postgresql中通过命令行执行sql文件
• Postgresql的pl/pgql使用操作--将多条执行语句作为一个事务
• Postgresql psql文件执行与批处理多个sql文件操作
• 在postgresql中结束掉正在执行的SQL语句操作
• PostgreSQL 实现sql放入文件批量执行

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！