PostgreSQL 实现定时job执行(pgAgent)

2021-01-16 10:43:30 作者：aladdin_sun

这篇文章主要介绍了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.8 IP： 192.168.230.134 port: 5432

安装pgAdmin III

yum install pgadmin3_95.x86_64 [root@localhost ~]# yum install pgadmin3_95.x86_64 Loaded plugins: fastestmirror, langpacks base | 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.com Resolving 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 Resolution Dependencies Resolved ======================================================================================== Package Arch Version Repository Size ======================================================================================== Installing: pgadmin3_95 x86_64 1.22.1-1.rhel7 pgdg95 3.2 M Installing 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 M Transaction Summary ======================================================================================== Install 1 Package (+3 Dependent packages) Total download size: 6.9 M Installed size: 27 M Is this ok [y/d/N]: y Downloading 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 check Running transaction test Transaction test succeeded Running 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.el7 Complete!

安装pgAgent

yum install pgagent_95 [root@localhost ~]# yum install pgagent_95 Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: mirrors.163.com * epel: mirrors.ustc.edu.cn * extras: mirrors.163.com * updates: mirrors.163.com Resolving Dependencies --> Running transaction check ---> Package pgagent_95.x86_64 0:3.4.0-9.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================== Package Arch Version Repository Size ======================================================================================== Installing: pgagent_95 x86_64 3.4.0-9.rhel7 pgdg95 42 k Transaction Summary ======================================================================================== Install 1 Package Total download size: 42 k Installed size: 151 k Is this ok [y/d/N]: y Downloading packages: pgagent_95-3.4.0-9.rhel7.x86_64.rpm | 42 kB 00:00:02 Running transaction check Running transaction test Transaction test succeeded Running 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 postgres postgres=# \i /usr/share/pgagent_95-3.4.0/pgagent.sql postgres=# \i /usr/share/pgagent_95-3.4.0/pgagent_upgrade.sql postgres=# \q [root@localhost pgagent_95-3.4.0]# psql -U postgres psql (9.5.9) Type "help" for help. postgres=# \i /usr/share/pgagent_95-3.4.0/pgagent.sql BEGIN CREATE SCHEMA COMMENT CREATE TABLE COMMENT CREATE TABLE CREATE INDEX COMMENT INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 CREATE TABLE COMMENT COMMENT CREATE TABLE CREATE INDEX COMMENT COMMENT COMMENT CREATE TABLE CREATE INDEX COMMENT CREATE TABLE CREATE INDEX CREATE INDEX COMMENT CREATE TABLE CREATE INDEX COMMENT COMMENT CREATE TABLE CREATE INDEX COMMENT COMMENT COMMENT CREATE FUNCTION CREATE FUNCTION COMMENT CREATE FUNCTION COMMENT CREATE FUNCTION COMMENT CREATE TRIGGER COMMENT CREATE FUNCTION COMMENT CREATE TRIGGER COMMENT CREATE FUNCTION COMMENT CREATE TRIGGER COMMENT COMMIT postgres=# \i /usr/share/pgagent_95-3.4.0/pgagent_upgrade.sql CREATE FUNCTION CREATE FUNCTION COMMENT psql:/usr/share/pgagent_95-3.4.0/pgagent_upgrade.sql:49: ERROR: column "jstconnstr" of relation "pga_jobstep" already exists ALTER TABLE ALTER TABLE postgres=# \q

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

* PostgreSQL 版本高于9.1

* pgAgent 版本高于3.4.0

可使用简易安装模式：

[root@localhost pgagent_95-3.4.0]# psql -U postgres psql (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