openGauss每日一练第20天

学习目标：opengauss逻辑备份恢复

1.首先利用gsql -r登录opengauss数据库

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

2.创建表空间，数据库testdb。创建具有sysadmin权限的用户用于备份恢复，并且在omm数据库下创建两张测试表。

omm=# create tablespace testtbs relative location 'tablespace/testtbs';
CREATE TABLESPACE
omm=# create database testdb with tablespace=testtbs;
omm=# CREATE DATABASE
create user test identified by 'Dongyunyun1!' sysadmin;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create table t1(id int);
CREATE TABLE
omm=# create table t2 (id int);
CREATE TABLE

3.逻辑备份数据库omm（sql）

omm@modb:~$ gsql -d omm -c '\dt'
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t1   | table | omm   | {orientation=row,compression=no}
 public | t2   | table | omm   | {orientation=row,compression=no}
(2 rows)

omm@modb:~$ gs_dump -U test -W Dongyunyun1! omm -F p -f /tmp/ommback.sql
gs_dump[port='5432'][omm][2022-12-13 10:58:15]: The total objects number is 413.
gs_dump[port='5432'][omm][2022-12-13 10:58:15]: [100.00%] 413 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 10:58:15]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 10:58:15]: total time: 1642  ms

4.逻辑恢复数据至testdb数据库

omm@modb:~$ gsql -U test -W Dongyunyun1! -d testdb -f /tmp/ommback.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: 22  ms
omm@modb:~$ gsql -d testdb -U test -W Dongyunyun1! -c '\dt';
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t1   | table | omm   | {orientation=row,compression=no}
 public | t2   | table | omm   | {orientation=row,compression=no}
(2 rows)

5.备份数据库omm（dump格式）并恢复验证

omm=# create table t3(id int);
CREATE TABLE
omm=# create table t4(id int);
CREATE TABLE
omm=# \q
omm@modb:~$ gs_dump -U test -W Dongyunyun1! omm -F p -f /tmp/ommbak.dump
gs_dump[port='5432'][omm][2022-12-13 11:03:39]: The total objects number is 417.
gs_dump[port='5432'][omm][2022-12-13 11:03:39]: [100.00%] 417 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:03:39]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:03:39]: total time: 1433  ms
omm@modb:~$ gsql -d testdb -U test -W Dongyunyun1! -f /tmp/ommbak.dump
SET
SET
SET
SET
SET
SET
gsql:/tmp/ommbak.dump:16: ERROR:  schema "test" already exists
ALTER SCHEMA
SET
SET
SET
gsql:/tmp/ommbak.dump:34: ERROR:  relation "t1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/tmp/ommbak.dump:46: ERROR:  relation "t2" 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: 23  ms
omm@modb:~$ gsql -d testdb -U test -W Dongyunyun1! -c '\dt';
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t1   | table | omm   | {orientation=row,compression=no}
 public | t2   | table | omm   | {orientation=row,compression=no}
 public | t3   | table | omm   | {orientation=row,compression=no}
 public | t4   | table | omm   | {orientation=row,compression=no}
(4 rows)

omm@modb:~$