centos 利用crontab进行gp集群按时备份

站在巨人的肩膀上

part.1 编写脚本

本脚本是在master节点下的gpadmin用户下编写操作的，所以其拥有者和拥有组位gpadmin，并且可执行755.。
具体思路是利用psql查找出所有的database，不包括template库，然后循环进行备份。

#!/bin/bash
dbnames=$(psql -A -q -t -h 192.168.1.213 -p 5432 -d sjgl_gp -U gpadmin  -c "select datname from pg_database where datname not like 'template%'")
backupdir="/data1/gpmaster/gpbackup/"
dbid=gpmaster
logdir=$backupdir
masterdir="/data1/gpmaster/gpseg-1/"
for dbname in $dbnames 
do 
#echo $dbname >> ~/log.txt
now=`date +%Y%m%d%H%M%S`
gpcrondump -a -C --dump-stats -g -G -h -r --use-set-session-authorization -x $dbname -u $backupdir --prefix $dbid -l $logdir -d $masterdir -K $now
done

part.2 crontab 添加

在master节点gpadmin用户下，使用crontab -e，并添加如下信息。
表示每个周日的0点整执行脚本进行备份。

$ crontab -e

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed
00 00 * * sun sh ~/gpbak.sh

但是经过测试，发现无法查询到数据库。

part.3 脚本的环境变量

因为在使用crontab时，无法得到当前用户的环境变量。所以我把当前用户的所有环境变量进行导入。在脚本的最前面加入以下内容。

export GPHOME="/usr/local/greenplum-db/."
export HISTCONTROL="ignoredups"
export HISTSIZE="1000"
export HOME="/home/gpadmin"
export HOSTNAME="mdw"
export LANG="en_US.UTF-8"
export LD_LIBRARY_PATH="/usr/local/greenplum-db/./lib:/usr/local/greenplum-db/./ext/python/lib:/usr/local/greenplum-db/./lib:/usr/local/greenplum-db/./ext/python/lib:"
export LESSOPEN="||/usr/bin/lesspipe.sh %s"
export LOGNAME="gpadmin"
export MAIL="/var/spool/mail/root"
export MASTER_DATA_DIRECTORY="/data1/gpmaster/gpseg-1"
export OLDPWD="/root"
export OPENSSL_CONF="/usr/local/greenplum-db/./etc/openssl.cnf"
export PATH="/usr/local/greenplum-db/./bin:/usr/local/greenplum-db/./ext/python/bin:/usr/local/greenplum-db/./bin:/usr/local/greenplum-db/./ext/python/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin"
export PWD="/home/gpadmin"
export PYTHONHOME="/usr/local/greenplum-db/./ext/python"
export PYTHONPATH="/usr/local/greenplum-db/./lib/python"
export SHELL="/bin/bash"
export SHLVL="2"
export SSH_CLIENT="192.168.1.145 51398 22"
export SSH_CONNECTION="192.168.1.145 51398 192.168.1.213 22"
export SSH_TTY="/dev/pts/0"
export TERM="vt100"
export USER="gpadmin"
export XDG_RUNTIME_DIR="/run/user/0"
export XDG_SESSION_ID="3"