PostgreSQL 定时任务方法2

背景

一种方法是使用pgagent客户端，结合pgagent插件，实现类似Oracle DBMS_JOBS的功能。

《PostgreSQL Oracle 兼容性之 - DBMS_JOBS - Daily Maintenance - Timing Tasks(pgagent)》

另一种方法是直接在客户端使用linux crontab来执行定时任务。

例子

1、安装PostgreSQL

PostgreSQL 10 on CentOS 6.x x64

yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm      
yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-6-x86_64/pgdg-centos10-10-2.noarch.rpm      
yum install -y postgresql10*        

PostgreSQL 10 on CentOS 7.x x64

yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm      
yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm      
yum install -y postgresql10*        

如果数据库用的是PostgreSQL商用发行版本 EnterpriseDB (EDB PPAS)，请到如下网站下载对应软件并安装

https://www.enterprisedb.com/software-downloads-postgres

2、配置环境变量

主要是调用psql的环境变量

vi /home/digoal/pgsql_env.sh  
  
export LANG=en_US.utf8    
export PGHOME=/opt/edb/as10  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH    
export DATE=`date +"%Y%m%d%H%M"`    
export PATH=$PGHOME/bin:$PATH:.    

3、配置密码文件

需要被连接用于执行任务的数据库，需要配置连接方法，将密码写在配置文件中，例如

vi /home/digoal/.pgpass  

# 格式如下
# hostname:port:database:username:password
  
111.111.111.111:5432:dbname1:username:pwd  
111.111.111.111:5432:dbname1:username2:pwd  
111.111.111.111:5432:dbname2:username1:pwd  
111.111.111.222:5432:dbname2:username1:pwd  
  
# 加上安全权限
chmod 400 /home/digoal/.pgpass  

3、创建任务脚本

例子：每天创建15天后的分区，并删除30天前的分区表。

测试父表如下

create table test (id int, dt date, info text, crt_time timestamp) partition by range (dt);  

注意，脚本中需要配置连接串用到的数据库环境变量，以及psql客户端命令需要用到的环境变量。

vi /home/digoal/job1.sh  
  
  
  
#!/bin/bash  
. /home/digoal/pgsql_env.sh  
  
export PGHOST=111.111.111.111   
export PGPORT=5432   
export PGDATABASE=postgres    
export PGUSER=digoal  
export PGPASSFILE=/home/digoal/.pgpass  
  
echo "`date +%F%T` start job."  
  
psql <<EOF   
set lock_timeout ='1s';  
do language plpgsql \$\$  
declare  
  suffix text := to_char(current_date+15, 'yyyymmdd');  
  dt date := current_date+15;  
  dpt text := to_char(current_date-90, 'yyyymmdd');  
begin  
  execute format ('create table test_%s PARTITION OF test for values from (''%s'') to (''%s'')', suffix, dt, dt+1) ;  
  execute format ('drop table test_%s', dpt) ;  
end;  
\$\$;  
EOF  
  
echo "`date +%F%T` end job."   
  
  
  
# 加上可执行权限
chmod 500 /home/digoal/job1.sh  

5、配置调度任务

在调用job脚本的OS用户中，配置crontab任务如下：

crontab -e  

1 3 * * * /home/digoal/job1.sh >> /home/digoal/log_job1.log 2>&1   

参考

man 5 crontab  
  
              field          allowed values  
              -----          --------------  
              minute         0-59  
              hour           0-23  
              day of month   1-31  
              month          1-12 (or names, see below)  
              day of week    0-7 (0 or 7 is Sunday, or use names)  
  
  
       A field may contain an asterisk (*), which always stands for "first-last".  
  
       Ranges of numbers are allowed.  Ranges are two numbers separated with a hyphen.    
       The specified range is inclusive.  For example, 8-11 for an  
  
       Lists are allowed.  A list is a set of numbers (or ranges) separated by commas.    
       Examples: "1,2,5,9", "0-4,8-12".  
  
       Step values can be used in conjunction with ranges.    
       Following a range with "/<number>"   
       specifies skips of the number's value through the range.    
       For example, "0-23/2" can be used in the 'hours' field to specify command exe‐  
       cution for every other hour   
       (the alternative in the V7 standard is "0,2,4,6,8,10,12,14,16,18,20,22").    
       Step values are also permitted after an asterisk,   
       so if specifying a job to be run every two hours, you can use "*/2".  
  
       Names can also be used for the 'month' and 'day of week' fields.    
       Use the first three letters of the particular day or month (case does not matter).    
       Ranges or lists of names are not allowed.  
  
       The "sixth" field (the rest of the line) specifies the command to be run.    
       The entire command portion of the line, up to a newline or a "%" character,   
       will be executed by /bin/sh or by the shell specified in the SHELL  vari‐  
       able of the cronfile.  A "%" character in the command, unless escaped with   
       a backslash (\), will be changed into newline characters, and all data   
       after the first % will be sent to the command as standard input.  
  
       Note:  The  day of a command's execution can be specified in the following   
       two fields — 'day of month', and 'day of week'.  If both fields are restricted   
       (i.e., do not contain the "*" character), the command will be run when  
       either field matches the current time.  For example,  
       "30 4 1,15 * 5" would cause a command to be run at 4:30 am on the 1st and   
       15th of each month, plus every Friday.  

digoal’s 大量PostgreSQL文章入口