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.导出数据库
–以sql文本格式导出backup数据库全量信息
gs_dump -f /home/omm/backup_database_all.sql backup -F p
–以sql文本格式导出backup数据库中的数据，不包含数据库对象定义
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
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
–查看导出的数据
more /home/omm/backup_schema_all.sql
4.导出表
–以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
课程作业
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:~$复制
