PostgreSQL中定時job執(zhí)行
業(yè)務(wù)分析
近期項目需要定期清理數(shù)據(jù)庫中的多余數(shù)據(jù)，即每月1號刪除指定表中一年以上的數(shù)據(jù)。
初步分析這種定時job可以使用一下兩種技術(shù)實現(xiàn)：
Linux的crontab功能
pgadmin的pgAgent功能
為了避免數(shù)據(jù)庫系統(tǒng)和操作系統(tǒng)的綁定，最后決定在數(shù)據(jù)庫層面實現(xiàn)自動清理功能，即使用pgAgent功能
介紹
pgAgent是pgAdmin III工具中的一個插件，它在pgAdmin III v1.4版本中引用。
主要用于PostgreSQL的作業(yè)調(diào)度代理，能夠在復(fù)雜的時間表上運行多步批處理shell和SQL任務(wù)。
需要注意的是，pgAgent需要一些數(shù)據(jù)庫表和其他對象的支持,因此需要先安裝pgAgent數(shù)據(jù)庫。
環(huán)境
PostgreSQL:
操作系統(tǒng)：CentOS Linux release 7.3.1611 (Core)
數(shù)據(jù)庫系統(tǒng)： 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
輸入：
[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!
在數(shù)據(jù)庫上配置pgAgent
找到pgagent.sql和pgagent_upgrade.sql文件路徑，并在需要job功能的數(shù)據(jù)庫上執(zhí)行
將會在數(shù)據(jù)庫中創(chuàng)建一個pgagent模式，并創(chuàng)建相應(yīng)的功能表和函數(shù)
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
如果數(shù)據(jù)庫滿足以下條件：
* 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守護(hù)程序
語法：
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
執(zhí)行計劃創(chuàng)建
使用pgadmin III工具連接目標(biāo)庫（可以使用其他機(jī)器遠(yuǎn)程連接目標(biāo)庫）
配置如下：
進(jìn)入pgadmin后可看到作業(yè)狀態(tài)，如下圖所示：
創(chuàng)建作業(yè)date_delete_new
然后在date_delete_new作業(yè)中添加執(zhí)行計劃和步驟
創(chuàng)建計劃delete_date_new
計劃其實就是作業(yè)的執(zhí)行時間表，可以在其中設(shè)置作業(yè)的運行時間，最小單位為min，設(shè)置方式與linux的cron控件類似
本次的業(yè)務(wù)要求是每月1號執(zhí)行刪除操作，顧只需設(shè)置每月一日執(zhí)行即可：
新建計劃：
設(shè)置日期為每月的1號
設(shè)置時間為00點00分
創(chuàng)建步驟delete_date_new
步驟是可以執(zhí)行的SQL腳本或shell腳本，當(dāng)計劃條件觸發(fā)時，作業(yè)的每個步驟將依次以字母數(shù)字名稱順序運行
本次的業(yè)務(wù)要求是刪除表中一年以前的數(shù)據(jù)，以test表為例
新建步驟：
需要指定操作的數(shù)據(jù)庫（postgres）
步驟1
在定義中寫入所要執(zhí)行的腳本，如：
DELETE FROM "test"."test"
where time CURRENT_DATE-('1 year')::INTERVAL;
步驟2
到此執(zhí)行計劃創(chuàng)建完成
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostgreSQL查看正在執(zhí)行的任務(wù)并強(qiáng)制結(jié)束的操作方法
- 解決PostgreSQL 執(zhí)行超時的情況
- 在postgresql中通過命令行執(zhí)行sql文件
- Postgresql的pl/pgql使用操作--將多條執(zhí)行語句作為一個事務(wù)
- Postgresql psql文件執(zhí)行與批處理多個sql文件操作
- 在postgresql中結(jié)束掉正在執(zhí)行的SQL語句操作
- PostgreSQL 實現(xiàn)sql放入文件批量執(zhí)行
