openGauss每日一练第 14 天 | 数据导出操作及课后作业

openGauss 每日一练第 14 天学习打卡，巩固 openGauss 数据库数据导出基本操作！

学习目标

学习 openGauss 数据导出

前面每日一练链接：

openGauss每日一练第 1 天 | 数据库和表的基本操作（一）
openGauss每日一练第 2 天 | 数据库和表的基本操作（二）
openGauss每日一练第 3 天 | 前三课作业实操练习
openGauss每日一练第 4 天 | 角色管理及课后作业
openGauss每日一练第 5 天 | 用户管理及课后作业
openGauss每日一练第 6 天 | 模式管理及课后作业
openGauss每日一练第 7 天 | 表空间管理及课后作业
openGauss每日一练第 8 天 | 分区表管理及课后作业
openGauss每日一练第 9 天 | 普通表索引管理及课后作业
openGauss每日一练第 10 天 | 分区表索引管理及课后作业
openGauss每日一练第 11 天 | 视图管理及课后作业
openGauss每日一练第 12 天 | 自定义数据类型管理及课后作业
openGauss每日一练第 13 天 | 数据导入操作及课后作业

课程学习

使用 gs_dump 和 gs_dumpall 命令导出数据！openGauss 提供的 gs_dump 和 gs_dumpall 工具，能够帮助用户导出需要的数据库对象或其相关信息。通过导入工具将导出的数据信息导入至需要的数据库，可以完成数据库信息的迁移。gs_dump 支持导出单个数据库或其内的对象，而 gs_dumpall 支持导出 openGauss 中所有数据库或各库的公共全局对象。

gs_dump和gs_dumpall通过-U指定执行导出的用户帐户。如果当前使用的帐户不具备导出所要求的权限时，会无法导出数据。此时，可在导出命令中设置–role参数来指定具备权限的角色。在执行命令后，gs_dump和gs_dumpall会使用–role参数指定的角色，完成导出动作。可使用该功能的场景请参见表1，详细操作请参见无权限角色导出数据。

gs_dump和gs_dumpall通过对导出的数据文件加密，导入时对加密的数据文件进行解密，可以防止数据信息泄露，为数据库的安全提供保证。注意，使用gs_dump加密的纯文本格式文件，如果导出的数据库中包含存储过程，不支持使用gsql命令恢复文件，请使用另外三种模式导出数据库，并使用gs_restore恢复。

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

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

注意事项

禁止修改导出的文件和内容，否则可能无法恢复成功。

如果数据库中包含的对象数量（数据表、视图、索引）在50万以上，为了提高性能且避免出现内存问题，建议通过gs_guc工具设置数据库节点的如下参数（如果参数值大于如下建议值，则无需设置）。

gs_guc set -N all -I all -c 'max_prepared_transactions = 1000'
gs_guc set -N all -I all -c 'max_locks_per_transaction = 512'

为了保证数据一致性和完整性，导出工具会对需要转储的表设置共享锁。如果表在别的事务中设置了共享锁，gs_dump和gs_dumpall会等待锁释放后锁定表。如果无法在指定时间内锁定某个表，转储会失败。用户可以通过指定–lock-wait-timeout选项，自定义等待锁超时时间。

由于gs_dumpall读取所有数据库中的表，因此必须以openGauss管理员身份进行连接，才能导出完整文件。在使用gsql执行脚本文件导入时，同样需要管理员权限，以便添加用户和组，以及创建数据库。

参考文档：https://opengauss.org/zh/docs/2.1.0/docs/Toolreference/gs_dump.html

1.准备数据

–数据存放在backup数据库中

create database backup;
\c backup
CREATE SCHEMA ds;
create table ds.t1(id int, name char(30));
insert into ds.t1 values(1 ,'xxxx');
CREATE TABLE customer_t
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
INSERT INTO customer_t VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

\q

2.导出数据库

-f, –file=FILENAME

将输出发送至指定文件或目录。如果省略该参数，则使用标准输出。如果输出格式为（-F c/-F d/-F t）时，必须指定-f参数。如果-f的参数值含有目录，要求当前用户对该目录具有读写权限，并且不能指定已有目录。

-F, –format=c|d|t|p

选择输出格式。格式如下：

    p|plain：输出一个文本SQL脚本文件（默认）。
    c|custom：输出一个自定义格式的归档，并且以目录形式输出，作为gs_restore输入信息。该格式是最灵活的输出格式，因为能手动选择，而且能在恢复过程中将归档项重新排序。该格式默认状态下会被压缩。
    d|directory：该格式会创建一个目录，该目录包含两类文件，一类是目录文件，另一类是每个表和blob对象对应的数据文件。
    t|tar：输出一个tar格式的归档形式，作为gs_restore输入信息。tar格式与目录格式兼容；tar格式归档形式在提取过程中会生成一个有效的目录格式归档形式。但是，tar格式不支持压缩且对于单独表有8GB的大小限制。此外，表数据项的相应排序在恢复过程中不能更改。
    输出一个tar格式的归档形式，也可以作为gsql输入信息。

–以sql文本格式导出backup数据库全量信息

-f 将导出文件发送至指定目录文件夹。如果这里省略，则使用标准输出。
-F 选择导出文件格式。-F 参数值如下：

p：纯文本格式
c：自定义归档
d：目录归档格式
t：tar归档格式

gs_dump -f /home/omm/backup_database_all.sql backup -F p

omm@modb:~$ gs_dump -f /home/omm/backup_database_all.sql backup -F p
gs_dump[port='5432'][backup][2021-12-17 12:59:23]: The total objects number is 391.
gs_dump[port='5432'][backup][2021-12-17 12:59:23]: [100.00%] 391 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-17 12:59:23]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 12:59:23]: total time: 102  ms
omm@modb:~$ ls -l
total 4
-rw------- 1 omm omm 1703 Dec 17 12:59 backup_database_all.sql

–以 sql 文本格式导出 backup 数据库中的数据，不包含数据库对象定义

-a, –data-only

只输出数据，不输出模式（数据定义）。转储表数据、大对象和序列值。

-s, –schema-only

只转储对象定义（模式），而非数据。

gs_dump -f /home/omm/backup_database_data.sql backup -a -F p 

–以sql文本格式导出backup数据库中对象的定义

gs_dump -f /home/omm/backup_database_define.sql backup -s -F p

–查看导出的数据

more /home/omm/backup_database_all.sql

omm@modb:~$ more /home/omm/backup_database_all.sql
--
-- PostgreSQL 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;

--
-- Name: ds; Type: SCHEMA; Schema: -; Owner: omm
--

CREATE SCHEMA ds;


SET default_tablespace = '';
--More--(22%)ALTER SCHEMA ds OWNER TO omm;

SET search_path = ds;


SET default_with_oids = false;

--
-- Name: t1; Type: TABLE; Schema: ds; Owner: omm; Tablespace: 
--

CREATE TABLE t1 (
    id integer,
    name character(30)
)
WITH (orientation=row, compression=no);


ALTER TABLE ds.t1 OWNER TO omm;

SET search_path = public;

--
-- Name: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace: 
--

CREATE TABLE customer_t (
    c_customer_sk integer,
    c_customer_id character(5),
    c_first_name character(6),
    c_last_name character(8)
)
WITH (orientation=row, compression=no);


ALTER TABLE public.customer_t OWNER TO omm;

SET search_path = ds;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: ds; Owner: omm
--

COPY t1 (id, name) FROM stdin;
1       xxxx                          
\.
;

SET search_path = public;

--
-- Data for Name: customer_t; Type: TABLE DATA; Schema: public; Owner: omm
--

COPY customer_t (c_customer_sk, c_customer_id, c_first_name, c_last_name) FROM stdin;
6885    1       Joes    Hunter  
4321    2       Lily    Carter  
9527    3       James   Cook    
9500    4       Lucy    Baker   
\.
;

--
-- 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;


--
-- PostgreSQL database dump complete
--

3.导出模式

–以sql文本格式导出backup数据库中ds模式的全量信息

gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p

–以sql文本格式导出backup数据库中ds模式的数据

gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p

–以sql文本格式导出backup数据库中ds模式的定义

gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p

omm@modb:~$ gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
gs_dump[port='5432'][backup][2021-12-17 19:44:23]: The total objects number is 380.
gs_dump[port='5432'][backup][2021-12-17 19:44:23]: [100.00%] 380 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-17 19:44:23]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 19:44:23]: total time: 90  ms
omm@modb:~$ gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p
gs_dump[port='5432'][backup][2021-12-17 19:44:58]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 19:44:58]: total time: 78  ms
omm@modb:~$ gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p
gs_dump[port='5432'][backup][2021-12-17 19:45:13]: The total objects number is 379.
gs_dump[port='5432'][backup][2021-12-17 19:45:13]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-17 19:45:13]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 19:45:13]: total time: 186  ms
omm@modb:~$ 
omm@modb:~$ ls -l
total 24
-rw------- 1 omm omm  666 Dec 17 19:45 backup_schema_define.sql
-rw------- 1 omm omm 1703 Dec 17 12:59 backup_database_all.sql
-rw------- 1 omm omm  742 Dec 17 13:13 backup_database_data.sql
-rw------- 1 omm omm 1231 Dec 17 13:13 backup_database_define.sql
-rw------- 1 omm omm  806 Dec 17 19:44 backup_schema_all.sql
-rw------- 1 omm omm  433 Dec 17 19:44 backup_schema_data.sql

–查看导出的数据

more /home/omm/backup_schema_all.sql

omm@modb:~$ more backup_schema_define.sql
--
-- PostgreSQL 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;

--
-- Name: ds; Type: SCHEMA; Schema: -; Owner: omm
--

ALTER SCHEMA ds OWNER TO omm;

SET search_path = ds;


CREATE SCHEMA ds;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: t1; Type: TABLE; Schema: ds; Owner: omm; Tablespace: 
--

CREATE TABLE t1 (
    id integer,
    name character(30)
)
WITH (orientation=row, compression=no);


ALTER TABLE ds.t1 OWNER TO omm;

--
-- PostgreSQL database dump complete
--

4.导出表

-t, –table=TABLE

指定转储的表（或视图、或序列、或外表）对象列表，可以使用多个-t选项来选择多个表，也可以使用通配符指定多个表对象。

当使用通配符指定多个表对象时，注意给 pattern 打引号，防止 shell 扩展通配符。

当使用 -t 时，-n 和 -N 没有任何效应，这是因为由 -t 选择的表的转储不受那些选项的影响。

说明：

-t参数选项个数必须小于等于100。

如果-t参数选项个数大于100，建议使用参数–include-table-file来替换。

当-t已指定时，gs_dump不会转储已选表所附着的任何其他数据库对象。因此，无法保证某个指定表的转储结果能够自行成功地储存到一个空数据库中。

-t tablename只转储在默认搜索路径中可见的表。-t '*.tablename'转储数据库下所有模式下的tablename表。-t schema.table转储特定模式中的表。

-t tablename不会导出表上的触发器信息。

-T, –exclude-table=TABLE

不转储的表（或视图、或序列、或外表）对象列表，可以使用多个-T选项来选择多个表，也可以使用通配符指定多个表对象。

当同时输入 -t和-T 时，会转储在 -t 列表中，而不在 -T 列表中的表对象。

–以 sql 文本格式导出 backup 数据库中表 customer_t 的全量信息

gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p

–以sql文本格式导出backup数据库中表customer_t的数据

gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p 

–以sql文本格式导出backup数据库中表customer_t的定义

gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p 

–查看导出的数据

more /home/omm/backup_table_all.sql

omm@modb:~$ gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p
gs_dump[port='5432'][backup][2021-12-17 19:58:17]: The total objects number is 379.
gs_dump[port='5432'][backup][2021-12-17 19:58:17]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-17 19:58:17]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 19:58:17]: total time: 72  ms
omm@modb:~$ gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p 
gs_dump[port='5432'][backup][2021-12-17 19:58:38]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 19:58:38]: total time: 65  ms
omm@modb:~$ gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p 
gs_dump[port='5432'][backup][2021-12-17 19:58:56]: The total objects number is 378.
gs_dump[port='5432'][backup][2021-12-17 19:58:56]: [100.00%] 378 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-17 19:58:56]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-17 19:58:56]: total time: 169  ms
omm@modb:~$ 
omm@modb:~$ more /home/omm/backup_table_all.sql
--
-- PostgreSQL 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: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace: 
--

CREATE TABLE customer_t (
    c_customer_sk integer,
    c_customer_id character(5),
    c_first_name character(6),
    c_last_name character(8)
)
WITH (orientation=row, compression=no);


ALTER TABLE public.customer_t OWNER TO omm;

--
-- Data for Name: customer_t; Type: TABLE DATA; Schema: public; Owner: omm
--

COPY customer_t (c_customer_sk, c_customer_id, c_first_name, c_last_name) FROM stdin;
6885    1       Joes    Hunter  
4321    2       Lily    Carter  
9527    3       James   Cook    
9500    4       Lucy    Baker   
\.
;

--
-- PostgreSQL database dump complete
--

课程作业

1.创建数据库 tpcc，在数据库 tpcc 中创建模式 schema1，在模式 schema1 中建表 products

create database tpcc;
\c tpcc
create schema schema1;
create table schema1.products
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
INSERT INTO schema1.products VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker'),
(8888,5,'jieke','xu');

2.使用 gs_dump 工具以文本格式导出数据库 tpcc 的全量数据

gs_dump -f /home/omm/backup_tpcc_full.sql tpcc -F p

3.使用 gs_dump 工具以文本格式导出模式 schema1 的定义

gs_dump -f /home/omm/backup_schema1_schema_def.sql tpcc -n schema1 -s -F p 

omm@modb:~$ gs_dump -f /home/omm/backup_schema1_schema_def.sql tpcc -n schema1 -s -F p 
gs_dump[port='5432'][tpcc][2021-12-17 20:27:50]: The total objects number is 379.
gs_dump[port='5432'][tpcc][2021-12-17 20:27:50]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-17 20:27:50]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-17 20:27:50]: total time: 187  ms
omm@modb:~$ more /home/omm/backup_schema1_schema_def.sql
--
-- PostgreSQL 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;

--
-- Name: schema1; Type: SCHEMA; Schema: -; Owner: omm
--

CREATE SCHEMA schema1;


ALTER SCHEMA schema1 OWNER TO omm;

SET search_path = schema1;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: products; Type: TABLE; Schema: schema1; Owner: omm; Tablespace: 
--

CREATE TABLE products (
    c_customer_sk integer,
    c_customer_id character(5),
    c_first_name character(6),
    c_last_name character(8)
)
WITH (orientation=row, compression=no);


ALTER TABLE schema1.products OWNER TO omm;

--
-- PostgreSQL database dump complete
--

4.使用 gs_dump 工具以文本格式导出数据库 tpcc 的数据，不包含定义

gs_dump -f /home/omm/backup_tpcc_onlydata.sql tpcc -a -F p 

omm@modb:~$ gs_dump -f /home/omm/backup_tpcc_onlydata.sql tpcc -a -F p 
gs_dump[port='5432'][tpcc][2021-12-17 20:32:10]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-17 20:32:10]: total time: 82  ms
omm@modb:~$ more /home/omm/backup_tpcc_onlydata.sql
--
-- PostgreSQL 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 = schema1;

--
-- Data for Name: products; Type: TABLE DATA; Schema: schema1; Owner: omm
--

COPY products (c_customer_sk, c_customer_id, c_first_name, c_last_name) FROM stdin;
6885    1       Joes    Hunter  
9500    4       Lucy    Baker   
8888    5       jieke   xu      
--More--(91%)4321       2       Lily    Carter  
9527    3       James   Cook    
\.
;

--
-- PostgreSQL database dump complete
--

5.删除表、模式和数据库

gsql -r
\c tpcc
drop table schema1.products;
drop schema schema1;
\c omm
drop database tpcc;

欧耶，第十四课数据导出管理实操及课后作业练习题完成啦！第十五课见！！！