postgres备份方案

1 热备份需要的基础备份：backup-mwa-cd03-data.sh

#!/bin/bash
date=`date +%Y%m%d`
bak_path="/var/lib/pgsql/9.3/backups"
log_path="/var/log/backup"
log="/var/log/backup/backup-mwa-data-$date.log"
wt_log()
{
if [ $? -ne 0 ] ;then 
    echo "${*}:失败！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
else
    echo "${*}:成功！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
fi
}

/usr/pgsql-9.3/bin/psql -h localhost -c "select pg_start_backup('hot_backup');">${log} 2>&1
wt_log 开启热备份
/bin/tar -cf ${bak_path}/base_backup_${date}.tar /var/lib/pgsql/9.3/data/ --exclude /var/lib/pgsql/9.3/data/pg_xlog>>${log} 2>&1
wt_log 备份data文件夹下（除xlog）所有内容
/usr/pgsql-9.3/bin/psql -h localhost -c "select pg_stop_backup();">>${log} 2>&1
wt_log 关闭热备份
[root@MWACentral shell]# cat backup-mwa-cd03-data.sh 
#!/bin/bash
date=`date +%Y%m%d`
bak_path="/var/lib/pgsql/9.3/backups"
log_path="/var/log/backup"
log="/var/log/backup/backup-mwa-data-$date.log"
wt_log()
{
if [ $? -ne 0 ] ;then 
    echo "${*}:失败！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
else
    echo "${*}:成功！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
fi
}

/usr/pgsql-9.3/bin/psql -h localhost -c "select pg_start_backup('hot_backup');">${log} 2>&1
wt_log 开启热备份
/bin/tar -cf ${bak_path}/base_backup_${date}.tar /var/lib/pgsql/9.3/data/ --exclude /var/lib/pgsql/9.3/data/pg_xlog>>${log} 2>&1
wt_log 备份data文件夹下（除xlog）所有内容
/usr/pgsql-9.3/bin/psql -h localhost -c "select pg_stop_backup();">>${log} 2>&1
wt_log 关闭热备份

2 sql转储，调用基础备份，删除旧备份文件backup-mwa-cd03-db.sh  shell放在/etc/cron.daily中，每天自动之行

#!/bin/bash
date=`date +%Y%m%d`
bak_path="/var/lib/pgsql/9.3/backups"
log_path="/var/log/backup"
log="/var/log/backup/backup-mwa-db-$date.log"
wt_log()
{
if [ $? -ne 0 ] ;
    then echo "${*}:失败！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
else
    echo "${*}:成功！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
fi
}


echo begin:`date +%Y%m%d'-'%H:%M:%S`>${log} 2>&1
#导出cd03的dump文件
/bin/su - postgres -c "/usr/pgsql-9.3/bin/pg_dump -h localhost -f ${bak_path}/cd03_${date}.dump -d cd03">>${log} 2>&1
wt_log 导出cd03的dump文件

#备份psql的data文件夹,间隔七天
#取出天数
day=`date +%d`
#天数是否是7的倍数？
rema=`expr ${day} % 7`
#天数如果是7的倍数，str=0x
str=`echo ${rema}x`
if [ ${str} = 0x ] ;
then 
    /bin/su - postgres -s /bin/bash /home/postgres/shell/backup-mwa-cd03-data.sh
    wt_log 备份psql的data文件夹
fi

#压缩文件
/usr/bin/find $bak_path -maxdepth 1 -type f -not -name "*.gz" -exec gzip -f -9 {} \;>>${log} 2>&1
wt_log 压缩备份文件

#删除七天前的数据文件
/usr/bin/find $bak_path -mtime +7 -exec rm {} \;>>${log} 2>&1
wt_log 删除七天前的数据文件

#删除七天前的日志文件
/usr/bin/find $log_path -name "backup-mwa*" -mtime +7 -exec rm {} \;>>${log} 2>&1
wt_log 删除七天前的日志文件

3备份文件同步回本地（rsync+ssh）

#!/bin/bash
date=`date +%Y%m%d`
rsync="/usr/bin/rsync"
server="128.***.***.***"
user="root"
server_path="/var/lib/pgsql/9.3/backups"
bak_path="/mnt/hd02/backup/cloud_hosts/mwa/cd03"
log_path="/var/log/backup"
log="/var/log/backup/backup-mwa-cd03-$date.log"
wt_log()
{
if [ $? -ne 0 ] ;
        then echo "${*}:失败！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
else
        echo "${*}:成功！[end:`date +%Y%m%d'-'%H:%M:%S`]">>${log} 2>&1
fi
}


echo begin:`date +%Y%m%d'-'%H:%M:%S`>${log} 2>&1

#用ssh通道同步服务器上的文件
$rsync -av -e "/usr/bin/ssh -i /root/.ssh/id_rsa" $user@$server:$server_path $bak_path>>${log} 2>&1
wt_log 同步服务器上的备份文件

#删除30天前的数据文件

/usr/bin/find $bak_path -mtime +30 -exec rm {} \;>>${log} 2>&1
wt_log 删除30天前的数据文件

#删除30天前的日志文件
/usr/bin/find $log_path -name "backup-mwa-cd03*" -mtime +30 -exec rm {} \;>>${log} 2>&1
wt_log 删除30天前的日志文件