使用pg_rman对postgresql进行数据库备份恢复

pg_rman软件下载与安装

软件下载地址：https://github.com/ossc-db/pg_rman/releases
选择合适的版本下载，我这里下载的是pg_rman-REL_13_STABLE.zip
我这里操作系统是Centos7.6

安装非常简单，参考readme即可。

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

备份

先制定备份策略。包括备份目录等
（1）计划每天1全备，保留7天
（2）备份目录为/postgresql/backup

具体步骤

编写备份脚本

$ 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 # 配置定时备份任务

备份相关问题与测试

11:00:47 做了一个全备
14:17左右，创建了一个t11表，并且插入了一些数据，之后，切换了归档（14:19左右切换归档）
14:20左右， t11表插入了id=4的数据。没有切换归档（也就是归档日志中没包含t11 id=4的事务记录）

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

问题1：用全量备份恢复会恢复到哪个时间点？

预期：恢复到创建备份的表，也就是没有表t11。

把11:00的全备拷贝到待恢复的机器，待恢复的机器与主数据库的安装一致（目录一致，但删除了pgdata的数据【目录为空】）
以下操作均为在待恢复的服务器上的操作

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

结论：表里没有t11，与预期相符

问题2：如果把主库的归档copy到恢复库目录（跟主库一样的目录），会恢复到哪个时间点？

预期：恢复到t11表 id=3的时刻（也就是没有t11 id=4这条记录）。

在主数据库中，之前在14:19进行了归档切换，如下


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

把000000010000000000000017 这个归档文件也同步到待恢复的服务器上，直接restore之后，启动数据库，看看会如何
以下语句为在待恢复的服务器上执行

$ 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)

与预期相符，启动会自动读取归档目录，跑到最新。

问题3：如果拷贝了归档到恢复库中，是否可以恢复到指定时间点，比如只恢复到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)

证明也是可行的！

问题4：如果有一个T1时刻和T2时刻的全备，能否恢复到T1~T2的任意时间点？

$ 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

测试恢复到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=>

问题5：那没有切换归档的数据，是不是丢了？怎么恢复到还没切归档的数据呢？

比如我上面的两个全备时间为2023-12-21 14:40:27 和2023-12-21 11:00:47 ，然后在2023-12-21 14:52:47 t11表插入了几条数据（5，6,7,8,9,10），并且没有切归档

$ 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)

如果用全备恢复的话，肯定是恢复到id=4的时候，后面id=5的全丢。如果将pg_wal里面的文件拷贝到待恢复的机器，能不能恢复呢？先试一下。
这个是在主库上执行的

$ 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日志吧（对PG不了解，拿Oracle来比较说的）。把这个文件当成归档拷贝到待恢复的机器，能不能恢复呢？另外，会不会有导致有脏数据呢？

$ 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
## 从这行可以看出，实质上是有从这个文件恢复的，那也就是说可以恢复出来。
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)

那也就是说实质上是可以的，但是这个wal文件是commit之后再写的，还是说先写了，再commit，会不会存在未提交的事务，这块还没有研究。

需要注意的话，恢复启动之后，数据目录下有一个recovery.signal文件，不删除该文件的话，是只读状态。

posted @ 2023-12-22 16:39 xuege 阅读(196) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

xuege

IT人

使用pg_rman对postgresql进行数据库备份恢复

pg_rman软件下载与安装

备份

具体步骤

备份相关问题与测试

问题1：用全量备份恢复会恢复到哪个时间点？

预期：恢复到创建备份的表，也就是没有表t11。

结论：表里没有t11，与预期相符

问题2：如果把主库的归档copy到恢复库目录（跟主库一样的目录），会恢复到哪个时间点？

预期：恢复到t11表 id=3的时刻（也就是没有t11 id=4这条记录）。

与预期相符，启动会自动读取归档目录，跑到最新。

问题3：如果拷贝了归档到恢复库中，是否可以恢复到指定时间点，比如只恢复到t11表id=2的值，（2023-12-21 14:18:30, 不含t3的值）？

证明也是可行的！

问题4：如果有一个T1时刻和T2时刻的全备，能否恢复到T1~T2的任意时间点？

问题5：那没有切换归档的数据，是不是丢了？怎么恢复到还没切归档的数据呢？

公告

xuege

IT人

使用pg_rman对postgresql进行数据库备份恢复

pg_rman软件下载与安装

备份

具体步骤

备份相关问题与测试

问题1： 用全量备份恢复会恢复到哪个时间点？

预期： 恢复到创建备份的表，也就是没有表t11。

结论：表里没有t11， 与预期相符

问题2：如果把主库的归档copy到恢复库目录（跟主库一样的目录），会恢复到哪个时间点？

预期：恢复到t11表 id=3的时刻（也就是没有t11 id=4这条记录）。

与预期相符，启动会自动读取归档目录，跑到最新。

问题3： 如果拷贝了归档到恢复库中，是否可以恢复到指定时间点，比如只恢复到t11表id=2的值，（2023-12-21 14:18:30, 不含t3的值）？

证明也是可行的！

问题4：如果有一个T1时刻和T2时刻的全备，能否恢复到T1~T2的任意时间点？

问题5： 那没有切换归档的数据，是不是丢了？ 怎么恢复到还没切归档的数据呢？

公告

问题1：用全量备份恢复会恢复到哪个时间点？

预期：恢复到创建备份的表，也就是没有表t11。

结论：表里没有t11，与预期相符

问题3：如果拷贝了归档到恢复库中，是否可以恢复到指定时间点，比如只恢复到t11表id=2的值，（2023-12-21 14:18:30, 不含t3的值）？

问题5：那没有切换归档的数据，是不是丢了？怎么恢复到还没切归档的数据呢？