GaussDB数据库备份详解(八)
GaussDB数据库备份详解(八)
本文将全面介绍GaussDB数据库的备份与恢复技术,帮助您制定合理的数据保护策略,确保业务数据安全可靠。
1 GaussDB备份恢复概述
数据是企业的核心资产,定期备份是数据库管理的基础工作。GaussDB提供了多种备份恢复方案,涵盖逻辑备份与物理备份两大类,可满足不同业务场景下的数据保护需求。通过合理的备份策略,企业能够有效预防数据丢失、人为误操作、系统故障等数据风险,确保业务连续性。
2 逻辑备份与恢复
逻辑备份是指将数据库中的逻辑数据(如表、视图等)导出为特定格式的文件,适用于数据迁移、跨版本恢复和部分数据恢复场景。
2.1 使用gs_dump进行备份
gs_dump
是GaussDB提供的逻辑备份工具,可以对单个database进行备份,支持备份不同的schema结构或数据。
基本语法:
bash
gs_dump --file=备份文件路径 --schema=模式名 --no-privileges --no-owner --username=用户名 数据库名
示例演示:
bash
# 创建备份
gs_dump --file=mes_way1.sql --schema=public --no-privileges --no-owner --username=omm mes
# 文本格式备份(方便查看和修改)
gs_dump --file=mes_way1.sql --schema=public --no-privileges --no-owner --username=omm mes
# 二进制备份(效率更高)
gs_dump --file=mes_way3.dmp --format=c --schema=mes --no-subscriptions --username=mes mes2
section子项备份:
为了更精细地控制备份过程,可以使用section参数将备份分为三个阶段:
bash
# 1. 数据备份前结构预处理
gs_dump --file=mes_way2_pre.sql --section=pre-data --schema=mes --no-subscriptions --username=mes mes2
# 2. 数据备份
gs_dump --file=mes_way2_data.sql --section=data --schema=mes --no-subscriptions --username=mes mes2
# 3. 数据备份后逻辑处理
gs_dump --file=mes_way2_post.sql --section=post-data --schema=mes --no-subscriptions --username=mes mes2
2.2 使用gs_dump进行恢复
文本格式恢复:
bash
gsql --file=mes_way1.sql --username=omm mes2
二进制格式恢复:
bash
gs_restore --username=mes -d mes3 mes_way3.dmp
section子项恢复:
bash
# 按顺序执行三个阶段的恢复
gsql --file=mes_way2_pre.sql --username=mes mes3
gsql --file=mes_way2_data.sql --username=mes mes3
gsql --file=mes_way2_post.sql --username=mes mes3
3 物理备份与恢复
物理备份直接拷贝数据库文件,恢复时更快速,适用于大数据量场景。
1、数据库对象管理及SQL应用
-- 请基于以下代码创建表及插入数据,并完成以下要求
drop table if exists student;
drop table if exists class;
create table student(sno int,sname varchar(20),score int,month int,cno int);
-- core表示当月月考总分,月考总分为NULL,说明当月缺考
create table class(cno int,cname varchar(20));
insert into class values(1,'class1'),(2,'class2');
insert into student values(1,'Lee',610,1,1),(2,'Jerry',510,1,1),(5,'Lee',410,1,1),(3,'Tom',400,1,2),(4,'Jack',300,1,2),(6,'Jacy',NULL,1,2),(1,'Lee',410,2,1),(2,'Jerry',510,2,1),(5,'Lee',210,2,1),(3,'Tom',600,2,2),(4,'Jack',300,2,2),(6,'Jacy',510,2,2),(1,'Lee',410,3,1),(2,'Jerry',510,3,1),(5,'Lee',NULL,3,1),(3,'Tom',NULL,3,2),(4,'Jack',300,3,2),(6,'Jacy',410,3,2);
(1)输出每月月考总分都比学号为5的同学分数高的所有学生信息
(1) 输出每月月考总分都比学号为5的同学分数高的所有学生信息
--计算的按照每个月相比,结果集累加(不符合题意)
select sno,sname,t1.month,t1.score from (select month,sno,sname,nvl(score,0) score from student) t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score order by sno;
--每月月考成绩均比学号为5的同学分数高(取交集)
(1)获取学号为5的学生每个月的成绩
select month,nvl(score,0) score from student where sno=5;
(2)判断月考成绩比学号为5的学生高的所有学生的学号,根据学号分组计算每位学生有几次成绩大于学号为5的学生的成绩,
select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno;
(3)having判断大于次数是否等于月份数量
select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno having count=(select count(distinct month) from student);
(4)根据以上得出的sno获取学生信息
select student.* from student join (select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno having count=(select count(distinct month) from student) ) stu2 on student.sno=stu2.sno;
(2)输出每次月考缺考的学生信息,要求打印姓名、班级编号和缺考次数
select month,sname,cno,count(1) as "缺考次数" from student where score is null group by month,sname,cno;
(3)输出每次月考都和tom同时缺考的所有学生信息,要求打印学号、姓名和月考总分
select month,cno,sname,sum(nvl(score,0)) from student where month in (select month from student where sname='Tom' and score is NULL) and score is NULL and sname != 'Tom' group by month,cno,sname;
(4)输出全校月考中位分数。(GaussDB分布式没有median函数)
select avg(nvl(score,0)) from (select score from student order by nvl(score,0) limit (select ceil(count(1)/2)-1 from student),case when (select count(1)%2 from student)=0 then 2 else 1 end);
(5)统计每个班每次月考的最高分数,要求打印班级名称,考试时间和月考分数
select cname,month,max(score) from student,class where student.cno=class.cno group by cname,month order by cname,month;
2、数据库对象管理及SQL应用2(表同上题)
(1)输出class1班级中比class2班级每月月考最低分还低的学生信息,要求打印学号、姓名和月考总分
select t1.sno,t1.sname,nvl(t1.score,0) from (select * from student where cno=1) t1 join (select month,min(score) score from student where cno=2 group by month) t2 on t1.month=t2.month where nvl(t1.score,0)<nvl(t2.score,0);
(2)打印月考总分平均分最高的学生信息,输出学号,姓名和月考平均分
select t1.* from (select sno,sname,round(avg(nvl(score,0)),2) /score from student group by sno,sname) t1 join (select max(score) score from (select sno,sname,round(avg(nvl(score,0)),2) score from student group by sno,sname)) t2 on t1.score=t2.score;
(3)输出每个学生月考平均分和最高月考平均分学生之间的分数差距,打印学号、姓名、月考平均分和差距分数
select sno,sname,round(avg(nvl(score,0)),2) avgscore ,(select max(avgscore) from (select round(avg(nvl(score,0)),2) avgscore from student group by sno ))-avgscore from student group by sno,sname;
--计算所有学生的月考平均值
select round(avg(nvl(score,0)),2),avg from student group by sno;
--从上一步计算的所有学生的月考平均值中获取最大值
select max(avgscore) from (select round(avg(nvl(score,0)),2),avg from student group by sno);
3、数据库对象管理及SQL应用3
基于以下学生成绩表,完成以下实验要求。
--创建表
create table stu(id int,math int,art int,phy int,music int);
--导入数据
insert into stu values (1,60,33,66,86);
(1)求math、phy总成绩以及art、music的总成绩
select sum(math+phy),sum(art+music) from stu;
(2)计算学生总成绩,并基于总成绩排序
select id,sum(math+phy+art+music) all_score from stu group by id order by all_score desc;
(3)art和music总分排名前5的总成绩加5分,查询最终的所有学生总成绩
(select stu.id,sum(score+math+phy) allscore from stu,(select id,sum(art+music)+5 score from stu group by id order by score desc limit 5) stu2 where stu.id=stu2.id group by stu.id)
union all
(select stu.id,sum(score+math+phy) allscore from stu,(select id,sum(art+music) score from stu group by id order by score desc offset 5) stu2 where stu.id=stu2.id group by stu.id);
4、数据库对象管理及SQL应用4
基于以下学生成绩表,完成以下实验要求。
--创建表
create table scopes(student_id int,chinese int,math int,english int,music int);
--导入数据
insert into scopes values(1,90,88,100,88);
insert into scopes values(2,988,88,100,99);
insert into scopes values(3,87,89,98,89);
insert into scopes values(4,91,88,76,99);
insert into scopes values(5,92,88,78,98);
insert into scopes values(6,93,88,76,87);
(1)计算每个学生的chinese和math总分,以及english和music总分,要求一条SQL语句实现,不能使用临时表
select student_id,sum(chinese+math),sum(english+music) from scopes group by student_id;
(2)目前有一张权重表(各科有不同的权重,目前权重策略有2个),请算出每个学生结合权重计算后的成绩总和。要求一条SQL语句实现,不能使用临时表。每个学生都对应两个权重成绩。
权重表结构如下
create table weight(weight_id int,chinese decimal(10,2),math decimal(10,2),english decimal(10,2),music decimal(10,2));
insert into weight values(1,0.3,0.2,0.2,0.3);
insert into weight values(2,0.2,0.1,0.3,0.4);
select student_id,weight_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) from scopes t1,weight t2 group by student_id,weight_id order by 1,2;
(3)结合上面的结果,将一个学生对应的两个权重成绩,合到一行,要求一条SQL语句实现,不能使用临时表
select tb1.student_id,weight_sum1,weight_sum2 from
--计算权重1
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1
join
--计算权重2
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2
--表连接
on tb1.student_id=tb2.student_id order by 1;
(4)按照两个权重成绩之和的大小,进行从大到小排序,且生成排序序号,要求生成连续排序序号,相同的值具有相同序号。一条SQL语句实现,不能使用临时表
select tb1.student_id,weight_sum1,dense_rank() over(order by weight_sum1 desc) weight_rank1,weight_sum2,dense_rank() over(order by weight_sum2 desc) weight_sum2 from
--计算权重1
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1
join
--计算权重2
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2
--表连接
on tb1.student_id=tb2.student_id;
--不要忘记把最后的order by 删除,最后的order by最后生效,会把dense_rank的排序更改
5、性能优化
当前有一张create table test(student_id int,class_id int,kemu varchar2(20),score int);
(1)查看202202班级里面语文最低分是多少,要保障走索引
--收集统计信息
analyze test;
--获取推荐索引
select * from gs_index_advise('select min(grade) from test where kemu=''语文'' and class_id=''202202''');
--创建索引
create index index_ke_cls on test(kemu,class_id);
--查询202202班级里面语文最低分
select /*+ indexscan(index_ke_cls) */ min(grafe) from test where kemu='语文' and class_id='202202';
(2)查202202班级同一科目成绩比202201班级最高分的同学,根据以下SQL优化重写
原SQL:select * from test where score>(select max(score) from test where class_id=202201) and class_id = '202202';
select * from test t1 join (select min(score) as min_score from test t2 where t1.class_id=202201) t3 on t1.score<t3.min_score where t1.class_id = 202202;
--原SQL中存在子查询,每扫描一次t1表,会遍历子查询结果,性能较差,改成join方式,消除子查询,性能有xxx提升至xxxms;
6、性能优化2
当前有三个表,分别是学生信息表student(sid,sname,cno)和202201班级成绩表score1(sid,course,score),202202班级成绩表score2(同score1)
(1)查202201班级和202202班级所有人语文成绩前十的记录,第一个查询要使用union
(select * from score1 where course='chinese' order by score limit 10)
union
(select * from score2 where course='chinese' order by score limit 10);
(2)对以上SQL语句进行优化
--将union改为union all
(select * from score1 where course='chinese' order by score limit 10)
union all
(select * from score2 where course='chinese' order by score limit 10);
(3)查看两个班级相同的科目,202201班在202202班中不存在的成绩,要求使用not in
select * from score1 where score not in (select score from score2 where score1.course=score2.course);
(4)对以上SQL语句进行优化
--not in改为not exists
select * from score1 where not exists (select score from score2 where score1.score=score2.score and score1.course=score2.course);
(5)查询班级202201语文成绩最高的学生,要求先创建索引,并且能保证一定会使用索引
create index chi_index on score1(course);
select s1.* from student s1,(select sid,max(score) from score1 where course='chinese') s2 where s1.sid=s2.sid;
(6)查询202201班级的学生的成绩比202202班级的学生最高成绩还要大的学生信息,对以下给出的SQL进行改写
SQL:select stu.sid,stu.sname,sum(score) sumscore from student stu,score1 s1 where stu.sid=s1.sid group by stu.id,stu.sname having sumscore > (select max(score) from (select sum(score) score from score2 group by sid));
--先查询把所有所需字段全部获取,然后再根据已有字段判断
select stu.sid,stu.sname,sum(score) sumscore from studnet stu join score1 s1 on stu.sid=s1.sid cross join (select max(score) maxscore from (select sum(score) score from score2 group by id)) group by stu.sno,stu.sname,maxscore having sumscore>maxscore;
7、性能优化3
基于学生表(sno,sname,sno),班级表(cno,cname),课程表(courid,courname),成绩表(sno,courid,score)完成关联查询
--创建表(考试时不一定有建表和数据插入代码)
create table student(sno varchar(20),sname varchar(50),cno int);
create table class(cno int,cname varchar(50));
create table course(courid int,courname varchar(50));
create table score(sno varchar(20),courid int,score int);
数据插入
insert into student values('1001','张三',1),('1002','李四',1),('1003','王五',2),('1004','赵六',2);
insert into class values(1,'1 班'),(2,'2 班');
insert into course values(1,'语文'),(2,'数学'),(3,'英语'),(4,'物理');
insert into score values('1001',1,84),('1001',1,64),('1001',2,86),('1001',2,94);
insert into score values('1001',3,76),('1001',3,56),('1001',4,48),('1001',4,84);
insert into score values('1002',1,83),('1002',1,85),('1002',2,46),('1002',2,74);
insert into score values('1002',3,65),('1002',3,76),('1002',4,56),('1002',4,98);
insert into score values('1003',1,86),('1003',1,74),('1003',2,88),('1003',2,54);
insert into score values('1003',3,86),('1003',3,76),('1003',4,67),('1003',4,76);
insert into score values('1004',1,100),('1004',1,100),('1004',2,87),('1004',2,86);
insert into score values('1004',3,69),('1004',3,67),('1004',4,84),('1004',4,92);
(1)语文平均成绩大于80的所有成绩,输出班级名,学号(或班级号),平均成绩,要求使用两个where非相关子查询
--in、where c1>(select ....)均属于非相关子查询
select t1.sno,cname,avg(score) from score t1,student t2,class t3 where t1.sno=t2.sno and t2.cno=t3.cno and t1.sno in (select sno from score where courid in (select courid from course where courname='语文') group by sno having avg(score)>80) and courid in (select courid from course where courname='语文') group by t1.sno,t3.cname;
(2)在上一题基础上,使用from查询优化
select t1.sno,cname,avgscore from (select t3.sno,t3.cno,avg(score) avgscore from score t1,course t2,student t3 where t1.sno=t3.sno and t1.courid=t2.courid and courname='语文' group by t3.sno,t3.cno having avgscore>80) t1,class t2 where t1.cno=t2.cno;
(3)在上一题基础上,使用父查询(消除子查询)
select t3.cno,t3.cname,avg(score) avgscore from score t1,student t2,class t3,course t4 where t1.sno=t2.sno and t2.cno=t3.cno and t1.courid=t4.courid and courname='语文' group by t1.sno,t3.cno,t3.cname having avgscore>80;
8、存储过程
当前有一张表stu(sno,math,art,physics,cno)
/*
create table stu(sno varchar(30),math float,art float,physics float,cno int);
insert into stu values('1001',56,85,72,1);
insert into stu values('1002',55,35,32,1);
insert into stu values('1003',59,45,92,1);
insert into stu values('1004',73,66,65,1);
insert into stu values('1005',90,39,81,1);
*/
(1)查看每门学生成绩与每门平均成绩的差值
select sno,math,math-avgmath 数学差值,art,art-avgart 美术差值,physics,physics-avgphysics 物理差值 from stu,(select avg(math) avgmath,avg(art) avgart,avg(physics) avgphysics from stu);
(2)编写存储过程,输入学生id和科目名称输出对应的绩点值,0-59给0,60-69给0.1,70-79给0.2,80-89给0.3,90-100给0.4
create or replace procedure pro10(id varchar(30),pro varchar(30),point out float)
as
begin
if pro = 'math' then
select (case when math <= 59 then 0 when math <= 69 then 0.1 when math <= 79 then 0.2 when math <= 89 then 0.3 when math <= 100 then 0.4 else 0 end) into point from stu where sno = id;
elseif pro = 'art' then
select (case when art <= 59 then 0 when art <= 69 then 0.1 when art <= 79 then 0.2 when art <= 89 then 0.3 when art <= 100 then 0.4 else 0 end) into point from stu where sno = id;
elseif pro = 'physics' then
select (case when physics <= 59 then 0 when physics <= 69 then 0.1 when physics <= 79 then 0.2 when physics <= 89 then 0.3 when physics <= 100 then 0.4 else 0 end) into point from stu where sno = id;
else raise notice '请输入正确科目名称!';
end if;
end;
/
9、触发器
本题根据以下表完成相应触发器创建使用
--创建表
create table tab1(sname text,deptno int,salary float,title text);
create table dept(id int,dept_name text);
create table logger(sname text,dept_name text,log_date date);
创建触发器,要求在tab1表插入一行数据时,自动往logger表中插入一条记录,记录sname和部门名称,并用当天的日期来标注该行数据的生成时间
(1)创建触发器函数T_INS_F
create or replace function T_INS_F() returns trigger as
$$
begin
insert into logger values(new.sname,(select dept_name from dept where id=new.deptno),sysdate);
return new;
end;
$$ language plpgsql;
(2)创建触发器 T_INS_TR
create trigger T_INS_TR after insert on tab1 for each row execute procedure T_INS_F();
(3)禁用表tab1上的所有触发器
alter table tab1 disable trigger all;
(4)删除T_INS_TR触发器
drop trigger T_INS_TR on tab1;
3.1 使用gs_probackup进行物理备份
gs_probackup
是一个用于管理openGauss数据库备份和恢复的工具。它具有以下特点:
- 可用于备份单机数据库或者主节点数据库,为物理备份
- 可备份外部目录的内容,如脚本文件、配置文件、日志文件等
- 支持增量备份、定期备份和远程备份
- 可设置备份的留存策略
使用流程:
-
初始化备份目录:
bash
gs_probackup init -B backup-path
-
添加备份实例:
bash
gs_probackup add-instance -B backup-path -D pgdata-path --instance=instance_name
-
执行备份:
bash
gs_probackup backup -B backup-path --instance=instance_name -b backup-mode
-
验证备份:
bash
gs_probackup validate -B backup-path --instance=instance_name
3.2 使用gs_probackup进行恢复
当数据库出现故障时,可以使用以下命令进行恢复:
bash
gs_probackup restore -B backup-path --instance=instance_name -D pgdata-path
重要提示: 使用restore子命令前,应先停止gaussdb进程。
3.3 使用gs_backup进行系统级备份
gs_backup
工具可帮助openGauss备份、恢复重要数据。
备份数据库主机:
bash
gs_backup -t backup --backup-dir=BACKUPDIR -h HOSTNAME --parameter --binary --all
恢复数据库主机:
bash
gs_backup -t restore --backup-dir=BACKUPDIR -h HOSTNAME --parameter --binary --all
4 云数据库GaussDB备份恢复
对于云上的GaussDB实例,华为云提供了控制台操作方式进行备份恢复。
4.1 创建手动备份
- 登录管理控制台,进入GaussDB实例页面
- 在"实例管理"页面,选择指定的实例,在操作列选择"创建备份"
- 在创建备份弹出框中,命名该备份,选择备份方式,并添加描述
- 单击"确定",提交备份创建
手动备份特点:
- 备份名称长度在4~64个字符之间,必须以字母开头
- 手动备份会一直保存,直到用户手动删除
- 备份方式支持物理备份、快照备份和一级备份
4.2 恢复实例数据
GaussDB支持多种恢复方式,满足不同业务需求:
- 恢复到新实例:创建一个和备份时间点数据相同的新实例,不影响原实例
- 恢复到当前实例:将当前实例上的数据全部覆盖,恢复过程中数据库不可用
- 恢复到已有实例:将备份数据恢复到已有实例,会覆盖目标实例的数据
操作步骤:
- 在"备份恢复管理"页面,选择需要恢复的备份,单击操作列的"恢复"
- 选择恢复方式(新实例、当前实例或已有实例)
- 根据提示完成参数配置
- 提交恢复任务并等待完成
4.3 下载备份文件
用户可以将GaussDB的备份文件下载到本地,用于本地存储或归档。
下载步骤:
- 在"备份恢复"页面,选择需要下载的可用备份,单击操作列的"下载"
- 下载方式选择"OBS Browser+下载",单击"确定"
- 使用OBS Browser+客户端下载备份文件
5 高级备份恢复场景
5.1 增量备份策略
Roach备份工具支持增量备份,可显著减少备份所需时间和存储空间。
增量备份示例:
bash
python GaussRoach.py -t backup --master-port 6000 --media-destination /backup/media --media-type Disk --metadata-destination /backup/media/metadata --parallel-process 3 --compression-level 6 --prior-backup-key 20200629_161652
5.2 账本数据库恢复
对于账本数据库,GaussDB提供了专门的修复函数:
sql
-- 对某个DN节点进行历史表修复操作
EXECUTE DIRECT ON (datanode1) 'select pg_catalog.ledger_hist_repair(''ledgernsp'', ''usertable'');';
-- 执行全局区块表修复操作
SELECT pg_catalog.ledger_gchain_repair('ledgernsp', 'usertable');
5.3 表级时间点恢复
GaussDB支持将数据库中的单个或者某些表恢复到指定时间点,这在误删除少量数据时非常有用,无需恢复整个实例。
6 备份恢复策略最佳实践
6.1 备份策略建议
- 定期自动备份:设置定期自动备份策略,确保数据安全
- 多地存储:将备份文件存储在不同地理位置,提高容灾能力
- 定期验证:定期验证备份文件的完整性和可恢复性
- 监控告警:设置备份失败告警,及时处理备份异常
6.2 恢复演练计划
- 定期演练:每季度至少进行一次恢复演练
- 文档记录:详细记录恢复步骤和注意事项
- 团队培训:确保多名管理员掌握备份恢复技能
6.3 性能优化建议
- 业务低峰期:在业务低峰期执行备份操作
- 增量备份:结合全量备份和增量备份,平衡效率与可靠性
- 并行处理:适当使用并行备份提高效率
7 常见问题与解决方案
问题1:自动备份失败怎么办?
可能原因和解决方案:
- 网络环境稳定性不足:系统会自动重试,也可手动备份
- 实例状态异常:等待实例恢复正常后再次执行备份
- 参数修改异常:检查参数设置,恢复默认参数重启实例
问题2:如何找回误删除的数据?
- 使用备份文件恢复:通过备份恢复误删除的数据
- 表级时间点恢复:如果只是部分表数据丢失,可使用表级恢复
问题3:备份文件如何收费?
- GaussDB提供了和实例磁盘大小相同的免费存储空间
- 只有超过免费容量的存储空间才开始计费
- 手动备份不会随实例删除而自动删除
- 点赞
- 收藏
- 关注作者
评论(0)