1. 主要参数配置

archive_mode=on
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /opt/archive5432/%f'
复制

2. 数据插入

create user repuser replication login connection limit 5 encrypted password'123456';
create table t2(id int,info text);
insert into t2 values(1,'data1 '||now());
insert into t2 values(2,'data2 '||now());
insert into t2 values(3,'data3 '||now());
复制

3. 基础备份

pg_basebackup -D /opt/data-pitr/ -Fp -Xs -v -P -h127.0.0.1 -p5432 -Urepuser
复制

4. 创建还原点

select pg_create_restore_point('my_res_point1');

--数据变更
 insert into t2 values(4,'data4 '||now());
 update t2 set info=null where id=2;
select * from t2 order by id;
 id |                info                 
----+-------------------------------------
  1 | data1 2019-12-27 13:37:23.986668+08
  2 | 
  3 | data3 2019-12-27 13:37:24.834763+08
  4 | data4 2019-12-27 13:39:16.07917+08
(4 rows)
复制

5. 恢复到还原点“my_res_point1”

$ pg_ctl -D /opt/data5432 stop
$ rm -rf data5432
$ cp -R data-pitr/ data5432

$ vi data5432/postgresql.conf 
restore_command = 'cp /opt/archive5432/%f %p'
recovery_target_name='my_res_point1'

$ touch data5432/recovery.signal  --保存空文件即可

$ pg_ctl -D /opt/data5432 start &
2019-12-27 13:42:00.626 CST [2554] LOG:  recovery stopping at restore point "my_res_point1", time 2019-12-27 13:39:01.703618+08
2019-12-27 13:42:00.626 CST [2554] LOG:  recovery has paused
2019-12-27 13:42:00.626 CST [2554] HINT:  Execute pg_wal_replay_resume() to continue.

$ psql -p5432 -Upostgres
-- 注意此时数据库进入read only 模式
postgres=# select * from t2;
 id |                info                 
----+-------------------------------------
  1 | data1 2019-12-27 13:37:23.986668+08
  2 | data2 2019-12-27 13:37:23.997032+08
  3 | data3 2019-12-27 13:37:24.834763+08
(3 rows)

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)
复制