openGauss每日一练第20天 | openGauss数据库的逻辑备份与恢复

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

## 创建备份目录omm@modb:~$ mkdir /var/lib/opengauss/backupomm@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.
## 创建备份使用的用户omm=# create user bp_user identified by 'abcd@123' sysadmin;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
## 创建备份使用的数据库omm=# create database bp_db;  
CREATE DATABASE
## 在omm数据库中创建表omm=# create table t1 (id int);
 CREATE TABLE
omm=# create table t2(id int);
CREATE TABLE
omm=# \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)
##使用gs_dump备份omm@modb:~$ gs_dump -U bp_user -W abcd@123 omm -F p -f /var/lib/opengauss/backup/backup.sql
gs_dump[port='5432'][omm][2022-12-13 11:06:45]: The total objects number is 413.
gs_dump[port='5432'][omm][2022-12-13 11:06:45]: [100.00%] 413 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:06:45]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:06:45]: total time: 1498  ms
omm@modb:~$ ls /var/lib/opengauss/backup/backup.sql
/var/lib/opengauss/backup/backup.sql
## 逻辑恢复omm@modb:~$ gsql -d bp_db -U bp_user -W abcd@123 -f /var/lib/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
REVOKE
REVOKE
GRANT
GRANT
total time: 20  ms
## 恢复验证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:~$ gsql -d bp_db -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)

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

## 创建新的备份数据库omm=# create database bp_db_new;
CREATE DATABASE## 创建新表
omm=# create table t3(id int);
CREATE TABLEomm=# \q## 使用gs_dump备份
omm@modb:~$ gs_dump -U bp_user -W abcd@123 omm -F p -f /var/lib/opengauss/backup/backup.dump
gs_dump[port='5432'][omm][2022-12-13 11:17:24]: The total objects number is 415.
gs_dump[port='5432'][omm][2022-12-13 11:17:24]: [100.00%] 415 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:17:24]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:17:24]: total time: 1587  ms## 备份恢复
omm@modb:~$ gsql -d bp_db_new -U bp_user -W abcd@123 -f /var/lib/opengauss/backup/backup.dump 
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
REVOKE
REVOKE
GRANT
GRANT
total time: 31  ms
##恢复验证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}
 public | t3   | table | omm   | {orientation=row,compression=no}
(3 rows)

omm@modb:~$ gsql -d bp_db_new -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}
(3 rows)