一、What is PITR
PITR是PostgreSQL中的一个重要功能特性,全称为Point-In-Time Recovery(基于时间点恢复)。它从PostgreSQL 8.0版本开始引入,该特性实现了基础备份+连续归档日志将数据库实例恢复到任意时间点。归档日志即WAL日志,WAL是PostgreSQL中用于保证数据完整性和可靠性的日志系统,它记录了所有对数据库所做的更改。在PITR中,我们需要开启WAL日志的归档(archiving)功能。
二、PITR的工作流程
- 从backup_label文件获取checkpoint位点值
- 从postgresql.conf(版本11之前是recovery.conf)获取restore_command、recovery_target_lsn\recovery_target_time\recovery_target_xid参数值
- 通过restore_command 获取wal日志文件,然后从基础备份中的checkpoint位点开始回放wal日志
- 回放完成后,会生成时间线历史文件,如:000000×.history
三、使用前提
主库需要开启归档,且wal日志级别为replica或者logical
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/postgres/archive/%f && cp %p /data/postgres/archive/%f’
四、具体实现步骤
1、生成基础备份
[postgres@pgserver1]$ pg_basebackup -D /data/postgres/data -Fp -Xs -v -P -h 192.168.10.145 -p 3372 -U replication
[postgres@pgserver]$ pg_basebackup -D /data/postgres/data/ -Fp -Xs -v -P -h 192.168.10.145 -p 3372 -U replication
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5010CA0 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_153170"
27855/27855 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/5011210
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
2、压缩打包,传到恢复机器并解压到data目录
[postgres@pgserver1]$ tar -zcvf data.tar.gz data
[postgres@pgserver1]$ scp data.tar.gz data 192.168.10.146:/data/postgres/
[postgres@pgserver2]$ tar -zxvf data.tar.gz
3、在data下面新建standby.signal文件
[postgres@pgserver2]$ touch standby.signal
4、将归档日志传到恢复机器目录/data/postgres/archive
5、获取预计恢复的xid位点
[postgres@pgserver2]$ pg_waldump -b -p /wal目录/ wal文件 > wal.log --解析wal日志查看目标位点
6、在postgresql.conf添加:
[postgres@pgserver2]$ vi postgresql.conf
restore_command = ‘cp /data/postgres/archive/%f %p’
recovery_target_xid = ‘目标lsn’
注意:这里也可以通过recovery_target_time或者recovery_target_lsn的方式
7、启动数据库服务,即可恢复到指定位点
[postgres@pgserver2]$ pg_ctl -D $PG_DATA start