openGauss每日一练第20天

原创 Mr Dong.ღ 2022-12-14

249

课程学习内容

软件版本 ：openGauss 3.0.0

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

数据库实操内容

环境准备

–创建逻辑备份的存储目录：
su - omm
mkdir /var/lib/opengauss/backup

Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.

root@modb:~# 
root@modb:~# su - omm
omm@modb:~$ mkdir /var/lib/opengauss/backup
omm@modb:~$

–创建备份恢复用户，需要具有super或者sysadmin权限
gsql -r
create user test IDENTIFIED BY ‘huawei@1234’ sysadmin ;

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=# create user  test IDENTIFIED BY 'huawei@1234' sysadmin ;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#

–创建恢复测试数据库testdb
CREATE TABLESPACE test_tbs RELATIVE LOCATION ‘tablespace/test_tbs1’;
CREATE DATABASE testdb WITH TABLESPACE = test_tbs;

omm=# CREATE TABLESPACE test_tbs RELATIVE LOCATION 'tablespace/test_tbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE testdb WITH TABLESPACE = test_tbs;
CREATE DATABASE
omm=#

–在omm数据库上，创建测试表test1、test2：
CREATE TABLE test1(col int);
CREATE TABLE test2(col int);
\q

omm=# CREATE TABLE test1(col int);
CREATE TABLE
omm=# CREATE TABLE test2(col int);
CREATE TABLE
omm=# \q
omm@modb:~$

–查看数据
gsql -d omm -c “\dt”

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

omm@modb:~$

逻辑备份和恢复案例1（sql格式）

逻辑备份，使用gs_dump备份数据库，生成sql文件：

–使用test用户，备份数据库omm：
gs_dump -U test -W huawei@1234 omm -F p -f /var/lib/opengauss/backup/backup.sql

omm@modb:~$ gs_dump -U test -W huawei@1234  omm -F p -f /var/lib/opengauss/backup/backup.sql
gs_dump[port='5432'][omm][2022-12-13 11:05:38]: The total objects number is 413.
gs_dump[port='5432'][omm][2022-12-13 11:05:38]: [100.00%] 413 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:05:38]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:05:38]: total time: 1603  ms
omm@modb:~$

逻辑恢复：
–使用用户test，执行用gs_dump生成的sql脚本，将数据恢复到testdb数据库中：
gsql -d testdb -U test -W huawei@1234 -f /var/lib/opengauss/backup/backup.sql

omm@modb:~$   gsql -d testdb   -U test  -W huawei@1234  -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: 21  ms
omm@modb:~$

恢复验证：
–验证数据库omm的备份已经被恢复到数据库testdb：
–源库（备份的数据库）：
gsql -d omm -c “\dt”

omm@modb:~$ gsql -d omm    -c "\dt"
 public | test2 | table | omm   | {orientation=row,compression=no}
(2 rows)

                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | test1 | table | omm   | {orientation=row,compression=no}
omm@modb:~$

–新库（恢复的数据库）：
gsql -d testdb -U test -W huawei@1234 -c “\dt”

omm@modb:~$ gsql -d omm    -c "\dt"
 public | test2 | table | omm   | {orientation=row,compression=no}
(2 rows)

                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | test1 | table | omm   | {orientation=row,compression=no}
omm@modb:~$ gsql -d testdb   -U test   -W huawei@1234 -c "\dt"
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 public | test1 | table | omm   | {orientation=row,compression=no}
 public | test2 | table | omm   | {orientation=row,compression=no}
(2 rows)

omm@modb:~$

openGauss数据库逻辑备份和恢复案例2（dump格式）

逻辑备份：使用gs_dump备份数据库，生成归档格式的备份文件

–创建测试数据
gsql -r
CREATE TABLE test3(col int);
CREATE TABLE test4(col int);
\q

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=# CREATE TABLE test3(col int);
CREATE TABLE
omm=#   CREATE TABLE test4(col int);
CREATE TABLE
omm=# \q
omm@modb:~$

–使用test用户，备份omm数据库，生成归档格式的备份文件:
gs_dump -U test -W huawei@1234 omm -F p -f /var/lib/opengauss/backup/backup.dump

omm@modb:~$ gs_dump -U test -W huawei@1234  omm -F p -f /var/lib/opengauss/backup/backup.dump
gs_dump[port='5432'][omm][2022-12-13 11:07:17]: The total objects number is 417.
gs_dump[port='5432'][omm][2022-12-13 11:07:17]: [100.00%] 417 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:07:17]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:07:17]: total time: 1613  ms
omm@modb:~$ 
omm@modb:~$

逻辑恢复：

–使用gs_dump生成的归档文件恢复数据库
gsql -d testdb -U test -W huawei@1234 -f /var/lib/opengauss/backup/backup.dump

omm@modb:~$  gsql -d testdb   -U test  -W huawei@1234  -f /var/lib/opengauss/backup/backup.dump
SET
SET
SET
SET
SET
SET
gsql:/var/lib/opengauss/backup/backup.dump:16: ERROR:  schema "test" already exists
ALTER SCHEMA
SET
SET
SET
gsql:/var/lib/opengauss/backup/backup.dump:34: ERROR:  relation "test1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup/backup.dump:46: ERROR:  relation "test2" 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: 19  ms
omm@modb:~$

恢复验证：

–源库（备份的数据库）：
gsql -d omm -c “\dt”

omm@modb:~$ gsql -d omm    -c "\dt"
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 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}
(4 rows)

omm@modb:~$

–新库（恢复的数据库）：
gsql -d testdb -U test -W huawei@1234 -c “\dt”

omm@modb:~$ gsql -d testdb   -U test   -W huawei@1234 -c "\dt"
                         List of relations
 Schema | Name  | Type  | Owner |             Storage              
--------+-------+-------+-------+----------------------------------
 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}
(4 rows)

omm@modb:~$

课程作业

逻辑备份和恢复案例1:

使用sql格式进行备份和恢复omm数据库

mkdir /var/lib/opengauss/backup1
create user  admin IDENTIFIED BY 'huawei@1234' sysadmin ;
CREATE TABLESPACE test_tbs10 RELATIVE LOCATION 'tablespace/test_tbs10';
CREATE DATABASE testdb10 WITH TABLESPACE = test_tbs;

CREATE TABLE test11(col int);
CREATE TABLE test22(col int);内容如下：
omm@modb:~$ mkdir /var/lib/opengauss/backup1
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=# create user  admin IDENTIFIED BY 'huawei@1234' sysadmin ;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE TABLESPACE test_tbs10 RELATIVE LOCATION 'tablespace/test_tbs10';
CREATE TABLESPACE
omm=# CREATE DATABASE testdb10 WITH TABLESPACE = test_tbs;
CREATE DATABASE
omm=# 
omm=# CREATE TABLE test11(col int);
CREATE TABLE
omm=# CREATE TABLE test22(col int);
CREATE TABLE
omm=# \q
omm@modb:~$ 

--使用admin用户，备份数据库omm：
omm@modb:~$ gs_dump -U admin -W huawei@1234  omm -F p -f /var/lib/opengauss/backup1/backup.sql
gs_dump[port='5432'][omm][2022-12-13 11:13:49]: The total objects number is 422.
gs_dump[port='5432'][omm][2022-12-13 11:13:49]: [100.00%] 422 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:13:49]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:13:49]: total time: 1582  ms
omm@modb:~$ 
逻辑恢复：
--使用用户admin，执行用gs_dump生成的sql脚本，将数据恢复到testdb10数据库中：
omm@modb:~$ gsql -d testdb10   -U admin  -W huawei@1234  -f /var/lib/opengauss/backup1/backup.sql
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
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
REVOKE
REVOKE
GRANT
GRANT
total time: 41  ms
omm@modb:~$ 
--验证数据库omm的备份已经被恢复到数据库testdb10：
--源库（备份的数据库）：
omm@modb:~$ gsql -d omm    -c "\dt"
                         List of relations
 Schema |  Name  | Type  | Owner |             Storage              
--------+--------+-------+-------+----------------------------------
 public | test1  | table | omm   | {orientation=row,compression=no}
 public | test11 | table | omm   | {orientation=row,compression=no}
 public | test2  | table | omm   | {orientation=row,compression=no}
 public | test22 | table | omm   | {orientation=row,compression=no}
 public | test3  | table | omm   | {orientation=row,compression=no}
 public | test4  | table | omm   | {orientation=row,compression=no}
(6 rows)

omm@modb:~$ 
--新库（恢复的数据库）：
omm@modb:~$ gsql -d testdb10   -U admin   -W huawei@1234 -c "\dt"
                         List of relations
 Schema |  Name  | Type  | Owner |             Storage              
--------+--------+-------+-------+----------------------------------
 public | test1  | table | omm   | {orientation=row,compression=no}
 public | test11 | table | omm   | {orientation=row,compression=no}
 public | test2  | table | omm   | {orientation=row,compression=no}
 public | test22 | table | omm   | {orientation=row,compression=no}
 public | test3  | table | omm   | {orientation=row,compression=no}
 public | test4  | table | omm   | {orientation=row,compression=no}
(6 rows)

omm@modb:~$

逻辑备份和恢复案例2:

使用dump格式进行备份和恢复omm数据库

---逻辑备份：使用gs_dump备份数据库，生成归档格式的备份文件
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=# CREATE TABLE test33(col int);
CREATE TABLE
omm=# CREATE TABLE test44(col int);
CREATE TABLE
omm=# \q
--使用admin用户，备份omm数据库，生成归档格式的备份文件:
omm@modb:~$ gs_dump -U admin -W huawei@1234  omm -F p -f /var/lib/opengauss/backup1/backup.dump
gs_dump[port='5432'][omm][2022-12-13 11:17:51]: The total objects number is 426.
gs_dump[port='5432'][omm][2022-12-13 11:17:51]: [100.00%] 426 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 11:17:51]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 11:17:51]: total time: 1711  ms
omm@modb:~$ 
逻辑恢复：
--使用gs_dump生成的归档文件恢复数据库
omm@modb:~$ gsql -d testdb10   -U admin  -W huawei@1234  -f /var/lib/opengauss/backup1/backup.dump
SET
SET
SET
SET
SET
SET
gsql:/var/lib/opengauss/backup1/backup.dump:16: ERROR:  schema "admin" already exists
ALTER SCHEMA
gsql:/var/lib/opengauss/backup1/backup.dump:25: ERROR:  schema "test" already exists
ALTER SCHEMA
SET
SET
SET
gsql:/var/lib/opengauss/backup1/backup.dump:43: ERROR:  relation "test1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup1/backup.dump:55: ERROR:  relation "test11" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup1/backup.dump:67: ERROR:  relation "test2" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup1/backup.dump:79: ERROR:  relation "test22" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup1/backup.dump:91: ERROR:  relation "test3" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
CREATE TABLE
ALTER TABLE
gsql:/var/lib/opengauss/backup1/backup.dump:115: ERROR:  relation "test4" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 31  ms
omm@modb:~$ 
--源库（备份的数据库）：
omm@modb:~$ gsql -d omm    -c "\dt"
                         List of relations
 Schema |  Name  | Type  | Owner |             Storage              
--------+--------+-------+-------+----------------------------------
 public | test1  | table | omm   | {orientation=row,compression=no}
 public | test11 | table | omm   | {orientation=row,compression=no}
 public | test2  | table | omm   | {orientation=row,compression=no}
 public | test22 | table | omm   | {orientation=row,compression=no}
 public | test3  | table | omm   | {orientation=row,compression=no}
 public | test33 | table | omm   | {orientation=row,compression=no}
 public | test4  | table | omm   | {orientation=row,compression=no}
 public | test44 | table | omm   | {orientation=row,compression=no}
(8 rows)

omm@modb:~$ 
--新库（恢复的数据库）：
omm@modb:~$ gsql -d testdb10   -U admin   -W huawei@1234 -c "\dt"
 public | test1  | table | omm   | {orientation=row,compression=no}
 public | test11 | table | omm   | {orientation=row,compression=no}
 public | test2  | table | omm   | {orientation=row,compression=no}
 public | test22 | table | omm   | {orientation=row,compression=no}
 public | test3  | table | omm   | {orientation=row,compression=no}
 public | test33 | table | omm   | {orientation=row,compression=no}
 public | test4  | table | omm   | {orientation=row,compression=no}
 public | test44 | table | omm   | {orientation=row,compression=no}
(8 rows)

                         List of relations
 Schema |  Name  | Type  | Owner |             Storage              
--------+--------+-------+-------+----------------------------------
omm@modb:~$

课程学习体会

通过第20天的培训学习，初步了解和掌握了openGauss逻辑备份与恢复的两种实现方式（使用gs_dump和gsql 方式备份和恢复数据库）

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者