如何在PostgreSQL中实现增量备份

简述

在postgres中,能够使用简单的pg_dump做某个数据库的完整备份。
此外,postgres中也同样能够实现增量备份,但针对的是整个数据库集群,即所有数据库都备份下来。

这里实现增量备份的思路是
1. 记录数据库系统的操作记录
2. 在某一时刻进行一次完整的数据库备份
3. 需要恢复时,还原上一次完整的数据库备份,根据操作记录恢复数据库至指定的某个时刻(即可以实现PITR 时间点恢复)

---

环境

系统：CentOS 6.5
数据库版本： Postgres 9.6

---

做法

1. 开启postgres的WAL归档

   WAL归档是记录对数据库的一些操作的序列文件,postgres将最近的WAL文件保存在 pg_xlog 中,所以我们还需要设置在产生WAL文件时将其持续保存到远程存储或者在本地的另外一个地方。WAL文件每个16MB,postgres自动进行分割。

   1.1 在数据目录/var/lib/pgsql/9.6/data 中找到posstgresql.conf配置文件(数据目录可能不同),这里需要设置参数:

   • wal_level

     设置保存操作日志的具体程度级别,要设置WAL归档至少要设置为replica

   • archive_mode

     是否启用WAL归档 , 设置为on

   • archive_command

     执行本地shell命令来归档已完成的WAL文件段,
     %p表示每一个产生的WAL文件位置,%f表示该文件名,

     以下命令表示把新产生的WAL文件转储到指定目录中
     后面的部分表示将正常输出,错误输出重定向到日志文件中,方便查看错误情况

     cp %p /mnt/server/archivedir/%f  > /usr/local/pgsql/test.log 2>&1 

   • (可选) archive_timeout

     archive_timeout强制服务器定期切换到新的WAL段文件,以至于WAL文件不会太少

   1.2 在成功配置之后,在/mnt/server/archivedir/下就能看到WAL归档文件

2. 进行文件系统级别的备份
   对postgres的数据库的数据目录 （我的是/var/lib/pgsql/9.6/data）进行压缩备份，作为系统的base backup

   2.1 在开始备份前,首先要在日志文件中做好标签

   使用默认账号,登入psql,输入

   select pg_start_backup('fullbackup');

   2.2 开始备份文件系统,在另一终端中,压缩备份postgres的数据目录

   tar -cvzf /var/lib/pgsql/9.6/pgdatabp20171112-1533.tar /var/lib/pgsql/9.6/data/

   2.3 结束备份,在刚刚的数据库连接中输入

   select pg_stop_backup();

   2.4 此时再使用ls -lsh查看下/mnt/server/archivedir/能够看到备份标签打在的时间点

   2.5 到此时已经完成数据库的基本备份(连同整个数据库的配置,数据等),可以进行一些数据库操作,记录这些操作进行的时间,以便之后进行恢复系统可以看到结果

3. 恢复系统
   使用之前备份过的data文件夹替换原来的,添加recovery.conf脚本,重启postgres服务

   3.1 假设系统意外崩溃

   kill -9 $(head -1 /var/lib/pgsql/9.6/data/postmaster.pid)

   3.2 先将旧的data目录重命名为bad.data,然后用之前备份的data目录替换之

   mv data bad.datatar -xvzf pgdatabp20171112-1533.tar mv var/lib/pgsql/9.6/data/ ./

   3.3 启动一次数据库看看,看到的应该就是备份时的数据,并没有备份后的操作结果存在

   /etc/init.d/postgresql-9.6 restart

   3.4 将bad.data内尚未归档的WAL文件手动归档至新的data目录中的pg_xlog目录

   cp /var/lib/pgsql/9.6/bad.data/pg_xlog/0* /var/lib/pgsql/9.6/data/pg_xlog

   3.5 在新的data目录内创建一个recovery.conf配置文件

   内容如下:

   restore_command = 'cp /mnt/server/archivedir/%f %p > /var/lib/pgsql/9.6/recovery.log 2>&1  'recovery_target_time = '2017-11-12 16:20:00'

   recovery_target_time 表示要恢复到的时间点
   recovery.conf文件将使PostgreSQL从 /mnt/server/archivedir/文件夹中取得归档日志文件，并恢复数据更改，直到 2017-11-12 16:20:00

   3.6 重启数据库

   /etc/init.d/postgresql-9.6 restart

---

引用

官方文档– https://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP-DATA
教程– https://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/