第20天：openGauss数据库的逻辑备份和恢复技术

原创 huiwenshu 2022-12-13

168

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

omm=# create user bakuser identified by 'qaz_hc123' sysadmin;
CREATE ROLE
omm=# \q
omm@911d320f8566:~$ gs_dump -Ubakuser -Wqaz_hc123 -d omm -f /home/omm/omm1213.sql
gs_dump: invalid option -- 'd'
Try "gs_dump --help" for more information.
omm@911d320f8566:~$  gs_dump -Ubakuser-Wqaz_hc123  omm -F p -f /home/omm/omm1213.sql
Password: 
omm@911d320f8566:~$ gs_dump -Ubakuser -Wqaz_hc123 -d omm -f /home/omm/omm1213.sql
gs_dump: invalid option -- 'd'
Try "gs_dump --help" for more information.
omm@911d320f8566:~$ gs_dump -Ubakuser -Wqaz_hc123  omm -F p -f /home/omm/omm1213.sql  
gs_dump[port='5432'][omm][2022-12-13 14:28:57]: The total objects number is 469.
gs_dump[port='5432'][omm][2022-12-13 14:28:57]: [100.00%] 469 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 14:28:57]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 14:28:57]: total time: 1181  msomm=# create tablespace testdb_tbs02 relative location 'tablespace/testdb_tbs02';
CREATE TABLESPACE
omm=# create database testdb with tablespace testdb02_tbs02;
ERROR:  tablespace "testdb02_tbs02" does not exist
omm=# create database testdb with tablespace testdb_tbs02;  
CREATE DATABASE
omm=# alter database testdb owner to hc;
ALTER DATABASE
omm=# quit
omm-# \q
omm@911d320f8566:~$ gsql -d omm    -c "\dt"
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage              
--------+---------------+-------+-------+----------------------------------
 public | tab_1213      | table | omm   | {orientation=row,compression=no}
 public | tab_crm1      | table | omm   | {orientation=row,compression=no}
 public | tab_newtest1  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest2  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest3  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest4  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest41 | table | omm   | {orientation=row,compression=no}
 public | tab_newtest42 | table | omm   | {orientation=row,compression=no}
 public | tab_t1        | table | omm   | {orientation=row,compression=no}
 public | tab_t2        | table | omm   | {orientation=row,compression=no}
 public | tab_test1     | table | omm   | {orientation=row,compression=no}
 public | test          | table | omm   | {orientation=row,compression=no}
(12 rows)

omm@911d320f8566:~$ gsql -d testdb   -Uhc -Wqaz_hc123 -f /home/omm/
.bash_logout  .bashrc       .profile      bak/          omm1213.sql   pg_log/       
omm@911d320f8566:~$ gsql -d testdb   -Uhc -Wqaz_hc123 -f /home/omm/omm1213.sql 
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
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
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
SET
CREATE TABLE
ALTER TABLE
SET
ALTER TABLE
 setval 
--------
      1
(1 row)

SET
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 335  ms
omm@911d320f8566:~$ gsql -d omm    -c "\dt"
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage              
--------+---------------+-------+-------+----------------------------------
 public | tab_1213      | table | omm   | {orientation=row,compression=no}
 public | tab_crm1      | table | omm   | {orientation=row,compression=no}
 public | tab_newtest1  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest2  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest3  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest4  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest41 | table | omm   | {orientation=row,compression=no}
 public | tab_newtest42 | table | omm   | {orientation=row,compression=no}
 public | tab_t1        | table | omm   | {orientation=row,compression=no}
 public | tab_t2        | table | omm   | {orientation=row,compression=no}
 public | tab_test1     | table | omm   | {orientation=row,compression=no}
 public | test          | table | omm   | {orientation=row,compression=no}
(12 rows)

omm@911d320f8566:~$ gsql -d testdb    -c "\dt"   
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage              
--------+---------------+-------+-------+----------------------------------
 public | tab_1213      | table | omm   | {orientation=row,compression=no}
 public | tab_crm1      | table | omm   | {orientation=row,compression=no}
 public | tab_newtest1  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest2  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest3  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest4  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest41 | table | omm   | {orientation=row,compression=no}
 public | tab_newtest42 | table | omm   | {orientation=row,compression=no}
 public | tab_t1        | table | omm   | {orientation=row,compression=no}
 public | tab_t2        | table | omm   | {orientation=row,compression=no}
 public | tab_test1     | table | omm   | {orientation=row,compression=no}
 public | test          | table | omm   | {orientation=row,compression=no}
(12 rows)

omm@911d320f8566:~$ gsql -Uhc -Wqaz_hc123 -d testdb
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:46 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb=> select count(1) from tab_1213;   
 count 
-------
   817
(1 row)

testdb=> \q
omm@911d320f8566:~$

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

omm@911d320f8566:~$ gs_dump -Uhc -Wqaz_hc123 omm -F c -f /home/omm/omm1213.dump
gs_dump[port='5432'][omm][2022-12-13 15:21:36]: The total objects number is 469.
gs_dump[port='5432'][omm][2022-12-13 15:21:36]: [100.00%] 469 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 15:21:36]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 15:21:36]: total time: 1780  ms
omm@911d320f8566:~$ gsql -r      
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:46 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 enmdb     | hc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb1  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb1    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb3    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 testdb    | hc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 testdb10  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/omm          +
           |       |          |             |             | omm=CTc/omm
(11 rows)

omm=# create tablespace tbs_02 relative location 'tablespace/tbs02';
CREATE TABLESPACE
omm=# create database testdb1 with tablespace tbs02;
ERROR:  tablespace "tbs02" does not exist
omm=# create database testdb1 with tablespace tbs_02 owner hc;
CREATE DATABASE
omm=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 enmdb     | hc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb1  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb1    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb2    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 newdb3    | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 omm       | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 template1 | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/omm           +
           |       |          |             |             | omm=CTc/omm
 testdb    | hc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 testdb1   | hc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 testdb10  | omm   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/omm          +
           |       |          |             |             | omm=CTc/omm
(12 rows)

omm=# \q  

omm@911d320f8566:~$ gs_restore -d testdb1 -Uhc -Wqaz_hc123 -Fc  /home/omm/omm1213.dump   
start restore operation ...
table tab_1213 complete data imported !
table tab_crm1 complete data imported !
table tab_newtest1 complete data imported !
table tab_newtest2 complete data imported !
table tab_newtest3 complete data imported !
table tab_newtest4 complete data imported !
table tab_newtest41 complete data imported !
table tab_newtest42 complete data imported !
table tab_t1 complete data imported !
table tab_t2 complete data imported !
table tab_test1 complete data imported !
table test complete data imported !
table t1 complete data imported !
Finish reading 46 SQL statements!
end restore operation ...
restore operation successful
total time: 714  ms
omm@911d320f8566:~$ gsql -r -d testdb1 -c "\dt"
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage              
--------+---------------+-------+-------+----------------------------------
 public | tab_1213      | table | omm   | {orientation=row,compression=no}
 public | tab_crm1      | table | omm   | {orientation=row,compression=no}
 public | tab_newtest1  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest2  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest3  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest4  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest41 | table | omm   | {orientation=row,compression=no}
 public | tab_newtest42 | table | omm   | {orientation=row,compression=no}
 public | tab_t1        | table | omm   | {orientation=row,compression=no}
 public | tab_t2        | table | omm   | {orientation=row,compression=no}
 public | tab_test1     | table | omm   | {orientation=row,compression=no}
 public | test          | table | omm   | {orientation=row,compression=no}
(12 rows)

omm@911d320f8566:~$ gsql -r -d omm -c "\dt"       
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage              
--------+---------------+-------+-------+----------------------------------
 public | tab_1213      | table | omm   | {orientation=row,compression=no}
 public | tab_crm1      | table | omm   | {orientation=row,compression=no}
 public | tab_newtest1  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest2  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest3  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest4  | table | omm   | {orientation=row,compression=no}
 public | tab_newtest41 | table | omm   | {orientation=row,compression=no}
 public | tab_newtest42 | table | omm   | {orientation=row,compression=no}
 public | tab_t1        | table | omm   | {orientation=row,compression=no}
 public | tab_t2        | table | omm   | {orientation=row,compression=no}
 public | tab_test1     | table | omm   | {orientation=row,compression=no}
 public | test          | table | omm   | {orientation=row,compression=no}
(12 rows)

omm@911d320f8566:~$

opengauss每日一练 opengauss

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

关注作者

第20天：openGauss数据库的逻辑备份和恢复技术

评论