PostgreSQL Daily Maintenance - Timing Tasks

定时任务管理 : 

PostgreSQL 定时任务可以通过操作系统的定时任务功能来实现, 例如Linux 的crontab. 

也可以通过pgagent插件来实现.

pgagent的调度信息存储在PostgreSQL数据库的postgres库中, 可以通过pgadmin可以在图形化界面来配置调度表.

pgagent是一个后台进程, 时刻读取配置好的调度信息, 根据调度信息执行job, 并记录执行日志.

一般来说建议将pgagent安装在本地数据库服务器中, 调度信息也存放在本地数据库的postgres库中, 那么在执行job时, pgagent连接本地数据库即可. 否则的话还需要配置目标库的密码, 建议用.pgpass来配置, 如果配置在命令行中容易被ps进程发现.

pgagent的安装 : 

首先下载pgagent : 

http://git.postgresql.org/gitweb/?p=pgagent.git;a=summary

阅读README, 编译pgagent需要先安装以下软件 :

- A C/C++ compiler, such as GCC or Microsoft Visual C++ on Windows.
- CMake 2.6 (from www.cmake.org)
- A wxWidgets 2.8.x installation, configured per the requirements for
  pgAdmin:
  http://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob_plain;f=INSTALL;hb=HEAD
- A PostgreSQL 8.3 or higher installation

安装gcc

rpm -ivh gcc-4.1.2-51.el5

安装cmake

tar -zxvf cmake-2.8.8.tar.gz
cd cmake-2.8.8
./bootstrap --prefix=/opt/cmake2.8.8
gmake
gmake install
安装好后将cmake的bin目录放到PATH变量.
vi ~/.bash_profile
export PATH=/opt/cmake2.8.8/bin:$PATH
. ~/.bash_profile

安装wxWidgets

wget http://prdownloads.sourceforge.net/wxwindows/wxGTK-2.8.12.tar.gz
tar -zxvf wxGTK-2.8.12.tar.gz
cd wxGTK-2.8.12
./configure --enable-shared=no --enable-unicode=yes --prefix=/opt/wxGTK-2.8.12
make
make install
将bin加入PATH
vi ~/.bash_profile
export PATH=/opt/wxGTK-2.8.12/bin:$PATH
export LD_LIBRARY_PATH=/opt/wxGTK-2.8.12/lib:$LD_LIBRARY_PATH
. ~/.bash_profile

安装PostgreSQL, 略.

将pg_config加入PATH

vi ~/.bash_profile
export PATH=/opt/pgsql9.3/bin:$PATH
. ~/.bash_profile

安装pgagent

tar -zxvf pgagent-d6c5a80.tar.gz
cd pgagent-d6c5a80
ccmake ./
生成配置
 CMAKE_BUILD_TYPE                                                                                                                  
 CMAKE_INSTALL_PREFIX             修改为此项 /opt/pgagent                                                                                     
 PG_CONFIG_PATH                   /opt/pgsql9.3/bin/pg_config                                                                      
 STATIC_BUILD                     ON                                                                                               
 WX_CONFIG_PATH                   /opt/wxGTK-2.8.12/bin/wx-config                                                                  
 WX_DEBUG                         OFF
按c配置,然后按g产生配置
接下来就可以编译安装了
make
make install
配置.bash_profile
vi ~/.bash_profile
export PATH=/opt/pgagent/bin:$PATH
. ~/.bash_profile

在本地库的postgres库中创建调度信息, 表, 函数 : 

psql -h 127.0.0.1 -p 1999 -U postgres -d postgres -f /opt/pgagent/share/pgagent.sql

安装好后, 使用pgadmin连接到本地库就可以看到jobs选项 : 

 

pgagent配置举例

启动pgagent.

pgagent是一个后台进程, 时刻要去读取存储在数据库中的调度信息以执行定时任务. 所以首先要启动pgagent.

[root@db-172-16-3-33 ~]# pgagent --help
Usage:
pgagent [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)>

例如, -t 1表示每秒读取一次调度数据 : 

pgagent -t 1 -l 2 -s /var/log/pgagent.log hostaddr=127.0.0.1 port=1999 dbname=postgres user=postgres
不要把password配置在命令行中, 尽量配置在密码文件中 : 
vi ~/.pgpass
127.0.0.1:1999:postgres:postgres:postgres
chmod 400 ~/.pgpass

启动好pgagent后就可以配置job了.

案例1 : 

需求 : 在本地数据库中定时执行sql

job配置

1. 在pgadmin的jobs上右键新建job.

2. 在属性栏配置job name, 例如j1.

3. 在steps中新建并配置step name, 本地库名;

在定义栏配置要执行的sql :

create table if not exists test_sum(sdate date, cnt int8);
insert into test_sum select current_date,count(*) from pg_class group by current_date;

可以配置多个step, 执行时按名称以及step id排序执行 : 

// job.cpp - pgAgent job

int Job::Execute()
{
        int rc = 0;
        bool succeeded = false;
        DBresult *steps = threadConn->Execute(
                              wxT("SELECT * ")
                              wxT("  FROM pgagent.pga_jobstep ")
                              wxT(" WHERE jstenabled ")
                              wxT("   AND jstjobid=") + jobid +
                              wxT(" ORDER BY jstname, jstid"));

4. 在schedule栏中 新建并 配置调度名, 开始时间, 如果有结束时间的话还需要配置结束时间.

days中可以配置周, 月, 日

times中可以配置小时, 分钟.

exceptions中配置排他.

配置完后, 在steps以及jos的统计信息栏可以看到每次JOB或者step执行的情况.

案例2 : 

需求 : 定时执行脚本(batch)

脚本配置

[root@db-172-16-3-33 ~]# cat /root/b.sh
#!/bin/bash
psql -h 127.0.0.1 -p 1999 -U postgres postgres <<EOF
create table if not exists t(id int);
insert into t values (1);
EOF
exit $?
chmod 500 b.sh

以上假设pgagent是以root用户执行的. 否则要考虑脚本的可执行权限.

job配置, 同上

schedule 配置, 同上

step 配置, 选择batch, 不需要选择数据库. 定义中输入/root/b.sh .其他同上.

案例3 : 

需求 : 在远程数据库中定时执行SQL

job配置, 同上.

schedule 配置, 同上.

step 配置, 需要输入远程数据库的连接信息. 密码配置在conn string中. 所以不安全.

例如 :

hostaddr=172.16.3.150 port=1000 user=postgres dbname=postgres password=pwd

这里支持.pgpass. 所以建议不要使用远程定时任务.

连接信息参考 : 

http://www.postgresql.org/docs/devel/static/libpq-connect.html

[参考]

1.  http://www.postgresql.org/docs/devel/static/libpq-connect.html

2.  http://pgadmin.org/