openGauss每日一练第14天 | 学习openGauss导出数据

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

omm=# create database tpcc;
CREATE DATABASE
omm=#

omm=#
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
tpcc(# tpcc-# ( product_id integer,
product_name char(30),
tpcc(# );tpcc(# category char(20)

CREATE TABLE
tpcc=# insert into schema1.products values
tpcc-# (1,'bear','toy'),
tpcc-# (2,'computer','computer'),
tpcc-# (3,'math','book'),
tpcc-# (4,'shirt','clothes');
INSERT 0 4
tpcc=#
tpcc=# \q

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

omm@modb:~$ gs_dump -f /home/omm/tpcc_database_all.sql tpcc -F p
gs_dump[port='5432'][tpcc][2021-12-14 18:40:03]: The total objects number is 389.
gs_dump[port='5432'][tpcc][2021-12-14 18:40:03]: [100.00%] 389 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-14 18:40:03]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 18:40:03]: total time: 97 ms
omm@modb:~$

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

omm@modb:~$ gs_dump -f /home/omm/tpcc_schema1_define.sql backup -n schema1 -s -F p
gs_dump[port='5432'][backup][2021-12-14 18:40:09]: no matching schemas were found for pattern "schema1"
gs_dump[port='5432'][backup][2021-12-14 18:40:09]: No matching schemas were found
omm@modb:~$

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

omm@modb:~$ gs_dump -f /home/omm/tpcc_database_data.sql tpcc -a -F p
gs_dump[port='5432'][tpcc][2021-12-14 18:40:16]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 18:40:16]: total time: 80 ms
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 SCHEMA
tpcc=# \q
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=# drop database tpcc;
DROP DATABASE
omm=#