pg_rman軟件下載與安裝

軟件下載地址：https://github.com/ossc-db/pg_rman/releases
選擇合適的版本下載，我這里下載的是pg_rman-REL_13_STABLE.zip
我這里操作系統(tǒng)是Centos7.6

安裝非常簡(jiǎn)單，參考readme即可。

# yum -y install zlib-devel
# unzip pg_rman-REL_13_STABLE.zip
# make
# make install

備份

先制定備份策略。包括備份目錄等
（1）計(jì)劃每天1全備，保留7天
（2）備份目錄為/postgresql/backup

具體步驟

編寫(xiě)備份腳本

$ vim backup_pg.sh 
#!/bin/bash

export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg13
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres

if [ $UID -ne 2000 ]
then
        echo "please run as pgsql user!"
fi

backup_path="/postgresql/backup"
ini_file="/postgresql/backup/pg_rman.ini"
if [ ! -f ${ini_file} ]
then
        pg_rman init -B ${backup_path}
fi
pg_rman backup --backup-mode=full --backup-path=${backup_path}
pg_rman validate --backup-mode=full --backup-path=${backup_path}

v_date=`date +%Y-%m-%d -d '7 day ago'`
echo ${v_date}
pg_rman  delete DATE ${v_date} --backup-path=${backup_path}
$ crontab -e # 配置定時(shí)備份任務(wù)

備份相關(guān)問(wèn)題與測(cè)試

11:00:47 做了一個(gè)全備
14:17左右，創(chuàng)建了一個(gè)t11表，并且插入了一些數(shù)據(jù)， 之后，切換了歸檔（14:19左右切換歸檔）
14:20左右， t11表插入了id=4的數(shù)據(jù)。沒(méi)有切換歸檔（也就是歸檔日志中沒(méi)包含t11 id=4的事務(wù)記錄）

$ pg_rman backup --backup-mode=full -B /postgresql/backup
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[pgsql@dgvxl14531 src]$ pg_rman validate  -B /postgresql/backup                
INFO: validate: "2023-12-21 11:00:45" backup and archive log files by CRC
INFO: backup "2023-12-21 11:00:45" is valid
[pgsql@dgvxl14531 src]$ pg_rman show -B /postgresql/backup/                    
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-12-21 11:00:45  2023-12-21 11:00:47  FULL   391MB     1  OK

mgx=> create table t11(id int, d_time timestamp default current_timestamp);
CREATE TABLE
mgx=> insert into t11(id) values(1);
INSERT 0 1
mgx=> insert into t11(id) values(2);
INSERT 0 1
mgx=> insert into t11(id) values(3);
INSERT 0 1

mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
  3 | 2023-12-21 14:18:42.18747
(3 rows)

mgx=> 
mgx=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/E000188
(1 row)

mgx=> insert into t11(id) values(4);
INSERT 0 1
mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
  3 | 2023-12-21 14:18:42.18747
  4 | 2023-12-21 14:20:14.987364
(4 rows)

mgx=> 

問(wèn)題1： 用全量備份恢復(fù)會(huì)恢復(fù)到哪個(gè)時(shí)間點(diǎn)？

預(yù)期： 恢復(fù)到創(chuàng)建備份的表，也就是沒(méi)有表t11。

把11:00的全備拷貝到待恢復(fù)的機(jī)器，待恢復(fù)的機(jī)器與主數(shù)據(jù)庫(kù)的安裝一致（目錄一致，但刪除了pgdata的數(shù)據(jù)【目錄為空】）
以下操作均為在待恢復(fù)的服務(wù)器上的操作

$ pg_rman show -B /postgresql/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-12-21 11:00:45  2023-12-21 11:00:47  FULL   391MB     1  OK
$ pg_rman restore  -B /postgresql/backup       
WARNING: pg_controldata file "/postgresql/pgdata/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2023-12-21 11:00:45"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2023-12-21 11:00:45" backup and archive log files by SIZE
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring database files from the full mode backup "2023-12-21 11:00:45"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring WAL files from backup "2023-12-21 11:00:45"
INFO: restoring online WAL files and server log files
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists
INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf
INFO: generating recovery.signal
INFO: removing standby.signal if exists to restore as primary
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.
$ chmod 700 pgdata/
$ pg_ctl start -l /postgresql/pg_log/pg.log 
waiting for server to start.... done
server started
postgres=# \c  - mgx
You are now connected to database "postgres" as user "mgx".
postgres=> \c mgx
You are now connected to database "mgx" as user "mgx".
mgx=> \dt
        List of relations
  Schema  | Name | Type  | Owner 
----------+------+-------+-------
 myschema | t1   | table | mgx
 myschema | t10  | table | mgx
 myschema | t2   | table | mgx
 myschema | t3   | table | mgx
 myschema | t4   | table | mgx
 myschema | t5   | table | mgx
 myschema | t6   | table | mgx
 myschema | t7   | table | mgx
 myschema | t8   | table | mgx
 myschema | t9   | table | mgx
(10 rows)

mgx=> 

結(jié)論：表里沒(méi)有t11， 與預(yù)期相符

問(wèn)題2：如果把主庫(kù)的歸檔copy到恢復(fù)庫(kù)目錄（跟主庫(kù)一樣的目錄），會(huì)恢復(fù)到哪個(gè)時(shí)間點(diǎn)？

預(yù)期：恢復(fù)到t11表 id=3的時(shí)刻（也就是沒(méi)有t11 id=4這條記錄）。

在主數(shù)據(jù)庫(kù)中，之前在14:19進(jìn)行了歸檔切換，如下

-rw------- 1 pgsql pgsql      347 Dec 21 11:00 000000010000000000000016.00000028.backup
-rw------- 1 pgsql pgsql 16777216 Dec 21 14:19 000000010000000000000017

把000000010000000000000017 這個(gè)歸檔文件也同步到待恢復(fù)的服務(wù)器上， 直接restore之后，啟動(dòng)數(shù)據(jù)庫(kù)，看看會(huì)如何
以下語(yǔ)句為在待恢復(fù)的服務(wù)器上執(zhí)行

$ pg_rman show -B /postgresql/backup                    
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-12-21 11:00:45  2023-12-21 11:00:47  FULL   391MB     1  OK
[pgsql@dgvxl14532 archive]$ pg_rman restore  -B /postgresql/backup --hard-copy    
WARNING: pg_controldata file "/postgresql/pgdata/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2023-12-21 11:00:45"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2023-12-21 11:00:45" backup and archive log files by SIZE
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring database files from the full mode backup "2023-12-21 11:00:45"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring WAL files from backup "2023-12-21 11:00:45"
INFO: restoring online WAL files and server log files
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists
INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf
INFO: generating recovery.signal
INFO: removing standby.signal if exists to restore as primary
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.
$ psql 
psql (13.6)
Type "help" for help.

postgres=# \c mgx
You are now connected to database "mgx" as user "postgres".
mgx=# \c - mgx
You are now connected to database "mgx" as user "mgx".

mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
  3 | 2023-12-21 14:18:42.18747
(3 rows)

與預(yù)期相符，啟動(dòng)會(huì)自動(dòng)讀取歸檔目錄，跑到最新。

問(wèn)題3： 如果拷貝了歸檔到恢復(fù)庫(kù)中，是否可以恢復(fù)到指定時(shí)間點(diǎn)，比如只恢復(fù)到t11表id=2的值，（2023-12-21 14:18:30, 不含t3的值）？

$ pg_rman restore  -B /postgresql/backup --hard-copy --recovery-target-time='2023-12-21 14:18:30'
WARNING: pg_controldata file "/postgresql/pgdata/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2023-12-21 11:00:45"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2023-12-21 11:00:45" backup and archive log files by SIZE
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring database files from the full mode backup "2023-12-21 11:00:45"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring WAL files from backup "2023-12-21 11:00:45"
INFO: restoring online WAL files and server log files
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists
INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf
INFO: generating recovery.signal
INFO: removing standby.signal if exists to restore as primary
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.

]$ psql 
psql (13.6)
Type "help" for help.

postgres=# \c mgx
You are now connected to database "mgx" as user "postgres".
mgx=# \c - mgx
You are now connected to database "mgx" as user "mgx".
mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
(2 rows)

證明也是可行的！

問(wèn)題4：如果有一個(gè)T1時(shí)刻和T2時(shí)刻的全備，能否恢復(fù)到T1~T2的任意時(shí)間點(diǎn)？

$ pg_rman show -B /postgresql/backup/                    
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-12-21 14:40:25  2023-12-21 14:40:27  FULL    72MB     1  OK
2023-12-21 11:00:45  2023-12-21 11:00:47  FULL   391MB     1  OK

測(cè)試恢復(fù)到2023-12-21 14:18:30， 也就是t11表只有id = 1,2的值，不含3,4的值。

$ pg_rman restore  -B /postgresql/backup --hard-copy --recovery-target-time='2023-12-21 14:18:30'
WARNING: pg_controldata file "/postgresql/pgdata/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2023-12-21 11:00:45"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2023-12-21 11:00:45" backup and archive log files by SIZE
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring database files from the full mode backup "2023-12-21 11:00:45"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2023-12-21 11:00:45" is valid
INFO: restoring WAL files from backup "2023-12-21 11:00:45"
INFO: backup "2023-12-21 14:40:25" is valid
INFO: restoring WAL files from backup "2023-12-21 14:40:25"
INFO: restoring online WAL files and server log files
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists
INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf
INFO: generating recovery.signal
INFO: removing standby.signal if exists to restore as primary
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.
[pgsql@dgvxl14532 pgdata]$ pg_ctl start
waiting for server to start....2023-12-21 14:42:33.589 CST [22947] LOG:  starting PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-12-21 14:42:33.589 CST [22947] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-12-21 14:42:33.589 CST [22947] LOG:  listening on IPv6 address "::", port 5432
2023-12-21 14:42:33.592 CST [22947] LOG:  listening on Unix socket "/postgresql/pgdata/.s.PGSQL.5432"
2023-12-21 14:42:33.595 CST [22948] LOG:  database system was interrupted; last known up at 2023-12-21 11:00:45 CST
2023-12-21 14:42:33.882 CST [22948] LOG:  starting point-in-time recovery to 2023-12-21 14:18:30+08
2023-12-21 14:42:33.895 CST [22948] LOG:  restored log file "000000010000000000000016" from archive
2023-12-21 14:42:33.918 CST [22948] LOG:  redo starts at 0/16000028
2023-12-21 14:42:33.919 CST [22948] LOG:  consistent recovery state reached at 0/16000100
2023-12-21 14:42:33.920 CST [22947] LOG:  database system is ready to accept read only connections
2023-12-21 14:42:33.934 CST [22948] LOG:  restored log file "000000010000000000000017" from archive
 done
server started
2023-12-21 14:42:33.953 CST [22948] LOG:  recovery stopping before commit of transaction 565, time 2023-12-21 14:18:42.187662+08
2023-12-21 14:42:33.953 CST [22948] LOG:  pausing at the end of recovery
2023-12-21 14:42:33.953 CST [22948] HINT:  Execute pg_wal_replay_resume() to promote.
[pgsql@dgvxl14532 pgdata]$ ps -ef | grep post
pgsql    22947     1  0 14:42 ?        00:00:00 /postgresql/pg13/bin/postgres
pgsql    22948 22947  0 14:42 ?        00:00:00 postgres: startup recovering 000000010000000000000017
pgsql    22951 22947  0 14:42 ?        00:00:00 postgres: checkpointer 
pgsql    22952 22947  0 14:42 ?        00:00:00 postgres: background writer 
pgsql    22955 22947  0 14:42 ?        00:00:00 postgres: stats collector 
pgsql    23524 12294  0 14:42 pts/3    00:00:00 grep --color=auto post
[pgsql@dgvxl14532 pgdata]$ psql 
psql (13.6)
Type "help" for help.

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \c mgx
You are now connected to database "mgx" as user "postgres".
mgx=# \c - mgx
You are now connected to database "mgx" as user "mgx".
mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
(2 rows)

mgx=> 

問(wèn)題5： 那沒(méi)有切換歸檔的數(shù)據(jù)，是不是丟了？ 怎么恢復(fù)到還沒(méi)切歸檔的數(shù)據(jù)呢？

比如我上面的兩個(gè)全備時(shí)間為2023-12-21 14:40:27 和2023-12-21 11:00:47 ，然后在2023-12-21 14:52:47 t11表插入了幾條數(shù)據(jù)（5，6,7,8,9,10），并且沒(méi)有切歸檔

$ pg_rman show -B /postgresql/backup/                    
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2023-12-21 14:40:25  2023-12-21 14:40:27  FULL    72MB     1  OK
2023-12-21 11:00:45  2023-12-21 11:00:47  FULL   391MB     1  OK

mgx=> insert into t11(id) values(5);
INSERT 0 1
mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
  3 | 2023-12-21 14:18:42.18747
  4 | 2023-12-21 14:20:14.987364
  5 | 2023-12-21 14:52:47.329725
  6 | 2023-12-21 14:54:36.907696
  7 | 2023-12-21 14:54:38.718448
  8 | 2023-12-21 14:54:41.037213
  9 | 2023-12-21 14:54:42.620105
 10 | 2023-12-21 14:54:44.441158
(10 rows)

如果用全備恢復(fù)的話，肯定是恢復(fù)到id=4的時(shí)候，后面id=5的全丟。如果將pg_wal里面的文件拷貝到待恢復(fù)的機(jī)器，能不能恢復(fù)呢？ 先試一下。
這個(gè)是在主庫(kù)上執(zhí)行的

$ ls -alrt /postgresql/pgdata/pg_wal
total 426000
-rw-------  1 pgsql pgsql 16777216 Dec 20 16:05 000000010000000000000001
-rw-------  1 pgsql pgsql 16777216 Dec 20 16:05 000000010000000000000002
drwx------ 19 pgsql pgsql     4096 Dec 20 16:07 ..
-rw-------  1 pgsql pgsql 16777216 Dec 20 16:08 000000010000000000000003
-rw-------  1 pgsql pgsql 16777216 Dec 20 16:08 000000010000000000000004
-rw-------  1 pgsql pgsql 16777216 Dec 20 16:34 000000010000000000000005
-rw-------  1 pgsql pgsql 16777216 Dec 20 17:05 000000010000000000000006
-rw-------  1 pgsql pgsql 16777216 Dec 20 17:14 000000010000000000000007
-rw-------  1 pgsql pgsql 16777216 Dec 20 17:18 000000010000000000000008
-rw-------  1 pgsql pgsql 16777216 Dec 20 17:22 000000010000000000000009
-rw-------  1 pgsql pgsql 16777216 Dec 20 18:12 00000001000000000000000A
-rw-------  1 pgsql pgsql 16777216 Dec 21 09:34 00000001000000000000000B
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:14 00000001000000000000000C
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:14 00000001000000000000000D
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:23 00000001000000000000000E
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:42 00000001000000000000000F
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:42 000000010000000000000010
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:46 000000010000000000000011
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:46 000000010000000000000012
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:55 000000010000000000000013
-rw-------  1 pgsql pgsql 16777216 Dec 21 10:55 000000010000000000000014
-rw-------  1 pgsql pgsql 16777216 Dec 21 11:00 000000010000000000000015
-rw-------  1 pgsql pgsql 16777216 Dec 21 11:00 000000010000000000000016
-rw-------  1 pgsql pgsql 16777216 Dec 21 14:19 000000010000000000000017
-rw-------  1 pgsql pgsql 16777216 Dec 21 14:40 000000010000000000000018
-rw-------  1 pgsql pgsql 16777216 Dec 21 14:40 000000010000000000000019
-rw-------  1 pgsql pgsql      347 Dec 21 14:40 000000010000000000000019.00000028.backup
drwx------  2 pgsql pgsql     4096 Dec 21 14:40 archive_status
drwx------  3 pgsql pgsql     4096 Dec 21 14:40 .
-rw-------  1 pgsql pgsql 16777216 Dec 21 14:55 00000001000000000000001A

00000001000000000000001A 就是在線的redo日志吧（對(duì)PG不了解，拿Oracle來(lái)比較說(shuō)的）。把這個(gè)文件當(dāng)成歸檔拷貝到待恢復(fù)的機(jī)器，能不能恢復(fù)呢？另外，會(huì)不會(huì)有導(dǎo)致有臟數(shù)據(jù)呢？

$ pg_rman restore  -B /postgresql/backup --hard-copy
WARNING: pg_controldata file "/postgresql/pgdata/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2023-12-21 14:40:25"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2023-12-21 14:40:25" backup and archive log files by SIZE
INFO: backup "2023-12-21 14:40:25" is valid
INFO: restoring database files from the full mode backup "2023-12-21 14:40:25"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2023-12-21 14:40:25" is valid
INFO: restoring WAL files from backup "2023-12-21 14:40:25"
INFO: restoring online WAL files and server log files
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists
INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf
INFO: generating recovery.signal
INFO: removing standby.signal if exists to restore as primary
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.
[pgsql@dgvxl14532 pgdata]$ pg_ctl start
waiting for server to start....2023-12-21 15:00:05.697 CST [28635] LOG:  starting PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-12-21 15:00:05.697 CST [28635] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-12-21 15:00:05.697 CST [28635] LOG:  listening on IPv6 address "::", port 5432
2023-12-21 15:00:05.699 CST [28635] LOG:  listening on Unix socket "/postgresql/pgdata/.s.PGSQL.5432"
2023-12-21 15:00:05.703 CST [28636] LOG:  database system was interrupted; last known up at 2023-12-21 14:40:25 CST
2023-12-21 15:00:05.798 CST [28636] LOG:  starting archive recovery
2023-12-21 15:00:05.815 CST [28636] LOG:  restored log file "000000010000000000000019" from archive
2023-12-21 15:00:05.842 CST [28636] LOG:  redo starts at 0/19000028
2023-12-21 15:00:05.843 CST [28636] LOG:  consistent recovery state reached at 0/19000100
2023-12-21 15:00:05.844 CST [28635] LOG:  database system is ready to accept read only connections
 done
server started
2023-12-21 15:00:05.862 CST [28636] LOG:  restored log file "00000001000000000000001A" from archive
## 從這行可以看出，實(shí)質(zhì)上是有從這個(gè)文件恢復(fù)的，那也就是說(shuō)可以恢復(fù)出來(lái)。
2023-12-21 15:00:05.883 CST [28636] LOG:  invalid record length at 0/1A000650: wanted 24, got 0
2023-12-21 15:00:05.883 CST [28636] LOG:  redo done at 0/1A000618
2023-12-21 15:00:05.883 CST [28636] LOG:  last completed transaction was at log time 2023-12-21 14:54:44.441295+08
2023-12-21 15:00:05.900 CST [28636] LOG:  restored log file "00000001000000000000001A" from archive
cp: cannot stat ‘/postgresql/archive/00000002.history’: No such file or directory
2023-12-21 15:00:05.926 CST [28636] LOG:  selected new timeline ID: 2
2023-12-21 15:00:05.956 CST [28636] LOG:  archive recovery complete
cp: cannot stat ‘/postgresql/archive/00000001.history’: No such file or directory
2023-12-21 15:00:05.974 CST [28635] LOG:  database system is ready to accept connections
$ psql
psql (13.6)
Type "help" for help.

postgres=# \c mgx
You are now connected to database "mgx" as user "postgres".

mgx=# \c - mgx
You are now connected to database "mgx" as user "mgx".
mgx=> select * from t11;
 id |           d_time           
----+----------------------------
  1 | 2023-12-21 14:18:11.937218
  2 | 2023-12-21 14:18:16.670504
  3 | 2023-12-21 14:18:42.18747
  4 | 2023-12-21 14:20:14.987364
  5 | 2023-12-21 14:52:47.329725
  6 | 2023-12-21 14:54:36.907696
  7 | 2023-12-21 14:54:38.718448
  8 | 2023-12-21 14:54:41.037213
  9 | 2023-12-21 14:54:42.620105
 10 | 2023-12-21 14:54:44.441158
(10 rows)

那也就是說(shuō)實(shí)質(zhì)上是可以的，但是這個(gè)wal文件是commit之后再寫(xiě)的，還是說(shuō)先寫(xiě)了，再commit，會(huì)不會(huì)存在未提交的事務(wù)， 這塊還沒(méi)有研究。

需要注意的話，恢復(fù)啟動(dòng)之后，數(shù)據(jù)目錄下有一個(gè)recovery.signal文件，不刪除該文件的話，是只讀狀態(tài)。