概述

本文档记录openGauss 3.0.0数据库每日一练第20天课程作业，掌握openGauss数据库的逻辑备份和恢复技术。

课程练习

逻辑备份和恢复案例1:使用sql格式进行备份和恢复omm数据库

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day20 RELATIVE LOCATION 'tablespace/day20';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb20 WITH TABLESPACE = day20;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb20sql WITH TABLESPACE = day20;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb20dump WITH TABLESPACE = day20;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user20 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user20  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb20 user20
Password for user user20: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb20" as user "user20".
user20@local:/opt/huawei/tmp [musicdb20]=>create table t1(id int, name varchar(20));
CREATE TABLE
user20@local:/opt/huawei/tmp [musicdb20]=>create table t2(id int, name varchar(20));
CREATE TABLE
user20@local:/opt/huawei/tmp [musicdb20]=>insert into t1 values(1,'aaaa');
INSERT 0 1
user20@local:/opt/huawei/tmp [musicdb20]=>insert into t2 values(2,'bbbb');
INSERT 0 1
--导出数据表
[omm@ogauss1 backup]$ gs_dump -U user20 -W zs@123456 -p 15400 musicdb20 -F p -f /home/omm/backup/backup.sql
gs_dump[port='15400'][musicdb20][2022-12-13 14:12:03]: The total objects number is 412.
gs_dump[port='15400'][musicdb20][2022-12-13 14:12:03]: [100.00%] 412 objects have been dumped.
gs_dump[port='15400'][musicdb20][2022-12-13 14:12:03]: dump database musicdb20 successfully
gs_dump[port='15400'][musicdb20][2022-12-13 14:12:03]: total time: 2433  ms

--可以看到 -F p的情况下，备份文本是可以直接查看的
[omm@ogauss1 backup]$ cat backup.sql 
................

CREATE TABLE t2 (
    id integer,
    name character varying(20)
)
WITH (orientation=row, compression=no);


ALTER TABLE public.t2 OWNER TO user20;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: user20
--

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

--
-- Data for Name: t2; Type: TABLE DATA; Schema: public; Owner: user20
--

COPY t2 (id, name) FROM stdin;
2       bbbb
\.
;
..........

--导入musicdb20sql
[omm@ogauss1 backup]$   gsql -d musicdb20sql   -U user20 -W zs@123456 -p 15400  -f /home/omm/backup/backup.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 12  ms

--验证，数据已导入成功
user20@local:/opt/huawei/tmp [musicdb20]=>\c musicdb20sql
Password for user user20: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb20sql" as user "user20".
user20@local:/opt/huawei/tmp [musicdb20sql]=>\d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user20 | {orientation=row,compression=no}
 public | t2   | table | user20 | {orientation=row,compression=no}
(2 rows)

user20@local:/opt/huawei/tmp [musicdb20sql]=>select * from t1;
 id | name 
----+------
  1 | aaaa
(1 row)

逻辑备份和恢复案例2:使用dump格式进行备份和恢复omm数据库

--新增2个数据表
user20@local:/opt/huawei/tmp [musicdb20sql]=>\c musicdb20
Password for user user20: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb20" as user "user20".
user20@local:/opt/huawei/tmp [musicdb20]=>create table t3(id int, name varchar(20));
CREATE TABLE
user20@local:/opt/huawei/tmp [musicdb20]=>create table t4(id int, name varchar(20));
CREATE TABLE
user20@local:/opt/huawei/tmp [musicdb20]=>insert into t3 values(3,'cccc');
INSERT 0 1
user20@local:/opt/huawei/tmp [musicdb20]=>insert into t4 values(4,'dddd');
INSERT 0 1
user20@local:/opt/huawei/tmp [musicdb20]=>\d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user20 | {orientation=row,compression=no}
 public | t2   | table | user20 | {orientation=row,compression=no}
 public | t3   | table | user20 | {orientation=row,compression=no}
 public | t4   | table | user20 | {orientation=row,compression=no}
(4 rows)

--执行-F c格式导出
[omm@ogauss1 backup]$ gs_dump -U user20 -W zs@123456 -p 15400 musicdb20 -F c -f /home/omm/backup/backup.dump
gs_dump[port='15400'][musicdb20][2022-12-13 14:22:06]: The total objects number is 416.
gs_dump[port='15400'][musicdb20][2022-12-13 14:22:06]: [100.00%] 416 objects have been dumped.
gs_dump[port='15400'][musicdb20][2022-12-13 14:22:06]: dump database musicdb20 successfully
gs_dump[port='15400'][musicdb20][2022-12-13 14:22:06]: total time: 2388  ms
--可以看到 -F c的情况下，备份文本是无法查看
[omm@ogauss1 backup]$ cat backup.dump 
PGDMP

z       ++_icdb209.2.49.2.4Y0ENCODINENCODING#SET c+ie++_e+c-di+g = 'SQL_ASCII';
fa+_eZ00
STDSTRINGS
STDSTRINGS(SET _+a+da_d_c-+f-_+i+g__+_i+g_ = '-+';
fa+_e[126232777 ++_icdb2DATABASE

--导入musicdb20dump
[omm@ogauss1 backup]$  gs_restore -d musicdb20dump   -U user20 -W zs@123456 -p 15400  -c -v -F c /home/omm/backup/backup.dump
connecting to database for restore
dropping TABLE "public.t4"
dropping TABLE "public.t3"
dropping TABLE "public.t2"
dropping TABLE "public.t1"
dropping SCHEMA "public"
start restore operation ...
creating SCHEMA "public"
creating COMMENT "SCHEMA public"
creating TABLE "public.t1"
creating TABLE "public.t2"
creating TABLE "public.t3"
creating TABLE "public.t4"
restoring data for table "public.t1"
table t1 complete data imported !
restoring data for table "public.t2"
table t2 complete data imported !
restoring data for table "public.t3"
table t3 complete data imported !
restoring data for table "public.t4"
table t4 complete data imported !
Finish reading 14 SQL statements!
end restore operation ...
setting owner and privileges for DATABASE "musicdb20"
setting owner and privileges for SCHEMA "public"
setting owner and privileges for COMMENT "SCHEMA public"
setting owner and privileges for ACL "public"
setting owner and privileges for TABLE "public.t1"
setting owner and privileges for TABLE "public.t2"
setting owner and privileges for TABLE "public.t3"
setting owner and privileges for TABLE "public.t4"
setting owner and privileges for TABLE DATA "public.t1"
setting owner and privileges for TABLE DATA "public.t2"
setting owner and privileges for TABLE DATA "public.t3"
setting owner and privileges for TABLE DATA "public.t4"
restore operation successful
total time: 64  ms

--数据验证
user20@local:/opt/huawei/tmp [musicdb20]=>\c musicdb20dump
Password for user user20: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb20dump" as user "user20".
user20@local:/opt/huawei/tmp [musicdb20dump]=>\d
                         List of relations
 Schema | Name | Type  | Owner  |             Storage              
--------+------+-------+--------+----------------------------------
 public | t1   | table | user20 | {orientation=row,compression=no}
 public | t2   | table | user20 | {orientation=row,compression=no}
 public | t3   | table | user20 | {orientation=row,compression=no}
 public | t4   | table | user20 | {orientation=row,compression=no}
(4 rows)