openGauss每日一练第14天|《openGauss导出数据》学习心得体会和课后练习
学习openGauss导出数据
课程学习
连接数据库
#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
1.准备数据
–数据存放在backup数据库中
create database backup;
\c backup
CREATE SCHEMA ds;
create table ds.t1(id int, name char(30));
insert into ds.t1 values(1 ,'xxxx');
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
INSERT INTO customer_t VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
backup=# select current_user,current_database();
(1 row)
backup=# current_user | current_database
--------------+------------------
omm | backup
backup=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------------+-------+-------+----------------------------------
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
backup=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(4 rows)
–退出数据库连接
\q
2.导出数据库
omm@modb:~$ gs_dump -h
gs_dump: option requires an argument -- 'h'
Try "gs_dump --help" for more information.
omm@modb:~$ gs_dump -help
connection to database "" failed: could not translate host name "elp" to address: Name or service not known
omm@modb:~$ gs_dump --help
gs_dump dumps a database as a text file or to other formats.
Usage:
gs_dump [OPTION]... [DBNAME]
General options:
-f, --file=FILENAME output file or directory name
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-v, --verbose verbose mode
-V, --version output version information, then exit
-Z, --compress=0-9 compression level for compressed formats
--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
-?, --help show this help, then exit
Options controlling the output content:
-a, --data-only dump only the data, not the schema
-b, --blobs include large objects in dump
-c, --clean clean (drop) database objects before recreating
-C, --create include commands to create database in dump
-E, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=SCHEMA dump the named schema(s) only
-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)
-o, --oids include OIDs in dump
-O, --no-owner skip restoration of object ownership in
plain-text format
-s, --schema-only dump only the schema, no data
-S, --sysadmin=NAME system admin user name to use in plain-text format
-t, --table=TABLE dump the named table(s) only
-T, --exclude-table=TABLE do NOT dump the named table(s)
--include-table-file=FileName dump the named table(s) only
--exclude-table-file=FileName do NOT dump the named table(s)
-x, --no-privileges/--no-acl do not dump privileges (grant/revoke)
--column-inserts/--attribute-inserts dump data as INSERT commands with column names
--disable-dollar-quoting disable dollar quoting, use SQL standard quoting
--disable-triggers disable triggers during data-only restore
--exclude-table-data=TABLE do NOT dump data for the named table(s)
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--no-tablespaces do not dump tablespace assignments
--serializable-deferrable wait until the dump can run without anomalies
--dont-overwrite-file do not overwrite the existing file in case of plain, tar and custom format
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
--no-unlogged-table-data do not dump unlogged table data
--include-alter-table dump the table delete column
--quote-all-identifiers quote all identifiers, even if not key words
--section=SECTION dump named section (pre-data, data, or post-data)
ALTER OWNER commands to set ownership
--with-encryption=AES128 dump data is encrypted using AES128
--with-key=KEY AES128 encryption key, must be 16 bytes in length
--with-salt=RANDVALUES used by gs_dumpall, pass rand value array
--include-extensions include extensions in dump
--binary-upgrade for use by upgrade utilities only
--binary-upgrade-usermap="USER1=USER2" to be used only by upgrade utility for mapping usernames
--non-lock-table for use by OM tools utilities only
--include-depend-objs dump the object which depends on the input object
--exclude-self do not dump the input object
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password=PASSWORD the password of specified database user
--role=ROLENAME do SET ROLE before dump
--rolepassword=ROLEPASSWORD the password for role
If no database name is supplied, then the PGDATABASE environment
variable value is used
–以sql文本格式导出backup数据库全量信息
gs_dump -f /home/omm/backup_database_all.sql backup -F p
-f 输出到那里？
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
omm@modb:~$ gs_dump -f /home/omm/backup_database_all.sql backup -F p
gs_dump[port='5432'][backup][2021-12-14 11:32:33]: The total objects number is 391.
gs_dump[port='5432'][backup][2021-12-14 11:32:33]: [100.00%] 391 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-14 11:32:33]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:32:33]: total time: 101 ms
–以sql文本格式导出backup数据库中的数据，不包含数据库对象定义
gs_dump -f /home/omm/backup_database_data.sql backup -a -F p
-a| -a, --data-only dump only the data, not the schema
omm@modb:~$ gs_dump -f /home/omm/backup_database_data.sql backup -a -F p
gs_dump[port='5432'][backup][2021-12-14 11:33:35]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:33:35]: total time: 82 ms
–以sql文本格式导出backup数据库中对象的定义
gs_dump -f /home/omm/backup_database_define.sql backup -s -F p
-s|--schema-only dump only the schema, no data
omm@modb:~$ gs_dump -f /home/omm/backup_database_define.sql backup -s -F p
gs_dump[port='5432'][backup][2021-12-14 11:34:23]: The total objects number is 389.
gs_dump[port='5432'][backup][2021-12-14 11:34:23]: [100.00%] 389 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-14 11:34:23]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:34:23]: total time: 190 ms
–查看导出的数据
more /home/omm/backup_database_all.sql
3.导出模式
–以sql文本格式导出backup数据库中ds模式的全量信息
gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
omm@modb:~$ gs_dump -f /home/omm/backup_schema_all.sql backup -n ds -F p
gs_dump[port='5432'][backup][2021-12-14 11:42:04]: The total objects number is 380.
gs_dump[port='5432'][backup][2021-12-14 11:42:04]: [100.00%] 380 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-14 11:42:04]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:42:04]: total time: 86 ms
-n -n, --schema=SCHEMA dump the named schema(s) only
–以sql文本格式导出backup数据库中ds模式的数据
gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p
omm@modb:~$ gs_dump -f /home/omm/backup_schema_data.sql backup -n ds -a -F p
gs_dump[port='5432'][backup][2021-12-14 11:46:18]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:46:18]: total time: 79 ms
–以sql文本格式导出backup数据库中ds模式的定义
gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p
omm@modb:~$ gs_dump -f /home/omm/backup_schema_define.sql backup -n ds -s -F p
gs_dump[port='5432'][backup][2021-12-14 11:46:31]: The total objects number is 379.
gs_dump[port='5432'][backup][2021-12-14 11:46:31]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-14 11:46:31]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:46:31]: total time: 184 ms
–查看导出的数据
more /home/omm/backup_schema_all.sql
omm@modb:~$ more /home/omm/backup_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;
ALTER SCHEMA ds OWNER TO omm;
SET search_path = ds;
SET default_tablespace = '';
--More--(47%)SET client_min_messages = warning;
--
-- Name: ds; Type: SCHEMA; Schema: -; Owner: omm
--
CREATE SCHEMA ds;
SET default_with_oids = false;
--
-- Name: t1; Type: TABLE; Schema: ds; Owner: omm; Tablespace:
--
CREATE TABLE t1 (
id integer,
name character(30)
)
WITH (orientation=row, compression=no);
ALTER TABLE ds.t1 OWNER TO omm;
--
-- Data for Name: t1; Type: TABLE DATA; Schema: ds; Owner: omm
--
COPY t1 (id, name) FROM stdin;
1 xxxx
\.
;
--
-- PostgreSQL database dump complete
--
4.导出表
–以sql文本格式导出backup数据库中表customer_t的全量信息
gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p
omm@modb:~$ gs_dump -f /home/omm/backup_table_all.sql backup -t customer_t -F p
gs_dump[port='5432'][backup][2021-12-14 11:47:13]: The total objects number is 379.
gs_dump[port='5432'][backup][2021-12-14 11:47:13]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-14 11:47:13]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:47:13]: total time: 74 ms
–以sql文本格式导出backup数据库中表customer_t的数据
gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p
omm@modb:~$ gs_dump -f /home/omm/backup_table_data.sql backup -t customer_t -a -F p
gs_dump[port='5432'][backup][2021-12-14 11:47:26]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:47:26]: total time: 67 ms
–以sql文本格式导出backup数据库中表customer_t的定义
gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p
omm@modb:~$ gs_dump -f /home/omm/backup_table_define.sql backup -t customer_t -s -F p
gs_dump[port='5432'][backup][2021-12-14 11:51:15]: The total objects number is 378.
gs_dump[port='5432'][backup][2021-12-14 11:51:15]: [100.00%] 378 objects have been dumped.
gs_dump[port='5432'][backup][2021-12-14 11:51:15]: dump database backup successfully
gs_dump[port='5432'][backup][2021-12-14 11:51:15]: total time: 171 ms
–查看导出的数据
more /home/omm/backup_table_all.sql
omm@modb:~$ more /home/omm/backup_table_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;
SET search_path = public;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
--More--(47%)CREATE TABLE customer_t (
c_customer_sk integer,
c_customer_id character(5),
c_first_name character(6),
c_last_name character(8)
)
WITH (orientation=row, compression=no);
ALTER TABLE public.customer_t OWNER TO omm;
--
-- Data for Name: customer_t; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY customer_t (c_customer_sk, c_customer_id, c_first_name, c_last_name) FROM stdin;
6885 1 Joes Hunter
4321 2 Lily Carter
9527 3 James Cook
9500 4 Lucy Baker
\.
;
--
-- PostgreSQL database dump complete
--
课程作业
1.创建数据库tpcc，在数据库tpcc中创建模式schema1，在模式schema1中建表products
create database tpcc;
\c tpcc
create schema schema1;
create table schema1.products (id int,name varchar(10));
insert into schema1.products values(1,'tv'),(2,'sofa'),(3,'car'),(4,'food');
2.使用gs_dump工具以文本格式导出数据库tpcc的全量数据
gs_dump -f /home/omm/backup_tpcc_all.sql tpcc -F p
omm@modb:~$ gs_dump -f /home/omm/backup_tpcc_all.sql tpcc -F p
gs_dump[port='5432'][tpcc][2021-12-14 11:58:28]: The total objects number is 389.
gs_dump[port='5432'][tpcc][2021-12-14 11:58:28]: [100.00%] 389 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-14 11:58:28]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 11:58:28]: total time: 100 ms
3.使用gs_dump工具以文本格式导出模式schema1的定义
gs_dump -f /home/omm/backup_tpcc_def.sql tpcc -n schema1 -F p -s
omm@modb:~$ gs_dump -f /home/omm/backup_tpcc_def.sql tpcc -n schema1 -F p -s
gs_dump[port='5432'][tpcc][2021-12-14 12:00:31]: The total objects number is 379.
gs_dump[port='5432'][tpcc][2021-12-14 12:00:31]: [100.00%] 379 objects have been dumped.
gs_dump[port='5432'][tpcc][2021-12-14 12:00:31]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 12:00:31]: total time: 183 ms
4.使用gs_dump工具以文本格式导出数据库tpcc的数据，不包含定义
gs_dump -f /home/omm/backup_products_data.sql tpcc -t schema1.products -F p -a
omm@modb:~$ gs_dump -f /home/omm/backup_products_data.sql tpcc -t schema1.products -F p -a
gs_dump[port='5432'][tpcc][2021-12-14 12:04:51]: dump database tpcc successfully
gs_dump[port='5432'][tpcc][2021-12-14 12:04:51]: total time: 65 ms
5.删除表、模式和数据库
\c tpcc
drop table schema1.products;
drop schema schema1;
\c postgres
drop database tpcc;
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
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
-----------+-------+----------+-------------+-------------+-------------------
backup | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
omm | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres=# | | | | | omm=CTc/omm
(5 rows)
最后修改时间：2021-12-24 15:16:08
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
