学习目标
掌握openGauss数据库的逻辑备份和恢复技术。
学习内容
本章熟悉了dump可以通过dump和sql方式进行逻辑的备份和恢复。
课程作业
1.逻辑备份和恢复案例1:使用sql格式进行备份和恢复omm数据库
准备环境：
omm=# CREATE TABLESPACE test10_tbs RELATIVE LOCATION 'tablespace/test10_tbs';
CREATE TABLESPACE
omm=# CREATE DATABASE testdb10 WITH TABLESPACE = test10_tbs;
CREATE DATABASE
omm=# CREATE USER user10 IDENTIFIED BY 'test@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE
omm=# alter user user10 sysadmin;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------------------------------------------------------------+------
-----
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
test | Sysadmin | {}
user10 | Sysadmin | {}
omm=# create table student(id int primary key, name varchar(50) not null, age int default 19);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"
CREATE TABLE
omm=# alter table student add column class bigint;
ALTER TABLE
omm=# insert into student (id,name,class) values (10001,'测试0001',10001),(10002,'测试0002',10001),(10003,'测试0003',10001);
INSERT 0 3
omm=# select * from student;
omm=# id | name | age | class
-------+------------+-----+-------
10001 | 测试0001 | 19 | 10001
10002 | 测试0002 | 19 | 10001
10003 | 测试0003 | 19 | 10001
(3 rows)
omm=# create view view_student as select * from student;
CREATE VIEW使用sql格式进行备份和恢复omm数据库
omm@modb:~$ gs_dump -U user10 -W test@1234 omm -F p -f /var/lib/opengauss/backup/test.sql
gs_dump[port='5432'][omm][2022-12-13 20:48:59]: The total objects number is 425.
gs_dump[port='5432'][omm][2022-12-13 20:48:59]: [100.00%] 425 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 20:48:59]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 20:48:59]: total time: 1528 ms
omm@modb:~$ cat /var/lib/opengauss/backup/test.sql
--
-- openGauss 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: test; Type: SCHEMA; Schema: -; Owner: test
--
CREATE SCHEMA test;
ALTER SCHEMA test OWNER TO test;
--
-- Name: user10; Type: SCHEMA; Schema: -; Owner: user10
--
CREATE SCHEMA user10;
ALTER SCHEMA user10 OWNER TO user10;
SET search_path = public;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: student; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
CREATE TABLE student (
id integer NOT NULL,
name character varying(50) NOT NULL,
age integer DEFAULT 19,
class bigint
)
WITH (orientation=row, compression=no);
ALTER TABLE public.student OWNER TO omm;
--
-- Name: test1; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
CREATE TABLE test1 (
col integer
)
WITH (orientation=row, compression=no);
ALTER TABLE public.test1 OWNER TO omm;
--
-- Name: test2; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
CREATE TABLE test2 (
col integer
)
WITH (orientation=row, compression=no);
ALTER TABLE public.test2 OWNER TO omm;
--
-- Name: test3; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
CREATE TABLE test3 (
col integer
)
WITH (orientation=row, compression=no);
ALTER TABLE public.test3 OWNER TO omm;
--
-- Name: test4; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
CREATE TABLE test4 (
col integer
)
WITH (orientation=row, compression=no);
ALTER TABLE public.test4 OWNER TO omm;
--
测试0002 19 10001
10003 测试0003 19 10001
\.
;
--
-- Data for Name: test1; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY test1 (col) FROM stdin;
\.
;
--
-- Data for Name: test2; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY test2 (col) FROM stdin;
\.
;
--
-- Data for Name: test3; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY test3 (col) FROM stdin;
\.-- Name: view_student; Type: VIEW; Schema: public; Owner: omm
--
CREATE VIEW view_student(id,name,age,class) AS
SELECT * FROM student;
ALTER VIEW public.view_student OWNER TO omm;
--
-- Data for Name: student; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY student (id, name, age, class) FROM stdin;
10001 测试0001 19 10001
10002 --
ALTER TABLE student
ADD CONSTRAINT student_pkey PRIMARY KEY (id);
--
-- 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;
--
-- openGauss database dump complete
--
;
--
-- Data for Name: test4; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY test4 (col) FROM stdin;
\.
;
--
-- Name: student_pkey; Type: CONSTRAINT; Schema: public; Owner: omm; Tablespace:恢复到testdb10
omm@modb:~$ gsql -d testdb10 -U user10 -W test@1234 -f /var/lib/opengauss/backup/test.sql
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER VIEW
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 43 ms
omm@modb:~$ gsql -d testdb10 -U user10 -c "\d"
Password for user user10:
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------+-------+-------+----------------------------------
public | student | table | omm | {orientation=row,compression=no}
public | test1 | table | omm | {orientation=row,compression=no}
public | test2 | table | omm | {orientation=row,compression=no}
public | test3 | table | omm | {orientation=row,compression=no}
public | test4 | table | omm | {orientation=row,compression=no}
public | view_student | view | omm |
(6 rows)
omm@modb:~$ gsql -d testdb10 -U user10 -W test@1234 -c 'select * from view_student;'
id | name | age | class
-------+------------+-----+-------
10001 | 测试0001 | 19 | 10001
10002 | 测试0002 | 19 | 10001
10003 | 测试0003 | 19 | 10001
(3 rows)2.逻辑备份和恢复案例2:使用dump格式进行备份和恢复omm数据库
omm@modb:~$ gs_dump -U user10 -W test@1234 omm -F p -f /var/lib/opengauss/backup/test.dump
gs_dump[port='5432'][omm][2022-12-13 21:02:27]: The total objects number is 425.
gs_dump[port='5432'][omm][2022-12-13 21:02:27]: [100.00%] 425 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 21:02:27]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 21:02:27]: total time: 1487 ms
omm@modb:~$ ls -lah /var/lib/opengauss/backup/test.dump
-rw------- 1 omm omm 2.9K Dec 13 21:02 /var/lib/opengauss/backup/test.dump
恢复
omm=# create database testdb11;
CREATE DATABASE
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
testdb | omm | UTF8 | C | C |
testdb10 | omm | UTF8 | C | C |
testdb11 | omm | UTF8 | C | C |
(7 rows)
omm=# \q
omm@modb:~$ gsql -d testdb11 -c "\d"
No relations found.
omm@modb:~$
omm@modb:~$
omm@modb:~$ gsql -d testdb11 -U user10 -W test@1234 -f /var/lib/opengauss/backup/test.dump
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER VIEW
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 42 ms
omm@modb:~$ gsql -d testdb11 -c "\d"
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------+-------+-------+----------------------------------
public | student | table | omm | {orientation=row,compression=no}
public | test1 | table | omm | {orientation=row,compression=no}
public | test2 | table | omm | {orientation=row,compression=no}
public | test3 | table | omm | {orientation=row,compression=no}
public | test4 | table | omm | {orientation=row,compression=no}
public | view_student | view | omm |
(6 rows)最后修改时间：2022-12-13 21:07:27
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
