openGauss每日一练第20天-备份与恢复

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

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

进行逻辑备份：

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 14:06:51]: The total objects number is 427.
gs_dump[port='5432'][omm][2022-12-13 14:06:51]: [100.00%] 427 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 14:06:52]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 14:06:52]: total time: 1787  ms
omm@modb:~$ 

进行逻辑恢复–把数据库omm的备份恢复到数据库testdb：

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
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
SET
CREATE TABLE
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
 setval 
--------
 200002
(1 row)

 setval 
--------
      1
(1 row)

SET
SET
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 333  ms

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

omm@modb:~$ 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 14:14:35]: The total objects number is 431.
gs_dump[port='5432'][omm][2022-12-13 14:14:35]: [100.00%] 431 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 14:14:35]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 14:14:35]: total time: 1810  ms
omm@modb:~$ 

进行恢复

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
gsql:/var/lib/opengauss/backup/backup.dump:25: ERROR:  schema "tpcds" already exists
ALTER SCHEMA

......

恢复后验证

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