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=#