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 t3(id int,info text);
insert into t3 values(1,'data1 '||now());
insert into t3 values(3,'data2 '||now());
insert into t3 values(5,'data3 '||now());
复制

3. 基础备份

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

4.开启事务删数据

postgres=# begin;
BEGIN
postgres=# select txid_current();
 txid_current 
--------------
          492
(1 row)

postgres=# delete from t3 where id=3;
DELETE 1
postgres=# end;
COMMIT
postgres=# select * from t3;
 id |                info                 
----+-------------------------------------
  1 | data1 2019-12-27 14:17:11.442802+08
  5 | data3 2019-12-27 14:17:12.330043+08
(2 rows)
复制

5. 恢复到事务的xid=491(前面的492-1)

$ 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_xid=491

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

$ pg_ctl -D /opt/data5432 start &
2019-12-27 14:21:47.748 CST [2879] LOG:  recovery stopping after abort of transaction 491, time 2019-12-27 14:18:51.63361+08
2019-12-27 14:21:47.748 CST [2879] LOG:  recovery has paused
2019-12-27 14:21:47.748 CST [2879] HINT:  Execute pg_wal_replay_resume() to continue.


$ psql -p5432 -Upostgres
-- 注意此时数据库进入read only 模式
postgres=# select * from t3;
 id |                info                 
----+-------------------------------------
  1 | data1 2019-12-27 14:17:11.442802+08
  3 | data2 2019-12-27 14:17:11.450694+08
  5 | data3 2019-12-27 14:17:12.330043+08
(3 rows)

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