昨天将自己写的postgres数据库的备份脚本放到公司内网实验环境上去验证准确性，但是今天早晨一来检查备份脚本，却发现备份脚本出问题了。
查看文件的内容也是报错：
很显然，备份文件有问题。那么问题来了，我自己在psotgres用户下执行脚本就能正确的备份文件，但是为什么到crontab定时任务下，就不能正确执行脚本了呢？
后来查询了一些资料发现，原来crontab在定期执行脚本的时候，不会缺省的从用户profile文件中读取环境变量参数，经常导致在手工执行某个脚本时是成功的，但是到crontab中试图让它定期执行时就是会出错。所以在脚本中，我们要把脚本必需的环境变量全部罗列，这样就不会出现问题了。
环境变量的书写有几种方式，第一种最简单直接的方式就是在shell脚本中直接export指定环境变量，例如：export PGDATA=/home/postgres/pgdata；第二种方法是在脚本中source用户的脚本文件，例如：source/home/postgres/.bash_profie;第三种方法是把环境变量单独写到一个文件shell_env里面，然后在shell脚本中source shell_env生效。
令附数据库的备份脚本，诸多不足，请多指教
备份脚本一：
#!/bin/bash ####################################################### ### 删除去七天前备份文件 ### 备份一天前的备份文件 #######################################################
source /home/postgres/.bash_profile export BACKUP_DIR=/data/backup/dump export DUMP_LOG=/data/log/dump.log export DATE_7=$(date --date '7 days ago' +"%Y-%m-%d") export DATE=$(date +"%Y-%m-%d") export.utf #要备份的数据库，只允许填写一个 export DB=zabbix
echo >> $DUMP_LOG echo >> $DUMP_LOG echo >> $DUMP_LOG echo "##################################################################" >> $DUMP_LOG echo "backup time: " >> $DUMP_LOG echo " `date +"%Y-%m-%d %H:%M:%S"` " >> $DUMP_LOG echo "##################################################################" >> $DUMP_LOG
del_dump(){ echo "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" >> $DUMP_LOG echo "`date +"%Y-%m-%d"` remove the backup of database zabbix 7 days ago" >> $DUMP_LOG echo "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" >> $DUMP_LOG
test -f $BACKUP_DIR/$DB-$DATE_7.dump if [ $? -eq 0 ]; then rm -f ${BACKUP_DIR:?var is empty}/${DB:?var is empty}-${ADATE_7:?var is empty}.dump fi
test -f $BACKUP_DIR/$DB-$DATE_7.dump if [ $? -eq 0 ]; then echo " Warning: the file '$DB-$DATE_7.dump' has not been moved." >> $DUMP_LOG else echo "the file '$DB-$DATE_7.dump' has been moved." >> $DUMP_LOG fi }
do_dump(){ echo "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" >> $DUMP_LOG echo "`date +"%Y-%m-%d"` backup the database zabbix today starting..." >> $DUMP_LOG echo "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" >> $DUMP_LOG
if [ -d $BACKUP_DIR ]; then echo "BACKUP Directory $BACKUP_DIR exist" >> $DUMP_LOG else mkdir -p $BACKUP_DIR fi
test -f $BACKUP_DIR/$DB-$DATE.dump if [ $? -eq 0 ]; then echo "ERROR: Today's(`date +"%Y-%m-%d"`) BACKUP job has done,quit！" >> $DUMP_LOG else pg_dump -h 127.0.0.1 -p 5432 -U postgres -Fc $DB > $BACKUP_DIR/$DB-$DATE.dump fi
test -f $BACKUP_DIR/$DB-$DATE.dump if [ X"$?" != X"0" ]; then echo “ERROR: the database $DB backup failed!” >> $DUMP_LOG else fsize fi } fsize(){ fsize=$(ls -l ${BACKUP_DIR:?var is empty}/${DB:?var is empty}-${DATE:?var is empty}.dump | awk -F" " '{print $5}') if [ X"$fsize" != X"0" ] ; then echo "`date +"%Y-%m-%d %H:%M:%S"`: the dump file ${BACKUP_DIR:?var is empty}/${DB-$DATE:?var is empty}.dump has been backuped successfully " >> $DUMP_LOG echo "$DATE.tar.gz's file size is about $fsize" >> $DUMP_LOG else echo "`ERROR: date +"%Y-%m-%d %H:%M:%S"`: the basedata $DATE-basedata.tar.gz has been backuped failed" >> $DUMP_LOG fi }
del_dump do_dump
|
恢复方案：
pg_restore –d zabbix zabbix-2017-02-09.dump
|
备份脚本二：
#!/bin/bash ## 脚本说明：数据库归档日志位置：MYARCLOG=/home/flying/arclog ## 数据库归档日志备份位置：MYARCLOG_BAK=/home/flying/arclog_bak ## 实现功能：脚本的定时任务建议在每天凌晨1点左右打包，一般是今天打包昨天的。 ## 在凌晨1-3点进行打包备份,保证备份的wal日志是连续
#要备份数据库连接信息 export IP=192.168.0.238 export PORT=5432 export USER=repuser
# 归档位置 export MYARCLOG=/home/postgres/archive export DATE=$(date --date '1 days ago' +"%Y-%m-%d") # 备份日志记录 export DUMP_LOG=/data/log/pitr.log # 备份位置 export MYARCLOG_BAK=/data/backup/wal_backup
echo >> $DUMP_LOG echo >> $DUMP_LOG echo >> $DUMP_LOG echo "##################################################################" >> $DUMP_LOG echo "backup time: " >> $DUMP_LOG echo " `date +"%Y-%m-%d %H:%M:%S"` " >> $DUMP_LOG echo "##################################################################" >> $DUMP_LOG
## 创建基础备份 basedata_dump(){ test -d $MYARCLOG_BAK/$DATE if [ X"$?" == X"0" ]; then mkdir -p ${MYARCLOG_BAK:?var is empty}/${DATE:?var is empty} fi pg_basebackup -h $IP -p $PORT -U $USER -F p -P -x -R -D $MYARCLOG_BAK/$DATE -l postgresbackup$DATE && tar -zcvf ${MYARCLOG_BAK:?var is empty}/$DATE-basedata.tar.gz ${MYARCLOG_BAK:?var is empty}/${DATE:?var is empty} --remove
fsize=$(ls -l ${MYARCLOG_BAK:?var is empty}/${DATE:?var is empty}-basedata.tar.gz | awk -F" " '{print $5}')
if [ X"$fsize" != X"0" ] ; then echo "`date +"%Y-%m-%d %H:%M:%S"`: the basedata $DATE-basedata.tar.gz has been backuped successfully in $MYARCLOG_BAK" >> $DUMP_LOG echo "$DATE-basedata.tar.gz's file size is about $fsize" >> $DUMP_LOG else echo "`date +"%Y-%m-%d %H:%M:%S"`: the basedata $DATE-basedata.tar.gz has been backuped failed" >> $DUMP_LOG fi }
# 创建归档日志备份 WAL_BACK(){ test -d $MYARCLOG_BAK
if [ X"$?" != X"0" ]; then mkdir -p $MYARCLOG_BAK fi
cd $MYARCLOG for i in `ls /home/postgres/archive --time-style=long -lhr | awk '{print $6}'| sed '1d'`; do if [ "$i" == "$DATE" ];then mkdir -p $MYARCLOG/$DATE for j in `ls --time-style=long -lhr | grep $i| awk '{print $8}'| grep -v '-' ` do echo ${MYARCLOG:?var is empty}/${j:?var is empty} | xargs -t -n 20 -i mv {} ${MYARCLOG:?var is empty}/${DATE:?var is empty} >> $DUMP_LOG done fi done
cd $MYARCLOG tar zcvf ${DATE:?var is empty}.tar.gz ${DATE:?var is empty} #建议保留原文件，去掉 --remove-files 参数即可。 mv ${MYARCLOG:?var is empty}/${DATE:?var is empty}.tar.gz ${MYARCLOG_BAK:?var is empty}/
fsize=$(ls -l ${MYARCLOG_BAK:?var is empty}/${DATE:?var is empty}.tar.gz | awk -F" " '{print $5}') if [ X"$fsize" != X"0" ] ; then echo "`date +"%Y-%m-%d %H:%M:%S"`: the wal file $DATE.tar.gz has been backuped successfully in $MYARCLOG_BAK" >> $DUMP_LOG echo "$DATE.tar.gz's file size is about $fsize" >> $DUMP_LOG else echo "`date +"%Y-%m-%d %H:%M:%S"`: the basedata $DATE-basedata.tar.gz has been backuped failed" >> $DUMP_LOG fi }
case "$1" in basedata_dump) basedata_dump ;;
WAL_BACK) WAL_BACK ;;
*) echo "Bad Parameter." echo "Usage: $0 [ basedata_dump | WAL_BACK ]" exit 1 ;; esac
|
恢复方案二：
Step 1. $ tar -zxvf data.tar 拷贝数据到$PGDATA目录 Step 2. $ vi recovery.conf standby_mode = 'on' recovery_target_timeline = 'latest' restore_command = 'cp /home/postgres/archive/%f %p' Step 3. $ pg_ctl start Step 4. $ pg_ctl stop 删除文件recovery.conf Step 5. $ pg_ctl start
|
定时任务：
#逻辑备份zabbix数据库 00 23 * * * /data/service/batch.daily/zabbix_dump.sh #备份打包前一天wal日志 30 1 * * * /data/service/batch.daily/pg_xlog_backup.sh WAL_BACK #数据库全库备份 30 2 * * 0 /data/service/batch.daily/pg_xlog_backup.sh basedata_dump |
