MogDB学习笔记系列之 — 如何利用BRM备份做表级恢复
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MogDB学习笔记系列之 — 如何利用BRM备份做表级恢复
MogDB作为云和恩墨基于OpenGauss内核的商业发行版，在此基础之上做了大量的Bug修复和功能增强，目前以及应用在大量客户生产环境中。上一篇文章我们简单研究了MogDB的后台线程，这里我们来看下如何进行备份恢复。
首先opengauss原生的备份工具gs_probackup,使用起来相对负责，因此我们基于gs_probackup做了一些封装和简化，简称BRM。
这里我们简单来测试一下如何使用BRM恢复工具，如下：
1、首先创建一个测试库表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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进行数据库全备份
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
[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、查看备份集信息
|
1 2 3 4 5 6 7 8 9 10 11 |
[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、测试表插入数据模拟增量数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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、进行增量备份
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
[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" |
这里需要注意的是，在进行增量备份之前，需要设置enable_cbm_tracking=on（将参数加入到postgresql.conf). 否则在进行增量备份的时候，会提示需要设置该参数并备份失败。增量备份完成后，我们再看下备份情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[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测试表
|
1 2 3 |
enmotech=# drop table test1122; DROP TABLE enmotech=# \q |
7、通过备份恢复到临时目录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[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到数据目录下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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、更新一下数据字典
|
1 2 3 4 5 6 7 |
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功能，尽情期待！
Leave a Reply
You must be logged in to post a comment.