#opengauss的第14天
1.创建数据库tpcc，在数据库tpcc中创建模式schema1，在模式schema1中建表products
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=#
tpcc=# create schema schema1;
CREATE SCHEMA
tpcc=# create table schema1.products(id bigint,col1 text,col2 text);
CREATE TABLE
tpcc=# insert into schema1.products seelct id,md5(random()::text),md5(random()::text) from generate_series(1,100) as id;
INSERT 0 100
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 15:50:04]: The total objects number is 389.
gs_dump[port=‘5432’][tpcc][2021-12-14 15:50:04]: [100.00%] 389 objects have been dumped.
gs_dump[port=‘5432’][tpcc][2021-12-14 15:50:04]: dump database tpcc successfully
gs_dump[port=‘5432’][tpcc][2021-12-14 15:50:04]: total time: 92 ms
omm@modb:~$ more tpcc_database_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;
SET default_tablespace = ‘’;
–More–(5%)ALTER SCHEMA schema1 OWNER TO omm;
SET search_path = schema1;
SET default_with_oids = false;
–
– Name: products; Type: TABLE; Schema: schema1; Owner: omm; Tablespace:
CREATE TABLE products (
id bigint,
col1 text,
col2 text
)
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 (id, col1, col2) FROM stdin;
1 5666be9e9a970c69c9242223e488c88d 3247c2cdcd533046fab6c6bef6f0df4d
2 21d45322f1cd37d319c00d446bfd9f00 2f2039409938d2cf6cc708a33a199cb1
3 e01b695b6b5e3e89c7d29f66e400a9ab 91984d700017338bef42404f0d8b3c74
4 53dc7d813f0b97d8aa871b2030109cd7 c708dab898d64ca015ba1573a008c6af
3.使用gs_dump工具以文本格式导出模式schema1的定义
omm@modb:~$ gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
gs_dump[port=‘5432’][tpcc][2021-12-14 15:52:47]: The total objects number is 380.
gs_dump[port=‘5432’][tpcc][2021-12-14 15:52:47]: [100.00%] 380 objects have been dumped.
gs_dump[port=‘5432’][tpcc][2021-12-14 15:52:47]: dump database tpcc successfully
gs_dump[port=‘5432’][tpcc][2021-12-14 15:52:47]: total time: 87 ms
omm@modb:~$ more schema1_schema_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
–More–(5%)
CREATE SCHEMA schema1;
ALTER SCHEMA schema1 OWNER TO omm;
SET search_path = schema1;
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 15:56:57]: dump database tpcc successfully
gs_dump[port=‘5432’][tpcc][2021-12-14 15:56:57]: total time: 79 ms
omm@modb:~$ more tpcc_database_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 (id, col1, col2) FROM stdin;
3 e01b695b6b5e3e89c7d29f66e400a9ab 91984d700017338bef42404f0d8b3c74
4 53dc7d813f0b97d8aa871b2030109cd7 c708dab898d64ca015ba1573a008c6af
5 26c212b308665cb6ce58104af42e1bc8 7af9af11109c6961697ed970920b9b44
1 5666be9e9a970c69c9242223e488c88d 3247c2cdcd533046fab6c6bef6f0df4d
2 21d45322f1cd37d319c00d446bfd9f00 2f2039409938d2cf6cc708a33a199cb1
6 31bfd4c895188d9f556db9a254d3ab6f aed8e73a4df4e0870921976348de5703
7 60735ecb244db5c49faf822498ecf6a3 2c867613a8fcdb14f9676431f5902423
8 bc7db52f5461fe8f1e4c0aff02761f1b 394eeb5374aa2b231db977a08ca26c6c
9 8a2efacb32a5e2b82598f85bf9d31a37 a33e07573a45262f662cf262f10b4417
10 9542c1caa844f5e3903c8d51047cc414 229afb297aafe1d0a6463ead908ecc05
11 fc21877058d940692411be78ce70e57b a9a6e18f10065c30356058829782db4b
12 bf1522408249448ffa72e37c3a1ddc6f 13e5425e23cc8ae5aa41174974f332c5
13 2d909d4bec9c35bc89016b9b37a64aaa 4cfc8b9c1afa5dfc1043cd08f98fd85f
14 9fab152ce9655dfa44960f5d6340423a 033e2d9e3d5387509ff79074f27a7161
15 26a735f65b4dc9ea2cfdef60d471ba44 401ddf4a386024700f485c6c96351dc4
23 3cb62dc0bb4a875860fda1c290ddde8b 5ebbfdfdd416fbdf9c742b19787b828f
24 6acbae5f20632d06f704d1e6237a9750 379bb7947b127587b20535156dc0182a
25 416a79b5c9b9a03cda7c4f0bcd015b2c 4f2d7ee54a6399dd4b584ad43b6f0667
5.删除表、模式和数据库
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------±------±---------±------------±------------±------------------
omm | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(5 rows)
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
tpcc | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
omm=# drop database tpcc;
DROP DATABASE
omm=# drop schema schema1;
ERROR: schema “schema1” does not exist
omm=# drop table t1;
ERROR: table “t1” does not exist
omm=# drop table schema1.t1;
ERROR: schema “schema1” does not exist
