openGauss每日一练第20天--openGaussd的逻辑备份和恢复技术

本次课程练习opengauss数据库逻辑备份恢复的两种方式

1、逻辑备份和恢复案例1:使用sql格式进行备份和恢复omm数据库
思路是先创建了一个测试库testdb，作为备份完omm库后的恢复目标对象；
然后在omm库中建立测试用户test并授予sysadmin权限，建立test1、test2表，用户恢复后的验证；
使用test用户备份omm数据库
使用omm用户恢复备份数据库到testdb中
最后验证testdb中是否有test1、test2表。

omm=# create user test identified by 'P@ssw0rd' sysadmin;
CREATE ROLE
omm=# 
omm=# create tablespace test_tbs relative location 'tablepace/test_tbs1';
CREATE TABLESPACE
omm=# create database testdb with tablespace=test_tbs;
CREATE DATABASE
omm=# create table test1(col int);
CREATE TABLE
omm=# create table test2(col int);
CREATE TABLE
omm=# \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | customer | table | omm   | {orientation=row,compression=no}
 public | test     | table | omm   | {orientation=row,compression=no}
 public | test1    | table | omm   | {orientation=row,compression=no}
 public | test2    | table | omm   | {orientation=row,compression=no}
(4 rows)


[omm@alldb ~]$ gs_dump -U test -W P@ssw0rd omm -p15432 -F p -f /opengauss/backup/backup.sql
gs_dump[port='15432'][omm][2022-12-13 18:32:38]: The total objects number is 445.
gs_dump[port='15432'][omm][2022-12-13 18:32:39]: [100.00%] 445 objects have been dumped.
gs_dump[port='15432'][omm][2022-12-13 18:32:39]: dump database omm successfully
gs_dump[port='15432'][omm][2022-12-13 18:32:39]: total time: 5320  ms

[omm@alldb ~]$ gsql -d testdb -U test -W P@ssw0rd -p15432 -f /opengauss/backup/backup.sql 
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
 setval 
--------
      1
(1 row)

 setval 
--------
      1
(1 row)

ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
total time: 40  ms
[omm@alldb ~]$ gsql -d omm -p 15432 -c "\dt"
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | customer | table | omm   | {orientation=row,compression=no}
 public | test     | table | omm   | {orientation=row,compression=no}
 public | test1    | table | omm   | {orientation=row,compression=no}
 public | test2    | table | omm   | {orientation=row,compression=no}
(4 rows)

[omm@alldb ~]$ gsql -d testdb -U test -W P@ssw0rd  -p 15432 -c "\dt"
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | customer | table | omm   | {orientation=row,compression=no}
 public | test     | table | omm   | {orientation=row,compression=no}
 public | test1    | table | omm   | {orientation=row,compression=no}
 public | test2    | table | omm   | {orientation=row,compression=no}
(4 rows)

2、逻辑备份和恢复案例2:使用dump格式进行备份和恢复omm数据库
先创建两个测试表供恢复后验证，操作方式与导出sql文件差不多

omm=# create table test3(col int);
CREATE TABLE
omm=# create table test4(col int);
CREATE TABLE
[omm@alldb ~]$ gs_dump -U test -W P@ssw0rd omm -p15432 -F p -f /opengauss/backup/newbackup.dump
gs_dump[port='15432'][omm][2022-12-13 18:55:22]: The total objects number is 449.
gs_dump[port='15432'][omm][2022-12-13 18:55:22]: [100.00%] 449 objects have been dumped.
gs_dump[port='15432'][omm][2022-12-13 18:55:22]: dump database omm successfully
gs_dump[port='15432'][omm][2022-12-13 18:55:22]: total time: 3483  ms
[omm@alldb ~]$ 
[omm@alldb ~]$ gsql -d testdb -U test -W P@ssw0rd -p15432  -f /opengauss/backup/newbackup.dump
SET
SET
SET
SET
SET
SET
gsql:/opengauss/backup/newbackup.dump:16: ERROR:  schema "test" already exists
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 TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
 setval 
--------
      1
(1 row)

 setval 
--------
      1
(1 row)

ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
total time: 67  ms

[omm@alldb ~]$ gsql -d testdb -U test -W P@ssw0rd  -p 15432  -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb=> 
testdb=> 
testdb=> \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | customer | table | omm   | {orientation=row,compression=no}
 public | test     | 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}
(6 rows)

testdb=>