MogDB学习笔记系列之 — 认识gs_dump逻辑备份工具
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MogDB学习笔记系列之 — 认识gs_dump逻辑备份工具
对于数据库的备份恢复，MogDB都提供了丰富的工具，这里一起来学习一下逻辑备份工具gs_dump.
|
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 |
[omm@mogdb ~]$ gs_dump --help gs_dump dumps a database as a text file or to other formats. Usage: gs_dump [OPTION]... [DBNAME] General options: -f, --file=FILENAME output file or directory name -F, --format=c|d|t|p output file format (custom, directory, tar, plain text (default)) -v, --verbose verbose mode -V, --version output version information, then exit -Z, --compress=0-9 compression level for compressed formats --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock -?, --help show this help, then exit Options controlling the output content: -a, --data-only dump only the data, not the schema -b, --blobs include large objects in dump -c, --clean clean (drop) database objects before recreating -C, --create include commands to create database in dump -E, --encoding=ENCODING dump the data in encoding ENCODING -n, --schema=SCHEMA dump the named schema(s) only -N, --exclude-schema=SCHEMA do NOT dump the named schema(s) -o, --oids include OIDs in dump -O, --no-owner skip restoration of object ownership in plain-text format -s, --schema-only dump only the schema, no data -S, --sysadmin=NAME system admin user name to use in plain-text format -t, --table=TABLE dump the named table(s) only -T, --exclude-table=TABLE do NOT dump the named table(s) --include-table-file=FileName dump the named table(s) only --exclude-table-file=FileName do NOT dump the named table(s) -x, --no-privileges/--no-acl do not dump privileges (grant/revoke) --column-inserts/--attribute-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore --exclude-table-data=TABLE do NOT dump data for the named table(s) --inserts dump data as INSERT commands, rather than COPY --no-security-labels do not dump security label assignments --no-tablespaces do not dump tablespace assignments --no-unlogged-table-data do not dump unlogged table data --include-alter-table dump the table delete column --quote-all-identifiers quote all identifiers, even if not key words --section=SECTION dump named section (pre-data, data, or post-data) --serializable-deferrable wait until the dump can run without anomalies --dont-overwrite-file do not overwrite the existing file in case of plain, tar and custom format --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership --with-encryption=AES128 dump data is encrypted using AES128 --with-key=KEY AES128 encryption key, must be 16 bytes in length --with-salt=RANDVALUES used by gs_dumpall, pass rand value array --include-extensions include extensions in dump --binary-upgrade for use by upgrade utilities only --binary-upgrade-usermap="USER1=USER2" to be used only by upgrade utility for mapping usernames --non-lock-table for use by OM tools utilities only --include-depend-objs dump the object which depends on the input object --exclude-self do not dump the input object 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 dump --rolepassword=ROLEPASSWORD the password for role If no database name is supplied, then the PGDATABASE environment variable value is used. |
从gs_dump 的命令来看，比较灵活，也非常简单。支持schema级别，表级别的备份，也可以只备份数据，或者在进行备份时排除某些不需要备份的对象。
同时也支持加密备份,也支持多种级别的压缩，这是类似Oracle expdp功能。
|
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 |
++++ 备份元数据（表结构和权限等） [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_enmotech_metaonly.sql -s gs_dump[port='26000'][enmotech][2021-11-24 00:53:29]: The total objects number is 388. gs_dump[port='26000'][enmotech][2021-11-24 00:53:29]: [100.00%] 388 objects have been dumped. gs_dump[port='26000'][enmotech][2021-11-24 00:53:29]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-11-24 00:53:29]: total time: 364 ms [omm@mogdb ~]$ ls -ltr roger_enmotech_metaonly.sql -rw-------. 1 omm dbgrp 1576 Nov 24 00:53 roger_enmotech_metaonly.sql [omm@mogdb ~]$ ++++ 备份整个schema到文本 [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger.txt gs_dump[port='26000'][enmotech][2021-11-24 00:23:18]: The total objects number is 390. gs_dump[port='26000'][enmotech][2021-11-24 00:23:18]: [100.00%] 390 objects have been dumped. gs_dump[port='26000'][enmotech][2021-11-24 00:23:20]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-11-24 00:23:20]: total time: 2865 ms [omm@mogdb ~]$ more roger.txt -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET xmloption = content; SET client_encoding = 'SQL_ASCII'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = public; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: test1122; Type: TABLE; Schema: public; Owner: roger; Tablespace: -- CREATE TABLE test1122 ( name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text, enumvals text[], boot_val text, reset_val text, sourcefile text, sourceline integer ) WITH (orientation=row, compression=no); ALTER TABLE public.test1122 OWNER TO roger; -- -- Name: test1123; Type: TABLE; Schema: public; Owner: omm; Tablespace: -- CREATE TABLE test1123 ( name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, [omm@mogdb ~]$ [omm@mogdb ~]$ [omm@mogdb ~]$ ++++ 自定义备份文件的格式 [omm@mogdb ~]$ [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_backup.tar -F t gs_dump[port='26000'][enmotech][2021-11-24 00:26:16]: The total objects number is 390. gs_dump[port='26000'][enmotech][2021-11-24 00:26:16]: [100.00%] 390 objects have been dumped. gs_dump[port='26000'][enmotech][2021-11-24 00:26:18]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-11-24 00:26:18]: total time: 2200 ms [omm@mogdb ~]$ ls -ltr roger* -rw-------. 1 omm dbgrp 143488832 Nov 24 00:23 roger.txt -rw-------. 1 omm dbgrp 143496704 Nov 24 00:26 roger_backup.tar ++++ 备份单表 [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger.txt -o -t test1123 gs_dump[port='26000'][enmotech][2021-11-24 00:35:08]: The total objects number is 379. gs_dump[port='26000'][enmotech][2021-11-24 00:35:08]: [100.00%] 379 objects have been dumped. gs_dump[port='26000'][enmotech][2021-11-24 00:35:09]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-11-24 00:35:09]: total time: 1237 ms [omm@mogdb ~]$ |
这里我们简单模拟一下同gs_dump备份的文件来进行恢复，恢复误删除的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 |
[omm@mogdb ~]$ 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=> enmotech=> drop table test1123; DROP TABLE enmotech=> \q [omm@mogdb ~]$ [omm@mogdb ~]$ gsql -p 26000 enmotech -r -f roger.txt SET SET SET SET SET SET SET SET SET CREATE TABLE ALTER TABLE total time: 37116 ms [omm@mogdb ~]$ [omm@mogdb ~]$ 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=> select count(1) from test1123; count -------- 615424 (1 row) enmotech=> |
还可以将备份文件中的copy命令转成insert。不过insert 方式恢复的话，性能要比copy差很多，不推荐使用：
|
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 |
[omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_test1123.sql -o -t test1123 -x --inserts options --inserts/--column-inserts and -o/--oids cannot be used together (The INSERT command cannot set OIDs.) [omm@mogdb ~]$ [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_test1123.sql -t test1123 -x --inserts gs_dump[port='26000'][enmotech][2021-11-24 00:46:15]: The total objects number is 379. gs_dump[port='26000'][enmotech][2021-11-24 00:46:15]: [100.00%] 379 objects have been dumped. gs_dump[port='26000'][enmotech][2021-11-24 00:46:20]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-11-24 00:46:20]: total time: 4762 ms [omm@mogdb ~]$ [omm@mogdb ~]$ more roger_test1123.sql -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET xmloption = content; SET client_encoding = 'SQL_ASCII'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = public; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: test1123; Type: TABLE; Schema: public; Owner: omm; Tablespace: -- CREATE TABLE test1123 ( name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text, enumvals text[], boot_val text, reset_val text, sourcefile text, sourceline integer ) WITH (orientation=row, compression=no); ALTER TABLE public.test1123 OWNER TO omm; -- -- Data for Name: test1123; Type: TABLE DATA; Schema: public; Owner: omm -- INSERT INTO test1123 VALUES ('acce_min_datasize_per_thread', '500000', 'kB', 'Ungrouped', 'Used to estimate whether pushdown the plan to the compute pool.', '0 means that plan always runs in the compute pool.', 'user', 'integer', 'default', '0', '2147483647', NULL, '500000', '500000', NULL, NULL); INSERT INTO test1123 VALUES ('acceleration_with_compute_pool', 'off', NULL, 'Query Tuning / Planner Method Configuration', 'If true, agg/scan may run in compute pool.', NULL, 'user', 'bool', 'default', NULL, NUL L, NULL, 'off', 'off', NULL, NULL); INSERT INTO test1123 VALUES ('advance_xlog_file_num', '0', NULL, 'Write-Ahead Log / Settings', 'Sets the number of xlog files to be initialized in advance.', NULL, 'postmaster', 'integer', 'default', '0', '100', NULL, '0', '0', NULL, NULL); ...... |
除此之外gs_dump还支持堆备份进行加密，而且支持多种加密算法，以保证备份安全。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
++++ 加密备份 [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_enmotech_metaonly.sql -s --with-encryption aes128 --with-key enmotech@2021 aes128 is not supported,only AES128 is available [omm@mogdb ~]$ [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_enmotech_metaonly.sql -s --with-encryption AES128 --with-key enmotech@2021 The key is illegal,the length must be 16 [omm@mogdb ~]$ [omm@mogdb ~]$ gs_dump -p 26000 -U roger -W roger@007 enmotech -f roger_enmotech_metaonly.sql -s --with-encryption AES128 --with-key enmotech20211125 gs_dump[port='26000'][enmotech][2021-11-24 00:58:05]: The total objects number is 388. gs_dump[port='26000'][enmotech][2021-11-24 00:58:05]: [100.00%] 388 objects have been dumped. gs_dump[port='26000'][enmotech][2021-11-24 00:58:06]: dump database enmotech successfully gs_dump[port='26000'][enmotech][2021-11-24 00:58:06]: total time: 458 ms [omm@mogdb ~]$ |
对于加密备份的文件，是无法直接查看的，看到的都是乱码：
|
1 2 3 4 5 6 7 |
[omm@mogdb ~]$ more roger_enmotech_metaonly.sql l 64 ȘOqdA^d64ore--(14%) [-++@+-gdb ~]$ [-++@+-gdb ~]$ ^C [-++@+-gdb ~]$ |
由于时加密备份，所以无比要记住key，否则是无法解密恢复的。
总的来讲gs_dump 使用简单而且较为灵活；最后简单总结一下gs_dump的主要功能点：
1、支持数据库、schema、表级别备份
2、支持只备份元数据和部分元数据
3、支持备份压缩和加密
4、支持include和exclude等用法，类似Oracle expdp。
5、对于备份文件格式可以自定义，使用较为灵活。
Leave a Reply
You must be logged in to post a comment.