数据库环境
openGauss:2.0.0 - 数据库实训平台
学习目标
学习openGauss导出数据
学习笔记
- 导出backup数据库
- 全量信息
- 数据，不包含数据库对象定义
- 对象的定义
gs_dump -f /home/omm/backup_database_all.sql backup -F p
gs_dump -f /home/omm/backup_database_data.sql backup -a -F p
gs_dump -f /home/omm/backup_database_define.sql backup -s -F p
- backup数据库中ds模式
- 全量信息
- 数据
- 定义
gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p
gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p
- backup数据库中表customer_t
- 全量信息
- 数据
- 定义
gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p
gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p
gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p
课后作业
1.创建数据库tpcc，在数据库tpcc中创建模式schema1，在模式schema1中建表products
omm=# create database tpcc;
CREATE DATABASE
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=# create schema schema1;
CREATE SCHEMA
tpcc=# create table schema1.products(product_id integer,product_name char(30));
CREATE TABLE
tpcc=# insert into schema1.products values
tpcc-# (1,'computer'),
tpcc-# (2,'laptop'),
tpcc-# (3,'phone');
INSERT 0 3
tpcc=# select * from schema1.products;
product_id | product_name
------------+--------------------------------
1 | computer
2 | laptop
3 | phone
(3 rows)
tpcc=# \q
omm@modb:~$
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-16 00:25:29]: The total objects number is 389.
gs_dump[port='5432'][tpcc][2021-12-16 00:25:29]: [100.00%] 389 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-16 00:25:29]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-16 00:25:29]: total time: 98 ms
omm@modb:~$ cat 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 (
product_id integer,
product_name character(30)
)
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 (product_id, product_name) FROM stdin;
1 computer
2 laptop
3 phone
\.
;
--
-- PostgreSQL database dump complete
--
--
-- 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;
3.使用gs_dump工具以文本格式导出模式schema1的定义
omm@modb:~$ gs_dump -f /home/omm/backup_tpcc_schema1_define.sql tpcc -n schema1 -s -F p
gs_dump[port='5432'][tpcc][2021-12-16 00:27:20]: The total objects number is 379.
gs_dump[port='5432'][tpcc][2021-12-16 00:27:20]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-16 00:27:20]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-16 00:27:20]: total time: 187 ms
omm@modb:~$ cat backup_tpcc_schema1_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: 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 (
product_id integer,
product_name character(30)
)
WITH (orientation=row, compression=no);
ALTER TABLE schema1.products OWNER TO omm;
--
-- PostgreSQL database dump complete
--
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-16 00:29:22]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-16 00:29:22]: total time: 80 ms
omm@modb:~$ cat backup_database_tpcc_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 = schema1;
--
-- Data for Name: products; Type: TABLE DATA; Schema: schema1; Owner: omm
--
COPY products (product_id, product_name) FROM stdin;
1 computer
2 laptop
3 phone
\.
;
--
-- PostgreSQL database dump complete
--
5.删除表、模式和数据库
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# \c tpcc
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 SCHEMA
tpcc=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
postgres=# drop database tpcc;
DROP DATABASE
postgres=# \q
omm@modb:~$
学习资源
- openGauss SQL学习参考资料
- 每日一练：openGauss数据库在线实训课程
- openGauss每日一练 | 21期养成好习惯，提升技术能力！
- 墨天轮Markdown编辑器使用介绍
- 墨天轮数据库在线实训平台V1.0操作手册
- 墨天轮数据社区
欢迎各位同学一起来交流学习心得！
最后修改时间：2021-12-16 18:38:02
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
