1.初始化数据 postgres=# create database tdb01; CREATE DATABASE postgres=# \c tdb01 tdb01=# create table t1(id serial primary key,uname varchar(20)); CREATE TABLE insert into t1 (uname) values ('mia'); insert into t1 (uname) values ('pg'); insert into t1 (uname) values ('redis'); tdb01=# select * from t1; id | uname ----+------- 1 | mia 2 | pg 3 | redis (3 rows)

tdb01=# select now(); now
//------------------------------- 2020-10-10 22:23:02.741721+08 (1 row)

tdb01=# tdb01=# tdb01=# tdb01=# select now(); now
//------------------------------- 2020-10-10 22:23:14.938469+08 (1 row)

tdb01=#

2、创建基础备份

$ pg_basebackup -h 192.168.73.128 -D /pgbackup/ -U postgres Password: $

3、修改数据库

tdb01=# insert into t1 (uname) values ('mogodb'); INSERT 0 1 tdb01=# insert into t1 (uname) values ('oarcle'); INSERT 0 1 tdb01=# delete from t1; DELETE 5 tdb01=# select * from t1; id | uname ----+------- (0 rows)

tdb01=# \q

4.停止数据库 [postgres@pg13 ~]$ pg_ctl stop -D /pgdata/ waiting for server to shut down.... done server stopped [postgres@pg13 /]$ exit logout

5.重命名数据库目录,原目录(pgdata-->pgold),备份目录(pgbackup-->pgdata) [root@pg13 ~]# cd / [root@pg13 /]# mv pgdata pgold [root@pg13 /]# mv pgbackup pgdata

6.复制运行时pg_wal到临时恢复目录 [postgres@pg13 pgold]$ pwd /pgold [postgres@pg13 pgold]$ cp -r pg_wal /home/postgres/ [postgres@pg13 pgold]$

7.验证WAL日志大小

[postgres@pg13 pgold]$ du -sh pg_wal/ 657M pg_wal/ [postgres@pg13 pgold]$

[postgres@pg13 ~]$ du -sh pg_wal/ 657M pg_wal/ [postgres@pg13 ~]$

8.创建recovery文件

[postgres@pg13 ~]$ cd /pgdata/ [postgres@pg13 pgdata]$ touch recovery.signal [postgres@pg13 pgdata]$

9.配置恢复时间点 [postgres@pg13 pgdata]$ vi postgresql.conf restore_command = 'cp /home/postgres/pg_wal/%f %p' recovery_target_time = '2020-10-10 22:23:02.741721+08' recovery_target_inclusive = off # just before the recovery target (off)

10.启动数据库 [postgres@pg13 pgdata]$ pg_ctl start -D /pgdata

11.验证恢复

tdb01=# select * from t1; id | uname ----+------- 1 | mia 2 | pg 3 | redis (3 rows)

tdb01=#

默认恢复完成,数据库为read_only状态 #recovery_target_action = 'pause'

tdb01=# show transaction_read_only ; transaction_read_only //----------------------- on (1 row)

tdb01=# select pg_is_in_recovery(); pg_is_in_recovery //------------------- t (1 row)

tdb01=# tdb01=# insert into t1 (uname) values ('golang'); ERROR: cannot execute INSERT in a read-only transaction tdb01=#

12.切换为读写状态

tdb01=# select pg_promote(); pg_promote //------------ t (1 row)

tdb01=# insert into t1 (uname) values ('golang'); INSERT 0 1 tdb01=#