权限要求：需要具有super或者sysadmin权限
1 逻辑备份
gs_dump工具在进行数据导出时，其他用户可以访问openGauss数据库(读或写)。
gs_dump工具支持导出完整一致的数据。例如，T1时刻启动gs_dump导出A数据库， 那么导出数据结果将会是T1时刻A数据库的数据状态，T1时刻之后对A数据库的修改不 会被导出。
1.1 sql格式
使用gs_dump备份数据库，生成sql文件：
-F, --format=c|d|t|p
– p|plain:输出一个文本SQL脚本文件(默认)。
-Z, --compress=0-9
指定使用的压缩比级别。
取值范围:0~9
– 0表示无压缩。
– 1表示压缩比最小，处理速度最快。
– 9表示压缩比最大，处理速度最慢。
[omm@node1 ~]$ gs_dump -p26000 -U test -W kunpeng@1234 postgres -F p -f /home/omm/backup/backup_postgres.sql
gs_dump[port='26000'][postgres][2022-12-13 10:55:02]: The total objects number is 435.
gs_dump[port='26000'][postgres][2022-12-13 10:55:02]: [100.00%] 435 objects have been dumped.
gs_dump[port='26000'][postgres][2022-12-13 10:55:02]: dump database postgres successfully
gs_dump[port='26000'][postgres][2022-12-13 10:55:02]: total time: 4087 ms
[omm@node1 ~]$
[omm@node1 backup]$ ls -lrt
total 4
-rw------- 1 omm dbgrp 2017 Dec 13 10:55 backup_postgres.sql
[omm@node1 backup]$
gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F p -f /home/omm/backup/backup_zzzdb.sql
[omm@node1 backup]$ gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F p -f /home/omm/backup/backup_zzzdb.sql
gs_dump[port='26000'][zzzdb][2022-12-13 10:58:08]: The total objects number is 478.
gs_dump[port='26000'][zzzdb][2022-12-13 10:58:08]: [100.00%] 478 objects have been dumped.
gs_dump[port='26000'][zzzdb][2022-12-13 10:58:08]: dump database zzzdb successfully
gs_dump[port='26000'][zzzdb][2022-12-13 10:58:08]: total time: 4249 ms
[omm@node1 backup]$
1.2 逻辑恢复：
-使用用户test，执行用gs_dump生成的sql脚本，将数据恢复到testdb数据库中：
gsql -d testdb -U test -W huawei@1234 -f /var/lib/opengauss/backup/backup.sql
恢复验证：
–验证数据库omm的备份已经被恢复到数据库testdb：
–源库（备份的数据库）：
gsql -d omm -c “\dt”
–新库（恢复的数据库）：
gsql -d testdb -U test -W huawei@1234 -c “\dt”
1.3 dump格式
逻辑备份：使用gs_dump备份数据库，生成归档格式的备份文件
是 -F c 吗？
--使用test用户，备份omm数据库，生成归档格式的备份文件: -c
gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F c -f /home/omm/backup/backup.dump
[omm@node1 backup]$ gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F c -f /home/omm/backup/backup.dump
gs_dump[port='26000'][zzzdb][2022-12-13 11:12:16]: The total objects number is 478.
gs_dump[port='26000'][zzzdb][2022-12-13 11:12:16]: [100.00%] 478 objects have been dumped.
gs_dump[port='26000'][zzzdb][2022-12-13 11:12:16]: dump database zzzdb successfully
gs_dump[port='26000'][zzzdb][2022-12-13 11:12:16]: total time: 4581 ms
[omm@node1 backup]$
[omm@node1 backup]$ ls -lrt
-rw------- 1 omm dbgrp 17642 Dec 13 10:58 backup_zzzdb.sql
-rw------- 1 omm dbgrp 15000 Dec 13 11:12 backup.dump
[omm@node1 backup]$ more backup.dump
PGDMP^L
[omm@node1 backup]$ more backup.dump
PGDMP^L
--More--(1%)
LESPACE = enmtbs DBCOMPATIBILITY = 'A';
--More--(3%)
--More--(4%)
################## -t
gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F t -f /home/omm/backup/backup.tar
[omm@node1 backup]$ gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F t -f /home/omm/backup/backup.tar
gs_dump[port='26000'][zzzdb][2022-12-13 11:13:29]: The total objects number is 478.
gs_dump[port='26000'][zzzdb][2022-12-13 11:13:29]: [100.00%] 478 objects have been dumped.
gs_dump[port='26000'][zzzdb][2022-12-13 11:13:29]: dump database zzzdb successfully
gs_dump[port='26000'][zzzdb][2022-12-13 11:13:29]: total time: 3857 ms
[omm@node1 backup]$
[omm@node1 backup]$ ls -lrt
-rw------- 1 omm dbgrp 17642 Dec 13 10:58 backup_zzzdb.sql
-rw------- 1 omm dbgrp 15000 Dec 13 11:12 backup.dump
-rw------- 1 omm dbgrp 48640 Dec 13 11:13 backup.tar
[omm@node1 temp]$ tar -xvf backup.tar
toc.dat
4871.dat ----这是数据文件
4872.dat
4867.dat
4879.dat
4881.dat
4882.dat
4868.dat
4869.dat
4873.dat
4874.dat
4876.dat
4877.dat
4875.dat
restore.sql >>>>>
################## -d
gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F d -f /home/omm/backup/zzz
[omm@node1 backup]$ gs_dump -p26000 -U test -W kunpeng@1234 zzzdb -F d -f /home/omm/backup/zzz
gs_dump[port='26000'][zzzdb][2022-12-13 11:16:02]: The total objects number is 478.
gs_dump[port='26000'][zzzdb][2022-12-13 11:16:02]: [100.00%] 478 objects have been dumped.
gs_dump[port='26000'][zzzdb][2022-12-13 11:16:02]: dump database zzzdb successfully
gs_dump[port='26000'][zzzdb][2022-12-13 11:16:02]: total time: 4772 ms
[omm@node1 backup]$
[omm@node1 backup]$ ls -lrt
total 40
-rw------- 1 omm dbgrp 2017 Dec 13 10:55 backup_postgres.sql
-rw------- 1 omm dbgrp 17642 Dec 13 10:58 backup_zzzdb.sql
-rw------- 1 omm dbgrp 15000 Dec 13 11:12 backup.dump
drwx------ 2 omm dbgrp 266 Dec 13 11:14 temp
drwx------ 2 omm dbgrp 284 Dec 13 11:16 zzz
[omm@node1 backup]$ du -hs *
16K backup.dump
4.0K backup_postgres.sql
20K backup_zzzdb.sql
84K temp
68K zzz
[omm@node1 backup]$
[omm@node1 zzz]$ ls -lrt
total 68
-rw------- 1 omm dbgrp 0 Dec 13 11:15 dir.lock
-rw------- 1 omm dbgrp 13010 Dec 13 11:16 toc.dat
-rw------- 1 omm dbgrp 48 Dec 13 11:16 4872.dat.gz
-rw------- 1 omm dbgrp 48 Dec 13 11:16 4871.dat.gz
-rw------- 1 omm dbgrp 26 Dec 13 11:16 4867.dat.gz
-rw------- 1 omm dbgrp 44 Dec 13 11:16 4879.dat.gz
-rw------- 1 omm dbgrp 51 Dec 13 11:16 4881.dat.gz
-rw------- 1 omm dbgrp 1562 Dec 13 11:16 4882.dat.gz
-rw------- 1 omm dbgrp 37 Dec 13 11:16 4868.dat.gz
-rw------- 1 omm dbgrp 42 Dec 13 11:16 4869.dat.gz
-rw------- 1 omm dbgrp 35 Dec 13 11:16 4874.dat.gz
-rw------- 1 omm dbgrp 26 Dec 13 11:16 4873.dat.gz
-rw------- 1 omm dbgrp 48 Dec 13 11:16 4877.dat.gz
-rw------- 1 omm dbgrp 53 Dec 13 11:16 4876.dat.gz
-rw------- 1 omm dbgrp 39 Dec 13 11:16 4875.dat.gz
[omm@node1 zzz]$
2 逻辑还原
2.1 使用sql格式进行，恢复zzzdb数据库到 re_zzz_sql
testdb100=# create database re_zzz_sql;
CREATE DATABASE
testdb100=#
gsql -p26000 -d re_zzz_sql -U test -W kunpeng@1234 -f /home/omm/backup/backup_zzzdb.sql
[omm@node1 backup]$ gsql -p26000 -d re_zzz_sql -U test -W kunpeng@1234 -f /home/omm/backup/backup_zzzdb.sql
SET
...
GRANT
GRANT
total time: 392 ms
testdb100=# \c re_zzz_sql
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "re_zzz_sql" as user "omm".
re_zzz_sql=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------------------+-------+--------+----------------------------------
public | invoice | table | user10 | {orientation=row,compression=no}
public | newtestwithdata | table | user10 | {orientation=row,compression=no}
public | t1 | table | user10 | {orientation=row,compression=no}
public | t_seq | table | user10 | {orientation=row,compression=no}
public | t_seq_pri | table | user10 | {orientation=row,compression=no}
public | t_tab | table | user10 | {orientation=row,compression=no}
public | test001 | table | user10 | {orientation=row,compression=no}
public | test002 | table | user10 | {orientation=row,compression=no}
public | testnewwithoutdata | table | user10 | {orientation=row,compression=no}
public | zyf_col_con | table | user10 | {orientation=row,compression=no}
public | zyf_col_default | table | user10 | {orientation=row,compression=no}
public | zyf_col_null_default | table | user10 | {orientation=row,compression=no}
public | zyf_tab_con | table | user10 | {orientation=row,compression=no}
(13 rows)
2.2 使用dump格式进行，恢复zzzdb数据库到 re_zzz_dump
create database re_zzz_dump;
gs_restore -p26000 -d re_zzz_dump -U test -W kunpeng@1234 /home/omm/backup/backup.dump
[omm@node1 backup]$ gs_restore -p26000 -d re_zzz_dump -U test -W kunpeng@1234 /home/omm/backup/backup.dump
start restore operation ...
table invoice complete data imported !
table newtestwithdata complete data imported !
table t1 complete data imported !
table t_seq complete data imported !
table t_seq_pri complete data imported !
table t_tab complete data imported !
table test001 complete data imported !
table test002 complete data imported !
table testnewwithoutdata complete data imported !
table zyf_col_con complete data imported !
table zyf_col_default complete data imported !
table zyf_col_null_default complete data imported !
table zyf_tab_con complete data imported !
Finish reading 51 SQL statements!
end restore operation ...
restore operation successful
total time: 302 ms
[omm@node1 backup]$
re_zzz_sql=# \c re_zzz_dump
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "re_zzz_dump" as user "omm".
re_zzz_dump=# \dt
No relations found.
re_zzz_dump=#
re_zzz_dump=#
re_zzz_dump=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------------------+-------+--------+----------------------------------
public | invoice | table | user10 | {orientation=row,compression=no}
public | newtestwithdata | table | user10 | {orientation=row,compression=no}
public | t1 | table | user10 | {orientation=row,compression=no}
public | t_seq | table | user10 | {orientation=row,compression=no}
public | t_seq_pri | table | user10 | {orientation=row,compression=no}
public | t_tab | table | user10 | {orientation=row,compression=no}
public | test001 | table | user10 | {orientation=row,compression=no}
public | test002 | table | user10 | {orientation=row,compression=no}
public | testnewwithoutdata | table | user10 | {orientation=row,compression=no}
public | zyf_col_con | table | user10 | {orientation=row,compression=no}
public | zyf_col_default | table | user10 | {orientation=row,compression=no}
public | zyf_col_null_default | table | user10 | {orientation=row,compression=no}
public | zyf_tab_con | table | user10 | {orientation=row,compression=no}
(13 rows)
re_zzz_dump=#
2.3 还原 tar格式
create database re_zzz_tar;
gs_restore -p26000 -d re_zzz_tar -U test -W kunpeng@1234 /home/omm/backup/backup.tar
[omm@node1 backup]$ gs_restore -p26000 -d re_zzz_tar -U test -W kunpeng@1234 /home/omm/backup/backup.tar
start restore operation ...
table invoice complete data imported !
table newtestwithdata complete data imported !
table t1 complete data imported !
table t_seq complete data imported !
table t_seq_pri complete data imported !
table t_tab complete data imported !
table test001 complete data imported !
table test002 complete data imported !
table testnewwithoutdata complete data imported !
table zyf_col_con complete data imported !
table zyf_col_default complete data imported !
table zyf_col_null_default complete data imported !
table zyf_tab_con complete data imported !
Finish reading 51 SQL statements!
end restore operation ...
restore operation successful
total time: 260 ms
[omm@node1 backup]$
re_zzz_dump=# \c re_zzz_tar
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "re_zzz_tar" as user "omm".
re_zzz_tar=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------------------+-------+--------+----------------------------------
public | invoice | table | user10 | {orientation=row,compression=no}
public | newtestwithdata | table | user10 | {orientation=row,compression=no}
public | t1 | table | user10 | {orientation=row,compression=no}
public | t_seq | table | user10 | {orientation=row,compression=no}
public | t_seq_pri | table | user10 | {orientation=row,compression=no}
public | t_tab | table | user10 | {orientation=row,compression=no}
public | test001 | table | user10 | {orientation=row,compression=no}
public | test002 | table | user10 | {orientation=row,compression=no}
public | testnewwithoutdata | table | user10 | {orientation=row,compression=no}
public | zyf_col_con | table | user10 | {orientation=row,compression=no}
public | zyf_col_default | table | user10 | {orientation=row,compression=no}
public | zyf_col_null_default | table | user10 | {orientation=row,compression=no}
public | zyf_tab_con | table | user10 | {orientation=row,compression=no}
(13 rows)
re_zzz_tar=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
