1. 主要参数配置

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 t(id int,info text);
insert into t values(1,'finish init '||now());
insert into t values(2,'before backup 1 '||now());
复制

3. 基础备份

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

4. 模拟灾难操作

insert into t values(3,'before delete '||now());
select current_timestamp;
       current_timestamp       
-------------------------------
 2019-12-27 12:53:07.530293+08
 
delete from t;
复制

5. 恢复操作与测试

$ 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_time='2019-12-27 12:53:07.530293+08'

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

$ pg_ctl -D /opt/data5432 start &
2019-12-27 12:55:15.825 CST [2393] LOG:  recovery has paused
2019-12-27 12:55:15.825 CST [2393] HINT:  Execute pg_wal_replay_resume() to continue.

$ psql -p5432 -Upostgres
-- 注意此时数据库进入read only 模式
postgres=# delete from t;
ERROR:  cannot execute DELETE in a read-only transaction

postgres=# select * from t;
 id |                     info                      
----+-----------------------------------------------
  1 | finish init 2019-12-27 12:46:34.823462+08
  2 | before backup 1 2019-12-27 12:46:35.631821+08
  3 | before delete 2019-12-27 12:47:07.919464+08
(3 rows)

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

postgres=# delete from t where id=2;
DELETE 1
postgres=# select * from t;
 id |                    info                     
----+---------------------------------------------
  1 | finish init 2019-12-27 12:46:34.823462+08
  3 | before delete 2019-12-27 12:47:07.919464+08
(2 rows)
复制

6. 注意事项

注意查看恢复过程数据库日志，确认数据后执行函数pg_wal_replay_resume()，否则数据库将一直处于read-only 状态。
复制