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

打卡第二十天,数据库的逻辑备份和恢复

opengauss 可使用gs_dump、gs_restore命令进行数据库数据的导出还原。

如导出纯文本格式用gsql执行相关备份文件即可。本节导出的dmp文件格式也是纯文本，个人认为使用参数-F c 是否更合理，导出二进制文件。

类似oracle的exp、imp或数据泵功能。

https://docs.opengauss.org/zh/docs/3.0.0-lite/docs/Administratorguide/gs_dump.html

课程作业

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

omm@modb:~$ gs_dump  omm -F p -f /var/lib/opengauss/backup/backup_omm.sql
gs_dump[port='5432'][omm][2022-12-14 07:33:01]: The total objects number is 417.
gs_dump[port='5432'][omm][2022-12-14 07:33:01]: [100.00%] 417 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-14 07:33:01]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-14 07:33:01]: total time: 1198  ms
omm@modb:~$ gsql -d omm  -f /var/lib/opengauss/backup/backup_omm.sql
SET
SET
SET
SET
SET
SET
gsql:/var/lib/opengauss/backup/backup_omm.sql:16: ERROR:  schema "test" already exists
ALTER SCHEMA
SET
SET
SET
gsql:/var/lib/opengauss/backup/backup_omm.sql: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_omm.sql:46: 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/backup/backup_omm.sql:58: ERROR:  relation "test3" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup/backup_omm.sql:70: ERROR:  relation "test4" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 16  ms
omm@modb:~$ 

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

omm@modb:~$ gs_dump  omm -F p -f /var/lib/opengauss/backup/backup_omm.dump
gs_dump[port='5432'][omm][2022-12-14 07:35:01]: The total objects number is 417.
gs_dump[port='5432'][omm][2022-12-14 07:35:01]: [100.00%] 417 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-14 07:35:01]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-14 07:35:01]: total time: 1422  ms
omm@modb:~$ gsql -d omm  -f /var/lib/opengauss/backup/backup_omm.dump
SET
SET
SET
SET
SET
SET
gsql:/var/lib/opengauss/backup/backup_omm.dump:16: ERROR:  schema "test" already exists
ALTER SCHEMA
SET
SET
SET
gsql:/var/lib/opengauss/backup/backup_omm.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_omm.dump:46: 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/backup/backup_omm.dump:58: ERROR:  relation "test3" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
gsql:/var/lib/opengauss/backup/backup_omm.dump:70: ERROR:  relation "test4" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 18  ms
omm@modb:~$