学习目标

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

课程作业

0.准备

omm=# create user  test IDENTIFIED BY 'huawei@1234' sysadmin ;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE
--创建恢复测试数据库testdb
omm=# CREATE TABLESPACE test_tbs RELATIVE LOCATION 'tablespace/test_tbs1';
omm=# CREATE DATABASE testdb WITH TABLESPACE = test_tbs;
CREATE TABLESPACE

CREATE DATABASE
omm=# 
omm=# --在omm数据库上，创建测试表test1、test2：
omm=#  
omm=# CREATE TABLE test1(col int);
CREATE TABLE
omm=# CREATE TABLE test2(col int);
CREATE TABLE

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

omm@modb:~$ gs_dump -U test -W huawei@1234  omm -F p -f /home/omm/testdb.sql
gs_dump[port='5432'][omm][2022-12-13 10:19:21]: The total objects number is 415.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: [100.00%] 415 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: total time: 1496  ms

恢复

omm@modb:~$ gs_dump -U test -W huawei@1234  omm -F p -f /home/omm/testdb.sql
gs_dump[port='5432'][omm][2022-12-13 10:19:21]: The total objects number is 415.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: [100.00%] 415 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: total time: 1496  ms
omm@modb:~$ gsql -d testdb   -U test  -W huawei@1234  -f /home/omm/testdb.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
REVOKE
REVOKE
GRANT
GRANT
total time: 25  ms
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.

omm=# drop database testdb;
DROP DATABASE
omm=# \q
omm@modb:~$ gsql -d testdb   -U test  -W huawei@1234  -f /home/omm/testdb.sql
gsql: FATAL:  database "testdb" does not exist

必须建好库才能恢复
2.逻辑备份和恢复案例2:使用dump格式进行备份和恢复omm数据库

omm@modb:~$ gs_dump -U test -W huawei@1234  omm -F p -f /home/omm/testdb.dump
gs_dump[port='5432'][omm][2022-12-13 10:19:21]: The total objects number is 415.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: [100.00%] 415 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: total time: 1496  ms

恢复

omm@modb:~$ gs_dump -U test -W huawei@1234  omm -F p -f /home/omm/testdb.dump
gs_dump[port='5432'][omm][2022-12-13 10:19:21]: The total objects number is 415.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: [100.00%] 415 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 10:19:22]: total time: 1496  ms
omm@modb:~$ gsql -d testdb   -U test  -W huawei@1234  -f /home/omm/testdb.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
REVOKE
REVOKE
GRANT
GRANT
total time: 25  ms

今天的每日一练，有点不太准确的地方

两个恢复案例：
1、逻辑备份和恢复案例1（sql格式）逻辑备份，使用gs_dump备份数据库，生成sql文件：
gs_dump -U test -W huawei@1234 omm -F p -f /var/lib/opengauss/backup/backup.sql
2、逻辑备份和恢复案例2（dump格式）逻辑备份：使用gs_dump备份数据库，生成归档格式的备份文件
gs_dump -U test -W huawei@1234 omm -F p -f /var/lib/opengauss/backup/backup.dump
因为都使用了-Fp的选项，其实sql和dump都是文本格式，只是文件名不同而已