postgres备份方案

浅浅的花香味﹌ 2022-08-18 03:23 228阅读 0赞

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天前的日志文件