openGauss每日一练第14天｜学习openGauss导出数据的操作

坚持学习openGauss数据库，坚持每天打卡。第十四天学习openGauss导出数据操作。

连接opengauss

root@modb:~# su - omm
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=#

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
tpcc-# ( c_customer_sk             integer,   
tpcc(#   c_customer_id             char(5),    
tpcc(#   c_first_name              char(6),    
tpcc(#   c_last_name               char(8) 
tpcc(# );
CREATE TABLE
tpcc=# insert into schema1.products values    
tpcc-# (6885, 1, 'joes', 'hunter'),    
tpcc-# (4321, 2, 'lily','carter'),    
tpcc-# (9527, 3, 'james', 'cook'),
tpcc-# (9500, 4, 'lucy', 'baker');
INSERT 0 4

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

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

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

omm@modb:~$ gs_dump -f /home/omm/backup_schema_define.sql tpcc -n schema1 -s -F p
gs_dump[port='5432'][tpcc][2021-12-14 23:16:54]: The total objects number is 379.
gs_dump[port='5432'][tpcc][2021-12-14 23:16:54]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-14 23:16:54]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 23:16:54]: total time: 187  ms

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

omm@modb:~$ gs_dump -f /home/omm/backup_database_data.sql tpcc -a -F p
gs_dump[port='5432'][tpcc][2021-12-14 23:20:05]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 23:20:05]: total time: 82  ms

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=# \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

通过学习openGauss导出数据操作，加上作业里面的练习，更加深入理解openGauss数据库的导出导入操作。