openGauss每日一练第14天

原创张辉 2021-12-15

827

准备数据库backup，schema ds,表custom_t及表内4条数据：

在shell中使用gs_dump导出数据库backup：

查看导出结果：

omm@modb:~$ ls -lrt
total 12
-rw------- 1 omm omm 1703 Dec 15 07:02 backup_database_all.sql
-rw------- 1 omm omm 742 Dec 15 07:03 backup_database_data.sql
-rw------- 1 omm omm 1231 Dec 15 07:04 backup_database_define.sql
omm@modb:~$ pwd
/home/omm
omm@modb:~$ cat backup_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: ds; Type: SCHEMA; Schema: -; Owner: omm
--

CREATE SCHEMA ds;

ALTER SCHEMA ds OWNER TO omm;

SET search_path = ds;

SET default_tablespace = '';

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;

SET search_path = public;

--
-- Name: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--

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;

SET search_path = ds;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: ds; Owner: omm
--

COPY t1 (id, name) FROM stdin;
1 xxxx
\.
;

SET search_path = public;

--
Lucy Baker
\.
;

--
-- Name: public; Type: ACL; Schema: -; Owner: omm
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM omm;
GRANT CREATE,USAGE ON SCHEMA public TO omm;
GRANT USAGE ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

-- 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 omm@modb:~$

使用SQL文本方式导出数据库backup的数据：

omm@modb:~$ cat backup_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 = ds;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: ds; Owner: omm
--

COPY t1 (id, name) FROM stdin;
1 xxxx
\.
;

SET search_path = public;

--
-- 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
--

使用SQL文本方式导出数据库backup的对象定义：

omm@modb:~$ cat backup_database_define.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: ds; Type: SCHEMA; Schema: -; Owner: omm
--

CREATE SCHEMA ds;

ALTER SCHEMA ds OWNER TO omm;

SET search_path = ds;

SET default_tablespace = '';

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;

SET search_path = public;

--
-- Name: customer_t; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--

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;

--
-- Name: public; Type: ACL; Schema: -; Owner: omm
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM omm;
GRANT CREATE,USAGE ON SCHEMA public TO omm;
GRANT USAGE ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

omm@modb:~$

导出schema ds（-n xx表示xx schema）

-a是data，-s是结构

查看结果：

可以看到数据其实是以copy .. from的方式进行记录的

导出表：

加上-t参数即可。

做作业：

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

create database tpcc;

\c tpcc;

create schema schema1;

create table schema1.products

(id integer,

name varchar(20));

insert into schema1.products values

(1,'1'),

(2,'2'),

(3,'3'),

(4,'4');

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

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

omm@modb:~$ cat backup_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;

ALTER SCHEMA schema1 OWNER TO omm;

SET search_path = schema1;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: products; Type: TABLE; Schema: schema1; Owner: omm; Tablespace:
--

CREATE TABLE products (
id integer,
name character varying(20)
)
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, name) FROM stdin;
1 1
2 2
3 3
4 4
\.
;

--
-- Name: public; Type: ACL; Schema: -; Owner: omm
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM omm;
GRANT CREATE,USAGE ON SCHEMA public TO omm;
GRANT USAGE ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--