【我和openGauss的故事】openGauss逻辑备份恢复
zhouwhui 2023-07-14 18:50 发表于 openGauss公众号
逻辑备份恢复
一、小数据集导入导出
1. copy 或 \copy
可以将文本数据作为数据源导入到数据表中，也可以将数据表或查询结果导出到指定的文件中。
2. copy 与 \copy 的区别
（1）copy 属于SQL命令，\copy属于元命令 （2）copy 导入导出的文件是数据库服务端的文件，\copy导入导出的文件是客户端文件 （3）copy 需要数据库管理员权限才可以执行，\copy任意用户都可以执行 （4）copy 对较大数据集的整体读写效率比 \copy 更好
3. 语法格式
copy 与 \copy 的语法格式与使用方式是一样的，以下以copy为例。
（1）数据导入 copy from
- 将 TXT 文件数据导入到数据表中
copy 表 from '文件路径/文件.txt'- TXT 文件默认列分隔符为 tab ，如果所指定分隔符不是 tab，可以使用 delimiter 指定
copy 表 from '文件路径/文件.txt' delimiter '分隔符'- 除了可以导入 TXT 文件，也可以是一些特定表格文件（如 CSV），因为默认文件类型为 TXT，可以通过 with 指定文件类型
- copy 表 from '文件路径/文件.csv' with csv- CSV 文件比较特殊，既可以看做表格文件也可以看做文本文件，看做文本文件时，默认列与列之间的分隔符为’,’，所以也可以使用以下SQL语句进行数据导入
- copy 表 from '文件路径文件.csv' delimiter ','（2）数据导出 copy to
- 将数据表导出到指定 TXT 文件中（默认分隔符是 tab）
copy 表 to '文件路径/文件.txt'- 将数据表导出到指定 TXT 文件中（文件，指定分隔符为’;’）
copy 表 to '文件路径/文件.txt' delimiter ';'- 将数据表导出到指定 CSV 文件中（默认分隔符为’,’）
copy 表 to '文件路径/文件.csv' with csv- 将查询结果集导出到指定 CSV 文件中（默认分隔符为’,’）
copy (查询语句) to '文件路径/文件.csv' with csv- 例如：将用户表中年龄大于等于18的用户信息进行导出
copy (select * from t_user where age>=18) to '文件路径/文件.csv' with csv- copy不支持导出视图数据，如果要导出视图数据可以替换为查询语句，例如：需要导出视图v_user
copy (select * from v_user) to '文件路径/文件.csv' with csv二、大数据集导入导出
1. gs_dump、gs_dumpall数据导出
（1）gs_dump
openGauss数据库提供的数据导出工具，可将指定数据库的相关数据对象定义及数据进行导出；在导出时可以指定要导出的数据对象或不导出的数据对象，也可以指定导出的模式，以及导出的文件类型。
gs_dump工具使用提示
- 由系统用户omm用户执行
- 在gs_dumnp执行数据导出过程中，不影响其他用户对数据库的访问（包括读写操作）
gs_dump参数说明
参数 | 说明 | 参数值说明 |
-U | 指定连接数据库执行导出的用户名 | -U omm |
-W | 指定连接数据库执行导出的用户的密码 | -W opengauss@123 |
-h | 指定要连接的数据库主机ip | -h 192.168.1.1 |
d | 要连接的数据库名称 | dbname |
-p | 数据库运行端口 | -p 15432 |
-F | 指定导出文件类型 | -F p (纯文本格式，即导出为sql文件，常用于小型数据库) -F c (自定义归档格式，常用于中大型数据库) -F d (目录归档格式) -F t (tar归档格式) |
-f | 导出得到的文件存储路径 | -f /home/omm/db.sql |
-T | 不导出的数据对象 | -T t_user |
-t | 指定导出的数据对象 | -t t_user |
-n | 指定导出的模式（schema）中的所有对象 | -n test |
-s | 仅导出对象定义 | -s （无参数值） |
-a | 仅导出对象数据 | -a （无参数值） |
在以上所有参数中必填参数有d、-p、-F、-f，其余参数根据实际需求而定
- 默认不指定-T、-t 的情况下表示导出所有对象
- 默认不指定-n 的情况下表示导出所有schema中的所有对象
- 默认不指定-s、-a 的情况下表示同时导出数据和定义
- 如果同时指定了-T 和-t 表示导出在-t 中声明且没有在-T 中声明的对象
- 要指定多个对象或者多个模式时需要多次配置相关参数，例如：同时导出t_user 和t_info 参数配置为
-t t_user -t t_infogs_dump 实例
- 导出student数据库中的所有对象信息，导出为纯文本格式
gs_dump -p 15432 student -F p -f /home/omm/student.sql- 导出student数据库中的stuinfo表定义，导出为tar归档格式
gs_dump -p 15432 student -t stuinfo -F t -f /home/omm/stuinfo.tar -s- 导出student数据库中的除stuinfo表以外其他对象的数据，导出为自定义归档格式
gs_dump -p 15432 student -T stuinfo -F c -f /home/omm/student.aaa -a- 导出student数据库中的info和example模式中的对象信息，导出为目录归档格式
gs_dump -p 15432 student -n info -n example -F d -f /home/omm/student（2）gs_dumpall
gs_dumpall 也是openGauss 数据库提供的一种数据导出工具，与gs_dump 的区别在于gs_dump 每次导出仅作用于一个指定数据库，gs_dumpall 针对所有数据库；在导出时可以指定导出模式，即导出对象定义+数据、仅定义、仅数据。
gs_dumpall 工具使用提示
- 由系统用户omm用户执行
- 在gs_dumnpall 执行数据导出过程中，不影响其他用户对数据库的访问（包括读写操作）
gs_dumpall 参数说明
参数 | 说明 | 参数值说明 |
-U | 指定连接数据库执行导出的用户名 | -U omm |
-W | 指定连接数据库执行导出的用户的密码 | -W opengauss@123 |
-h | 指定要连接的数据库主机ip | -h 192.168.1.1 |
-p | 数据库运行端口 | -p 15432 |
-f | 导出得到的文件存储路径 | -f /home/omm/db.sql |
-s | 仅导出对象定义 | -s （无参数值） |
-a | 仅导出对象数据 | -a （无参数值） |
在以上所有参数中必填参数仅有-p，其余参数根据实际需求而定
- gs_dumpall 数据导出文件只有纯文本格式
- gs_dumpall 面向的是所有数据库，所以不支持指定数据库
- 默认不指定-s、-a 的情况下表示同时导出数据和定义
gs_dumpall 实例
- 导出所有数据库的全量信息
gs_dumpall -p 15432 -f /home/omm/dball.sql- 导出所有数据库的对象定义
gs_dumpall -p 15432 -f /home/omm/dball.sql -s- 导出所有数据库的对象数据
gs_dumpall -p 15432 -f /home/omm/dball.sql -a2. gs_restore、gsql数据导入
（1）纯文本格式文件导入（gsql）
使用gs_dump 或gs_dumpall 导出的纯文本文件就是sql 文件，如果想要将该内容导入到数据库中，执行sql 文件就可以实现；执行sql 文件使用gsql 客户端工具。gsql是用于连接数据库，执行sql语句，执行元命令的客户端工具。
gsql 参数说明
参数 | 说明 | 参数值说明 |
-U | 连接数据库执行导出的用户名 | -U omm |
-W | 连接数据库执行导出的用户的密码 | -W opengauss@123 |
-h | 要连接的数据库主机ip | -h 192.168.1.1 |
dbname | 连接的数据库名称 | postgres （openGauss安装后默认自带数据库） |
-p | 数据库运行端口 | -p 15432 |
-c | 不进入shell的情况下执行sql语句 | -c ‘sql语句’ |
-f | 要执行的sql文件 | -f 文件路径/文件.sql |
shell 的打开方式 | 进入到数据库shell 窗口时的模式 | -S （默认，单行模式，光标不可移动、每行必须以;结束） -l （在不进入shell的情况下查看当前可用数据库） -q （安静模式，除了查询结果外，不会有额外的信息打印） -r （编辑模式，可任意切换光标位置，sql语句未结束前可以换行，以;识别一段完整的SQL语句） |
执行sql文件如下：
gsal -p 15432 postgres -f /home/omm/dball.sql（2）归档格式文件导入（gs_retore）
使用gs_dump 可以导出三种归档格式（-c 自定义归档，-d 目录归档，-t tar归档），因为归档格式文件是以二进制方式存储信息，无法像纯文本一样，可以人为拆解sql语句，所以如果想要将SQL文件导入到数据库时需要通过gs_restore进行解析后导入。
gs_restore 参数说明
参数 | 说明 | 参数值说明 |
-U | 连接数据库执行导出的用户名 | -U omm |
-W | 连接数据库执行导出的用户的密码 | -W opengauss@123 |
-h | 要连接的数据库主机ip | -h 192.168.1.1 |
-d | 导入的数据库名称 | -d postgres （因为gs_dump每次导出一定是一个数据库，gs_restore导入也必须指定要导入的数据库） |
-F | 导入的归档文件的格式 | -F c （自定义归档） -F d （目录归档） -F t （tar归档） |
filename | 导入的归档文件的路径 | 文件路径/文件.后缀 |
-f | 输出文件的路径（将归档格式文件转换为sql文件） | -f 文件路径/文件.sql |
-l | 列出归档形式内容 | -l （无参数值） |
-c | 再导入对象前先将已存在的对象清除再导入 | -c （无参数值） |
-e | 遇到错误时终止执行（默认在遇到异常时会跳过异常继续执行，执行完成后抛出异常信息） | -e （无参数值） |
- gs_restore也提供可以指定-T、-t、-n限制要导入或不导入哪些对象
- 当指定了-d要导入的数据库时，会将归档文件信息导入到指定的数据库中，不指定-d时，可以指定-f将归档文件转换为sql文件，相当于gs_dump -F p
- -d与-f不能同时使用，也就是要不就导入数据库，要不就转换为sql文件
- 转换为sql文件时除了-f还需要指定-l，因为gs_restore要求需要执行-d或-l，因为-d与-f不能同时使用，所以指定-f时一定需要再指定-l
gs_restore 实例
- 将tar归档文件导入到student数据库中
gs_restore -p 15432 -d student -F t /home/omm/student.tar -e -c- 将目录归档文件导入到student数据库中
gs_restore -p 15432 -d student -F d /home/omm/student -e -c- 将自定义归档文件导入到student数据库中
gs_restore -p 15432 -d student -F c /home/omm/student.aaa -e -c- 将目录归档文件转换为sql文件
gs_restore -p 15432 -f /home/omm/student_trans.sql -l -F d /home/omm/student -e -c