openGauss数据库的逻辑备份和恢复技术。
gs_dump工具介绍
omm@modb:~$ gs_dump --help gs_dump dumps a database as a text file or to other formats. Usage: gs_dump [OPTION]... [DBNAME] General options: -f, --file=FILENAME output file or directory name -F, --format=c|d|t|p output file format (custom, directory, tar, plain text (default)) -a, --data-only dump only the data, not the schema -b, --blobs include large objects in dump -c, --clean clean (drop) database objects before recreating -C, --create include commands to create database in dump -E, --encoding=ENCODING dump the data in encoding ENCODING -g, --exclude-guc=GUC_PARAM do NOT dump the GUC_PARAM set -v, --verbose verbose mode -V, --version output version information, then exit -Z, --compress=0-9 compression level for compressed formats --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock -?, --help show this help, then exit Options controlling the output content: -n, --schema=SCHEMA dump the named schema(s) only -N, --exclude-schema=SCHEMA do NOT dump the named schema(s) -o, --oids include OIDs in dump -O, --no-owner skip restoration of object ownership in plain-text format -s, --schema-only dump only the schema, no data -q, --target=VERSION dump data format can compatible Gaussdb version (v1 or ..) -S, --sysadmin=NAME system admin user name to use in plain-text format -t, --table=TABLE dump the named table(s) only -T, --exclude-table=TABLE do NOT dump the named table(s) --include-table-file=FileName dump the named table(s) only --exclude-table-file=FileName do NOT dump the named table(s) --pipeline use pipeline to pass the password, forbidden to use in terminal -x, --no-privileges/--no-acl do not dump privileges (grant/revoke) --column-inserts/--attribute-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore --exclude-table-data=TABLE do NOT dump data for the named table(s) --exclude-with do NOT dump WITH() of table(s) --inserts dump data as INSERT commands, rather than COPY --no-publications do not dump publications --no-security-labels do not dump security label assignments --no-subscriptions do not dump subscriptions --no-tablespaces do not dump tablespace assignments --no-unlogged-table-data do not dump unlogged table data --include-alter-table dump the table delete column --quote-all-identifiers quote all identifiers, even if not key words --section=SECTION dump named section (pre-data, data, or post-data) --serializable-deferrable wait until the dump can run without anomalies ALTER OWNER commands to set ownership --exclude-function do not dump function and procedure --with-encryption=AES128 dump data is encrypted using AES128 --with-key=KEY AES128 encryption key, must be 16 bytes in length --dont-overwrite-file do not overwrite the existing file in case of plain, tar and custom format --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of --with-salt=RANDVALUES used by gs_dumpall, pass rand value array --include-extensions include extensions in dump --binary-upgrade for use by upgrade utilities only --rolepassword=ROLEPASSWORD the password for role If no database name is supplied, then the PGDATABASE environment variable value is used. --binary-upgrade-usermap="USER1=USER2" to be used only by upgrade utility for mapping usernames --non-lock-table for use by OM tools utilities only --include-depend-objs dump the object which depends on the input object --exclude-self do not dump the input object Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password=PASSWORD the password of specified database user --role=ROLENAME do SET ROLE before dump
-h, 指定数据库主机IP/主机名
-p, 指定数据库端口号
-U, 指定连接数据库使用的用户
-W, 指定数据库用户密码
-f, 指定备份文件存放目录
-F，指定备份文件格式（c:客制化，d:目录格式，t:tar压缩文件，p:SQL格式（默认格式））
逻辑备份和恢复案例
1.逻辑备份和恢复案例1:使用sql格式进行备份和恢复omm数据库
为逻辑备份准备环境
--创建逻辑备份的存储目录： root@modb:~# su - omm omm@modb:~$ mkdir /var/lib/opengauss/backup --创建备份恢复用户，需要具有super或者sysadmin权限 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 user test IDENTIFIED BY 'huawei@1234' sysadmin ; NOTICE: The encrypted password contains MD5 ciphertext, which is not secure. CREATE ROLE --创建恢复测试数据库testdb omm=# CREATE TABLESPACE test_tbs RELATIVE LOCATION 'tablespace/test_tbs1'; CREATE TABLESPACE omm=# CREATE DATABASE testdb WITH TABLESPACE = test_tbs; CREATE DATABASE --在omm数据库上，创建测试表test1、test2： omm=# CREATE TABLE test1(col int); CREATE TABLE omm=# CREATE TABLE test2(col int); CREATE TABLE omm=# \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:~$ 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)
逻辑备份，使用gs_dump备份数据库，生成sql文件
--使用test用户，备份数据库omm： 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 10:52:44]: The total objects number is 413. gs_dump[port='5432'][omm][2022-12-13 10:52:44]: [100.00%] 413 objects have been dumped. gs_dump[port='5432'][omm][2022-12-13 10:52:44]: dump database omm successfully gs_dump[port='5432'][omm][2022-12-13 10:52:44]: total time: 1477 ms 逻辑恢复： --使用用户test，执行用gs_dump生成的sql脚本，将数据恢复到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 SET SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE REVOKE REVOKE GRANT GRANT total time: 20 ms 恢复验证： --验证数据库omm的备份已经被恢复到数据库testdb： --源库（备份的数据库）： 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:~$ gsql -d testdb -U test -W huawei@1234 -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)
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=# drop database if exists testdb;
DROP DATABASE
omm=# CREATE DATABASE testdb WITH TABLESPACE = test_tbs;
CREATE DATABASE
omm=# \q
--使用test用户，备份omm数据库，生成归档格式的备份文件:
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 10:56:03]: The total objects number is 417.
gs_dump[port='5432'][omm][2022-12-13 10:56:04]: [100.00%] 417 objects have been dumped.
gs_dump[port='5432'][omm][2022-12-13 10:56:04]: dump database omm successfully
gs_dump[port='5432'][omm][2022-12-13 10:56:04]: total time: 1475 ms
逻辑恢复：
--使用gs_dump生成的归档文件恢复数据库
omm@modb:~$ gsql -d testdb -U test -W huawei@1234 -f /var/lib/opengauss/backup/backup.dump
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
total time: 26 ms
恢复验证：
--源库（备份的数据库）：
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}
public | test3 | table | omm | {orientation=row,compression=no}
public | test4 | table | omm | {orientation=row,compression=no}
(4 rows)
--新库（恢复的数据库）：
omm@modb:~$ gsql -d testdb -U test -W huawei@1234 -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}
public | test3 | table | omm | {orientation=row,compression=no}
public | test4 | table | omm | {orientation=row,compression=no}
(4 rows)
最后修改时间：2022-12-13 10:59:37
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
