openGauss | 导出数据

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

学习内容

\c 数据库名 ，进入数据库插入数据

\q 退出数据库

gs_dump ~;

作业内容

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

create database tpcc;
\c tpcc;
create schema schema1;
create table schema1.products(id int, name char(30));
insert into schema1.products values (1,'pingguo'),(2,'xiangli'),(3,'lizhi');
复制

omm=# create database tpcc;
CREATE DATABASE
omm=# \c tpcc;
tpcc=# create schema schema1;
CREATE SCHEMA
tpcc=# create table schema1.products(id int, name char(30));
CREATE TABLE
tpcc=# insert into schema1.products values (1,'pingguo'),(2,'xiangli'),(3,'lizhi');
INSERT 0 3
复制

2.使用gs_dump工具以文本格式导出数据库tpcc的全量数据
记得先\q退出数据库

gs_dump -f /home/omm/backup_database_all.sql backup -F p;
复制

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

gs_dump -f  /home/omm/backup_schema_define.sql backup -n schema1 -s -F p;
复制

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

gs_dump -f  /home/omm/backup_database_data.sql backup -a -F p;
复制

tpcc=# \q
omm@modb:~$ gs_dump -f /home/omm/backup_database_all.sql backup -F p;
gs_dump[port='5432'][backup][2021-12-31 23:53:10]: The total objects number is 391.
gs_dump[port='5432'][backup][2021-12-31 23:53:10]: [100.00%] 391 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-31 23:53:10]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-31 23:53:10]: total time: 103  ms
omm@modb:~$ gs_dump -f  /home/omm/backup_schema_define.sql backup -n schema1 -s -F p;
gs_dump[port='5432'][backup][2021-12-31 23:53:40]: no matching schemas were found for pattern "schema1"
gs_dump[port='5432'][backup][2021-12-31 23:53:40]: No matching schemas were found
omm@modb:~$ gs_dump -f  /home/omm/backup_database_data.sql backup -a -F p;
gs_dump[port='5432'][backup][2021-12-31 23:53:46]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-31 23:53:46]: total time: 85  ms
复制

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

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