学习目标
学习openGauss导出数据
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=# backup-# INSERT INTO customer_t VALUES
(6885, 1, 'Joes', 'Hunter'),
backup-# (4321, 2, 'Lily','Carter'),
backup-# (9527, 3, 'James', 'Cook'),
backup-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
backup=#
backup=#
backup=#
backup=#
backup=# \q
omm@modb:~$ gs_dump -f /home/omm/backup_database_all.sql backup -F p
gs_dump[port='5432'][backup][2021-12-20 16:25:27]: The total objects number is 391.
gs_dump[port='5432'][backup][2021-12-20 16:25:28]: [100.00%] 391 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-20 16:25:28]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:25:28]: total time: 111 ms
omm@modb:~$ gs_dump -f /home/omm/backup_database_data.sql backup -a -F p
gs_dump[port='5432'][backup][2021-12-20 16:25:31]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:25:31]: total time: 84 ms
omm@modb:~$ gs_dump -f /home/omm/backup_database_define.sql backup -s -F p
gs_dump[port='5432'][backup][2021-12-20 16:25:35]: The total objects number is 389.
gs_dump[port='5432'][backup][2021-12-20 16:25:35]: [100.00%] 389 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-20 16:25:35]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:25:35]: total time: 194 ms
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;--More--(22%)
ALTER SCHEMA ds OWNER TO omm;
SET search_path = ds;
SET default_tablespace = '';
...skipping 1 lineALTER TABLE ds.t1 OWNER TO omm;
SET search_path = public;
--
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);
-- 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)
)
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 WITH (orientation=row, compression=no);
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
\.
;
...skipping 23 lines
omm@modb:~$ /home/omm/backup_schema_all.sql backup -n ds -F p
-bash: /home/omm/backup_schema_all.sql: No such file or directory
omm@modb:~$
omm@modb:~$
omm@modb:~$ gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
gs_dump[port='5432'][backup][2021-12-20 16:25:46]: The total objects number is 380.
gs_dump[port='5432'][backup][2021-12-20 16:25:46]: [100.00%] 380 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-20 16:25:46]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:25:46]: total time: 90 ms
omm@modb:~$
omm@modb:~$ gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p
gs_dump[port='5432'][backup][2021-12-20 16:25:54]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:25:54]: total time: 83 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-20 16:25:58]: The total objects number is 379.
gs_dump[port='5432'][backup][2021-12-20 16:25:58]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-20 16:25:58]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:25:58]: total time: 184 ms
omm@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;
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;
--
-- Data for Name: t1; Type: TABLE DATA; Schema: ds; Owner: omm
--
COPY t1 (id, name) FROM stdin;
1 xxxx
\.
;
--
-- PostgreSQL database dump complete
--
omm@modb:~$ gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p
gs_dump[port='5432'][backup][2021-12-20 16:26:09]: The total objects number is 379.
gs_dump[port='5432'][backup][2021-12-20 16:26:09]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-20 16:26:09]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:26:09]: total time: 73 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-20 16:26:12]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:26:12]: total time: 67 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-20 16:26:16]: The total objects number is 378.
gs_dump[port='5432'][backup][2021-12-20 16:26:16]: [100.00%] 378 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-20 16:26:16]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-20 16:26:16]: total time: 171 ms
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:
--
c_customer_sk integer,
--More--(47%)
CREATE TABLE customer_t (
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:~$
omm@modb:~$ omm@modb:~$
omm@modb:~$ omm@modb:~$
omm@modb:~$
omm@modb:~$
课程作业
1.创建数据库tpcc，在数据库tpcc中创建模式schema1，在模式schema1中建表products
2.使用gs_dump工具以文本格式导出数据库tpcc的全量数据
3.使用gs_dump工具以文本格式导出模式schema1的定义
4.使用gs_dump工具以文本格式导出数据库tpcc的数据，不包含定义
5.删除表、模式和数据库
课程作业
1.创建数据库tpcc，在数据库tpcc中创建模式schema1，在模式schema1中建表products
create database tpcc;
\c tpcc
CREATE SCHEMA schema1;
create table schema1.products(id int, name char(30));
2.使用gs_dump工具以文本格式导出数据库tpcc的全量数据
\q
gs_dump -f /home/omm/backup_database_all.sql tpcc -F p
3.使用gs_dump工具以文本格式导出模式schema1的定义
gs_dump -f /home/omm/backup_database_data.sql tpcc -a -F p
4.使用gs_dump工具以文本格式导出数据库tpcc的数据，不包含定义
gs_dump -f /home/omm/backup_schema_define.sql tpcc -n ds -s -F p
5.删除表、模式和数据库
drop database tpcc;
drop schema schema1;
drop table products;
