逻辑备份和还原
什么物理备份和还原
（1）逻辑备份是对整个数据库或数据库中的部分对象利用逻辑备份工具导出数据到备份文件
（2）在需要的数据库恢复的情况下利用逻辑备份还原工具吧备份文件恢复到数据库中
| 使用场景 |
|---|
| 主要用于数据逻辑错误的恢复，回复后对其他数据没有太大的影响 |
| 可用于大型数据库中的执行部分的静态对象的备份 |
| 可以实现跨平台的数据恢复 |
| 在生产中是对物理备份的有效补充 |
sys_dump
| 介绍 |
|---|
| 是人大金仓提供用于数据逻辑备份的工具，只支持线上备份 |
| 支持多种类型的文件格式，包含可跨平台的sql 格式 |
| 支持压缩、并行、加密等 |
连接参数
| 参数 | 简介 |
|---|---|
| -d | 指定要连接的数据库 |
| -h | 指定要连接数据库服务器的主机或ip |
| -p | 指定数据库服务的监听端口号 |
| -U | 指定数据库连接的用户名 |
| -W | 指定连接数据库时，提示输入密码 |
| –role | 在执行备份前根据指定的角色名称运行 set role |
一般参数
| 参数 | 简介 |
|---|---|
| -f | 指定输出的备份文件名或本分存储的目录名 |
| -F | 输出文件格式，分别表示定制（c）目录（d）tar（t）,明文（p） |
| -j | 执行多个备份任务进行备份转储工作，只有选择目录格式的时候支持此参数 |
| -z | 指定压缩格式的压缩级别 |
| -lock-wait-timeout | 指定等待表的锁时的时间超时会备份任务并且报错退出 |
| -v | 向屏幕输出备份任务的详细信息 |
（3）其他参数
| 参数 | 简介 |
|---|---|
| -a | 只转储表的数据，不包括对象的定义 |
| -c | 在备份文件中包含删除数据库对象的DDL语句（再恢复时先删除再创建） |
| -C | 在转储中包括命令以便创建数据库 |
| -E | 以encoding形式编码进行存储 |
| -n | 只转储指定的模式中对象。此参数可以复用 |
| -N | 只转储指定模式以外的模式中的对象，此参数可以复用 |
| -t | 只转储指定表，此参数可以复用 |
| -T | 只转储指定表意外的表，此参数可以 复用 |
| -s | 只转储模式中定于的对象，不转储表中的数据跟-a参数是相反的 |
| -x | 不要转储权限 |
| –column-inserts | 以带有列名的insert的命令形式转储数据 |
| –inserts | 以insert命令，而不是使用copy的命令形式进行转储数据 |
| – disable-triggers | 在恢复数据的过程中禁用触发器 |
sys_dumpall
参数基本与sys_dump参数一致
| 参数 | 简介 |
|---|---|
| -a | 只转储表中的数据，不包括对象的定义 |
| -c | 在备份数据库文件中包含删除数据库对象的DDL语句（在恢复的时候先删除再创建） |
| -t | 只转储表空间，不包括数据中的数据和队形 |
| -g | 只转储全局对象，不包括数据库中的对象 |
| -s | 只转储模式以及模式中对象的定义，不转储表的数据 |
| -r | 只转储角色，不包括数据库或表空间 |
| -x | 不要转储存储权限 |
| –colnumn-inserts | 以带有列名的insert命令的形式转储数据 |
| –inserts | 以insert命令，而不是copy命令的形式转储数据 |
| –disable-tirggers | 在只恢复数据的过程中禁用触发器 |
| –no-tablespaces | 不转储表空间的分配信息 |
| no-unlogged-table-data | 不转储没有日志的数据 |
sys_restore
| 介绍 |
|---|
| 是人大金仓提供的用于逻辑还原的工具，只支持在线还原 |
| 支持还原除了sql脚本以外的所有的逻辑还原的类型文件 |
| 支持并行任务进行恢复工作 |
| 支持还原时指定表空间映射 |
连接参数
–参考sys_dump
| 参数 | 简介 |
|---|---|
| -l | 整个恢复任务作为单个数据执行 |
| -g | 备份文件中只保留的对象属性信息 |
| -G | 将备份文件中模式恢复到指定的新模式中（模式映射） |
| -e | 发生错误退出此参数，默认为继续 |
演示：
环境准备：
create schema schema01 ;
create table schema01.t01(id int,name text);
create table schema01.t02(id int,name text);
insert into schema01.t01 values(1,'yujinlong');
insert into schema01.t01 values(2,'yuning');
insert into schema01.t02 values(1,'yuweixing');
insert into schema01.t02 values(2,'fengxiue');
执行逻辑备份：
# 备份单表或者多表
sys_dump -Usystem -dtest -f '/backup/dump/table.dmp' -Fc -t scheam01.t*
# 备份模式
sys_dump -Usystem -dtest -f '/backup/dump/schema01.dmp' -Fc -n schema01
# 备份整个数据库
sys_dump -Usystem -dtest -f '/backup/dump/dir2' -Fd --insert -j 2
# 备份数据库到copy格式的sql脚本
sys_dump -Usystem -dtest -f '/backup/dump/t01_copt.sql' -FP -t schema.t01
# 备份数据到insert格式的sql脚本
sys_dump -Usystem -dtest -f '/backup/dump/t01_insert.sql' -Fp --insert -t schema01.t01
# 只备份对象定义，不包含表的数据
sys_dump -Usystem -dtest -f /backup/dump/data.sql -Fp -s
# 只备份数据，不包含对象的定义
sys_dump -Usystem -dtest -f /backup/dump/data.sql -Fp -a
执行逻辑还原
# 从dump格式的备份还原表
sys_restore -Usystem -dtest /backup/dump/table.dmp -Fc -t t01;
# 从sql 格式的备份中还原表
\i /backup/dump/t01_copt.sql
# 还原到新的模式
sys_dump -Usystem -dtest -Fc f /backup/dump/schema01.dmp -Fc -g schema01 -G scheam02
逻辑备份支持错超时
# 在会话1中执行事务
begin;
# BEGIN
truncate table schema01.t01;
# TRUNCATE TABLE
# 会话2中执行备份
sys_dump -Usystem -dtest -f '/backup/dump/t01.dmp' -Fc -t schema01.t01
# 会话3查询会话信息
select query,state,wait_event_type from sys_stat_activity;
query | state | wait_event_type
------------------------------------------------------------+---------------------+-----------------
| | Activity
| idle | Extension
| | Activity
truncate table schema01.t01; | idle in transaction | Client
LOCK TABLE schema01.t01 IN ACCESS SHARE MODE | active | Lock
select query,state,wait_event_type from sys_stat_activity; | active |
| | Activity
| | Activity
| | Activity
(9 行记录)
# 会话4 设置等待超时参数
sys_dump -Usystem -dtest -Fc -f /backup/dump/t01.dmp --lock-wait-time=10s
# sys_dump: error: query failed: 错误: 由于语句执行超时，正在取消查询命令
# sys_dump: error: query was: LOCK TABLE schema01.t01 IN ACCESS SHARE MODE
逻辑还原后验证数据的一致性
# 创建t03表
create table schema01.t03 (id int ,name text);
# 插入1000行数据条
insert into schema01.t03 select generate_series(1,1000),md5(random());
# 查询t03的hash值
select sum(hashtext('schema01.t03.*'::text)) from schema01.t03 ;
#备份t03数据表
sys_dump -Usystem -dtest -f /backup/dump/t03.sql -Fp -t schema01.t03
# 删除表
drop table if exists schema01.t03;
# 还原表
\i /backup/dump/t03.sql
# 再次查询hash值
\i /backup/dump/t03.sql
sys_dumpall
环境准备
# 新建数据库
create database db01;
# 在新键数据库中创建新模式
create schema schema03;
# 在新模式中创建表t03,t04,并且插入两行数据
create table schema03.t03 (id int,name varchar(10));
create table schema03.t04 (id int,optime sysdate)
insert into schema03.t03 values(1,'a'),(2,'b');
insert into schema03.t04 values(1,sysdate),(2,sysdate);
备份数据库
sys_dump -Usystem -f /backup/dump/db.sql --inserts
从备份文件恢复所有数据库
ksql -Usystem -dtest -f /backup/dump/db.sql
备份全局对象
# 1、只备份自定义的角色信息
sys_dumpall -Usystem -r -f /backup/dump/roles.sql
grep ^[a-zA-Z] /backup/dump/roles.sql
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
CREATE ROLE system;
ALTER ROLE system WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:xsQcxzA8ORKOemOtfaEU8Q==$qLKWcgEwHZC5jx/EA6Xlhn8KI5FwWozfHMGYo5TU4jY=:PjrU62/Yi+fNOHmMkcfOmaOLiIkPZ3CieOlf2+Kecbw=';
CREATE ROLE user01;
ALTER ROLE user01 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:NDbz9JcxIESXu0L+vVovGg==$7Y/55Hl7y12RIbHGI07+B0VcLcbUPaj9y5t4SdLhggk=:TR9TRsG7/7bOGozjXHFA9aPFZKQwxl/kRlaBkLUjb34=';
# 2、只备份自定义表空间的定义信息
sys_dumpall -Usystem -t -f /backup/dump/tablespace.sql
grep ^[a-zA-Z] /backup/dump/tablespace.sql
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
3、同时备份自定义角色和表空间的定义信息
sys_dumpall -Usystem -g -f /backup/dump/global.sql
grep ^[a-zA-Z] /backup/dump/global.sql
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
CREATE ROLE system;
ALTER ROLE system WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:xsQcxzA8ORKOemOtfaEU8Q==$qLKWcgEwHZC5jx/EA6Xlhn8KI5FwWozfHMGYo5TU4jY=:PjrU62/Yi+fNOHmMkcfOmaOLiIkPZ3CieOlf2+Kecbw=';
CREATE ROLE user01;
ALTER ROLE user01 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:NDbz9JcxIESXu0L+vVovGg==$7Y/55Hl7y12RIbHGI07+B0VcLcbUPaj9y5t4SdLhggk=:TR9TRsG7/7bOGozjXHFA9aPFZKQwxl/kRlaBkLUjb34=';
# 只备份所有对象的定义信息（不包含数据）
sys_dumpall -Usystem -s -f /backup/dump/schema.sql
cat /backup/dump/schema.sql |grep -i -E 'create|alter|revoke|copy'
逻辑备份的有缺点
优点：
（1）移植性比较好，可跨平台恢复和还原
（2）灵活性强，可选择对象执行颗粒度备份和还原
（3）备份转储的文件类型丰富，其中文本格式的文件支持内容查看和修改
（4）在线设备和其他并发的DML操作不会冲突
缺点：
（1）由于逻辑备份速度较慢，不适合对大型系统执行全量数据备份
（2）逻辑备份的内容只是在数据在备份时间点的快照，无法利用归档日志执行完全恢复
sys_dump 和 sys_dumpall的区别
1、sys_dump 支持细度备份，sys_dumpall 强制备份整个数据库集中的数据
2、sys_dump 支持四种备份模式，sys_dumpall 只支持备份为sql 脚本格式
3、sys_dump支持压缩和加密备份，sysdumpall不支持压缩和加密备份
4、sys_dump必须连接指定数据库，sys_dumpall不能指定链接的数据库
逻辑还原的表时注意事项
1、在进行逻辑还原恢复时 -t 参数不能通过“模式名.表名”
2、在逻辑本备份中包含有多个模式的同名表时，使用‘-t 表名’的方式执行恢复时如果指定-n选项，那么所有模式下与‘表名’同名的表都将被恢复，所以如果想恢复指定模式下的表时，需要使用-n选项
3、当备份指定模式的表时，需要使用’-t 模式名.表名’来限定，此时使用-n选项是不生效的，如果不指定‘模式名’，那么备份工具会通过读取search_path来搜索待备份的表，如果找不到将发生错误
517
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
