gs_dump备份工具

由迪 2024-04-01

786

原作者：宗可阳

背景信息

gs_dump是MogDB用于导出数据库相关信息的工具，用户可以自定义导出一个数据库或其中的对象（模式、表、视图等），回收站对象除外。支持导出的数据库可以是默认数据库postgres，也可以是自定义数据库。

gs_dump工具由安装MogDB数据库的操作系统用户执行。

gs_dump工具在进行数据导出时，其他用户可以访问MogDB数据库（读或写）。

gs_dump工具支持导出完整一致的数据。例如，T1时刻启动gs_dump导出A数据库，那么导出数据结果将会是T1时刻A数据库的数据状态，T1时刻之后对A数据库的修改不会被导出。

gs_dump工具在进行数据导出时生成的列不会被转储。

gs_dump支持导出兼容v1版本数据库的文本格式文件。

gs_dump支持将数据库信息导出至纯文本格式的SQL脚本文件或其他归档文件中。

导出文件格式

格式名称	-F的参数值	说明	建议	对应导入工具
纯文本格式	p	纯文本脚本文件包含SQL语句和命令。命令可以由gsql命令行终端程序执行，用于重新创建数据库对象并加载表数据。	小型数据库，一般推荐纯文本格式。	使用gsql工具恢复数据库对象前，可根据需要使用文本编辑器编辑纯文本导出文件。
自定义归档格式	c	一种二进制文件。支持从导出文件中恢复所有或所选数据库对象。	中型或大型数据库，推荐自定义归档格式。	使用gs_restore可以选择要从自定义归档/目录归档/tar归档导出文件中导入相应的数据库对象。
目录归档格式	d	该格式会创建一个目录，该目录包含两类文件，一类是目录文件，另一类是每个表和blob对象对应的数据文件。	-	使用gs_restore可以选择要从自定义归档/目录归档/tar归档导出文件中导入相应的数据库对象。
tar归档格式	t	tar归档文件支持从导出文件中恢复所有或所选数据库对象。tar归档格式不支持压缩且对于单独表大小应小于8GB。	-	使用gs_restore可以选择要从自定义归档/目录归档/tar归档导出文件中导入相应的数据库对象。

语法和参数详解

[omm@node1 ~]$ 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
  -g, --exclude-guc=GUC_PARAM                 do NOT dump the GUC_PARAM set
  -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
  -q, --target=VERSION                        dump data format can compatible Gaussdb version (v1 or ..)
  -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)
  --pipeline                                  use pipeline to pass the password,
                                              forbidden to use in terminal
  -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)
  --exclude-with                              do NOT dump WITH() of table(s)
  --inserts                                   dump data as INSERT commands, rather than COPY
  --no-publications                           do not dump publications
  --no-security-labels                        do not dump security label assignments
  --no-subscriptions                          do not dump subscriptions
  --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
  --exclude-function                          do not dump function and procedure
  --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.

测试用例

指定数据库导出
文本格式
[omm@node1 ~]$ gs_dump -f backup.sql postgres -F p
gs_dump[port='26000'][postgres][2023-12-08 15:34:16]: The total objects number is 429.
gs_dump[port='26000'][postgres][2023-12-08 15:34:16]: [100.00%] 429 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-08 15:34:16]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-08 15:34:16]: total time: 4915  ms
[omm@node1 ~]$ cat backup.sql
--
-- openGauss database dump
--

SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET session_replication_role = replica;
SET client_min_messages = warning;

--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: omm
--

COMMENT ON DATABASE postgres IS 'default administrative connection database';


--
-- Name: zky; Type: SCHEMA; Schema: -; Owner: zky
--

CREATE SCHEMA zky;


ALTER SCHEMA zky OWNER TO zky;

SET search_path = zky;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test1; Type: TABLE; Schema: zky; Owner: zky; Tablespace:
--

CREATE TABLE test1 (
    id integer
)
WITH (orientation=row, compression=no);


ALTER TABLE zky.test1 OWNER TO zky;

--
-- Data for Name: test1; Type: TABLE DATA; Schema: zky; Owner: zky
--

COPY test1 (id) FROM stdin;
1
1
1
\.
;

--
-- Name: public; Type: ACL; Schema: -; Owner: omm
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM omm;
GRANT CREATE,USAGE ON SCHEMA public TO omm;
GRANT USAGE ON SCHEMA public TO PUBLIC;


--
-- openGauss database dump complete
--

目录格式
[omm@node1 ~]$ gs_dump -f backup postgres -F d
gs_dump[port='26000'][postgres][2023-12-08 15:44:51]: The total objects number is 429.
gs_dump[port='26000'][postgres][2023-12-08 15:44:51]: [100.00%] 429 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-08 15:44:51]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-08 15:44:51]: total time: 5023  ms
[omm@node1 ~]$ cd backup
[omm@node1 backup]$ ls
5099.dat.gz  dir.lock  toc.dat
[omm@node1 backup]$

自定义归档格式
[omm@node1 backup]$ gs_dump -f backup.dmp postgres -F c
gs_dump[port='26000'][postgres][2023-12-08 15:45:43]: The total objects number is 429.
gs_dump[port='26000'][postgres][2023-12-08 15:45:43]: [100.00%] 429 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-08 15:45:43]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-08 15:45:43]: total time: 5046  ms
[omm@node1 backup]$ ls backup.dmp
backup.dmp

tar格式
[omm@node1 backup]$ gs_dump -f backup.tar  postgres -F t
gs_dump[port='26000'][postgres][2023-12-08 15:46:11]: The total objects number is 429.
gs_dump[port='26000'][postgres][2023-12-08 15:46:11]: [100.00%] 429 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-08 15:46:11]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-08 15:46:11]: total time: 4895  ms
[omm@node1 backup]$ ls backup.tar
backup.tar

指定schema导出
文本格式

[omm@node1 backup]$ gs_dump -f /home/omm/zky.sql -n zky -F p
gs_dump[port='26000'][postgres][2023-12-08 15:54:47]: The total objects number is 422.
gs_dump[port='26000'][postgres][2023-12-08 15:54:47]: [100.00%] 422 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-08 15:54:47]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-08 15:54:47]: total time: 4898  ms
[omm@node1 ~]$ cat zky.sql
--
-- openGauss database dump
--

SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET session_replication_role = replica;
SET client_min_messages = warning;

--
-- Name: zky; Type: SCHEMA; Schema: -; Owner: zky
--

CREATE SCHEMA zky;


ALTER SCHEMA zky OWNER TO zky;

SET search_path = zky;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test1; Type: TABLE; Schema: zky; Owner: zky; Tablespace:
--

CREATE TABLE test1 (
    id integer
)
WITH (orientation=row, compression=no);


ALTER TABLE zky.test1 OWNER TO zky;

--
-- Data for Name: test1; Type: TABLE DATA; Schema: zky; Owner: zky
--

COPY test1 (id) FROM stdin;
1
1
1
\.
;

--
-- openGauss database dump complete
--

目录格式
[omm@node1 ~]$ gs_dump -f /home/omm/zky -n zky -F d
gs_dump[port='26000'][postgres][2023-12-08 16:02:05]: The total objects number is 422.
gs_dump[port='26000'][postgres][2023-12-08 16:02:05]: [100.00%] 422 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-08 16:02:05]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-08 16:02:05]: total time: 4647  ms
[omm@node1 ~]$ cd zky
[omm@node1 zky]$ ls -lrt
total 8
-rw-------. 1 omm omm   0 Dec  8 16:02 dir.lock
-rw-------. 1 omm omm 907 Dec  8 16:02 toc.dat
-rw-------. 1 omm omm  30 Dec  8 16:02 5099.dat.gz

自定义格式
[omm@node1 ~]$ gs_dump -f /home/omm/zky.dmp -n zky -F c
gs_dump[port='26000'][postgres][2023-12-12 16:51:42]: The total objects number is 422.
gs_dump[port='26000'][postgres][2023-12-12 16:51:42]: [100.00%] 422 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-12 16:51:42]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-12 16:51:42]: total time: 9436  ms
[omm@node1 ~]$ ls -l zky.dmp
-rw-------. 1 omm omm 950 Dec 12 16:51 zky.dmp

tar归档格式
mm@node1 ~]$ gs_dump -f /home/omm/zky.tar -n zky -F t
gs_dump[port='26000'][postgres][2023-12-12 16:53:04]: The total objects number is 422.
gs_dump[port='26000'][postgres][2023-12-12 16:53:04]: [100.00%] 422 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-12 16:53:04]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-12 16:53:04]: total time: 9366  ms
[omm@node1 ~]$ ls -l zky.tar
-rw-------. 1 omm omm 5632 Dec 12 16:53 zky.tar


指定单表导出
文本格式
[omm@node1 ~]$ gs_dump -f /home/omm/test1.sql -t zky.test1 -F p
gs_dump[port='26000'][postgres][2023-12-12 16:59:42]: The total objects number is 421.
gs_dump[port='26000'][postgres][2023-12-12 16:59:42]: [100.00%] 421 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-12 16:59:42]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-12 16:59:42]: total time: 8169  ms

[omm@node1 ~]$ cat test1.sql
--
-- openGauss database dump
--

SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET session_replication_role = replica;
SET client_min_messages = warning;

SET search_path = zky;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test1; Type: TABLE; Schema: zky; Owner: zky; Tablespace:
--

CREATE TABLE test1 (
    id integer
)
WITH (orientation=row, compression=no);


ALTER TABLE zky.test1 OWNER TO zky;

--
-- Data for Name: test1; Type: TABLE DATA; Schema: zky; Owner: zky
--

COPY test1 (id) FROM stdin;
1
1
1
\.
;

--
-- openGauss database dump complete
--

目录格式
[omm@node1 ~]$ gs_dump -f /home/omm/test1 -t zky.test1 -F d
gs_dump[port='26000'][postgres][2023-12-12 17:00:44]: The total objects number is 421.
gs_dump[port='26000'][postgres][2023-12-12 17:00:44]: [100.00%] 421 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-12 17:00:44]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-12 17:00:44]: total time: 8182  ms
[omm@node1 ~]$ ls -lrt /home/omm/test1
total 8
-rw-------. 1 omm omm   0 Dec 12 17:00 dir.lock
-rw-------. 1 omm omm 747 Dec 12 17:00 toc.dat
-rw-------. 1 omm omm  30 Dec 12 17:00 4176.dat.gz

自定义格式
[omm@node1 ~]$ gs_dump -f /home/omm/test1.dmp -t zky.test1 -F c
gs_dump[port='26000'][postgres][2023-12-12 17:01:24]: The total objects number is 421.
gs_dump[port='26000'][postgres][2023-12-12 17:01:24]: [100.00%] 421 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-12 17:01:24]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-12 17:01:24]: total time: 8182  ms
[omm@node1 ~]$ ls -lrt /home/omm/test1.dmp
-rw-------. 1 omm omm 786 Dec 12 17:01 /home/omm/test1.dmp

tar归档格式
[omm@node1 ~]$ gs_dump -f /home/omm/test1.tar -t zky.test1 -F t
gs_dump[port='26000'][postgres][2023-12-12 17:02:11]: The total objects number is 421.
gs_dump[port='26000'][postgres][2023-12-12 17:02:11]: [100.00%] 421 objects have been dumped.
gs_dump[port='26000'][postgres][2023-12-12 17:02:11]: dump database postgres successfully
gs_dump[port='26000'][postgres][2023-12-12 17:02:11]: total time: 8198  ms
[omm@node1 ~]$ ls -lrt /home/omm/test1.tar
-rw-------. 1 omm omm 5120 Dec 12 17:02 /home/omm/test1.tar