postgres-基于恢复到指定时间测试

--//当前数据

[postgres@postgres ~]$ p
psql (11.1)
Type "help" for help.

postgres=# select *from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2020-04-02 11:02:33.039506-04
 34 |    2 |             | 2020-04-02 13:52:24.368258-04
 67 |    3 |             | 2020-04-02 14:32:05.711384-04
 68 |    4 |             | 2020-04-02 14:40:01.597981-04
(4 rows)

postgres=# 
postgres=# 

--//记录当前时间

postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-02 15:19:15.260655-04
(1 row)

--//插入数据

postgres=# insert into tbl(ival) values (5);
INSERT 0 1
postgres=# 
postgres=# 

--//再次记录时间

postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-02 15:19:37.369442-04
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/150003A8
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/16000078
(1 row)

postgres=# 

--//做一个基础备份

pg_basebackup -Ft -Pv -Xf -z -Z5 -h127.0.0.1 -p 5442 -D /home/postgres/11/backups/new
[postgres@postgres ~]$ pg_basebackup -Ft -Pv -Xf -z -Z5 -h127.0.0.1 -p 5442 -D /home/postgres/11/backups/new
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/18000028 on timeline 9
41743/41743 kB (100%), 2/2 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/180000F8
pg_basebackup: base backup completed
[postgres@postgres ~]$ 

--//再次往tbl表中插入数据

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2020-04-02 11:02:33.039506-04
 34 |    2 |             | 2020-04-02 13:52:24.368258-04
 67 |    3 |             | 2020-04-02 14:32:05.711384-04
 68 |    4 |             | 2020-04-02 14:40:01.597981-04
 69 |    5 |             | 2020-04-02 15:19:29.752758-04
(5 rows)

postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-02 15:21:38.791391-04
(1 row)

postgres=# 
postgres=# insert into tbl(ival) values (6);
INSERT 0 1
postgres=# 
postgres=# 
postgres=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-02 15:21:54.126422-04
(1 row)

postgres=# insert into tbl(ival) values (7);
INSERT 0 1
postgres=# 
postgres=#  SELECT current_timestamp;
      current_timestamp       
------------------------------
 2020-04-02 15:22:05.36129-04
(1 row)

postgres=# 
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/19000458
(1 row)

postgres=# 

--//现在打算把数据恢复到 2020-04-02 15:21:54.126422-04(不恢复第7行数据)
--//关闭数据库

[postgres@postgres ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@postgres ~]$ 
[postgres@postgres pg_wal]$ ls -ltr
total 147484
-rw------- 1 postgres postgres       50 Apr  2 14:30 00000004.history
-rw------- 1 postgres postgres       92 Apr  2 15:05 00000005.history
-rw------- 1 postgres postgres      135 Apr  2 15:08 00000006.history
-rw------- 1 postgres postgres 16777216 Apr  2 15:08 000000060000000000000013
-rw------- 1 postgres postgres 16777216 Apr  2 15:08 000000060000000000000014
-rw------- 1 postgres postgres      135 Apr  2 15:08 RECOVERYHISTORY
-rw------- 1 postgres postgres      178 Apr  2 15:08 00000009.history
-rw------- 1 postgres postgres 16777216 Apr  2 15:19 000000090000000000000015
-rw------- 1 postgres postgres 16777216 Apr  2 15:19 000000090000000000000016
-rw------- 1 postgres postgres 16777216 Apr  2 15:21 000000090000000000000017
-rw------- 1 postgres postgres 16777216 Apr  2 15:21 000000090000000000000018
-rw------- 1 postgres postgres      340 Apr  2 15:21 000000090000000000000018.00000028.backup
-rw------- 1 postgres postgres 16777216 Apr  2 15:22 000000090000000000000019
-rw------- 1 postgres postgres 16777216 Apr  2 15:23 00000009000000000000001A
drwx------ 2 postgres postgres     4096 Apr  2 15:23 archive_status
-rw------- 1 postgres postgres 16777216 Apr  2 15:23 00000009000000000000001B
[postgres@postgres pg_wal]$ 

--//删除数据库

[postgres@postgres pg_wal]$  rm -rf /home/postgres/pg11/
[postgres@postgres pg_wal]$  mkdir -p /home/postgres/pg11/
[postgres@postgres pg_wal]$  chmod 0700 /home/postgres/pg11/
[postgres@postgres pg_wal]$ 
[postgres@postgres new]$ ls -ltr /home/postgres/11/backups/new
total 2948
-rw------- 1 postgres postgres     433 Apr  2 14:34 16387.tar.gz
-rw------- 1 postgres postgres 3014491 Apr  2 14:34 base.tar.gz
[postgres@postgres new]$ 

--//恢复

tar -xvf /home/postgres/11/backups/new/base.tar.gz -C /home/postgres/pg11/
[postgres@postgres new]$ tar -xvf /home/postgres/11/backups/new/base.tar.gz -C /home/postgres/pg11/
backup_label
tablespace_map
current_logfiles
pg_multixact/
pg_multixact/offsets/
pg_multixact/offsets/0000
pg_multixact/members/
.
.
.

--//启动数据库

cp /home/postgres/recovery.done /home/postgres/pg11/recovery.conf
[postgres@postgres ~]$ cp /home/postgres/recovery.done /home/postgres/pg11/recovery.conf
[postgres@postgres ~]$ 

[postgres@postgres ~]$ grep -v '^#' /home/postgres/pg11/recovery.conf
restore_command = 'cp /home/postgres/archivedir/%f %p'
recovery_target_time = '2020-04-02 15:21:54.126422-04'  --//指定时间
[postgres@postgres ~]$ 

[postgres@postgres ~]$ pg_ctl start
waiting for server to start....2020-04-02 15:26:34.344 EDT [48503] LOG:  listening on IPv4 address "0.0.0.0", port 5442
2020-04-02 15:26:34.344 EDT [48503] LOG:  listening on IPv6 address "::", port 5442
2020-04-02 15:26:34.346 EDT [48503] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5442"
2020-04-02 15:26:34.358 EDT [48503] LOG:  redirecting log output to logging collector process
2020-04-02 15:26:34.358 EDT [48503] HINT:  Future log output will appear in directory "on".
 done
server started
[postgres@postgres ~]$ 

--//检查数据

[postgres@postgres ~]$ p
psql (11.1)
Type "help" for help.

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2020-04-02 11:02:33.039506-04
 34 |    2 |             | 2020-04-02 13:52:24.368258-04
 67 |    3 |             | 2020-04-02 14:32:05.711384-04
 68 |    4 |             | 2020-04-02 14:40:01.597981-04
 69 |    5 |             | 2020-04-02 15:19:29.752758-04
 70 |    6 |             | 2020-04-02 15:21:49.947237-04
(6 rows)

postgres=# 

--//查看详细日志

[postgres@postgres on]$ vi postgresql-2020-04-02_152634.csv
2020-04-02 15:26:34.358 EDT,,,48503,,5e863c6a.bd77,1,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2020-04-02 15:26:34.361 EDT,,,48505,,5e863c6a.bd79,1,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"database system was interrupted; last known up at 2020-04-02 15:21:01 EDT",,,,,,,,,""
--//提示point-in-time
2020-04-02 15:26:34.396 EDT,,,48505,,5e863c6a.bd79,2,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"starting point-in-time recovery to 2020-04-02 15:21:54.126422-04",,,,,,,,,""
2020-04-02 15:26:34.398 EDT,,,48505,,5e863c6a.bd79,3,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"restored log file ""00000009.history"" from archive",,,,,,,,,""
2020-04-02 15:26:34.423 EDT,,,48505,,5e863c6a.bd79,4,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"restored log file ""000000090000000000000018"" from archive",,,,,,,,,""
2020-04-02 15:26:34.460 EDT,,,48505,,5e863c6a.bd79,5,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"redo starts at 0/18000028",,,,,,,,,""
2020-04-02 15:26:34.460 EDT,,,48505,,5e863c6a.bd79,6,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"consistent recovery state reached at 0/180000F8",,,,,,,,,""
2020-04-02 15:26:34.461 EDT,,,48503,,5e863c6a.bd77,2,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2020-04-02 15:26:34.476 EDT,,,48505,,5e863c6a.bd79,7,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"restored log file ""000000090000000000000019"" from archive",,,,,,,,,""
2020-04-02 15:26:34.499 EDT,,,48505,,5e863c6a.bd79,8,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"recovery stopping before commit of transaction 581, time 2020-04-02 15:22:00.054457-04",,,,,,,,,""

--//recovery has paused,需要执行select  pg_wal_replay_resume()
2020-04-02 15:26:34.499 EDT,,,48505,,5e863c6a.bd79,9,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"recovery has paused",,"Execute pg_wal_replay_resume() to continue.",,,,,,,""
~

--//如果不执行select  pg_wal_replay_resume()，开启新事务会有如下报错

postgres=# begin;
BEGIN
postgres=# select txid_current();
ERROR:  cannot execute txid_current() during recovery
postgres=# 
postgres=# rollback;
ROLLBACK
postgres=# select  pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2020-04-02 11:02:33.039506-04
 34 |    2 |             | 2020-04-02 13:52:24.368258-04
 67 |    3 |             | 2020-04-02 14:32:05.711384-04
 68 |    4 |             | 2020-04-02 14:40:01.597981-04
 69 |    5 |             | 2020-04-02 15:19:29.752758-04
 70 |    6 |             | 2020-04-02 15:21:49.947237-04
(6 rows)

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

postgres=# 

--//后台日志变化

2020-04-02 15:29:21.798 EDT,,,48505,,5e863c6a.bd79,10,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"redo done at 0/190003E0",,,,,,,,,""
2020-04-02 15:29:21.798 EDT,,,48505,,5e863c6a.bd79,11,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"last completed transaction was at log time 2020-04-02 15:21:49.947677-04",,,,,,,,,""
2020-04-02 15:29:21.803 EDT,,,48505,,5e863c6a.bd79,12,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"selected new timeline ID: 10",,,,,,,,,""
2020-04-02 15:29:21.847 EDT,,,48505,,5e863c6a.bd79,13,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2020-04-02 15:29:21.849 EDT,,,48505,,5e863c6a.bd79,14,,2020-04-02 15:26:34 EDT,1/0,0,LOG,00000,"restored log file ""00000009.history"" from archive",,,,,,,,,""
2020-04-02 15:29:21.953 EDT,,,48503,,5e863c6a.bd77,3,,2020-04-02 15:26:34 EDT,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""