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

逻辑备份，使用gs_dump工具来备份。如果按文本类型备份成sql脚本，可以使用gsql客户端工具来执行sql脚本作恢复。gs_restore是openGauss提供的针对gs_dump导出数据的导入工具。

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

su - omm

--磁盘上新建备份目录
omm@modb:~$ mkdir /var/lib/opengauss/backup

--准备工作
gsql -r
omm=# create user test identified by 'huawei@1234' sysadmin;
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

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

omm=# CREATE DATABASE testdb WITH TABLESPACE = test_tbs;
CREATE DATABASE

omm=# CREATE TABLE test1(col int);
omm=# CREATE TABLE

omm=# CREATE TABLE test2(col int);
CREATE TABLE
omm=# \q

--可以看到，在omm数据库上已创建了2张表
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:~$ \q

--逻辑备份
--使用test用户，备份数据库omm，其中-F参数值p表示按文本类型备份，-f参数指定备份文件名称
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 18:11:05]: The total objects number is 413.
gs_dump[port='5432'][omm][2022-12-13 18:11:05]: [100.00%] 413 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 18:11:05]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 18:11:05]: total time: 1677  ms

--看到了备份的文件backup.sql
omm@modb:~$ ls /var/lib/opengauss/backup           
backup.sql

--查看备份文件大小
omm@modb:~$ du -sh /var/lib/opengauss/backup/backup.sql
4.0K    /var/lib/opengauss/backup/backup.sql

--备份的sql脚本文件，可以查看，里面就是一些sql语句等
cat /var/lib/opengauss/backup/backup.sql

--逻辑恢复
--使用用户test，执行用gs_dump生成的sql脚本，将backup.sql数据恢复到testdb数据库中
--使用gsql客户端工具，直接执行备份的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: 22  ms

--登录testdb，可以看到testdb已经有test1、test2两张表了
omm@modb:~$ gsql -d testdb -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.

testdb=# \d
                         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)

2、逻辑备份和恢复案例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 test3(col int);
CREATE TABLE

omm=> CREATE TABLE test4(col int);
CREATE TABLE

--查看omm数据库，有4个表
omm=# \d
 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=> \q


--使用test用户，备份omm数据库，生成归档格式的备份文件。-F参数值c表示导出自定义归档格式
omm@modb:~$ gs_dump -U test -W huawei@1234 omm -F c -f /var/lib/opengauss/backup/backup.dump
gs_dump[port='5432'][omm][2022-12-13 18:50:04]: The total objects number is 421.
gs_dump[port='5432'][omm][2022-12-13 18:50:04]: [100.00%] 421 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 18:50:04]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 18:50:04]: total time: 1528  ms
omm@modb:~$ 

omm@modb:~$ du -sh /var/lib/opengauss/backup/backup.dump 
4.0K    /var/lib/opengauss/backup/backup.dump


--逻辑恢复
gsql -r
\c testdb
testdb=# drop database omm;

--删除omm数据库
testdb=# DROP DATABASE
testdb=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 testdb    | omm   | UTF8     | C       | C     | 
(4 rows)

--使用gs_dump备份的omm数据库文件backup.dump来恢复omm数据库
--使用gs_restore工具来执行gs_dump备份的文件，来执行恢复
--其中-d表示登录数据库；-c表示先删除，在重新创建数据库对象前，清理(删除)已存在的目标数据库对象；-C表示创建；-v表示显示执行过程；-F表示备份的文件
omm@modb:~$ gs_restore -d postgres -c -C -v -F c /var/lib/opengauss/backup/backup.dump
connecting to database for restore
dropping DATABASE "omm"
start restore operation ...
creating DATABASE "omm"
connecting to new database "omm"
connecting to database "omm" as user "omm"
creating SCHEMA "public"
creating COMMENT "SCHEMA public"
creating SCHEMA "test"
creating TABLE "public.test1"
creating TABLE "public.test2"
creating TABLE "public.test3"
creating TABLE "public.test4"
creating TABLE "test.test3"
creating TABLE "test.test4"
restoring data for table "public.test1"
table test1 complete data imported !
restoring data for table "public.test2"
table test2 complete data imported !
restoring data for table "public.test3"
table test3 complete data imported !
restoring data for table "public.test4"
table test4 complete data imported !
restoring data for table "test.test3"
table test3 complete data imported !
restoring data for table "test.test4"
table test4 complete data imported !
Finish reading 19 SQL statements!
end restore operation ...
setting owner and privileges for DATABASE "omm"
setting owner and privileges for COMMENT "SCHEMA public"
setting owner and privileges for ACL "public"
setting owner and privileges for SCHEMA "public"
setting owner and privileges for SCHEMA "test"
setting owner and privileges for TABLE "public.test1"
setting owner and privileges for TABLE "public.test2"
setting owner and privileges for TABLE "public.test3"
setting owner and privileges for TABLE "public.test4"
setting owner and privileges for TABLE "test.test3"
setting owner and privileges for TABLE "test.test4"
setting owner and privileges for TABLE DATA "public.test1"
setting owner and privileges for TABLE DATA "public.test2"
setting owner and privileges for TABLE DATA "public.test3"
setting owner and privileges for TABLE DATA "public.test4"
setting owner and privileges for TABLE DATA "test.test3"
setting owner and privileges for TABLE DATA "test.test4"
restore operation successful
total time: 1483  ms

--omm数据库已恢复，可以查看到4张表
omm=# \d
                         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=#