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

执行计划创建

使用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

到此执行计划创建完成

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

(0)

相关推荐

  • postgresql 如何查看pg_wal目录下xlog文件总大小

    当然如果你登录服务器所在主机,直接在$PGDAT/pg_wal下执行: du -h --max-depth=1 ./ 可以得到. #du -h --max-depth=1 ./ 4.0K ./archive_status 193M ./ 如果通过客户端怎么做呢? 答案:pg_ls_waldir()函数.pg_ls_waldir()是pg 10.0引入的函数,可以输出数据库WAL目录的所有文件. postgres=# select sum(size) from pg_ls_waldir(); su

  • postgresql之使用lsn 获取 wal文件名的实例

    10.0及以后版本: pg_walfile_name() 用法: postgres=# select pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), pg_walfile_name_offset(pg_current_wal_lsn()); pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset --------------------+---------

  • 开源数据库postgreSQL13在麒麟v10sp1源码安装过程详解

    一.中标麒麟v10sp1在飞腾2000+系统安装略 二.系统依赖包安装 [root@ft2000db opt]# yum install bzip* [root@ft2000db opt]# nkvers ############## Kylin Linux Version ################# Release: Kylin Linux Advanced Server release V10 (Tercel) Kernel: 4.19.90-17.ky10.aarch64 Buil

  • postgresql 12版本搭建及主备部署操作

    postgresql 12版本主备部署 环境搭建 centos 7+ postgresql 12.0 # 网络检查 ping -c2 baidu.com #关闭防火墙,selinux systemctl stop firewalld && sudo systemctl disable firewalld sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config setenforce 0 ### 配置阿里云yum源

  • Postgresql去重函数distinct的用法说明

    在项目中我们常会对数据进行去重处理,有时候会用in或者EXISTS函数.或者通过group by也是可以实现查重 不过Postgresql还有自带去重函数:distinct 下面是distinct 的实例: 1.创建表:user CREATE TABLE `user` ( `name` varchar(30) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `us

  • postgresql初始化之initdb的使用详解

    initdb 官网 initdb创建了一个新的PostgreSQL数据库集群.数据库集群是由单个服务器实例管理的数据库集合. 创建数据库集群包括数据库所在的目录.生成共享目录表(属于整个集群而不是任何特定数据库的表)以及创建template1和postgres数据库.稍后创建新数据库时,将复制template1数据库中的所有内容(因此,template1中安装的任何内容都会自动复制到以后创建的每个数据库中.),postgres数据库是供用户.实例程序和第三方应用程序使用的默认数据库. 尽管ini

  • Postgresql中xlog生成和清理逻辑操作

    0 前言 1.2部分是对XLOG生成和清理逻辑的分析,XLOG暴涨的处理直接看第3部分. 1 WAL归档 # 在自动的WAL检查点之间的日志文件段的最大数量 checkpoint_segments = # 在自动WAL检查点之间的最长时间 checkpoint_timeout = # 缓解io压力 checkpoint_completion_target = # 日志文件段的保存最小数量,为了备库保留更多段 wal_keep_segments = # 已完成的WAL段通过archive_comm

  • postgresql 利用xlog进行热备操作

    一.验证postgresql增量合并的方案 结果:没有有效可行的增量合并方案,暂时放弃 二.梳理postgresql基于wal的增量备份 物理备份与还原适用于跨小版本的恢复但是不能跨平台 逻辑备份与还原备份数据适用于跨版本和跨平台的恢复 postgersql增量备份步骤 1.首先创建归档目录 例如:归档目录为/archive_pg_xlog/xlog 1>mkdir -p /archive_pg_xlog/xlog 2>chown -R postgres:postgres /archive_p

  • PostgreSQL 实现定时job执行(pgAgent)

    PostgreSQL中定时job执行 业务分析 近期项目需要定期清理数据库中的多余数据,即每月1号删除指定表中一年以上的数据. 初步分析这种定时job可以使用一下两种技术实现: Linux的crontab功能 pgadmin的pgAgent功能 为了避免数据库系统和操作系统的绑定,最后决定在数据库层面实现自动清理功能,即使用pgAgent功能 介绍 pgAgent是pgAdmin III工具中的一个插件,它在pgAdmin III v1.4版本中引用. 主要用于PostgreSQL的作业调度代理

  • python循环定时中断执行某一段程序的实例

    问题说明 最近在写爬虫,由于单个账号访问频率太高会被封,所以需要在爬虫执行一段时间间隔后自己循环切换账号 所以就在想,有没有像单片机那样子设置一个定时中断,再定义一个中断入口,这样子每隔一段时间执行一次中断 当然不能用sleep,这样子整个进程就停在这了,而不是接着爬数据 解决方法 用到threading的Timer,也类似单片机那样子,在中断程序中再重置定时器,设置中断,python实例代码如下 import threading import time def change_user(): p

  • Linux环境使用crontab命令设置定时周期性执行任务【含php执行代码】

    本文实例讲述了Linux环境使用crontab命令设置定时周期性执行任务.分享给大家供大家参考,具体如下: 从linux帮助中查看crontab命令有以下参数: -u username:指定用户操作定时器 -e:编辑定时器(所有) -l:查看定时器 -r:删除定时器(从/var/spool/cron目录中删除某个用户的crontab文件,默认删除当前用户的) -i:删除定时器(删除之前给出确认提示) 使用场景1: 执行一些周期性统计的业务操作,例如每天凌晨0:00统计前一天所有业务员及各个小组的

  • Android定时器实现定时执行、重复执行、定时重复执行、定次数执行的多种方式

    作用: 1.定时执行某种功能 2.重复执行.定时重复执行.定次数执行某种功能 类别: 1. Thread(new Runnable) 2.Thread() 3.Timer 4.Handler ····· 代码如下: 1.布局 <?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/andro

  • centOS6中使用crontab定时运行执行jar程序的脚本

    1.编写一个简单的java程序 public class testshell { public static void main(String[] args) { int count = 0; for (String arg : args) { System.out.println("第" + (++count) + "个参数:" + arg); } } } 将上述程序打包放到centOS6中 2.编写shell脚本 #!/bin/bash export JAVA_

  • Spring Boot定时+多线程执行过程解析

    这篇文章主要介绍了Spring Boot定时+多线程执行过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 Spring Boot 定时任务有多种实现方式,我在一个微型项目中通过注解方式执行定时任务. 具体执行的任务,通过多线程方式执行,单线程执行需要1小时的任务,多线程下5分钟就完成了. 执行效率提升10倍以上,执行效率提升10倍以上,执行效率提升10倍以上. 重要的事情说三遍! 本文不深入介绍具体的原理,大家如果要实现类似的功能,只需要

  • shell之定时周期性执行脚本的方法示例

    示例 1.编写测试脚本脚本 time .sh $ cat time.sh #! /bin/bash echo $(date +%s) >> /home/ocean/out.txt 保存完毕后记得给予权限 chmod 777 test.sh 2.添加定时任务 $ crontab -e no crontab for ocean - using an empty one Select an editor. To change later, run 'select-editor'. 1. /bin/n

  • Postgresql 查看SQL语句执行效率的操作

    Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看 SQL 语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法: explain select - from - [where ...] 例如: explain select * from dual; 这里有一个简单的例子,如下: EXPLAIN SELECT * FROM tenk1; QUERY PLAN ---------

  • c#实现用SQL池,多线程定时批量执行SQL语句的方法

    在实际项目开发中,业务逻辑层的处理速度往往很快,特别是在开发Socket通信服务的时候,网络传输很快,但是一旦加上数据库操作,性能一落千丈,数据库操作的效率往往成为一个系统整体性能的瓶颈.面对这问题,我们怎么办呢?好,下面我就为大家介绍一种方法:构建SQL池,分离业务逻辑层和数据访问层,让业务逻辑层从低效的数据库操作解脱,以提高系统整体性能. (一)SQL池 SQL池是SQL容器,用于存放业务逻辑层抛过来的SQL语句.SQL池主要提供以下几种方法: 1)internal string Pop()

  • .net全局定时定期执行某些操作在Global.asax中具体实现

    复制代码 代码如下: <%@ Application Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <%@ import Namespace="System.Collections" %> <%@ Import Na

随机推荐