学习目标
学习openGauss导出数据
课程学习实操
连接数据库
#第一次进入等待15秒 #数据库启动中... su - omm gsql -r
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');omm=# create database backup; CREATE DATABASE omm=# \c backup Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "backup" as user "omm". backup=# CREATE SCHEMA ds; CREATE SCHEMA backup=# create table ds.t1(id int, name char(30)); CREATE TABLE backup=# insert into ds.t1 values(1 ,'xxxx'); INSERT 0 1 backup=# CREATE TABLE customer_t backup-# ( c_customer_sk integer, backup(# c_customer_id char(5), backup(# c_first_name char(6), backup(# c_last_name char(8) ) ; backup(# CREATE TABLE backup=# INSERT INTO customer_t VALUES backup-# (6885, 1, 'Joes', 'Hunter'), backup-# (4321, 2, 'Lily','Carter'), backup-# (9527, 3, 'James', 'Cook'), backup-# (9500, 4, 'Lucy', 'Baker'); INSERT 0 4 backup=#
–退出数据库连接
\qbackup=# \q omm@modb:~$
2.导出数据库
–以sql文本格式导出backup数据库全量信息
gs_dump -f /home/omm/backup_database_all.sql backup -F pomm@modb:~$ gs_dump -f /home/omm/backup_database_all.sql backup -F p gs_dump[port='5432'][backup][2021-12-14 09:33:53]: The total objects number is 391. gs_dump[port='5432'][backup][2021-12-14 09:33:53]: [100.00%] 391 objects have been dumped. gs_dump[port='5432'][backup][2021-12-14 09:33:53]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:33:53]: total time: 105 ms
–以sql文本格式导出backup数据库中的数据，不包含数据库对象定义
gs_dump -f /home/omm/backup_database_data.sql backup -a -F pomm@modb:~$ gs_dump -f /home/omm/backup_database_data.sql backup -a -F p gs_dump[port='5432'][backup][2021-12-14 09:34:32]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:34:32]: total time: 87 ms
–以sql文本格式导出backup数据库中对象的定义
gs_dump -f /home/omm/backup_database_define.sql backup -s -F pomm@modb:~$ gs_dump -f /home/omm/backup_database_define.sql backup -s -F p gs_dump[port='5432'][backup][2021-12-14 09:34:46]: The total objects number is 389. gs_dump[port='5432'][backup][2021-12-14 09:34:46]: [100.00%] 389 objects have been dumped. gs_dump[port='5432'][backup][2021-12-14 09:34:46]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:34:46]: total time: 190 ms
–查看导出的数据
more /home/omm/backup_database_all.sqlomm@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; ALTER SCHEMA ds OWNER TO omm; SET search_path = 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; SET search_path = public; -- -- Name: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace: -- CREATE TABLE customer_t ( --More--(44%)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; c_customer_sk integer, c_customer_id character(5), c_first_name character(6), c_last_name character(8) ) -- -- 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 --omm@modb:~$ cat /home/omm/backup_database_data.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 = 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 \. ; -- -- PostgreSQL database dump complete -- omm@modb:~$ cat /home/omm/backup_database_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 -- CREATE SCHEMA ds; ALTER SCHEMA ds OWNER TO omm; SET search_path = 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; 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; -- -- 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 -- omm@modb:~$
3.导出模式
–以sql文本格式导出backup数据库中ds模式的全量信息
gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F pomm@modb:~$ gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p gs_dump[port='5432'][backup][2021-12-14 09:38:03]: The total objects number is 380. gs_dump[port='5432'][backup][2021-12-14 09:38:03]: [100.00%] 380 objects have been dumped. gs_dump[port='5432'][backup][2021-12-14 09:38:03]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:38:03]: total time: 87 ms omm@modb:~$
–以sql文本格式导出backup数据库中ds模式的数据
gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F pomm@modb:~$ gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p gs_dump[port='5432'][backup][2021-12-14 09:38:59]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:38:59]: total time: 79 ms
–以sql文本格式导出backup数据库中ds模式的定义
gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F pomm@modb:~$ gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p gs_dump[port='5432'][backup][2021-12-14 09:39:09]: The total objects number is 379. gs_dump[port='5432'][backup][2021-12-14 09:39:09]: [100.00%] 379 objects have been dumped. gs_dump[port='5432'][backup][2021-12-14 09:39:09]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:39:09]: total time: 187 ms omm@modb:~$
–查看导出的数据
more /home/omm/backup_schema_all.sqlomm@modb:~$ more /home/omm/backup_schema_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; ALTER SCHEMA ds OWNER TO omm; --More--(47%)SET search_path = ds; SET default_tablespace = ''; -- 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; -- -- Data for Name: t1; Type: TABLE DATA; Schema: ds; Owner: omm -- COPY t1 (id, name) FROM stdin; 1 xxxx \. SET default_with_oids = false; -- ; -- -- PostgreSQL database dump complete -- omm@modb:~$
4.导出表
–以sql文本格式导出backup数据库中表customer_t的全量信息
gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F pomm@modb:~$ gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p gs_dump[port='5432'][backup][2021-12-14 09:41:14]: The total objects number is 379. gs_dump[port='5432'][backup][2021-12-14 09:41:14]: [100.00%] 379 objects have been dumped. gs_dump[port='5432'][backup][2021-12-14 09:41:14]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:41:14]: total time: 72 ms
–以sql文本格式导出backup数据库中表customer_t的数据
gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F pomm@modb:~$ gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p gs_dump[port='5432'][backup][2021-12-14 09:41:22]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:41:22]: total time: 65 ms
–以sql文本格式导出backup数据库中表customer_t的定义
gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F pomm@modb:~$ gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p gs_dump[port='5432'][backup][2021-12-14 09:41:27]: The total objects number is 378. gs_dump[port='5432'][backup][2021-12-14 09:41:27]: [100.00%] 378 objects have been dumped. gs_dump[port='5432'][backup][2021-12-14 09:41:27]: dump database backup successfully gs_dump[port='5432'][backup][2021-12-14 09:41:27]: total time: 170 ms
–查看导出的数据
more /home/omm/backup_table_all.sqlomm@modb:~$ more /home/omm/backup_table_all.sql -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET xmloption = content; -- Name: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace: -- CREATE TABLE customer_t (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; -- --More--(47%) 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 -- omm@modb:~$
课程作业
1.创建数据库tpcc，在数据库tpcc中创建模式schema1，在模式schema1中建表products
omm=# create database tpcc; CREATE DATABASEomm=# \c tpcc Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "tpcc" as user "omm". tpcc=# create schema schema1; CREATE SCHEMA tpcc=# create table schema1.products tpcc-# (prod_id int primary key, tpcc(# prod_name varchar(30), tpcc(# prod_price decimal(10,2), tpcc(# prod_date date); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "products_pkey" for table "products" CREATE TABLE tpcc=# tpcc=# insert into schema1.products values(1,'memory',300.23,sysdate); INSERT 0 1 tpcc=# insert into schema1.products values(2,'cpu',1300.23,sysdate); INSERT 0 1 tpcc=# insert into schema1.products values(3,'disk',600.23,sysdate); INSERT 0 1 tpcc=# insert into schema1.products values(4,'mouse',120,sysdate); INSERT 0 1tpcc=# select * from schema1.products ; prod_id | prod_name | prod_price | prod_date ---------+-----------+------------+------------ 1 | memory | 300.23 | 2021-12-14 2 | cpu | 1300.23 | 2021-12-14 3 | disk | 600.23 | 2021-12-14 4 | mouse | 120.00 | 2021-12-14 (4 rows) tpcc=#
2.使用gs_dump工具以文本格式导出数据库tpcc的全量数据
omm@modb:~$ gs_dump -f /home/omm/backup_database_tpcc_all.sql tpcc -F p
gs_dump[port='5432'][tpcc][2021-12-14 09:53:19]: The total objects number is 391.
gs_dump[port='5432'][tpcc][2021-12-14 09:53:19]: [100.00%] 391 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-14 09:53:19]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 09:53:19]: total time: 99 ms
omm@modb:~$
3.使用gs_dump工具以文本格式导出模式schema1的定义
omm@modb:~$ gs_dump -f /home/omm/backup_database_tpcc_schema1.sql tpcc -n schema1 -s -F p
gs_dump[port='5432'][tpcc][2021-12-14 09:54:20]: The total objects number is 381.
gs_dump[port='5432'][tpcc][2021-12-14 09:54:20]: [100.00%] 381 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-14 09:54:20]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 09:54:20]: total time: 186 ms
omm@modb:~$
4.使用gs_dump工具以文本格式导出数据库tpcc的数据，不包含定义
omm@modb:~$ gs_dump -f /home/omm/backup_database_tpcc_data.sql tpcc -a -F p gs_dump[port='5432'][tpcc][2021-12-14 09:55:06]: dump database tpcc successfully gs_dump[port='5432'][tpcc][2021-12-14 09:55:06]: total time: 81 ms omm@modb:~$omm@modb:~$ cat /home/omm/backup_database_tpcc_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: 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 ( prod_id integer NOT NULL, prod_name character varying(30), prod_price numeric(10,2), prod_date date ) WITH (orientation=row, compression=no); ALTER TABLE schema1.products OWNER TO omm; -- -- Data for Name: products; Type: TABLE DATA; Schema: schema1; Owner: omm -- COPY products (prod_id, prod_name, prod_price, prod_date) FROM stdin; 1 memory 300.23 2021-12-14 2 cpu 1300.23 2021-12-14 3 disk 600.23 2021-12-14 4 mouse 120.00 2021-12-14 \. ; -- -- Name: products_pkey; Type: CONSTRAINT; Schema: schema1; Owner: omm; Tablespace: -- ALTER TABLE products ADD CONSTRAINT products_pkey PRIMARY KEY (prod_id); -- -- 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 -- omm@modb:~$
5.删除表、模式和数据库
omm=# \c tpcc Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "tpcc" as user "omm".tpcc=# drop table schema1.products; DROP TABLE tpcc=# drop schema schema1; DROP SCHEMAtpcc=# \c omm Non-SSL connection (SSL connection is recommended when requiring high-security) You are now connected to database "omm" as user "omm". omm=# drop database tpcc; DROP DATABASE omm=#
学习总结
通过本节课的学习，我掌握了通过工具gs_dump进行数据导出的基本操作。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
