1 前言

一个问题，一篇文章，一出故事。
今天有个需求需要备份PostgreSQL，于是整理当前章节。

2.1 设置PostgreSQL备份环境

如何设置PostgreSQL数据库备份？

2.2 配置存储

2.2.1 存储方法介绍

– 此案例使用廉价的NFS和Samba作为后端的存储
– 存储统一使用autofs自动挂载（基于稳定性考量）
– 方案将准备日备份将MySQL binglog数据备份到目录“/backup/dailyBackup”
– 方案将准备周备份将MySQLDump（全备）数据备份到目录“/backup/weeklyBackup”

2.2.2 NFS自动挂载的配置

如果你尚未配置，请参阅以下链接配置，

如何配置nfs客户端挂载？

2.2.3 Samba自动挂载的配置

如果你尚未配置，请参阅以下链接配置，

如何配置samba客户端挂载？

2.3 创建备份的计划任务

2.3.1 创建备份脚本存放目录

mkdir ~/scripts/

2.3.2 创建备份脚本

vim ~/scripts/psqlBackup.sh

加入如下内容，

#!/bin/bash

dailyBakDIR='/backup/dailyBackup'
weeklyBakDIR='/backup/weeklyBackup'
logFile='/var/log/psqlbackup.log'
psqlUser='postgres'
psqlPwd='postgrespwd'
psqlHost='localhost'
psqlPort="5432"
dailyBakKeeptime='+7'
weeklyBakKeeptime='+30'

Today="`date +%a`"
dailyBackup="dailyBackup-`date +%Y%m%d`.sql.gz"
weeklyBackup="weeklyBackup-`date +%Y%m%d`.sql.gz"

#########begin###############
case $Today in
Mon|Tue|Wed|Thu|Fri|Sat)
     echo "`date +'%Y-%m-%d %H:%M:%s'` dailyBackup start" | tee -a $logFile
     for ((i=1;i<10;i++))
     do
         if [ $i -ge 5 ]; then
             echo "`date +'%Y-%m-%d %H:%M:%s'` dailyBackupStorage is unavailable!" | tee -a $logFile
             exit 1
         fi
         cd $dailyBakDIR
         if [ $? = 0 ]; then
              break
         fi
         sleep 2
     done

     #find $dailyBakDIR -mtime $dailyBakKeeptime -type f -name dailyBackup-\*.gz -exec ls -l {} \;
     find $dailyBakDIR -mtime $dailyBakKeeptime -type f -name dailyBackup-\*.gz -exec rm -f {} \;
     export PGPASSWORD="$psqlPwd"
     pg_dumpall -U $psqlUser -h $psqlHost -p $psqlPort | gzip > $dailyBakDIR"/"$dailyBackup
     if [ $? = 0 ]; then
         echo "`date +'%Y-%m-%d %H:%M:%s'` dailyBackup finished" | tee -a $logFile
         exit 0
     fi
;;
Sun)
     echo "`date +'%Y-%m-%d %H:%M:%s'` weeklyBackup start" | tee -a $logFile
     for ((i=1;i<10;i++))
     do
         if [ $i -ge 5 ]; then
             echo "`date +'%Y-%m-%d %H:%M:%s'` dailyBackupStorage is unavailable!" | tee -a $logFile
             exit 1
         fi
         cd $dailyBakDIR
         if [ $? = 0 ]; then
              break
         fi
         sleep 2
     done

     #find $weeklyBakDIR -mtime $weeklyBakKeeptime -type f -name weeklyBackup-\*.gz -exec ls -l {} \;
     find $weeklyBakDIR -mtime $weeklyBakKeeptime -type f -name weeklyBackup-\*.gz -exec rm -f {} \;
     export PGPASSWORD="$psqlPwd"
     pg_dumpall -U $psqlUser -h $psqlHost -p $psqlPort | gzip > $weeklyBakDIR"/"$weeklyBackup
     if [ $? = 0 ]; then
         echo "`date +'%Y-%m-%d %H:%M:%s'` weeklyBackup finished" | tee -a $logFile
         exit 0
     fi
;;
esac
########end######################

注：
– 变量“dailyBakDIR”定义日备的目录为“/backup/dailyBackup”
– 变量“weeklyBakDIR”定义周备的目录为“/backup/weeklyBackup”
– 变量“logFile”定义备份的日志文件路径为“/var/log/psqlbackup.log”
– 变量“dailyBakKeeptime”定义日备份的保留时间为“+7”，即7天以外的备份数据删除
– 变量“weeklyBakKeeptime”定义周备份的保留时间为“+30”，即30天以外的备份数据删除

2.3 创建备份计划任务

crontab -e

加入如下内容

#############################data backup###############################
0 2 * * * sh ~/scripts/psqlBackup.sh