PostgreSQL利用归档恢复数据

duqk1982

已于 2023-04-24 10:12:24 修改

阅读量1.1k

点赞数 2

分类专栏： PostgreSQL 文章标签： postgresql 数据库

于 2023-04-22 17:16:09 首次发布

本文链接：https://blog.csdn.net/dqk1982/article/details/130307231

版权

PostgreSQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

本次恢复过程是数据库没有备份，但保留了所有的归档的前提下，通过归档恢复数据库的操作。

本次恢复注意点：在create database之前，手工发起checkpoint，作为恢复的起始点，不能以create database的那个lsn作为恢复的起始点。（当然也可以从现有归档最早的checkpoint处开始恢复数据，测试可以做到。）测试过程如下：

测试环境：PostgreSQL 14.7

前提：
1.数据库开启归档；
2.数据库从最先开始的归档都存在；
3.postgres为源码编译安装；
4.$PGDATA对应的目录为/data/pgdata；

1.检查数据库归档情况

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# show archive_mode;
 archive_mode
--------------
 on
(1 row)

postgres=# show archive_command;
       archive_command
-----------------------------
 cp %p /data/archive_wals/%f
(1 row)

postgres=# select * from pg_stat_archiver\gx
-[ RECORD 1 ]------+------------------------------
archived_count     | 2
last_archived_wal  | 000000010000000000000002
last_archived_time | 2023-04-22 15:53:29.02742+08
failed_count       | 0
last_failed_wal    |
last_failed_time   |
stats_reset        | 2023-03-12 11:01:26.265778+08

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/30001D8
(1 row)

postgres=# \q
[postgres@du101 ~]$ ls -trl /data/archive_wals/*
-rw------- 1 postgres postgres 16777216 Apr 22 15:52 /data/archive_wals/000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr 22 15:53 /data/archive_wals/000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr 22 15:54 /data/archive_wals/000000010000000000000003
[postgres@du101 ~]$

–确认数据库归档存在。

2.模拟业务

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/4000060
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/4000160
(1 row)

postgres=#

–此处在create database之前手工执行checkpoint。恢复开始的时间点就从这个checkpoint的lsn开始。

postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table t(id int);
CREATE TABLE
testdb=# insert into t select generate_series(1,10000);
INSERT 0 10000
testdb=# delete from t;
DELETE 10000
testdb=# truncate table t;
TRUNCATE TABLE
testdb=# insert into t select generate_series(1,10);
INSERT 0 10
testdb=# update t set id=100;
UPDATE 10
testdb=# checkpoint;
CHECKPOINT
testdb=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/5148220
(1 row)

testdb=# select pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/6000000
(1 row)

testdb=#

— 0/6000000将作为终止恢复的lsn

3.模拟故障

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# drop database testdb;
DROP DATABASE
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/6000798
(1 row)

postgres=# \q
[postgres@du101 ~]$

–这里模拟直接删库操作。

4.恢复数据

查看pg_controldata确认信息：

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ pg_controldata
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7209488936723521799
Database cluster state:               shut down
pg_control last modified:             Sat 22 Apr 2023 04:00:48 PM CST
Latest checkpoint location:           0/8000028
Latest checkpoint's REDO location:    0/8000028
Latest checkpoint's REDO WAL file:    000000010000000000000008
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:744
Latest checkpoint's NextOID:          16389
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        727
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 22 Apr 2023 04:00:48 PM CST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            834385bcab0e3981e07462a667cdf6129d91cf7096244316c84a12cfd1a557e1
[postgres@du101 ~]$

需要关注的地方：

Latest checkpoint location:           0/8000028
Latest checkpoint's REDO location:    0/8000028
Latest checkpoint's REDO WAL file:    000000010000000000000008
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1

4.1修改控制文件的Latest checkpoint location

查看归档信息：

[postgres@du101 ~]$ ls -trl /data/archive_wals/*
-rw------- 1 postgres postgres 16777216 Apr 22 15:52 /data/archive_wals/000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr 22 15:53 /data/archive_wals/000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr 22 15:54 /data/archive_wals/000000010000000000000003
-rw------- 1 postgres postgres 16777216 Apr 22 15:56 /data/archive_wals/000000010000000000000004
-rw------- 1 postgres postgres 16777216 Apr 22 15:58 /data/archive_wals/000000010000000000000005
-rw------- 1 postgres postgres 16777216 Apr 22 16:00 /data/archive_wals/000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr 22 16:00 /data/archive_wals/000000010000000000000007

[postgres@du101 ~]$ pg_waldump /data/archive_wals/000000010000000000000004
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/04000028, prev 0/030001C0, desc: RUNNING_XACTS nextXid 734 latestCompletedXid 733 oldestRunningXid 734
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/04000060, prev 0/04000028, desc: RUNNING_XACTS nextXid 734 latestCompletedXid 733 oldestRunningXid 734
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/04000098, prev 0/04000060, desc: CHECKPOINT_ONLINE redo 0/4000060; tli 1; prev tli 1; fpw true; xid 0:734; oid 13893; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 734; online
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/04000110, prev 0/04000098, desc: RUNNING_XACTS nextXid 734 latestCompletedXid 733 oldestRunningXid 734
rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/04000148, prev 0/04000110, desc: SWITCH
[postgres@du101 ~]$

[postgres@du101 ~]$ pg_waldump /data/archive_wals/000000010000000000000005|head -20
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/05000028, prev 0/04000148, desc: RUNNING_XACTS nextXid 734 latestCompletedXid 733 oldestRunningXid 734
rmgr: XLOG        len (rec/tot):     30/    30, tx:          0, lsn: 0/05000060, prev 0/05000028, desc: NEXTOID 24576
rmgr: Heap        len (rec/tot):     54/  1238, tx:        734, lsn: 0/05000080, prev 0/05000060, desc: INSERT off 4 flags 0x00, blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Btree       len (rec/tot):     53/   197, tx:        734, lsn: 0/05000558, prev 0/05000080, desc: INSERT_LEAF off 4, blkref #0: rel 1664/0/2671 blk 1 FPW
rmgr: Btree       len (rec/tot):     53/   173, tx:        734, lsn: 0/05000620, prev 0/05000558, desc: INSERT_LEAF off 4, blkref #0: rel 1664/0/2672 blk 1 FPW
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/050006D0, prev 0/05000620, desc: RUNNING_XACTS nextXid 735 latestCompletedXid 733 oldestRunningXid 734; 1 xacts: 734
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/05000708, prev 0/050006D0, desc: CHECKPOINT_ONLINE redo 0/50006D0; tli 1; prev tli 1; fpw true; xid 0:735; oid 24576; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 734; online
rmgr: Database    len (rec/tot):     42/    42, tx:        734, lsn: 0/05000780, prev 0/05000708, desc: CREATE copy dir 1663/1 to 1663/16384
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/050007B0, prev 0/05000780, desc: RUNNING_XACTS nextXid 735 latestCompletedXid 733 oldestRunningXid 734; 1 xacts: 734
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/050007E8, prev 0/050007B0, desc: CHECKPOINT_ONLINE redo 0/50007B0; tli 1; prev tli 1; fpw true; xid 0:735; oid 24576; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 734; online
rmgr: Transaction len (rec/tot):     66/    66, tx:        734, lsn: 0/05000860, prev 0/050007E8, desc: COMMIT 2023-04-22 15:57:05.460327 CST; inval msgs: catcache 21; sync
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/050008A8, prev 0/05000860, desc: RUNNING_XACTS nextXid 735 latestCompletedXid 734 oldestRunningXid 735
rmgr: Storage     len (rec/tot):     42/    42, tx:          0, lsn: 0/050008E0, prev 0/050008A8, desc: CREATE base/16384/16385
rmgr: Heap        len (rec/tot):     54/  8162, tx:        735, lsn: 0/05000910, prev 0/050008E0, desc: INSERT off 43 flags 0x01, blkref #0: rel 1663/16384/1247 blk 13 FPW
rmgr: Btree       len (rec/tot):     53/  4813, tx:        735, lsn: 0/05002910, prev 0/05000910, desc: INSERT_LEAF off 236, blkref #0: rel 1663/16384/2703 blk 2 FPW
rmgr: Btree       len (rec/tot):     53/  6129, tx:        735, lsn: 0/05003BE0, prev 0/05002910, desc: INSERT_LEAF off 134, blkref #0: rel 1663/16384/2704 blk 2 FPW
rmgr: Heap2       len (rec/tot):     57/  8205, tx:        735, lsn: 0/050053F0, prev 0/05003BE0, desc: MULTI_INSERT 1 tuples flags 0x03, blkref #0: rel 1663/16384/2608 blk 54 FPW
rmgr: Btree       len (rec/tot):     53/  4649, tx:        735, lsn: 0/05007418, prev 0/050053F0, desc: INSERT_LEAF off 105, blkref #0: rel 1663/16384/2673 blk 37 FPW
rmgr: Btree       len (rec/tot):     53/  5321, tx:        735, lsn: 0/05008660, prev 0/05007418, desc: INSERT_LEAF off 187, blkref #0: rel 1663/16384/2674 blk 52 FPW
rmgr: Heap        len (rec/tot):    211/   211, tx:        735, lsn: 0/05009B30, prev 0/05008660, desc: INSERT+INIT off 1 flags 0x00, blkref #0: rel 1663/16384/1247 blk 14
[postgres@du101 ~]$

—从最早的归档中找CREATE copy dir 1663/1 to 1663/16384 ，然后从这个之前的checkpoint的lsn开始恢复（ lsn: 0/04000098, prev 0/04000060）。
–不能选择CREATE copy dir 的lsn. create database过程中会做两次checkpoint

以16进制的方式手工修改pg_control：

[atlasdb@du104 ~]$ vi -b /data/pgdata/global/pg_control
:%!xxd 进入16进制进行编辑
编辑前：
0000000: 07a1 838d fd3f 0d64 1405 0000 2de9 0b0c  .....?.d....-...
0000010: 0100 0000 0000 0000 3094 4364 0000 0000  ........0.Cd....
0000020: 2800 0008 0000 0000 2800 0008 0000 0000  (.......(.......
0000030: 0100 0000 0100 0000 0100 0000 0000 0000  ................
0000040: e802 0000 0000 0000 0540 0000 0100 0000  .........@......
0000050: 0000 0000 d702 0000 0100 0000 0100 0000  ................

从上面pg_datacontroldata的输出确认要修改的地方：
Latest checkpoint location:           0/8000028
Latest checkpoint's REDO location:    0/8000028
Latest checkpoint's REDO WAL file:    000000010000000000000008
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1

编辑后：
0000000: 07a1 838d fd3f 0d64 1405 0000 2de9 0b0c  .....?.d....-...
0000010: 0100 0000 0000 0000 3094 4364 0000 0000  ........0.Cd....
0000020: 9800 0004 0000 0000 6000 0004 0000 0000  (.......(.......
0000030: 0100 0000 0100 0000 0100 0000 0000 0000  ................
0000040: e802 0000 0000 0000 0540 0000 0100 0000  .........@......
0000050: 0000 0000 d702 0000 0100 0000 0100 0000  ................

:%!xxd -r退出16进制
保存退出vi编辑

再次查看pg_ctontroldata的输出，确认Latest checkpoint location，Latest checkpoint’s REDO location，Latest checkpoint’s REDO WAL file，Latest checkpoint’s TimeLineID和Latest checkpoint’s PrevTimeLineID是否正确。

[postgres@du101 ~]$ pg_controldata
WARNING: Calculated CRC checksum does not match value stored in file.
Either the file is corrupt, or it has a different layout than this program
is expecting.  The results below are untrustworthy.

pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7209488936723521799
Database cluster state:               shut down
pg_control last modified:             Sat 22 Apr 2023 04:00:48 PM CST
Latest checkpoint location:           0/4000098
Latest checkpoint's REDO location:    0/4000060
Latest checkpoint's REDO WAL file:    000000010000000000000004
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:744
Latest checkpoint's NextOID:          16389
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        727
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 22 Apr 2023 04:00:48 PM CST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            834385bcab0e3981e07462a667cdf6129d91cf7096244316c84a12cfd1a557e1
[postgres@du101 ~]$

4.2修改控制文件中checksum

从pg_controldata的输出有CRC checksum 不匹配的告警，原因是每次修改pg_control，则这个文件pg_control对应的checksum都会发生变换，所以还需要修改pg_control对应的checksum。

通过gdb的方式打印数据目录和控制文件中的crc：

[postgres@du101 ~]$ whereis postgres
postgres: /opt/pgsql/bin/postgres
[postgres@du101 ~]$ gdb /opt/pgsql/bin/postgres
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-120.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /opt/pgsql/bin/postgres...done.
(gdb) set args -D /data/pgdata
(gdb) break ReadControlFile
Breakpoint 1 at 0x51b890: file xlog.c, line 4793.
(gdb) run
Starting program: /opt/pgsql/bin/postgres -D /data/pgdata
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".

Breakpoint 1, ReadControlFile () at xlog.c:4793
4793    xlog.c: No such file or directory.
Missing separate debuginfos, use: debuginfo-install audit-libs-2.8.5-4.el7.x86_64 glibc-2.17-326.el7_9.x86_64 keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.15.1-54.el7_9.x86_64 libcap-ng-0.7.5-4.el7.x86_64 libcom_err-1.42.9-19.el7.x86_64 libselinux-2.5-15.el7.x86_64 libxml2-2.9.1-6.el7_9.6.x86_64 openssl-libs-1.0.2k-25.el7_9.x86_64 pam-1.1.8-23.el7.x86_64 pcre-8.32-17.el7.x86_64 xz-libs-5.2.2-2.el7_9.x86_64 zlib-1.2.7-20.el7_9.x86_64
(gdb)

结合源码(src\backend\access\transam\xlog.c)分析，到了4860之后，所需要的crc的值才会被赋值：
在这里插入图片描述
所以一直按n，到达4860这一行，然后开始打印变量：

(gdb) n
4802    in xlog.c
(gdb) n
4793    in xlog.c
(gdb) n
4802    in xlog.c
(gdb) n
4804    in xlog.c
(gdb) n
4802    in xlog.c
(gdb) n
4804    in xlog.c
(gdb) n
4810    in xlog.c
(gdb) n
4811    in xlog.c
(gdb) n
4810    in xlog.c
(gdb) n
4811    in xlog.c
(gdb) n
4812    in xlog.c
(gdb) n
4811    in xlog.c
(gdb) n
4812    in xlog.c
(gdb) n
4825    in xlog.c
(gdb) n
4827    in xlog.c
(gdb) n
4825    in xlog.c
(gdb) n
4827    in xlog.c
(gdb) n
4836    in xlog.c
(gdb) n
4855    in xlog.c
(gdb) n
4860    in xlog.c
(gdb) n
4858    in xlog.c
(gdb) n
4860    in xlog.c
(gdb) p crc
$1 = 2292045281
(gdb) p ControlFile->crc
$2 = 1614206884
(gdb) p /x crc
$3 = 0x889dd5e1
(gdb) p /x ControlFile->crc
$4 = 0x6036d7a4
(gdb) quit
A debugging session is active.

        Inferior 1 [process 1504] will be killed.

Quit anyway? (y or n) y
[postgres@du101 ~]$

说明：

说明：
•手工修改后的pg_control对应的crc：
(gdb) p crc
$1 = 2292045281
•控制文件记录的之前修改对应的crc：
(gdb) p ControlFile->crc
$2 = 1614206884
•以16进制的方式打印手工修改后的pg_control对应的crc：
(gdb) p /x crc
$3 = 0x889dd5e1
•以16进制的方式打印制文件记录的之前修改对应的crc：
(gdb) p /x ControlFile->crc
$4 = 0x6036d7a4

再次以16进制的方式修改控制文件

修改前：
00000f0: cc07 0000 0008 0000 0100 0000 0000 0000  ................
0000100: 8343 85bc ab0e 3981 e074 62a6 67cd f612  .C....9..tb.g...
0000110: 9d91 cf70 9624 4316 c84a 12cf d1a5 57e1  ...p.$C..J....W.
0000120: a4d7 3660 0000 0000 0000 0000 0000 0000  ..6`............
0000130: 0000 0000 0000 0000 0000 0000 0000 0000  ................

找出上一步算出的ControlFile对应该的crc：
(gdb) p /x ControlFile->crc
$4 = 0x6036d7a4 
修改成手工修改后的pg_control对应的crc：
(gdb) p /x crc
$3 = 0x889dd5e1

修改后：
00000f0: cc07 0000 0008 0000 0100 0000 0000 0000  ................
0000100: 8343 85bc ab0e 3981 e074 62a6 67cd f612  .C....9..tb.g...
0000110: 9d91 cf70 9624 4316 c84a 12cf d1a5 57e1  ...p.$C..J....W.
0000120: e1d5 9d88 0000 0000 0000 0000 0000 0000  ..6`............
0000130: 0000 0000 0000 0000 0000 0000 0000 0000  ................

再次查看pg_controldata输出确认无误：

[postgres@du101 ~]$ pg_controldata
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7209488936723521799
Database cluster state:               shut down
pg_control last modified:             Sat 22 Apr 2023 04:00:48 PM CST
Latest checkpoint location:           0/4000098
Latest checkpoint's REDO location:    0/4000060
Latest checkpoint's REDO WAL file:    000000010000000000000004
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:744
Latest checkpoint's NextOID:          16389
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        727
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 22 Apr 2023 04:00:48 PM CST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            834385bcab0e3981e07462a667cdf6129d91cf7096244316c84a12cfd1a557e1
[postgres@du101 ~]$

4.3创建recovery.conf恢复数据

[postgres@du101 ~]$ vi /data/pgdata/postgresql.conf
[postgres@du101 ~]$ tail -5 /data/pgdata/postgresql.conf
max_wal_size=500MB
wal_keep_size=16MB
restore_command = 'cp /data/archive_wals/%f %p'
recovery_target_timeline = 'latest'
recovery_target_lsn='0/6000000'
[postgres@du101 ~]$
---增加这几行

[postgres@du101 ~]$ touch /data/pgdata/recovery.signal
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-04-22 16:47:43.338 CST [1774] LOG:  00000: redirecting log output to logging collector process
2023-04-22 16:47:43.338 CST [1774] HINT:  Future log output will appear in directory "pg_log".
2023-04-22 16:47:43.338 CST [1774] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@du101 ~]$ ll /data/pgdata/
total 68K
drwx------ 6 postgres postgres   54 Apr 22 16:47 base
-rw------- 1 postgres postgres   40 Apr 22 16:47 current_logfiles
drwx------ 2 postgres postgres 4.0K Apr 22 16:47 global
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_commit_ts
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_dynshmem
-rw------- 1 postgres postgres 4.8K Mar 12 11:00 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Mar 12 10:59 pg_ident.conf
drwx------ 2 postgres postgres   72 Apr 22 16:41 pg_log
drwx------ 4 postgres postgres   68 Apr 22 16:00 pg_logical
drwx------ 4 postgres postgres   36 Mar 12 10:59 pg_multixact
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_notify
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_replslot
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_serial
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_snapshots
drwx------ 2 postgres postgres    6 Apr 22 16:47 pg_stat
drwx------ 2 postgres postgres    6 Apr 22 16:00 pg_stat_tmp
drwx------ 2 postgres postgres   18 Mar 12 10:59 pg_subtrans
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_tblspc
drwx------ 2 postgres postgres    6 Mar 12 10:59 pg_twophase
-rw------- 1 postgres postgres    3 Mar 12 10:59 PG_VERSION
drwx------ 3 postgres postgres  252 Apr 22 16:47 pg_wal
drwx------ 2 postgres postgres   18 Mar 12 10:59 pg_xact
-rw------- 1 postgres postgres   88 Mar 12 10:59 postgresql.auto.conf
-rw------- 1 postgres postgres  29K Apr 22 16:43 postgresql.conf
-rw------- 1 postgres postgres   24 Apr 22 16:47 postmaster.opts
-rw------- 1 postgres postgres   67 Apr 22 16:47 postmaster.pid
-rw-rw-r-- 1 postgres postgres    0 Apr 22 16:46 recovery.signal
[postgres@du101 ~]$

数据库日志输出如下：

2023-04-22 16:47:43.338 CST,,,1774,,64439f2f.6ee,1,,2023-04-22 16:47:43 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,"PostmasterMain, postmaster.c:1119","","postmaster",,0
2023-04-22 16:47:43.338 CST,,,1774,,64439f2f.6ee,2,,2023-04-22 16:47:43 CST,,0,LOG,00000,"starting PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit",,,,,,,,"PostmasterMain, postmaster.c:1129","","postmaster",,0
2023-04-22 16:47:43.338 CST,,,1774,,64439f2f.6ee,3,,2023-04-22 16:47:43 CST,,0,LOG,00000,"listening on IPv4 address ""0.0.0.0"", port 1921",,,,,,,,"StreamServerPort, pqcomm.c:585","","postmaster",,0
2023-04-22 16:47:43.338 CST,,,1774,,64439f2f.6ee,4,,2023-04-22 16:47:43 CST,,0,LOG,00000,"listening on IPv6 address ""::"", port 1921",,,,,,,,"StreamServerPort, pqcomm.c:585","","postmaster",,0
2023-04-22 16:47:43.344 CST,,,1774,,64439f2f.6ee,5,,2023-04-22 16:47:43 CST,,0,LOG,00000,"listening on Unix socket ""./.s.PGSQL.1921""",,,,,,,,"StreamServerPort, pqcomm.c:579","","postmaster",,0
2023-04-22 16:47:43.351 CST,,,1776,,64439f2f.6f0,1,,2023-04-22 16:47:43 CST,,0,LOG,00000,"database system was shut down at 2023-04-22 16:00:48 CST",,,,,,,,"StartupXLOG, xlog.c:6553","","startup",,0
2023-04-22 16:47:43.355 CST,,,1776,,64439f2f.6f0,2,,2023-04-22 16:47:43 CST,,0,LOG,00000,"starting point-in-time recovery to WAL location (LSN) ""0/6000000""",,,,,,,,"StartupXLOG, xlog.c:6665","","startup",,0
2023-04-22 16:47:43.367 CST,,,1776,,64439f2f.6f0,3,,2023-04-22 16:47:43 CST,,0,LOG,00000,"restored log file ""000000010000000000000004"" from archive",,,,,,,,"RestoreArchivedFile, xlogarchive.c:215","","startup",,0
2023-04-22 16:47:43.413 CST,,,1776,,64439f2f.6f0,4,,2023-04-22 16:47:43 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/4000060",,,,,,,,"CheckRecoveryConsistency, xlog.c:8333","","startup",,0
2023-04-22 16:47:43.413 CST,,,1776,,64439f2f.6f0,5,,2023-04-22 16:47:43 CST,1/0,0,LOG,00000,"redo starts at 0/4000060",,,,,,,,"StartupXLOG, xlog.c:7389","","startup",,0
2023-04-22 16:47:43.413 CST,,,1774,,64439f2f.6ee,6,,2023-04-22 16:47:43 CST,,0,LOG,00000,"database system is ready to accept read-only connections",,,,,,,,"sigusr1_handler, postmaster.c:5238","","postmaster",,0
2023-04-22 16:47:43.426 CST,,,1776,,64439f2f.6f0,6,,2023-04-22 16:47:43 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000005"" from archive",,,,,,,,"RestoreArchivedFile, xlogarchive.c:215","","startup",,0
2023-04-22 16:47:43.947 CST,,,1776,,64439f2f.6f0,7,,2023-04-22 16:47:43 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000006"" from archive",,,,,,,,"RestoreArchivedFile, xlogarchive.c:215","","startup",,0
2023-04-22 16:47:43.980 CST,,,1776,,64439f2f.6f0,8,,2023-04-22 16:47:43 CST,1/0,0,LOG,00000,"recovery stopping after WAL location (LSN) ""0/6000028""",,,,,,,,"recoveryStopsAfter, xlog.c:5990","","startup",,0
2023-04-22 16:47:43.980 CST,,,1776,,64439f2f.6f0,9,,2023-04-22 16:47:43 CST,1/0,0,LOG,00000,"pausing at the end of recovery",,"Execute pg_wal_replay_resume() to promote.",,,,,,"recoveryPausesHere, xlog.c:6110","","startup",,0

—从日志看出已经应用往所需要的归档，此时数据库库为只读状态，待确认数据无异常之后，执行函数pg_wal_replay_resume()，恢复到可读写。

5.检查数据

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     |
(4 rows)

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select * from t;
 id
-----
 100
 100
 100
 100
 100
 100
 100
 100
 100
 100
(10 rows)

testdb=#

确认数据库完全恢复后执行函数pg_wal_replay_resume，恢复数据库为可读写。

testdb=# select pg_wal_replay_resume();
 pg_wal_replay_resume
----------------------

(1 row)

testdb=# \q
[postgres@du101 ~]$
[postgres@du101 ~]$ ll /data/pgdata/base/
total 48K
drwx------ 2 postgres postgres 8.0K Mar 12 10:59 1
drwx------ 2 postgres postgres 8.0K Mar 12 10:59 13891
drwx------ 2 postgres postgres 8.0K Apr 22 16:49 13892
drwx------ 2 postgres postgres 8.0K Apr 22 16:49 16384
[postgres@du101 ~]$
[postgres@du101 ~]$ ll /data/pgdata/recovery.signal
ls: cannot access /data/pgdata/recovery.signal: No such file or directory
[postgres@du101 ~]$ 
---手工创建的recovery.signal不存在了。