磐维数据库gs_probackup的PITR恢复演练

杨金福 2025-03-21

背景信息

当数据库崩溃或希望回退到数据库之前的某一状态时，磐维数据库的即时恢复功能（Point-In-Time Recovery，简称PITR）可以支持恢复到备份归档数据之后的任意时间

说明:

PITR仅支持恢复到物理备份数据之后的某一时间点。

仅主节点可以进行PITR恢复，备机需要进行全量build达成与主机数据同步。

恢复前建议对已归档的XLOG进行备份。因为若恢复后不满意当前的恢复状态，想重新进行恢复，但此时已归档的XLOG可能会被新归档的XLOG所覆盖，从而可能导致恢复失败。

前提条件

基于经过物理备份的全量数据文件。

基于已归档的WAL日志文件。

PITR恢复流程

将物理备份的文件替换目标数据库目录。

删除数据库目录下pg_xlog/中的所有文件。

将归档的WAL日志文件复制到pg_xlog文件中（此步骤可以省略，通过配置recovery.conf恢复命令文件中的restore_command项替代）。

在数据库目录下创建恢复命令文件recovery.conf，指定数据库恢复的程度。

启动数据库。

连接数据库，查看是否恢复到希望预期的状态。

若已经恢复到预期状态，通过pg_xlog_replay_resume()指令使主节点对外提供服务。

演练步骤

1.环境准备

注:本次演练基于PanWeiDB_V2.0单节点

1.1 检查数据库已开归档和归档路径

[omm@pwnode1 ~]$ gsql -r

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

postgres=# show archive_mode;

archive_mode

--------------

(1 row)

postgres=# show archive_dest;

archive_dest

--------------

/archive

(1 row)

postgres=#

1.2 检查数据库gs_probackup备份配置

[omm@pwnode1 ~]$ gs_probackup show -B /backup/probackup --instance=panweidb

=======================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================

[omm@pwnode1 ~]$

1.3 备份数据库

[omm@pwnode1 ~]$ gs_probackup backup --backup-path=/backup/probackup --instance=panweidb --backup-mode=full

INFO: Backup start, pw_probackup version: 2.4.2, instance: panweidb, backup ID: STGW7H, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1

LOG: Backup destination is initialized

LOG: This PanWeiDB instance was initialized with data block checksums. Data block corruption will be detected

LOG: Database backup start

LOG: started streaming WAL at 0/6000000 (timeline 1)

[2025-03-21 17:40:30]: check identify system success

[2025-03-21 17:40:30]: send START_REPLICATION 0/6000000 success

[2025-03-21 17:40:30]: keepalive message is received

INFO: PGDATA size: 703MB

INFO: Start transferring data files

LOG: Creating page header map “/backup/probackup/backups/panweidb/STGW7H/page_header_map”

[2025-03-21 17:40:30]: keepalive message is received

[2025-03-21 17:40:35]: keepalive message is received

INFO: Data files are transferred, time elapsed: 6s

INFO: wait for pg_stop_backup()

INFO: pg_stop backup() successfully executed

LOG: stop_lsn: 0/6000270

LOG: Looking for LSN 0/6000270 in segment: 000000010000000000000006

LOG: Found WAL segment: /backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006

LOG: Thread [0]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

LOG: Found LSN: 0/6000270

[2025-03-21 17:40:41]:(null): not renaming “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000007”, segment is not complete.

LOG: finished streaming WAL at 0/70000B0 (timeline 1)

LOG: Getting the Recovery Time from WAL

LOG: Thread [0]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

INFO: Syncing backup files to disk

INFO: Backup files are synced, time elapsed: 0

INFO: Validating backup STGW7H

INFO: Backup STGW7H data files are valid

INFO: Backup STGW7H resident size: 735MB

INFO: Backup STGW7H completed

[omm@pwnode1 ~]$

[omm@pwnode1 ~]$ gs_probackup show -B /backup/probackup --instance=panweidb

=======================================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================================

panweidb 9.2 STGW7H 2025-03-21 17:40:36+08 FULL STREAM 1/0 12s 719MB 16MB 0.98 0/6000028 0/6000270 FILE OK

[omm@pwnode1 ~]$

1.4 创建测试数据

插入数据并记录时间点

hrdb=> create table pitr_test(id int,t timestamp);

CREATE TABLE

hrdb=> insert into pitr_test select 1,now;

INSERT 0 1

hrdb=> select * from pitr_test;

id | t

----±--------------------------

1 | 2025-03-21 17:50:47.20499

(1 row)

hrdb=> select now;

now

-------------------------------

2025-03-21 17:51:50.906044+08

(1 row)

hrdb=>

切换wal日志

hrdb=> hrdb=> select pg_xlogfile_name(pg_switch_xlog());

pg_xlogfile_name

--------------------------

000000010000000000000007

(1 row)

插入数据并创建还原点

hrdb=> insert into pitr_test select 2,now;

INSERT 0 1

hrdb=> select * from pitr_test;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

(2 rows)

hrdb=> select pg_create_restore_point(‘point1’);

pg_create_restore_point

-------------------------

0/80008C8

(1 row)

hrdb=>

切换wal日志

hrdb=> select pg_xlogfile_name(pg_switch_xlog());

pg_xlogfile_name

--------------------------

000000010000000000000008

(1 row)

hrdb=>

插入数据并记录事务ID

hrdb=> insert into pitr_test select 3,now;

INSERT 0 1

hrdb=> select * from pitr_test;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

3 | 2025-03-21 18:02:23.628427

(3 rows)

hrdb=> select txid_current(),txid_current_snapshot();

txid_current | txid_current_snapshot

--------------±----------------------

29321 | 29321:29321:

(1 row)

hrdb=>

切换wal日志

hrdb=> select pg_xlogfile_name(pg_switch_xlog());

pg_xlogfile_name

--------------------------

000000010000000000000009

(1 row)

hrdb=>

插入数据并记录lsn

hrdb=> insert into pitr_test select 4,now;

INSERT 0 1

hrdb=> select * from pitr_test;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

3 | 2025-03-21 18:02:23.628427

4 | 2025-03-21 18:05:19.635836

(4 rows)

hrdb=> select pg_current_xlog_location();

pg_current_xlog_location

--------------------------

0/A0003D8

(1 row)

hrdb=>

切换wal日志

hrdb=> select pg_xlogfile_name(pg_switch_xlog());

pg_xlogfile_name

--------------------------

00000001000000000000000A

(1 row)

hrdb=>

2.PITR恢复

2.1 备份wal归档日志

[root@pwnode1 /]# ls -ltr /archive

total 114692

-rw------- 1 omm dbgrp 16777216 Mar 21 17:40 000000010000000000000005

-rw------- 1 omm dbgrp 16777216 Mar 21 17:40 000000010000000000000006

-rw------- 1 omm dbgrp 324 Mar 21 17:40 000000010000000000000006.00000028.backup

-rw------- 1 omm dbgrp 16777216 Mar 21 17:53 000000010000000000000007

-rw------- 1 omm dbgrp 16777216 Mar 21 17:59 000000010000000000000008

-rw------- 1 omm dbgrp 16777216 Mar 21 18:04 000000010000000000000009

-rw------- 1 omm dbgrp 16777216 Mar 21 18:06 00000001000000000000000A

-rw------- 1 omm dbgrp 16777216 Mar 21 20:26 00000001000000000000000B

[root@pwnode1 /]# cp -a /archive /archive_bak

[root@pwnode1 /]#

说明：

2.2基于时间点恢复

2.2.1 停止数据库并删除data目录

[omm@pwnode1 ~]$ gs_om -t stop

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

[omm@pwnode1 ~]$ rm -rf /database/panweidb/data

[omm@pwnode1 ~]$

2.2.2 查看备份并恢复数据库

[omm@pwnode1 ~]$ gs_probackup show --backup-path=/backup/probackup --instance=panweidb

=======================================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================================

panweidb 9.2 STGW7H 2025-03-21 17:40:36+08 FULL STREAM 1/0 12s 719MB 16MB 0.98 0/6000028 0/6000270 FILE OK

[omm@pwnode1 ~]$ gs_probackup restore -B /backup/probackup --instance=panweidb -i STGW7H -D /database/panweidb/data

LOG: Restore begin.

LOG: there is no file tablespace_map

LOG: check tablespace directories of backup STGW7H

LOG: check external directories of backup STGW7H

INFO: Validating backup STGW7H

INFO: Backup STGW7H data files are valid

LOG: Thread [1]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

INFO: Backup STGW7H WAL segments are valid

INFO: Backup STGW7H is valid.

INFO: Restoring the database from backup at 2025-03-21 17:40:29+08

LOG: there is no file tablespace_map

LOG: Restore directories and symlinks… in /database/panweidb/data

INFO: Start restoring backup files. DATA size: 719MB

LOG: Start thread 1

INFO: Backup files are restored. Transfered bytes: 735MB, time elapsed: 0

INFO: Restore incremental ratio (less is better): 102% (735MB/719MB)

INFO: Syncing restored files to disk

INFO: Restored backup files are synced, time elapsed: 0

The pw_guc run with the following arguments: [gs_guc -D /database/panweidb/data -c pw_wal_directory=‘pg_xlog’ set ].

expected instance path: [/database/panweidb/data/postgresql.conf]

gs_guc set: pw_wal_directory=‘pg_xlog’: [/database/panweidb/data/postgresql.conf]

Total instances: 1. Failed instances: 0.

Success to perform gs_guc!

INFO: Restore of backup STGW7H completed.

[omm@pwnode1 ~]$

2.2.3 创建归档恢复配置文件，输入以下内容

[omm@pwnode1 ~]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 ~]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_time=‘2025-03-21 17:51:50.906044+08’

recovery_target_inclusive =true

[omm@pwnode1 ~]$

2.2.4 启动数据库并检查数据恢复情况

[omm@pwnode1 ~]$ gs_om -t start

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 21:36:25.178 [unknown] [unknown] localhost 139783888867904 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 21:36:25.182 [unknown] [unknown] localhost 139783888867904 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 21:36:25.478 [unknown] [unknown] localhost 139783888867904 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 21:36:25.478 [unknown] [unknown] localhost 139783888867904 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±--------------------------

1 | 2025-03-21 17:50:47.20499

(1 row)

hrdb=>

2.3基于还原点恢复

2.3.1 停止数据库并删除data目录

[omm@pwnode1 ~]$ gs_om -t stop

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

[omm@pwnode1 ~]$ rm -rf /database/panweidb/data

[omm@pwnode1 ~]$

2.3.2 查看备份并恢复数据库

[omm@pwnode1 ~]$ gs_probackup show --backup-path=/backup/probackup --instance=panweidb

=======================================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================================

panweidb 9.2 STGW7H 2025-03-21 17:40:36+08 FULL STREAM 1/0 12s 719MB 16MB 0.98 0/6000028 0/6000270 FILE OK

[omm@pwnode1 ~]$ gs_probackup restore -B /backup/probackup --instance=panweidb -i STGW7H -D /database/panweidb/data

LOG: Restore begin.

LOG: there is no file tablespace_map

LOG: check tablespace directories of backup STGW7H

LOG: check external directories of backup STGW7H

INFO: Validating backup STGW7H

INFO: Backup STGW7H data files are valid

LOG: Thread [1]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

INFO: Backup STGW7H WAL segments are valid

INFO: Backup STGW7H is valid.

INFO: Restoring the database from backup at 2025-03-21 17:40:29+08

LOG: there is no file tablespace_map

LOG: Restore directories and symlinks… in /database/panweidb/data

INFO: Start restoring backup files. DATA size: 719MB

LOG: Start thread 1

INFO: Backup files are restored. Transfered bytes: 735MB, time elapsed: 0

INFO: Restore incremental ratio (less is better): 102% (735MB/719MB)

INFO: Syncing restored files to disk

INFO: Restored backup files are synced, time elapsed: 0

The pw_guc run with the following arguments: [gs_guc -D /database/panweidb/data -c pw_wal_directory=‘pg_xlog’ set ].

expected instance path: [/database/panweidb/data/postgresql.conf]

gs_guc set: pw_wal_directory=‘pg_xlog’: [/database/panweidb/data/postgresql.conf]

Total instances: 1. Failed instances: 0.

Success to perform gs_guc!

INFO: Restore of backup STGW7H completed.

[omm@pwnode1 ~]$

2.3.3 创建归档恢复配置文件，输入以下内容

[omm@pwnode1 ~]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 ~]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_name=‘point1’

recovery_target_inclusive =true

[omm@pwnode1 ~]$

2.3.4 启动数据库并检查数据恢复情况

[omm@pwnode1 ~]$ gs_om -t start

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 21:48:22.760 [unknown] [unknown] localhost 139791161537088 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 21:48:22.765 [unknown] [unknown] localhost 139791161537088 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 21:48:23.000 [unknown] [unknown] localhost 139791161537088 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 21:48:23.000 [unknown] [unknown] localhost 139791161537088 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

(2 rows)

hrdb=>

2.4基于事务xid恢复

2.4.1 停止数据库并删除data目录

[omm@pwnode1 ~]$ gs_om -t stop

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

[omm@pwnode1 ~]$ rm -rf /database/panweidb/data

[omm@pwnode1 ~]$

2.4.2 查看备份并恢复数据库

[omm@pwnode1 ~]$ gs_probackup show --backup-path=/backup/probackup --instance=panweidb

=======================================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================================

panweidb 9.2 STGW7H 2025-03-21 17:40:36+08 FULL STREAM 1/0 12s 719MB 16MB 0.98 0/6000028 0/6000270 FILE OK

[omm@pwnode1 ~]$ gs_probackup restore -B /backup/probackup --instance=panweidb -i STGW7H -D /database/panweidb/data

LOG: Restore begin.

LOG: there is no file tablespace_map

LOG: check tablespace directories of backup STGW7H

LOG: check external directories of backup STGW7H

INFO: Validating backup STGW7H

INFO: Backup STGW7H data files are valid

LOG: Thread [1]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

INFO: Backup STGW7H WAL segments are valid

INFO: Backup STGW7H is valid.

INFO: Restoring the database from backup at 2025-03-21 17:40:29+08

LOG: there is no file tablespace_map

LOG: Restore directories and symlinks… in /database/panweidb/data

INFO: Start restoring backup files. DATA size: 719MB

LOG: Start thread 1

INFO: Backup files are restored. Transfered bytes: 735MB, time elapsed: 0

INFO: Restore incremental ratio (less is better): 102% (735MB/719MB)

INFO: Syncing restored files to disk

INFO: Restored backup files are synced, time elapsed: 0

The pw_guc run with the following arguments: [gs_guc -D /database/panweidb/data -c pw_wal_directory=‘pg_xlog’ set ].

expected instance path: [/database/panweidb/data/postgresql.conf]

gs_guc set: pw_wal_directory=‘pg_xlog’: [/database/panweidb/data/postgresql.conf]

Total instances: 1. Failed instances: 0.

Success to perform gs_guc!

INFO: Restore of backup STGW7H completed.

[omm@pwnode1 ~]$

2.4.3 创建归档恢复配置文件，输入以下内容

[omm@pwnode1 ~]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 ~]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_xid=‘29321’

recovery_target_inclusive =true

[omm@pwnode1 ~]$

2.4.4 启动数据库并检查数据恢复情况

[omm@pwnode1 ~]$ gs_om -t start

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 21:56:04.172 [unknown] [unknown] localhost 140198626169408 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 21:56:04.176 [unknown] [unknown] localhost 140198626169408 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 21:56:04.412 [unknown] [unknown] localhost 140198626169408 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 21:56:04.412 [unknown] [unknown] localhost 140198626169408 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

3 | 2025-03-21 18:02:23.628427

(3 rows)

hrdb=>

2.5基于lsn恢复

2.5.1 停止数据库并删除data目录

[omm@pwnode1 ~]$ gs_om -t stop

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

[omm@pwnode1 ~]$ rm -rf /database/panweidb/data

[omm@pwnode1 ~]$

2.5.2 查看备份并恢复数据库

[omm@pwnode1 ~]$ gs_probackup show --backup-path=/backup/probackup --instance=panweidb

=======================================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================================

panweidb 9.2 STGW7H 2025-03-21 17:40:36+08 FULL STREAM 1/0 12s 719MB 16MB 0.98 0/6000028 0/6000270 FILE OK

[omm@pwnode1 ~]$ gs_probackup restore -B /backup/probackup --instance=panweidb -i STGW7H -D /database/panweidb/data

LOG: Restore begin.

LOG: there is no file tablespace_map

LOG: check tablespace directories of backup STGW7H

LOG: check external directories of backup STGW7H

INFO: Validating backup STGW7H

INFO: Backup STGW7H data files are valid

LOG: Thread [1]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

INFO: Backup STGW7H WAL segments are valid

INFO: Backup STGW7H is valid.

INFO: Restoring the database from backup at 2025-03-21 17:40:29+08

LOG: there is no file tablespace_map

LOG: Restore directories and symlinks… in /database/panweidb/data

INFO: Start restoring backup files. DATA size: 719MB

LOG: Start thread 1

INFO: Backup files are restored. Transfered bytes: 735MB, time elapsed: 1s

INFO: Restore incremental ratio (less is better): 102% (735MB/719MB)

INFO: Syncing restored files to disk

INFO: Restored backup files are synced, time elapsed: 0

The pw_guc run with the following arguments: [gs_guc -D /database/panweidb/data -c pw_wal_directory=‘pg_xlog’ set ].

expected instance path: [/database/panweidb/data/postgresql.conf]

gs_guc set: pw_wal_directory=‘pg_xlog’: [/database/panweidb/data/postgresql.conf]

Total instances: 1. Failed instances: 0.

Success to perform gs_guc!

INFO: Restore of backup STGW7H completed.

[omm@pwnode1 ~]$

2.5.3 创建归档恢复配置文件，输入以下内容

[omm@pwnode1 ~]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 ~]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_lsn=‘0/A0003D8’

recovery_target_inclusive =true

[omm@pwnode1 ~]$

2.5.4 启动数据库并检查数据恢复情况

[omm@pwnode1 ~]$ gs_om -t start

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 22:03:09.076 [unknown] [unknown] localhost 140225485603392 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 22:03:09.079 [unknown] [unknown] localhost 140225485603392 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 22:03:09.080 [unknown] [unknown] localhost 140225485603392 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 22:03:09.316 [unknown] [unknown] localhost 140225485603392 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 22:03:09.316 [unknown] [unknown] localhost 140225485603392 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

3 | 2025-03-21 18:02:23.628427

4 | 2025-03-21 18:05:19.635836

(4 rows)

hrdb=>

2.6基于时间点的，以小步长重放xlog的恢复

在典型的设置中，找到一个合理的终止恢复的点的最好的方法是使用pause_at_recovery_target。如果它被设置为ture，达到恢复点时，PostgreSQL将不会自动转变为生产实例。相反，他将等待数据库管理员的指令。如果您不知道要重放xlog到什么位置，这是非常有用的。您可以重放，登录，看看到数据库的哪个位置，更改为下一个目标时间，并不断以小步长来重放。

假设"2025-03-21 18:03:00.628427"为我们所需重放的位置，下面将演练重放至“2025-03-21 17:51:50.906044+08”，“2025-03-21 18:02:20.628427+08”，““2025-03-21 18:03:00.628427+08”，多次重放xlog的恢复

2.6.1 停止数据库并删除data目录

[omm@pwnode1 ~]$ gs_om -t stop

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

[omm@pwnode1 ~]$ rm -rf /database/panweidb/data

[omm@pwnode1 ~]$

2.6.2 查看备份并恢复数据库

[omm@pwnode1 data]$ gs_om -t stop

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

[omm@pwnode1 data]$ rm -rf /database/panweidb/data

[omm@pwnode1 data]$

2.6.3 查看备份并恢复数据库

[omm@pwnode1 ~]$ gs_probackup show --backup-path=/backup/probackup --instance=panweidb

=======================================================================================================================================

Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Type Status

=======================================================================================================================================

panweidb 9.2 STGW7H 2025-03-21 17:40:36+08 FULL STREAM 1/0 12s 719MB 16MB 0.98 0/6000028 0/6000270 FILE OK

[omm@pwnode1 ~]$ gs_probackup restore -B /backup/probackup --instance=panweidb -i STGW7H -D /database/panweidb/data

LOG: Restore begin.

LOG: there is no file tablespace_map

LOG: check tablespace directories of backup STGW7H

LOG: check external directories of backup STGW7H

INFO: Validating backup STGW7H

INFO: Backup STGW7H data files are valid

LOG: Thread [1]: Opening WAL segment “/backup/probackup/backups/panweidb/STGW7H/database/pg_xlog/000000010000000000000006”

INFO: Backup STGW7H WAL segments are valid

INFO: Backup STGW7H is valid.

INFO: Restoring the database from backup at 2025-03-21 17:40:29+08

LOG: there is no file tablespace_map

LOG: Restore directories and symlinks… in /database/panweidb/data

INFO: Start restoring backup files. DATA size: 719MB

LOG: Start thread 1

INFO: Backup files are restored. Transfered bytes: 735MB, time elapsed: 1s

INFO: Restore incremental ratio (less is better): 102% (735MB/719MB)

INFO: Syncing restored files to disk

INFO: Restored backup files are synced, time elapsed: 0

The pw_guc run with the following arguments: [gs_guc -D /database/panweidb/data -c pw_wal_directory=‘pg_xlog’ set ].

expected instance path: [/database/panweidb/data/postgresql.conf]

gs_guc set: pw_wal_directory=‘pg_xlog’: [/database/panweidb/data/postgresql.conf]

Total instances: 1. Failed instances: 0.

Success to perform gs_guc!

INFO: Restore of backup STGW7H completed.

[omm@pwnode1 ~]$

2.6.4 检查hot_standby已开启

[omm@pwnode1 ~]$ grep -i hot_standby $PGDATA/postgresql.conf

wal_level = hot_standby # minimal, archive, hot_standby or logical

hot_standby = on # “on” allows queries during recovery

#hot_standby_feedback = off # send info from standby to prevent

[omm@pwnode1 ~]$

2.6.5 创建归档恢复配置文件，输入以下内容

[omm@pwnode1 ~]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 ~]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_time=‘2025-03-21 17:51:50.906044+08’

recovery_target_inclusive =true

pause_at_recovery_target =true

[omm@pwnode1 ~]$

2.6.6 启动数据库并检查数据恢复情况

[omm@pwnode1 ~]$ gs_om -t start

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 22:48:50.707 [unknown] [unknown] localhost 140144426639936 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 22:48:50.711 [unknown] [unknown] localhost 140144426639936 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 22:48:51.010 [unknown] [unknown] localhost 140144426639936 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 22:48:51.010 [unknown] [unknown] localhost 140144426639936 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±--------------------------

1 | 2025-03-21 17:50:47.20499

(1 row)

hrdb=> select pg_is_xlog_replay_paused();

pg_is_xlog_replay_paused

--------------------------

(1 row)

hrdb=>

2.6.7 更改恢复时间点，继续重放xlog

[omm@pwnode1 data]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 data]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_time=‘2025-03-21 18:02:20.628427’

recovery_target_inclusive =true

pause_at_recovery_target =true

[omm@pwnode1 data]$

2.6.8 重启数据库继续恢复

[omm@pwnode1 ~]$ gs_om -t stop && gs_om -t start

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 23:22:27.683 [unknown] [unknown] localhost 140388788097600 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 23:22:27.687 [unknown] [unknown] localhost 140388788097600 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 23:22:27.916 [unknown] [unknown] localhost 140388788097600 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 23:22:27.916 [unknown] [unknown] localhost 140388788097600 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$

2.6.9 检查数据恢复情况及恢复进程状态

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

(2 rows)

hrdb=> select pg_is_xlog_replay_paused();

pg_is_xlog_replay_paused

--------------------------

(1 row)

hrdb=>

2.6.10 再次更改恢复时间点，继续重放xlog

[omm@pwnode1 ~]$ vi /database/panweidb/data/recovery.conf

[omm@pwnode1 ~]$ more /database/panweidb/data/recovery.conf

restore_command=‘cp /archive/%f %p’

recovery_target_time=‘2025-03-21 18:03:00.628427+08’

recovery_target_inclusive =true

pause_at_recovery_target =true

[omm@pwnode1 ~]$

2.6.11 重启数据库继续恢复

[omm@pwnode1 ~]$ gs_om -t stop && gs_om -t start

Stopping cluster.

=========================================

Successfully stopped cluster.

=========================================

End stop cluster.

Starting cluster.

=========================================

[SUCCESS] pwnode1

2025-03-21 23:30:07.593 [unknown] [unknown] localhost 140514130022976 0[0:0#0] 0 [BACKEND] WARNING: Error happen when load license, error code 2, error message cannot write data to dir /etc/panweidb/license .

2025-03-21 23:30:07.597 [unknown] [unknown] localhost 140514130022976 0[0:0#0] 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets

2025-03-21 23:30:07.834 [unknown] [unknown] localhost 140514130022976 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm information: Cgroup does not exist(50002)

2025-03-21 23:30:07.834 [unknown] [unknown] localhost 140514130022976 0[0:0#0] 0 [BACKEND] WARNING: Cgroup get_cgroup Gaussdb:omm/Class/DefaultClass information: Cgroup does not exist(50002)

=========================================

Successfully started.

[omm@pwnode1 ~]$

2.6.12 检查数据恢复情况

[omm@pwnode1 ~]$ gsql -U hr -d hrdb -r

Password for user hr:

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

hrdb=> select * from pitr_test ;

id | t

----±---------------------------

1 | 2025-03-21 17:50:47.20499

2 | 2025-03-21 17:56:56.838657

3 | 2025-03-21 18:02:23.628427

(3 rows)

hrdb=> select pg_is_xlog_replay_paused();

pg_is_xlog_replay_paused

--------------------------

(1 row)

hrdb=> \q

2.6.13 终止恢复，使实例可读可写,对外提供服务

[omm@pwnode1 ~]$ gsql -r

gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type “help” for help.

postgres=# select pg_xlog_replay_resume();

pg_xlog_replay_resume

-----------------------

(1 row)

postgres=# select pg_is_xlog_replay_paused();

ERROR: recovery is not in progress

HINT: Recovery control functions can only be executed during recovery.

CONTEXT: referenced column: pg_is_xlog_replay_paused

postgres=# create table test1(a int);

CREATE TABLE

postgres=#

3.参考文档

https://docs.opengauss.org/zh/docs/latest/docs/ToolandCommandReference/gs_probackup.html

https://docs.opengauss.org/zh/docs/latest/docs/DatabaseOMGuide/%E7%89%A9%E7%90%86%E5%A4%87%E4%BB%BD%E4%B8%8E%E6%81%A2%E5%A4%8D.html#pitr%E6%81%A2%E5%A4%8D