openGauss每日一练第20天

openGauss的逻辑备份和恢复

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

--创建测试用户
omm=# create user test identified by 'kunpeng@1234' sysadmin;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

--创建测试表空间
omm=# create tablespace  test_tbs relative location 'tablespace/test_tbs01';
CREATE TABLESPACE

--创建测试数据库
omm=# create database testdb with tablespace  = test_tbs;
omm=# CREATE DATABASE

--在OMM数据库创建测试表和数据
omm=# create table  testtab1(c1 int);
CREATE TABLE
omm=# create table testtab2(c1 int);
CREATE TABLE
omm=# insert into testtab1 values(1),(2);
INSERT 0 2
omm=# insert into testtab2 values(3),(4);
INSERT 0 2

--使用gs_dump进行数据库逻辑备份（SQL文件样式）
omm@modb:~$ gs_dump -U test -W kunpeng@1234 omm -F p -f /var/lib/opengauss/backup/omm.bk.sql
gs_dump[port='5432'][omm][2022-12-13 13:18:29]: The total objects number is 413.
gs_dump[port='5432'][omm][2022-12-13 13:18:29]: [100.00%] 413 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 13:18:29]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 13:18:29]: total time: 1501  ms

--使用备份的文件蒋数据库恢复到testdb数据库中
omm@modb:~$ gsql  -U test -W kunpeng@1234 testdb -f /var/lib/opengauss/backup/omm.bk.sql 
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 18  ms

--连接testdb查看恢复的表和数据
testdb=# \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | testtab1 | table | omm   | {orientation=row,compression=no}
 public | testtab2 | table | omm   | {orientation=row,compression=no}
(2 rows)

testdb=# select * from testtab1;
(2 rows)

testdb=#  c1 
----
  1
  2
select * from testtab2;
 c1 
----
  3
  4
(2 rows)

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

--连接Omm数据库创建测试表和数据
omm=# create table testtab3 (c1 varchar(30));
CREATE TABLE
omm=# create table testtab4 (c2 varchar(30));
omm=# CREATE TABLE

omm=# insert into testtab3 values ('Hello openGauss !');
INSERT 0 1
omm=# insert into testtab4 values ('Hello postgres !');
INSERT 0 1
omm=# \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | testtab1 | table | omm   | {orientation=row,compression=no}
 public | testtab2 | table | omm   | {orientation=row,compression=no}
 public | testtab3 | table | omm   | {orientation=row,compression=no}
 public | testtab4 | table | omm   | {orientation=row,compression=no}
(4 rows)


--使用gs_dump进行数据库的备份（dump方式）
omm@modb:~$ gs_dump -U test -W kunpeng@1234 omm -F p -f /var/lib/opengauss/backup/omm.bk.dump
gs_dump[port='5432'][omm][2022-12-13 13:24:23]: The total objects number is 417.
gs_dump[port='5432'][omm][2022-12-13 13:24:23]: [100.00%] 417 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 13:24:23]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 13:24:23]: total time: 1454  ms

--使用dump将备份的数据恢复到testdb
omm@modb:~$ gsql -U test -W kunpeng@1234 testdb -f /var/lib/opengauss/backup/omm.bk.dump 
SET
SET
SET
SET
SET
SET
gsql:/var/lib/opengauss/backup/omm.bk.dump:16: ERROR:  schema "test" already exists
ALTER SCHEMA
SET
SET
SET
gsql:/var/lib/opengauss/backup/omm.bk.dump:34: ERROR:  relation "testtab1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup/omm.bk.dump:46: ERROR:  relation "testtab2" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 30  ms


--连接testdb数据库验证恢复的数据
omm=# \c testdb 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "omm".
testdb=# \dt
                          List of relations
 Schema |   Name   | Type  | Owner |             Storage              
--------+----------+-------+-------+----------------------------------
 public | testtab1 | table | omm   | {orientation=row,compression=no}
 public | testtab2 | table | omm   | {orientation=row,compression=no}
 public | testtab3 | table | omm   | {orientation=row,compression=no}
 public | testtab4 | table | omm   | {orientation=row,compression=no}
(4 rows)

testdb=# select * from testtab3;
        c1         
-------------------
 Hello openGauss !
(1 row)

testdb=# select * from testtab4;
        c2        
------------------
 Hello postgres !
(1 row)