MogDB 学习笔记之 -- gs_dump 备份工具

对于数据库的备份恢复，MogDB都提供了丰富的工具，这里一起来学习一下逻辑备份工具gs_dump.

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、备份元数据表结构等

[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 ~]$ 

2、备份整个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 ~]$ 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

3、备份单表

[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的备份进行恢复。

[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 ~]$

接下来通过gsql 进行恢复：

[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差很多，不推荐使用：

[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);
......

除此之外MogDB 的gs_dump 命令也支持堆备份进行加密，支持多种加密算法：

[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 ~]$ 

对于加密备份的文件，是无法直接查看的，看到的都是乱码：

[omm@mogdb ~]$ more roger_enmotech_metaonly.sql 
l
64
ȘOqdA^d64ore--(14%)
[-++@+-gdb ~]$ 
[-++@+-gdb ~]$ ^C
[-++@+-gdb ~]$ 

由于时加密备份，所以务必要记住key，否则是无法解密恢复的。

总的来讲gs_dump 使用简单而且较为灵活。 下一篇我们将来接续介绍MogDB 的物理备份和恢复工具。