暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGuass每日一练第20天|学习心得体会

openGuass每日一练第20天|学习心得体会

原创 小严 2022-12-13
311

课程学习内容

软件版本 ：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:~$

课程学习体会

 通过本次的实训操作，掌握数据库的逻辑备份和恢复技术。

opengaussopengauss每日一练opengauss训练营
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论