MogDB gs_dump使用

由迪 2024-02-27

原作者：计议

主机	IP	角色
mogdb01	192.168.10.13	主
mogdb02	192.168.10.14	备

1 创建测试表

解释
[omm@mogdb02 ~]$ gs_om -t status --detail
[   Cluster State   ]

cluster_state   : Normal
redistributing  : No
current_az      : AZ_ALL

[  Datanode State   ]

    node   node_ip         port      instance                state
----------------------------------------------------------------------------------
1  mogdb01 192.168.10.13   26000      6001 /opt/mogdb/data   P Primary Normal
2  mogdb02 192.168.10.14   26000      6002 /opt/mogdb/data   S Standby Normal

创建测试数据库和用户

解释create user mog03 with password 'enmo@123';   
create database mogtest3 owner mog03;
GRANT ALL PRIVILEGES ON DATABASE mogtest3 to mog03;
ALTER ROLE mog03 CREATEDB;
GRANT ALL PRIVILEGES TO mog03;


[omm@mogdb01 ~]$ gsql -d mogtest3 -h 192.168.10.13 -U mog03 -p 26000 -W enmo@123
gsql ((MogDB 3.0.2 build 9bc79be5) compiled at 2022-09-18 00:38:16 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

主库

解释mogtest3=> create table dump_tables as select * from pg_catalog.pg_tables;
INSERT 0 126
mogtest3=> create table dump_tables_1 as select * from pg_catalog.pg_tables;
INSERT 0 127
mogtest3=> select count(*) from dump_tables;
 count 
-------
   126
(1 row)

从库查看同步情况

解释[omm@mogdb02 ~]$ gsql -d mogtest3 -h 192.168.10.13 -U mog03 -p 26000 -W enmo@123
gsql ((MogDB 3.0.2 build 9bc79be5) compiled at 2022-09-18 00:38:16 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
mogtest3=> select count(*) from dump_tables;
 count 
-------
   126
(1 row)

2 数据导出命令gs_dump

2.1 文本文件格式

通过指定-F参数 p表示导出文本文件格式，默认也是这种格式

2.1.1 导出库中的所有信息包括表结构和数据

解释[omm@mogdb01 data]$ gs_dump -p 26000 mogtest3 -f dump1.sql
gs_dump[port='26000'][mogtest3][2022-10-23 20:27:58]: The total objects number is 413.
gs_dump[port='26000'][mogtest3][2022-10-23 20:27:58]: [100.00%] 413 objects have been dumped.
gs_dump[port='26000'][mogtest3][2022-10-23 20:27:58]: dump database mogtest3 successfully
gs_dump[port='26000'][mogtest3][2022-10-23 20:27:58]: total time: 1898  ms

文件中表结构如下

解释[omm@mogdb01 data]$ ls -lrt dump1.sql 
-rw------- 1 omm omm 14877 Oct 23 20:27 dump1.sql

[omm@mogdb01 data]$ more dump1.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 client_min_messages = warning;

SET search_path = public;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dump_tables; Type: TABLE; Schema: public; Owner: mog03; Tablespace: 
--

CREATE TABLE dump_tables (
    schemaname name,
    tablename name,
    tableowner name,
    tablespace name,
    hasindexes boolean,
    hasrules boolean,
    hastriggers boolean,
    tablecreator name,
    created timestamp with time zone,
    last_ddl_time timestamp with time zone
)
WITH (orientation=row, compression=no);


ALTER TABLE public.dump_tables OWNER TO mog03;

--
-- Name: dump_tables_1; Type: TABLE; Schema: public; Owner: mog03; Tablespace: 
--

CREATE TABLE dump_tables_1 (
    schemaname name,
    tablename name,
    tableowner name,
    tablespace name,
    hasindexes boolean,
    hasrules boolean,
    hastriggers boolean,
    tablecreator name,
    created timestamp with time zone,
    last_ddl_time timestamp with time zone
)
WITH (orientation=row, compression=no);


ALTER TABLE public.dump_tables_1 OWNER TO mog03;
...
...

数据格式如下

解释--
-- Data for Name: dump_tables; Type: TABLE DATA; Schema: public; Owner: mog03
--

COPY dump_tables (schemaname, tablename, tableowner, tablespace, hasindexes, hasrules, hastriggers, tablecreator, created, last_ddl_time) FROM 
stdin;
pg_catalog      pg_type omm     \N      t       f       f       \N      \N      \N
public  dump_tables     mog03   \N      f       f       f       mog03   2022-10-23 20:24:49.271124+08   2022-10-23 20:24:49.271124+08
pg_catalog      gs_client_global_keys_args      omm     \N      t       f       f       \N      \N      \N
pg_catalog      pg_authid       omm     pg_global       t       f       f       \N      \N      \N
pg_catalog      pg_statistic    omm     \N      t       f       f       \N      \N      \N
pg_catalog      pg_statistic_ext        omm     \N      t       f       f       \N      \N      \N
pg_catalog      gs_wlm_instance_history omm     \N      f       f       f       \N      \N      \N
pg_catalog      gs_wlm_session_query_info_all   omm     \N      f       f       f       \N      \N      \N
pg_catalog      gs_wlm_user_resource_history    omm     \N      f       f       f       \N      \N      \N
pg_catalog      pg_user_mapping omm     \N      t       f       f       \N      \N      \N
pg_catalog      gs_wlm_operator_info    omm     \N      f       f       f       \N      \N      \N
...
...

通过文件格式查看，这种文件形式是无法给其他异构数据库直接使用。

2.1.2 导出库中的所有表结构

解释[omm@mogdb01 data]$ gs_dump -p 26000 mogtest3 --schema-only -f dump2.sql
gs_dump[port='26000'][mogtest3][2022-10-23 20:29:56]: The total objects number is 411.
gs_dump[port='26000'][mogtest3][2022-10-23 20:29:56]: [100.00%] 411 objects have been dumped.
gs_dump[port='26000'][mogtest3][2022-10-23 20:29:56]: dump database mogtest3 successfully
gs_dump[port='26000'][mogtest3][2022-10-23 20:29:56]: total time: 1957  ms

2.1.3 导出库中的所有表数据

解释[omm@mogdb01 data]$ gs_dump -p 26000 mogtest3 -a -f dump3.sql
gs_dump[port='26000'][mogtest3][2022-10-23 20:31:16]: dump database mogtest3 successfully
gs_dump[port='26000'][mogtest3][2022-10-23 20:31:16]: total time: 1834  ms

[omm@mogdb01 data]$ more dump3.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 client_min_messages = warning;

SET search_path = public;

--
-- Data for Name: dump_tables; Type: TABLE DATA; Schema: public; Owner: mog03
--

COPY dump_tables (schemaname, tablename, tableowner, tablespace, hasindexes, hasrules, hastriggers, tablecreator, created, last_ddl_time) FROM 
stdin;
pg_catalog      pg_type omm     \N      t       f       f       \N      \N      \N
public  dump_tables     mog03   \N      f       f       f       mog03   2022-10-23 20:24:49.271124+08   2022-10-23 20:24:49.271124+08
pg_catalog      gs_client_global_keys_args      omm     \N      t       f       f       \N      \N      \N
pg_catalog      pg_authid       omm     pg_global       t       f       f       \N      \N      \N
pg_catalog      pg_statistic    omm     \N      t       f       f       \N      \N      \N
pg_catalog      pg_statistic_ext        omm     \N      t       f       f       \N      \N      \N
....
....

2.1.4 导出表

[omm@mogdb01 data]$ gs_dump -p 26000 mogtest3 -U mog03 -W enmo@123 --table=dump_tables -f dump4.sql

2.1.5 导出表数据为sql语句格式

以上几种导出方式都是按照cope的方式导出，也可以指定导出insert格式的结构，这样如果数据量小的话可以在其他异构数据库中执行。

解释[omm@mogdb01 data]$ gs_dump -p 26000 mogtest3 -U mog03 -W enmo@123 --table=dump_tables -f dump5.sql --insert
[omm@mogdb01 data]$ more dump5.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 client_min_messages = warning;

SET search_path = public;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dump_tables; Type: TABLE; Schema: public; Owner: mog03; Tablespace: 
--

CREATE TABLE dump_tables (
    schemaname name,
    tablename name,
    tableowner name,
    tablespace name,
    hasindexes boolean,
    hasrules boolean,
    hastriggers boolean,
    tablecreator name,
    created timestamp with time zone,
    last_ddl_time timestamp with time zone
)
WITH (orientation=row, compression=no);


ALTER TABLE public.dump_tables OWNER TO mog03;

--
-- Data for Name: dump_tables; Type: TABLE DATA; Schema: public; Owner: mog03
--

INSERT INTO dump_tables VALUES ('pg_catalog', 'pg_type', 'omm', NULL, true, false, false, NULL, NULL, NULL);
INSERT INTO dump_tables VALUES ('public', 'dump_tables', 'mog03', NULL, false, false, false, 'mog03', '2022-10-23 20:24:49.271124+08', '2022-10
-23 20:24:49.271124+08');
INSERT INTO dump_tables VALUES ('pg_catalog', 'gs_client_global_keys_args', 'omm', NULL, true, false, false, NULL, NULL, NULL);
INSERT INTO dump_tables VALUES ('pg_catalog', 'pg_authid', 'omm', 'pg_global', true, false, false, NULL, NULL, NULL);
INSERT INTO dump_tables VALUES ('pg_catalog', 'pg_statistic', 'omm', NULL, true, false, false, NULL, NULL, NULL);
INSERT INTO dump_tables VALUES ('pg_catalog', 'pg_statistic_ext', 'omm', NULL, true, false, false, NULL, NULL, NULL);
INSERT INTO dump_tables VALUES ('pg_catalog', 'gs_wlm_instance_history', 'omm', NULL, false, false, false, NULL, NULL, NULL);
INSERT INTO dump_tables VALUES ('pg_catalog', 'gs_wlm_session_query_info_all', 'omm', NULL, false, false, false, NULL, NULL, NULL);
.....
....

2.2 自定义归档模式

定c参数导出自定义归档模式，支持从导出文件中恢复所有或所选数据库对象，使用gs_restore可以选择要从自定义归档导出文件中导入相应的数据库对象，这种格式是二进制格式。

2.2.1 导出整个数据库

解释[omm@mogdb01 datadump]$ gs_dump -h 192.168.10.13 -p 26000 -U mog03 -W enmo@123 mogtest3 -F c -f /home/omm/datadump/dumpc1
gs_dump[port='26000'][mogtest3][2022-10-23 21:54:07]: The total objects number is 413.
gs_dump[port='26000'][mogtest3][2022-10-23 21:54:07]: [100.00%] 413 objects have been dumped.
gs_dump[port='26000'][mogtest3][2022-10-23 21:54:07]: dump database mogtest3 successfully
gs_dump[port='26000'][mogtest3][2022-10-23 21:54:07]: total time: 2493  ms

[omm@mogdb01 datadump]$  ls -lrt
total 8
-rw------- 1 omm omm 5027 Oct 23 21:54 dumpc1

2.2.2 导出某个表

解释[omm@mogdb01 datadump]$ gs_dump -h 192.168.10.13 -p 26000 -U mog03 -W enmo@123 mogtest3 -F c -f /home/omm/datadump/dumpc2 --table=dump_tables
PGDMP^L
    schemaname name,
    tablename name,
    tableowner name,
    tablespace name,
    hasindexes boolean,
    hasrules boolean,
    hastriggers boolean,
    tablecreator name,
    created timestamp with time zone,
    last_ddl_time timestamp with time zone
)
WITH (orientation=row, compression=no);

time) FROM stdin;
,xō=όjS:+cX|Dv|LpCCr*!nڀe)x:L#Lhˈ._xD,dhQɀcC&]f[7m--More--(83%)

2.2.3 只导出某个表数据

[omm@mogdb01 datadump]$ gs_dump -h 192.168.10.13 -p 26000 -U mog03 -W enmo@123 mogtest3 -a -F c -f /home/omm/datadump/dumpc3 --table=dump_tables

2.3 目录归档格式

通过-F参数指定d选项，此选项会创建一个目录.

解释[omm@mogdb01 ~]$ gs_dump -h 192.168.10.13 -p 26000 -U mog03 -W enmo@123 mogtest3 -F d -f /home/omm/dumpfile
gs_dump[port='26000'][mogtest3][2022-10-23 22:00:11]: The total objects number is 413.
gs_dump[port='26000'][mogtest3][2022-10-23 22:00:11]: [100.00%] 413 objects have been dumped.
gs_dump[port='26000'][mogtest3][2022-10-23 22:00:11]: dump database mogtest3 successfully
gs_dump[port='26000'][mogtest3][2022-10-23 22:00:11]: total time: 2372  ms
[omm@mogdb01 ~]$ ls -lrt
total 0
drwx------ 2 omm omm 48 Oct 23 21:58 datadump
drwx------ 2 omm omm 75 Oct 23 22:00 dumpfile
[omm@mogdb01 ~]$ cd dumpfile/
[omm@mogdb01 dumpfile]$ ls -lrt
total 12
-rw------- 1 omm omm    0 Oct 23 22:00 dir.lock
-rw------- 1 omm omm 2824 Oct 23 22:00 toc.dat
-rw------- 1 omm omm 1218 Oct 23 22:00 4746.dat.gz
-rw------- 1 omm omm 1240 Oct 23 22:00 4747.dat.gz

2.4 tar归档模式

通过-F参数指定t选项，tar归档文件支持从导出文件中恢复所有或所选数据库对象。tar归档格式不支持压缩且对于单独表大小应小于8GB。

解释[omm@mogdb01 dumpfile]$ gs_dump -h 192.168.10.13 -p 26000 -U mog03 -W enmo@123 mogtest3 -F t -f /home/omm/datat.tar
[omm@mogdb01 ~]$ ls -lrt
total 24
drwx------ 2 omm omm    48 Oct 23 21:58 datadump
drwx------ 2 omm omm    75 Oct 23 22:00 dumpfile
-rw------- 1 omm omm 22528 Oct 23 22:02 datat.tar

加压生成的datat.tar

解释[omm@mogdb01 ~]$ tar -xvf datat.tar
toc.dat
4746.dat
4747.dat
restore.sql

-rw------- 1 omm omm  2865 Oct 23 22:02 restore.sql
-rw------- 1 omm omm  6498 Oct 23 22:02 4747.dat
-rw------- 1 omm omm  6396 Oct 23 22:02 4746.dat
-rw------- 1 omm omm 22528 Oct 23 22:02 datat.tar

其中restore.sql是ddl语句，其余的是每个表对应一个二进制dat文件。

mogdb

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Mogdb 学习记录（共582篇）

Mogdb 学习指南