postgresql热备份与恢复

一 热备份的原理是“基础备份文件”+“归档日志”进行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