openGauss每日一练第 14 天 |openGauss利用gs_dump命令导出数据

学习openGauss的第十四天

主要内容是openGauss利用gs_dump命令导出数据


连接数据库

root@modb:~# su - omm
omm@modb:~$ gsql -r
failed to connect Unknown:5432.
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.

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

create database tpcc ;
\c tpcc
create schema schema1 ;
create table schema1.products(col1 int,col2 char(20),col3 char(20)) ;

insert into schema1.products values(1,'a','a'),(2,'b','b'),(3,'c','c') ;

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(col1 int,col2 char(20),col3 char(20)) ;
CREATE TABLE
tpcc=# insert into schema1.products values(1,'a','a'),(2,'b','b'),(3,'c','c') ;
INSERT 0 3
tpcc=# \q
omm@modb:~$

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

gs_dump -f /home/omm/back_database_all.sql tpcc -F p

omm@modb:~$ gs_dump -f /home/omm/back_database_all.sql tpcc -F p
gs_dump[port='5432'][tpcc][2021-12-25 17:39:13]: The total objects number is 389.
gs_dump[port='5432'][tpcc][2021-12-25 17:39:13]: [100.00%] 389 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-25 17:39:13]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-25 17:39:13]: total time: 103 ms

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

gs_dump -f /home/omm/backup_schema.sql tpcc -n schema1 -F p

omm@modb:~$ gs_dump -f /home/omm/backup_schema.sql tpcc -n schema1 -F p
gs_dump[port='5432'][tpcc][2021-12-25 17:39:54]: The total objects number is 380.
gs_dump[port='5432'][tpcc][2021-12-25 17:39:54]: [100.00%] 380 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-25 17:39:54]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-25 17:39:54]: total time: 91 ms
omm@modb:~$

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

gs_dump -f /home/omm/backup_database_data.sql tpcc -a -F p

omm@modb:~$ gs_dump -f /home/omm/backup_database_data.sql tpcc -a -F p
gs_dump[port='5432'][tpcc][2021-12-25 17:40:51]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-25 17:40:51]: total time: 80 ms

5.删除表、模式和数据库

drop table schema1.products ;
drop schema schema1 ;
drop database tpcc ;

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 table schema1.products ;
ERROR: schema "schema1" does not exist
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 omm
omm=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# drop database tpcc ;
omm=# DROP DATABASE