postgresql热备份与恢复

╰半橙微兮° 2022-08-18 03:24 421阅读 0赞

一 热备份的原理是“基础备份文件”+“归档日志”进行restore。

1 配置postgresql.conf文件

wal\_level = hot\_standby

archive\_mode = on

archive\_command = 'test ! -f /usr/local/backups/%f && cp %p /usr/local/backups/%f'

重启数据库是配置生效。

2 进行基础文件的备份

\#!/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 关闭热备份

基础备份不必每天备份，之需要每天备份归档日志就可以了，所以这个shell可以设置间隔一段时间运行。

(1)pg\_start\_backup('hot\_backup')函数告诉数据库我要进行文件备份，把脏数据给我刷新到磁盘上（checkpoint）;置写日志标志为：XLogCtl->Insert.forcePageWrites = true，也就是把这个标志设置为true后，数据库会把变化的整个数

据块都记录到数据库中，而不仅仅是块中记录的变化。防止后面一边复制文件一边写文件同一个数据快数据不一致。

(2)把data文件打包压缩到另外一个地方上。（关键是做这个）

(3)pg\_stop\_backup()函数告诉数据库文件备份完成了。

二 模拟数据库崩溃

删除data下的部分文件; kill  postgres主进程;启动数据库失败，数据库崩溃

三 恢复数据到当前

1 保留当期data文件夹下pg\_xlog下的日志，因为有可能日志没有来得及归档。

2 删除data文件夹。

3 解压之前备份的data文件夹到原来data所在目录。

4 在data目录下创建pg\_xlog/archive\_status文件夹

5 创建恢复配置文件。 cp /usr/local/pgsql9.4.3/share/recovery.conf.sample /usr/local/pgsql9.4.3/data

增加以下两行：

restore\_command='cp /usr/local/backups/%f %p'  
    recovery\_target\_timeline = 'latest'

其中restore\_command指定归档日志路径;recovery\_target\_timeline = 'latest' 代表数据库恢复到当前最新。（注意：如果/usr/local/backups 下面的归档日志不是最新的，要把崩溃后的war日志拷贝到这里）

6 起动数据库，数据库开始redo

\[postgres@LAMP1 data\]$ pg\_ctl -D $PGDATA start  
pg\_ctl: another server might be running; trying to start server anyway  
server starting  
\[postgres@LAMP1 data\]$ LOG:  database system was interrupted; last known up at 2015-06-29 10:29:14 CST  
LOG:  restored log file "00000002.history" from archive  
cp: cannot stat \`/usr/local/backups/00000003.history': No such file or directory  
LOG:  starting archive recovery  
LOG:  restored log file "00000002.history" from archive  
LOG:  restored log file "000000020000000000000002" from archive  
LOG:  redo starts at 0/2000028  
LOG:  consistent recovery state reached at 0/2000240  
LOG:  restored log file "000000020000000000000003" from archive  
cp: cannot stat \`/usr/local/backups/000000020000000000000004': No such file or directory  
LOG:  redo done at 0/3000430  
LOG:  last completed transaction was at log time 2015-06-29 15:11:00.35998+08  
LOG:  restored log file "000000020000000000000003" from archive  
cp: cannot stat \`/usr/local/backups/00000003.history': No such file or directory  
LOG:  selected new timeline ID: 3  
LOG:  restored log file "00000002.history" from archive  
LOG:  archive recovery complete  
LOG:  database system is ready to accept connections  
LOG:  autovacuum launcher started