MogDB学习笔记系列 -- 通过BRM备份恢复工具来进行单表恢复

MogDB作为云和恩墨基于OpenGauss内核的商业发行版，在此基础之上做了大量的Bug修复和功能增强，目前以及应用在大量客户生产环境中。上一篇文章我们简单研究了MogDB的后台线程，这里我们来看下如何进行备份恢复。

首先opengauss原生的备份工具gs_probackup,使用起来相对负责，因此我们基于gs_probackup做了一些封装和优化，简称BRM。

这里我们简单来测试一下如何使用BRM恢复工具，如下：
1、首先创建一个测试库表

postgres=# create user roger encrypted password 'roger@007';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES TO roger;
ALTER ROLE
postgres=# create database enmotech template template0 DBCOMPATIBILITY 'A';
CREATE DATABASE
postgres=# alter database enmotech owner to roger;
ALTER DATABASE
postgres=# \q
[omm@mogdb base]$ 
[omm@mogdb base]$ 
[omm@mogdb base]$ 
[omm@mogdb base]$ gsql -d enmotech -Uroger -p 26000        
Password for user roger: 
gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

enmotech=> \l  
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 enmotech  | roger | SQL_ASCII | C       | C     | 
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(4 rows)

enmotech=> create table test1122 as select * from pg_settings;
INSERT 0 601
enmotech=> 

复制

2、通过BRM进行数据库全备份

对于brm工具的安装，这里略，rpm -ivh安装 brm rpm包即可。
++++全备

[omm@mogdb enmotech]$ brm backup-instance -i enmotech -b full
time="2021-11-22 01:17:56.835105" level=info msg="Using config file:/etc/brm.yaml"
time="2021-11-22 01:17:56.835901" level=info msg="the gs_probackup path /data/mogdb/bin/gs_probackup"
time="2021-11-22 01:17:56.845717" level=info msg="the gs_probackup version 2.0.0"
time="2021-11-22 01:17:56.845772" level=info msg="the gs_ctl path /data/mogdb/bin/gs_ctl"
time="2021-11-22 01:17:56.85606" level=info msg="gs_ctl version 9.2.4 "
time="2021-11-22 01:17:56.878623" level=info msg="Run Backup Process Id 15479"
time="2021-11-22 01:17:56.880513" level=info msg="INFO: Backup start, gs_probackup version: 2.4.2, instance: enmotech, backup ID: R2YV5W, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1"
time="2021-11-22 01:17:56.884998" level=info msg="LOG: Backup destination is initialized"
time="2021-11-22 01:17:56.897959" level=info msg="VERBOSE: (query) SELECT pg_catalog.current_setting($1)"
time="2021-11-22 01:17:56.898168" level=info msg="VERBOSE:      (param:0) = block_size"
time="2021-11-22 01:17:56.898737" level=info msg="VERBOSE: (query) SELECT pg_catalog.current_setting($1)"
time="2021-11-22 01:17:56.898995" level=info msg="VERBOSE:      (param:0) = wal_block_size"
time="2021-11-22 01:17:56.899069" level=info msg="VERBOSE: (query) SELECT proname FROM pg_proc WHERE proname='pgpro_edition'"
time="2021-11-22 01:17:56.901675" level=info msg="VERBOSE: (query) SELECT pg_catalog.pg_is_in_recovery()"
time="2021-11-22 01:17:56.901753" level=info msg="WARNING: This MogDB instance was initialized without data block checksums. gs_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'."
time="2021-11-22 01:17:56.901773" level=info msg="LOG: Database backup start"
time="2021-11-22 01:17:56.901785" level=info msg="VERBOSE: (query) SELECT pg_catalog.pg_start_backup($1, $2)"
time="2021-11-22 01:17:56.901803" level=info msg="VERBOSE:      (param:0) = 2021-11-22 01:17:56-08 with pg_probackup"
time="2021-11-22 01:17:56.901819" level=info msg="VERBOSE:      (param:1) = true"
time="2021-11-22 01:17:57.017225" level=info msg="VERBOSE: (query) show checkpoint_timeout"
time="2021-11-22 01:17:57.02665" level=info msg="VERBOSE: (query) IDENTIFY_SYSTEM"
time="2021-11-22 01:17:57.029656" level=info msg="VERBOSE: Excluding directory content: pg_xlog"
time="2021-11-22 01:17:57.029734" level=info msg="VERBOSE: Excluding directory content: pg_notify"
time="2021-11-22 01:17:57.029752" level=info msg="VERBOSE: Excluding directory content: pg_serial"
time="2021-11-22 01:17:57.029785" level=info msg="VERBOSE: Excluding directory content: pg_snapshots"
time="2021-11-22 01:17:57.029796" level=info msg="INFO: Cannot parse path "base""
time="2021-11-22 01:17:57.03248" level=info msg="VERBOSE: Excluding directory content: pg_replslot"
time="2021-11-22 01:17:57.0326" level=info msg="VERBOSE: Excluding directory content: pg_stat_tmp"
time="2021-11-22 01:17:57.032615" level=info msg="VERBOSE: Excluding file: postmaster.opts"
time="2021-11-22 01:17:57.032626" level=info msg="VERBOSE: Excluding file: postmaster.pid"
time="2021-11-22 01:17:57.032661" level=info msg="VERBOSE: (query) SELECT oid, datname FROM pg_catalog.pg_database WHERE datname NOT IN ('template1', 'template0', 'postgres')"
time="2021-11-22 01:17:57.03521" level=info msg="INFO: PGDATA size: 565MB"
time="2021-11-22 01:17:57.035301" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/base'"
time="2021-11-22 01:17:57.035352" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/base/1'"
time="2021-11-22 01:17:57.035364" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/base/15098'"
time="2021-11-22 01:17:57.035385" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/base/15103'"
time="2021-11-22 01:17:57.035408" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/base/16423'"
time="2021-11-22 01:17:57.035422" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/base/pgsql_tmp'"
time="2021-11-22 01:17:57.035437" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/global'"
time="2021-11-22 01:17:57.035456" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_cbm'"
time="2021-11-22 01:17:57.035574" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_clog'"
time="2021-11-22 01:17:57.035589" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_csnlog'"
time="2021-11-22 01:17:57.035609" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_errorinfo'"
time="2021-11-22 01:17:57.03562" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_llog'"
time="2021-11-22 01:17:57.035632" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_llog/mappings'"
time="2021-11-22 01:17:57.035643" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_llog/snapshots'"
time="2021-11-22 01:17:57.035678" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_location'"
time="2021-11-22 01:17:57.035694" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_multixact'"
time="2021-11-22 01:17:57.035705" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_multixact/members'"
time="2021-11-22 01:17:57.035716" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_multixact/offsets'"
time="2021-11-22 01:17:57.035727" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_notify'"
time="2021-11-22 01:17:57.035742" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_replslot'"
time="2021-11-22 01:17:57.035753" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_serial'"
time="2021-11-22 01:17:57.035764" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_snapshots'"
time="2021-11-22 01:17:57.035784" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_stat_tmp'"
time="2021-11-22 01:17:57.035796" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_tblspc'"
time="2021-11-22 01:17:57.035806" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_twophase'"
time="2021-11-22 01:17:57.03582" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV5W/database/pg_xlog'"
time="2021-11-22 01:17:57.035836" level=info msg="LOG: started streaming WAL at 0/14000000 (timeline 1)"
time="2021-11-22 01:17:57.03585" level=info msg="[2021-11-22 01:17:57]: check identify system success"
time="2021-11-22 01:17:57.041117" level=info msg="[2021-11-22 01:17:57]: send START_REPLICATION 0/14000000 success"
time="2021-11-22 01:17:57.041186" level=info msg="[2021-11-22 01:17:57]: keepalive message is received"
time="2021-11-22 01:17:57.042763" level=info msg="[2021-11-22 01:17:57]: keepalive message is received"
time="2021-11-22 01:17:57.046135" level=info msg="INFO: Start transferring data files"
time="2021-11-22 01:17:57.046226" level=info msg="VERBOSE: Start thread num: 0"
time="2021-11-22 01:17:57.052476" level=info msg="LOG: Creating page header map "/var/lib/brm/backups/enmotech/R2YV5W/page_header_map""
time="2021-11-22 01:17:57.585013" level=info msg="INFO: Data files are transferred, time elapsed: 0"
time="2021-11-22 01:17:57.585098" level=info msg="VERBOSE: (query) SET client_min_messages = warning;"
time="2021-11-22 01:17:57.587626" level=info msg="VERBOSE: (query) SET datestyle = 'ISO, DMY';"
time="2021-11-22 01:17:57.590578" level=info msg="VERBOSE: (query) SELECT pg_catalog.pg_create_restore_point($1)"
time="2021-11-22 01:17:57.590642" level=info msg="VERBOSE:      (param:0) = pg_probackup, backup_id R2YV5W"
time="2021-11-22 01:17:57.591288" level=info msg="VERBOSE: (query) SELECT pg_catalog.txid_snapshot_xmax(pg_catalog.txid_current_snapshot()), current_timestamp(0)::timestamptz, pg_catalog.pg_stop_backup() as lsn"
time="2021-11-22 01:17:57.654593" level=info msg="VERBOSE: finished segment at 0/15000000 (timeline 1)"
time="2021-11-22 01:17:58.593533" level=info msg="INFO: wait for pg_stop_backup()"
time="2021-11-22 01:17:58.593727" level=info msg="INFO: pg_stop backup() successfully executed"
time="2021-11-22 01:17:58.593782" level=info msg="LOG: stop_lsn: 0/140001E0"
time="2021-11-22 01:17:58.593808" level=info msg="LOG: Looking for LSN 0/140001E0 in segment: 000000010000000000000014"
time="2021-11-22 01:17:58.593825" level=info msg="LOG: Found WAL segment: /var/lib/brm/backups/enmotech/R2YV5W/database/pg_xlog/000000010000000000000014"
time="2021-11-22 01:17:58.593838" level=info msg="VERBOSE: Thread [0]: Need to switch to the next WAL segment, page LSN 0/14000000, record being read LSN 0/140001E0"
time="2021-11-22 01:17:58.593857" level=info msg="LOG: Thread [0]: Opening WAL segment "/var/lib/brm/backups/enmotech/R2YV5W/database/pg_xlog/000000010000000000000014""
time="2021-11-22 01:17:58.593869" level=info msg="LOG: Found LSN: 0/140001E0"
time="2021-11-22 01:18:02.717183" level=info msg="[2021-11-22 01:18:02]:(null): not renaming 000000010000000000000015, segment is not complete."
time="2021-11-22 01:18:02.717289" level=info msg="LOG: finished streaming WAL at 0/15000140 (timeline 1)"
time="2021-11-22 01:18:02.717385" level=info msg="LOG: Getting the Recovery Time from WAL"
time="2021-11-22 01:18:02.717743" level=info msg="VERBOSE: Thread [0]: Need to switch to the next WAL segment, page LSN 0/14000000, record being read LSN 0/140001E0"
time="2021-11-22 01:18:02.717762" level=info msg="LOG: Thread [0]: Opening WAL segment "/var/lib/brm/backups/enmotech/R2YV5W/database/pg_xlog/000000010000000000000014""
time="2021-11-22 01:18:02.744533" level=info msg="INFO: Syncing backup files to disk"
time="2021-11-22 01:18:02.943513" level=info msg="INFO: Backup files are synced, time elapsed: 0"
time="2021-11-22 01:18:02.946488" level=info msg="INFO: Validating backup R2YV5W"
[omm@mogdb enmotech]$ 
复制

这里我省略了2个步骤，分别的add-server和add instance的注册过程，大家测试过程中brm –help即可，使用非常简单。

3、查看备份集信息

[omm@mogdb enmotech]$ brm show-backup
time="2021-11-22 01:18:37.145233" level=info msg="Using config file:/etc/brm.yaml"
time="2021-11-22 01:18:37.145658" level=info msg="the gs_probackup path /data/mogdb/bin/gs_probackup"
time="2021-11-22 01:18:37.154606" level=info msg="the gs_probackup version 2.0.0"
time="2021-11-22 01:18:37.154652" level=info msg="the gs_ctl path /data/mogdb/bin/gs_ctl"
time="2021-11-22 01:18:37.163752" level=info msg="gs_ctl version 9.2.4 "
data source: local
===================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode  WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
===================================================================================================================================
 enmotech  9.2      R2YV5W  2021-11-22 01:17:57-08  FULL  STREAM    1/0    6s  581MB  16MB    0.97  0/14000028  0/140001E0  OK 


复制

从上述信息来看，我们可以看到opengauss内核是基于PostgreSQL 9.2.4的。其中-b 选项分别可以指定full 和ptrack 。表示全备

和增量备份。备份完成后，可以通过brm show-backup查看备份集信息。

4、测试表插入数据模拟增量数据

enmotech=> insert into test1122 select * from test1122;
INSERT 0 601
enmotech=>   select count(1) from test1122;
 count 
-------
  1202
(1 row)

enmotech=> 
enmotech=> select pg_relation_filepath('test1122');
 pg_relation_filepath 
----------------------
 base/16423/16424
(1 row)

enmotech=> select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
     1 | template1
 15098 | template0
 16423 | enmotech
 15103 | postgres
(4 rows)

复制

5、增量备份

+++增量备份
 [omm@mogdb enmotech]$ brm backup-instance -i enmotech -b PTRACK
time="2021-11-22 01:19:35.686438" level=info msg="Using config file:/etc/brm.yaml"
time="2021-11-22 01:19:35.68765" level=info msg="the gs_probackup path /data/mogdb/bin/gs_probackup"
time="2021-11-22 01:19:35.709631" level=info msg="the gs_probackup version 2.0.0"
time="2021-11-22 01:19:35.709698" level=info msg="the gs_ctl path /data/mogdb/bin/gs_ctl"
time="2021-11-22 01:19:35.719606" level=info msg="gs_ctl version 9.2.4 "
time="2021-11-22 01:19:35.74155" level=info msg="Run Backup Process Id 15516"
time="2021-11-22 01:19:35.745239" level=info msg="INFO: Backup start, gs_probackup version: 2.4.2, instance: enmotech, backup ID: R2YV8N, backup mode: PTRACK, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1"
time="2021-11-22 01:19:35.750105" level=info msg="LOG: Backup destination is initialized"
time="2021-11-22 01:19:35.763082" level=info msg="VERBOSE: (query) SELECT pg_catalog.current_setting($1)"
time="2021-11-22 01:19:35.763152" level=info msg="VERBOSE:      (param:0) = block_size"
time="2021-11-22 01:19:35.76363" level=info msg="VERBOSE: (query) SELECT pg_catalog.current_setting($1)"
time="2021-11-22 01:19:35.763671" level=info msg="VERBOSE:      (param:0) = wal_block_size"
time="2021-11-22 01:19:35.763684" level=info msg="VERBOSE: (query) SELECT proname FROM pg_proc WHERE proname='pgpro_edition'"
time="2021-11-22 01:19:35.763977" level=info msg="VERBOSE: (query) SELECT pg_catalog.pg_is_in_recovery()"
time="2021-11-22 01:19:35.764286" level=info msg="WARNING: This MogDB instance was initialized without data block checksums. gs_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'."
time="2021-11-22 01:19:35.764316" level=info msg="LOG: Database backup start"
time="2021-11-22 01:19:35.764329" level=info msg="VERBOSE: (query) SELECT pg_catalog.pg_start_backup($1, $2)"
time="2021-11-22 01:19:35.764339" level=info msg="VERBOSE:      (param:0) = 2021-11-22 01:19:35-08 with pg_probackup"
time="2021-11-22 01:19:35.764351" level=info msg="VERBOSE:      (param:1) = true"
time="2021-11-22 01:19:35.899529" level=info msg="LOG: Latest valid FULL backup: R2YV5W"
time="2021-11-22 01:19:35.899617" level=info msg="INFO: Parent backup: R2YV5W"
time="2021-11-22 01:19:35.913532" level=info msg="VERBOSE: (query) show checkpoint_timeout"
time="2021-11-22 01:19:35.922491" level=info msg="VERBOSE: (query) IDENTIFY_SYSTEM"
time="2021-11-22 01:19:35.92257" level=info msg="VERBOSE: Excluding directory content: pg_xlog"
time="2021-11-22 01:19:35.922601" level=info msg="VERBOSE: Excluding directory content: pg_notify"
time="2021-11-22 01:19:35.922638" level=info msg="VERBOSE: Excluding directory content: pg_serial"
time="2021-11-22 01:19:35.922661" level=info msg="VERBOSE: Excluding directory content: pg_snapshots"
time="2021-11-22 01:19:35.922673" level=info msg="INFO: Cannot parse path "base""
time="2021-11-22 01:19:35.922685" level=info msg="VERBOSE: Excluding directory content: pg_replslot"
time="2021-11-22 01:19:35.922695" level=info msg="VERBOSE: Excluding directory content: pg_stat_tmp"
time="2021-11-22 01:19:35.922717" level=info msg="VERBOSE: Excluding file: postmaster.opts"
time="2021-11-22 01:19:35.922728" level=info msg="VERBOSE: Excluding file: postmaster.pid"
time="2021-11-22 01:19:35.922742" level=info msg="VERBOSE: (query) SELECT oid, datname FROM pg_catalog.pg_database WHERE datname NOT IN ('template1', 'template0', 'postgres')"
time="2021-11-22 01:19:35.922753" level=info msg="INFO: PGDATA size: 565MB"
time="2021-11-22 01:19:35.922768" level=info msg="LOG: Current tli: 1"
time="2021-11-22 01:19:35.922779" level=info msg="LOG: Parent start_lsn: 0/14000028"
time="2021-11-22 01:19:35.92279" level=info msg="LOG: start_lsn: 0/16000028"
time="2021-11-22 01:19:35.922803" level=info msg="INFO: Extracting pagemap of changed blocks"
time="2021-11-22 01:19:35.922814" level=info msg="VERBOSE: (query) CHECKPOINT;"
time="2021-11-22 01:19:35.924677" level=info msg="LOG: started streaming WAL at 0/16000000 (timeline 1)"
time="2021-11-22 01:19:35.924739" level=info msg="[2021-11-22 01:19:35]: check identify system success"
time="2021-11-22 01:19:35.926494" level=info msg="[2021-11-22 01:19:35]: send START_REPLICATION 0/16000000 success"
time="2021-11-22 01:19:35.926537" level=info msg="[2021-11-22 01:19:35]: keepalive message is received"
time="2021-11-22 01:19:35.933514" level=info msg="[2021-11-22 01:19:35]: keepalive message is received"
time="2021-11-22 01:19:36.023509" level=info msg="VERBOSE: (query) SELECT pg_cbm_tracked_location()"
time="2021-11-22 01:19:36.023599" level=info msg="VERBOSE: (query) SELECT path,changed_block_number,changed_block_list FROM pg_cbm_get_changed_block($1, $2)"
time="2021-11-22 01:19:36.023636" level=info msg="VERBOSE:      (param:0) = 0/14000028"
time="2021-11-22 01:19:36.02365" level=info msg="VERBOSE:       (param:1) = 00000000/16000140"
time="2021-11-22 01:19:36.0265" level=info msg="VERBOSE: Using ptrack pagemap for file "base/16423/16424""
time="2021-11-22 01:19:36.026537" level=info msg="INFO: Pagemap successfully extracted, time elapsed: 1 sec"
time="2021-11-22 01:19:36.026549" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/base'"
time="2021-11-22 01:19:36.026571" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/base/1'"
time="2021-11-22 01:19:36.026587" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/base/15098'"
time="2021-11-22 01:19:36.026598" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/base/15103'"
time="2021-11-22 01:19:36.026615" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/base/16423'"
time="2021-11-22 01:19:36.026629" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/base/pgsql_tmp'"
time="2021-11-22 01:19:36.026643" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/global'"
time="2021-11-22 01:19:36.026654" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_cbm'"
time="2021-11-22 01:19:36.026666" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_clog'"
time="2021-11-22 01:19:36.026679" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_csnlog'"
time="2021-11-22 01:19:36.02669" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_errorinfo'"
time="2021-11-22 01:19:36.026702" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_llog'"
time="2021-11-22 01:19:36.026713" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_llog/mappings'"
time="2021-11-22 01:19:36.026751" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_llog/snapshots'"
time="2021-11-22 01:19:36.026764" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_location'"
time="2021-11-22 01:19:36.026778" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_multixact'"
time="2021-11-22 01:19:36.026789" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_multixact/members'"
time="2021-11-22 01:19:36.026803" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_multixact/offsets'"
time="2021-11-22 01:19:36.026816" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_notify'"
time="2021-11-22 01:19:36.026828" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_replslot'"
time="2021-11-22 01:19:36.026839" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_serial'"
time="2021-11-22 01:19:36.026854" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_snapshots'"
time="2021-11-22 01:19:36.02687" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_stat_tmp'"
time="2021-11-22 01:19:36.026882" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_tblspc'"
time="2021-11-22 01:19:36.026892" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_twophase'"
time="2021-11-22 01:19:36.026904" level=info msg="VERBOSE: Create directory '/var/lib/brm/backups/enmotech/R2YV8N/database/pg_xlog'"
time="2021-11-22 01:19:36.031501" level=info msg="INFO: Start transferring data files"
time="2021-11-22 01:19:36.03155" level=info msg="VERBOSE: Start thread num: 0"
time="2021-11-22 01:19:36.031565" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/global/15022_fsm""
time="2021-11-22 01:19:36.031594" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/15103/14811_fsm""
time="2021-11-22 01:19:36.031607" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14623""
time="2021-11-22 01:19:36.03162" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14613_fsm""
time="2021-11-22 01:19:36.031638" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14624""
time="2021-11-22 01:19:36.031651" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14638""
time="2021-11-22 01:19:36.034469" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14637""
time="2021-11-22 01:19:36.034509" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14678""
time="2021-11-22 01:19:36.034536" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14782""
time="2021-11-22 01:19:36.034552" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/global/config_exec_params""
time="2021-11-22 01:19:36.034609" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/global/14904""
time="2021-11-22 01:19:36.034628" level=info msg="VERBOSE: Skipping the unchanged file: "/data/mogdb/data/db1/base/1/14694""
.......
time="2021-11-22 01:19:36.310152" level=info msg="VERBOSE: (query) SELECT pg_catalog.pg_create_restore_point($1)"
time="2021-11-22 01:19:36.310163" level=info msg="VERBOSE:      (param:0) = pg_probackup, backup_id R2YV8N"
time="2021-11-22 01:19:36.310177" level=info msg="VERBOSE: (query) SELECT pg_catalog.txid_snapshot_xmax(pg_catalog.txid_current_snapshot()), current_timestamp(0)::timestamptz, pg_catalog.pg_stop_backup() as lsn"
time="2021-11-22 01:19:36.372034" level=info msg="VERBOSE: finished segment at 0/17000000 (timeline 1)"
time="2021-11-22 01:19:37.29763" level=info msg="INFO: wait for pg_stop_backup()"
time="2021-11-22 01:19:37.297731" level=info msg="INFO: pg_stop backup() successfully executed"
time="2021-11-22 01:19:37.297747" level=info msg="LOG: stop_lsn: 0/160002F8"
time="2021-11-22 01:19:37.297763" level=info msg="LOG: Looking for LSN 0/160002F8 in segment: 000000010000000000000016"
time="2021-11-22 01:19:37.297776" level=info msg="LOG: Found WAL segment: /var/lib/brm/backups/enmotech/R2YV8N/database/pg_xlog/000000010000000000000016"
time="2021-11-22 01:19:37.297795" level=info msg="VERBOSE: Thread [0]: Need to switch to the next WAL segment, page LSN 0/16000000, record being read LSN 0/160002F8"
time="2021-11-22 01:19:37.297808" level=info msg="LOG: Thread [0]: Opening WAL segment "/var/lib/brm/backups/enmotech/R2YV8N/database/pg_xlog/000000010000000000000016""
time="2021-11-22 01:19:37.297824" level=info msg="LOG: Found LSN: 0/160002F8"
time="2021-11-22 01:19:41.367193" level=info msg="[2021-11-22 01:19:41]:(null): not renaming 000000010000000000000017, segment is not complete."
time="2021-11-22 01:19:41.367266" level=info msg="LOG: finished streaming WAL at 0/17000140 (timeline 1)"
time="2021-11-22 01:19:41.370272" level=info msg="LOG: Getting the Recovery Time from WAL"
time="2021-11-22 01:19:41.370325" level=info msg="VERBOSE: Thread [0]: Need to switch to the next WAL segment, page LSN 0/16000000, record being read LSN 0/160002F8"
time="2021-11-22 01:19:41.370345" level=info msg="LOG: Thread [0]: Opening WAL segment "/var/lib/brm/backups/enmotech/R2YV8N/database/pg_xlog/000000010000000000000016""
time="2021-11-22 01:19:41.385402" level=info msg="INFO: Syncing backup files to disk"
time="2021-11-22 01:19:41.493332" level=info msg="INFO: Backup files are synced, time elapsed: 0"
time="2021-11-22 01:19:41.498656" level=info msg="INFO: Validating backup R2YV8N"
time="2021-11-22 01:19:41.637502" level=info msg="INFO: Backup R2YV8N data files are valid"
time="2021-11-22 01:19:41.637606" level=info msg="INFO: Backup R2YV8N resident size: 289MB"
time="2021-11-22 01:19:41.637622" level=info msg="INFO: Backup R2YV8N completed"
[omm@mogdb enmotech]$ 
[omm@mogdb enmotech]$ 


复制

这里需要注意的是，在进行增量备份之前，需要设置enable_cbm_tracking=on（将参数加入到postgresql.conf).
否则在进行增量备份的时候，会提示需要设置该参数并备份失败。增量备份完成后，我们再看下备份情况：

[omm@mogdb enmotech]$ brm show-backup
time="2021-11-22 01:19:45.730158" level=info msg="Using config file:/etc/brm.yaml"
time="2021-11-22 01:19:45.730524" level=info msg="the gs_probackup path /data/mogdb/bin/gs_probackup"
time="2021-11-22 01:19:45.73976" level=info msg="the gs_probackup version 2.0.0"
time="2021-11-22 01:19:45.739813" level=info msg="the gs_ctl path /data/mogdb/bin/gs_ctl"
time="2021-11-22 01:19:45.750589" level=info msg="gs_ctl version 9.2.4 "
data source: local
=====================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode    WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
=====================================================================================================================================
 enmotech  9.2      R2YV8N  2021-11-22 01:19:36-08  PTRACK  STREAM    1/1    6s  273MB  16MB    0.94  0/16000028  0/160002F8  OK     
 enmotech  9.2      R2YV5W  2021-11-22 01:17:57-08  FULL    STREAM    1/0    6s  581MB  16MB    0.97  0/14000028  0/140001E0  OK    

复制

6、模拟误删除test1122测试表

enmotech=# drop table test1122;
DROP TABLE
enmotech=# \q

复制

7、通过备份恢复到临时目录

+++++恢复增量到tmp目录

[omm@mogdb enmotech]$ brm restore -i enmotech -D /tmp/16423 -b R2YV8N
time="2021-11-22 01:26:05.890798" level=info msg="Using config file:/etc/brm.yaml"
time="2021-11-22 01:26:05.891172" level=info msg="the gs_probackup path /data/mogdb/bin/gs_probackup"
time="2021-11-22 01:26:05.900684" level=info msg="the gs_probackup version 2.0.0"
time="2021-11-22 01:26:05.900735" level=info msg="the gs_ctl path /data/mogdb/bin/gs_ctl"
time="2021-11-22 01:26:05.930789" level=info msg="gs_ctl version 9.2.4 "
time="2021-11-22 01:26:05.948501" level=info msg="Instance enmotech begin restore"
time="2021-11-22 01:26:05.954701" level=info msg="Run Backup Process Id 15642"
time="2021-11-22 01:26:05.96002" level=info msg="LOG: Restore begin."
time="2021-11-22 01:26:05.960086" level=info msg="LOG: there is no file tablespace_map"
time="2021-11-22 01:26:05.960123" level=info msg="LOG: check tablespace directories of backup R2YV8N"
time="2021-11-22 01:26:05.960145" level=info msg="LOG: check external directories of backup R2YV8N"
time="2021-11-22 01:26:05.960162" level=info msg="INFO: Validating parents for backup R2YV8N"
time="2021-11-22 01:26:05.960209" level=info msg="WARNING: Process 15479 which used backup R2YV5W no longer exists"
time="2021-11-22 01:26:05.974546" level=info msg="INFO: Validating backup R2YV5W"
time="2021-11-22 01:26:06.234783" level=info msg="INFO: Backup R2YV5W data files are valid"
time="2021-11-22 01:26:06.234922" level=info msg="WARNING: Process 15516 which used backup R2YV8N no longer exists"
time="2021-11-22 01:26:06.244558" level=info msg="INFO: Validating backup R2YV8N"
time="2021-11-22 01:26:06.37181" level=info msg="INFO: Backup R2YV8N data files are valid"
time="2021-11-22 01:26:06.377659" level=info msg="LOG: Thread [1]: Opening WAL segment "/var/lib/brm/backups/enmotech/R2YV8N/database/pg_xlog/000000010000000000000016""
time="2021-11-22 01:26:06.378529" level=info msg="INFO: Backup R2YV8N WAL segments are valid"
time="2021-11-22 01:26:06.378574" level=info msg="INFO: Backup R2YV8N is valid."
time="2021-11-22 01:26:06.37859" level=info msg="INFO: Restoring the database from backup at 2021-11-22 01:19:35-08"
time="2021-11-22 01:26:06.405017" level=info msg="LOG: there is no file tablespace_map"
time="2021-11-22 01:26:06.405122" level=info msg="LOG: Restore directories and symlinks..."
time="2021-11-22 01:26:06.405139" level=info msg="INFO: Start restoring backup files. PGDATA size: 581MB"
time="2021-11-22 01:26:06.405151" level=info msg="LOG: Start thread 1"
time="2021-11-22 01:26:06.807536" level=info msg="INFO: Backup files are restored. Transfered bytes: 597MB, time elapsed: 0"
time="2021-11-22 01:26:06.807638" level=info msg="INFO: Restore incremental ratio (less is better): 103% (597MB/581MB)"
time="2021-11-22 01:26:06.807667" level=info msg="INFO: Syncing restored files to disk"
time="2021-11-22 01:26:07.05638" level=info msg="Instance enmotech restore completed."
time="2021-11-22 01:26:07.056485" level=info msg="Instance enmotech begin write recovery.conf"
time="2021-11-22 01:26:07.056691" level=info msg="Instance enmotech no need recovery"
time="2021-11-22 01:26:07.056718" level=info msg="Instance enmotech begin start"
time="2021-11-22 01:26:07.057213" level=info msg="new instance post 26000"
time="2021-11-22 01:26:07.057811" level=error msg="chekc port 26000 err listen tcp :26000: bind: address already in use"
restore err listen tcp :26000: bind: address already in use
复制

这里我将备份恢复到/tmp目录。注意我这里直接指定的是最新增量备份集的backup ID。MogDB在进行恢复过程中会自动恢复，包括进行xlog恢复，将库恢复到最新状态。当然在实际恢复过程中，建议做基于时间点的恢复。

8、创建表空间并将恢复的文件cp到数据目录下

++++ 创建空表

enmotech=> create table enmotech2 as select * from pg_settings where 1=2; 
INSERT 0 0
enmotech=> select pg_relation_filepath('enmotech2');
 pg_relation_filepath 
----------------------
 base/16423/16433
(1 row)

复制

直接拷贝文件到数据文件目录下，无法识别：

[omm@mogdb 16423]$ mv 16445 16433
[omm@mogdb 16423]$ mv 16445_fsm 16433_fsm
[omm@mogdb 16423]$ ls -ltr 164*          
-rw-------. 1 omm dbgrp  24576 Nov 22 01:19 16424_fsm_bak
-rw-------. 1 omm dbgrp 262144 Nov 22 01:21 16424_bak
-rw-------. 1 omm dbgrp  24576 Nov 22 01:38 16433_fsm
-rw-------. 1 omm dbgrp 262144 Nov 22 01:39 16433
-rw-------. 1 omm dbgrp      0 Nov 22 01:45 16436
-rw-------. 1 omm dbgrp   8192 Nov 22 01:45 16438

enmotech=> select count(1) from test1122;
ERROR:  relation "test1122" does not exist on dn_6001
LINE 1: select count(1) from test1122;
                             ^

复制

在MogDB数据库中，直接将恢复的文件复制早数据目录中，是无法访问识别的，因为数据字典无法识别。

因此这里我们来手工构造一个表结构一样的空表即可。

9、更新一下数据字典

enmotech=# update pg_class set relname='test1122' where oid=16433;
UPDATE 1
enmotech=# select count(1) from test1122;
 count 
-------
  1202
(1 row)

复制

可以看到，我们删除的表已经被成功恢复了。当然这里我们模拟的是知道表结构的情况下，如果不知道表结构，就比较麻烦了。

所以对于表结构还是需要定期信息备份。最佳方案还是做好数据库容灾，或者考虑云和恩墨ZDBM 实时备份解决方案。

PS：MogDB在后面版本会增加flashback table功能，尽情期待！