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