MogDB学习笔记之 –认识gd_restore恢复工具
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MogDB学习笔记之 –认识gd_restore恢复工具
前面学习了MogDB的备份工具gs_dump，主要用于逻辑备份，其中还有对应的逻辑恢复工具gs_restore。这里来跟大家一起学习。
|
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 |
[omm@mogdb bin]$ ./gs_restore --help gs_restore restores a MogDB database from an archive created by gs_dump. Usage: gs_restore [OPTION]... FILE General options: -d, --dbname=NAME connect to database name -f, --file=FILENAME output file name -F, --format=c|d|t backup file format (should be automatic) -l, --list print summarized TOC of the archive -v, --verbose verbose mode -V, --version output version information, then exit -?, --help show this help, then exit Options controlling the restore: -a, --data-only restore only the data, no schema -c, --clean clean (drop) database objects before recreating -C, --create create the target database -e, --exit-on-error exit on error, default is to continue -I, --index=NAME restore named index(s) -j, --jobs=NUM use this many parallel jobs to restore -L, --use-list=FILENAME use table of contents from this file for selecting/ordering output -n, --schema=NAME restore only objects in this schema(s) -O, --no-owner skip restoration of object ownership -P, --function=NAME(args) restore named function(s) -s, --schema-only restore only the schema, no data -S, --sysadmin=NAME system admin user name to use for disabling triggers -t, --table=NAME restore named table(s) -T, --trigger=NAME restore named trigger(s) -x, --no-privileges/--no-acl skip restoration of access privileges (grant/revoke) -1, --single-transaction restore as a single transaction --disable-triggers disable triggers during data-only restore --no-data-for-failed-tables do not restore data of tables that could not be created --no-security-labels do not restore security labels --no-tablespaces do not restore tablespace assignments --section=SECTION restore named section (pre-data, data, or post-data) --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password=PASSWORD the password of specified database user --role=ROLENAME do SET ROLE before restore --rolepassword=ROLEPASSWORD the password for role [omm@mogdb bin]$ |
从上面的介绍信息来看，gs_restore 也支持多种粒度的还原操作。这里来进行相关测试。
|
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 |
++++准备测试表 [omm@mogdb ~]$ gsql -d enmotech -p26000 gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr ) NOTICE : The password has been expired, please change the password. Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. enmotech=# select count(1) from db2mogdb; count ------- 4 (1 row) enmotech=# ++++ 备份整个database [omm@mogdb ~]$ gs_dump -p 26000 -U test -W test@1234 enmotech -f enmotech_20211201.tar -F t gs_dump[port='26000'][enmotech][2021-12-01 16:24:15]: The total objects number is 388. gs_dump[port='26000'][enmotech][2021-12-01 16:24:15]: [100.00%] 388 objects have been dumped. gs_dump[port='26000'][enmotech][2021-12-01 16:24:15]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-12-01 16:24:15]: total time: 384 ms [omm@mogdb ~]$ ++++ 模拟误删除表 [omm@mogdb ~]$ gsql -d enmotech -p26000 gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr ) NOTICE : The password has been expired, please change the password. Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. enmotech=# drop table db2mogdb; DROP TABLE enmotech=# \q ++++ 通过gs_restore进行还原操作 [omm@mogdb ~]$ gs_restore enmotech_20211201.tar -d enmotech -p26000 -Utest -W test@1234 start restore operation ... table db2mogdb complete data imported ! Finish reading 8 SQL statements! end restore operation ... restore operation successful total time: 13 ms [omm@mogdb ~]$ [omm@mogdb ~]$ ++++ 检查数据恢复是否成功 [omm@mogdb ~]$ gsql -d enmotech -p26000 -Utest -W test@1234 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=> \dt List of relations Schema | Name | Type | Owner | Storage --------+----------+-------+-------+---------------------------------- public | db2mogdb | table | test | {orientation=row,compression=no} (1 row) enmotech=> select count(1) from db2mogdb; count ------- 4 (1 row) enmotech=> |
可以看到通过gs_restore成功恢复了我们前面模拟drop table的表。
那么对对于truncate table操作呢？已经存在的对象，数据被清空，恢复理论上一样，如下：
|
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 |
[omm@mogdb ~]$ gsql -d enmotech -p26000 gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr ) NOTICE : The password has been expired, please change the password. Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. enmotech=# truncate table db2mogdb; TRUNCATE TABLE enmotech=# \q [omm@mogdb ~]$ gs_restore enmotech_20211201.tar -d enmotech -p26000 -Utest -W test@1234 start restore operation ... Error while PROCESSING TOC: Error from TOC entry 468; 1259 16522 TABLE db2mogdb test could not execute query: ERROR: relation "db2mogdb" already exists Command was: CREATE TABLE db2mogdb ( age integer ) WITH (orientation=row, compression=no); table db2mogdb complete data imported ! Finish reading 8 SQL statements! end restore operation ... WARNING: errors ignored on restore: 1 restore operation successful total time: 17 ms [omm@mogdb ~]$ [omm@mogdb ~]$ gsql -d enmotech -p26000 gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr ) NOTICE : The password has been expired, please change the password. Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. enmotech=# select count(1) from db2mogdb; count ------- 4 (1 row) enmotech=# |
除了对于database级别备份恢复之外，gs_restore也支持schema级别、表级别的还原操作，如下再次进行一些测试：
|
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 |
++++创建测试schema enmotech=# create schema roger; CREATE SCHEMA enmotech=# enmotech=# create table roger.test1201 as select * from db2mogdb; INSERT 0 4 enmotech=# insert into roger.test1201 select * from roger.test1201; INSERT 0 4 ...... enmotech=# insert into roger.test1201 select * from roger.test1201; INSERT 0 32768 enmotech=# insert into roger.test1201 select * from roger.test1201; INSERT 0 65536 enmotech=# insert into roger.test1201 select * from roger.test1201; INSERT 0 131072 enmotech=# select count(1) from roger.test1201; count -------- 262144 (1 row) +++++ 备份整个测试库enmotech [omm@mogdb ~]$ gs_dump -p 26000 -U test -W test@1234 enmotech -f enmotech_all.tar -F t gs_dump[port='26000'][enmotech][2021-12-01 16:39:56]: The total objects number is 391. gs_dump[port='26000'][enmotech][2021-12-01 16:39:56]: [100.00%] 391 objects have been dumped. gs_dump[port='26000'][enmotech][2021-12-01 16:39:56]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-12-01 16:39:56]: total time: 430 ms [omm@mogdb ~]$ ++++ 删除schema enmotech=# drop schema roger CASCADE; NOTICE: drop cascades to table roger.test1201 DROP SCHEMA enmotech=# \q ++++从整个database 备份中恢复单个schema [omm@mogdb ~]$ gs_restore enmotech_all.tar -d enmotech -n roger -p26000 -Utest -W test@1234 start restore operation ... table test1201 complete data imported ! Finish reading 11 SQL statements! end restore operation ... restore operation successful total time: 120 ms [omm@mogdb ~]$ [omm@mogdb ~]$ gsql -d enmotech -p26000 gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr ) NOTICE : The password has been expired, please change the password. Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. enmotech=# \dn List of schemas Name | Owner -------------+------- cstore | omm dbe_perf | omm pkg_service | omm public | omm roger | omm snapshot | omm (6 rows) enmotech=# select count(1) from roger.test1201; count -------- 262144 (1 row) enmotech=# ++++ 从整个database 备份中恢复单个表 [omm@mogdb ~]$ gs_restore enmotech_all.tar -d enmotech -n roger -t test1201 -p26000 -Utest -W test@1234 start restore operation ... table test1201 complete data imported ! Finish reading 11 SQL statements! end restore operation ... restore operation successful total time: 166 ms [omm@mogdb ~]$ |
最后简单总结一下gs_restore 恢复工具的主要功能点：
1、支持多种粒度的还原操作（database、schema、table等级别）
2、由于是逻辑备份，因此可以从全备中还原单个schema和单个表，操作灵活
3、支持并行操作
4、支持触发器等多种数据库对象；如果在还原数据时因为有trigger导致性能较低，可以关闭触发器，支持–disable-triggers参数。
Leave a Reply
You must be logged in to post a comment.