学习目标

掌握openGauss数据库的逻辑备份和恢复技术。

课程作业

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

--omm库下创建一张表
openGauss=# \c omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# create table omm1214(name varchar);
CREATE TABLE
omm=# insert into omm1214 values('omm1214');
INSERT 0 1

--逻辑备份
[omm@og3 datafs]$   gs_dump    -p 26000  -U  user1 -W  user154321! omm  -F p -f /datafs/1214/omm.sql
gs_dump[port='26000'][omm][2022-12-14 16:08:50]: The total objects number is 429.
gs_dump[port='26000'][omm][2022-12-14 16:08:50]: [100.00%] 429 objects have been dumped.
gs_dump[port='26000'][omm][2022-12-14 16:08:50]: dump database omm successfully
gs_dump[port='26000'][omm][2022-12-14 16:08:50]: total time: 3969  ms
[omm@og3 datafs]$

--创建数据库omm1
openGauss=# create database omm1;
CREATE DATABASE
openGauss=# alter database omm1 owner to user1;
ALTER DATABASE
openGauss=# grant all privileges on database omm1 to user1;
GRANT



--恢复到omm1中
[omm@og3 datafs]$  gsql -d omm1   -U user1  -W  user154321!  -p 26000 -f /datafs/1214/omm.sql
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 738  ms

--验证恢复的表已恢复
openGauss=# \c omm1
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm1" as user "omm".
omm1=# select * from omm1214;
  name
---------
 omm1214
(1 row)

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

--dump格式备份
[omm@og3 datafs]$ gs_dump -U user1 -p 26000 -W user154321!  omm -F p -f /datafs/1214/omm.dump
gs_dump[port='26000'][omm][2022-12-14 16:14:47]: The total objects number is 429.
gs_dump[port='26000'][omm][2022-12-14 16:14:47]: [100.00%] 429 objects have been dumped.
gs_dump[port='26000'][omm][2022-12-14 16:14:47]: dump database omm successfully
gs_dump[port='26000'][omm][2022-12-14 16:14:47]: total time: 2934  ms
[omm@og3 datafs]$

--创建omm2库
openGauss=# create database omm2;
CREATE DATABASE
openGauss=# alter database omm2 owner to user1;
ALTER DATABASE
openGauss=# grant all privileges on database omm2 to user1;
GRANT


--恢复
[omm@og3 datafs]$  gsql -d omm2  -p 26000 -U user1  -W user154321! -f /datafs/1214/omm.dump
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 43  ms
--验证
openGauss=# \c omm2
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm2" as user "omm".
omm2=# select * from omm1214;
  name
---------
 omm1214
(1 row)