目录
方法一(使用recovery_target_xid,事务ID 进行回放)
引言
在开发过程由于权限控制,或者开发规范性不足 容易产生误操作,此时了解备份恢复就尤为重要。
原理
我们把数据库备份出来,被误操作后通过指定的时间、lsn、事务ID 、时间线节点,对备份库进行重放,找到误操作前的数据移植到原先数据库中去。指定的节点参数最多只能使用一个,如果在配置文件中使用了多个,将使用最后一个。
相关参数
recovery_target_lsn:
此参数指定恢复将继续进行的预写日志位置的LSN。精确的停靠点也受 recovery_target_inclusive的影响。 使用系统数据类型pg_lsn解析此参数。
recovery_target_name:
这个参数指定(pg_create_restore_point()
所创建)的已命名的恢复点,恢复将进入该恢复点。
recovery_target_time:
这个参数指定恢复将进入的时间戳。
recovery_target_xid:
这个参数指定恢复将进入的事务 ID。记住虽然事务 ID 是在事务开始时顺序分配的,但是事务可能以不同的数字顺序完成。那些在指定事务之前(也可以包括该事务)提交的事务将被恢复。精确的停止点也受到recovery_target_inclusive的影响。
recovery_target_timeline
:指定恢复到一个特定的时间线中。默认值是沿着基础备份建立时的当前时间线恢复。将这个参数设置为latest
会恢复到该归档中能找到的最新的时间线,这在一个后备服务器中有用。除此之外,你只需要在复杂的重恢复情况下设置这个参数,在这种情况下你需要返回到一个状态,该状态本身是在一次时间点恢复之后到达的。
recovery_target_inclusive
:指定我们是否仅在指定的恢复目标之后停止(on
)恢复包含恢复当前指定的恢复节点,或者仅在恢复目标之前停止(off
)恢复不包含指定的节点。 适用于recovery_target_lsn、recovery_target_time或者recovery_target_xid被指定的情况
注意:这里使用recovery_target_time,recovery_target_lsn 回放方式较为常见。
recovery_target_action
:
使用pause
设置的目的是:备库回访完启动后,只读状态。 调用pg_wal_replay_resume()
函数就可以恢复读写状态,
使用shutdown
设置的目的是:备份将无法启动,除非该配置被改变或者recovery.conf
文件被手工移除。
使用promote
设置的目的是:备库回访完启动后,可读可写状态。
基础备份
作为数据库DBA要定期巡查数据备份情况,当意识到数据或表被误删后,方便利用备份数据读取运行库的归档数据进行重放。
步骤1:基础备份
步骤2:模拟误操作,确定要重放到的节点
步骤3:切换wal日志
步骤4:修改端口,配置参数
步骤5:touch recovery.signal --启动重放模式
步骤6:启动数据库,常看表数据
步骤7:数据迁移至主库
步骤8:删除备份库
首先
配置运行库的两个参数
--运行库 创建归档路径
mkdir -p /home/postgres/archive_dir/
chown -R postgres:postgres /home/postgres/archive_dir/
--打开归档参数
archive_mode = on ---打开归档模式
archive_command = 'test ! -f /home/postgres/archive_dir/%f && cp %p /home/postgres/archive_dir/%f' --设置归档路径
方法一(使用recovery_target_xid,事务ID 进行回放
)
使用recovery_target_xid,事务ID 进行
重放
创建基础备份
pg_basebackup -Fp -P -v -D backup/ --备份地址和名称可以自定义
pg_verifybackup backup/ --检查是否成功有效的备份(通过校验backup/backup_manifest)
more backup/backup_label --检查备份文件的相关起始状态
模拟误操作
create table pg_text(id int,remark varchar);
insert into pg_text(id ,remark) select n,'text' from generate_series(1,10) as n;
select txid_current(); --记录一个回放时间节点
drop table pg_text ; --模拟删表误操作
select pg_switch_wal(); --误操作后迅速切换wal日志 使其在wal_buffer中的记录落盘并归档。
我在删表误操作前的事务ID 为816 其实我需要回放到删表前的数据
修改备份库的端口 并指定相关参数
vim backup/postgresql.conf --编辑备份库的相关配置参数信息
restore_command = 'cp /home/postgres/archive_dir/%f %p' --在备份指定其回放时,从归档路径中寻找历史wal
recovery_target_xid = '816' --执行要回放的事务ID节点
port = 5433 --指定不同于运行库的端口号
修改完参数启动数据库前
touch recovery.signal --启动回放模式
pg_ctl -D backup/ start -p 5433 --启动数据库
启动备份库 查看表数据,此时的表数据已经恢复
进入备份库时会有这样的提示:HINT: Execute pg_wal_replay_resume() to promote.
由于备份库的参数中没有特别定义recovery_target_action
参数,其默认值为pause(只读)
此时我们的数据库是一个只读状态,可以通过在会话中执行一下函数,打开读写功能
select pg_wal_replay_resume() ;
此时我们可以通过copy指定将数据导出,传入至运行便可。copy指令不再累述。
方法二(通过recovery_target_name回放
)
通过recovery_target_name
重放,需要在操作中使用
pg_create_restore_point()创建重放的name,
将原先的备份删掉,
pg_basebackup -Fp -P -v -D backup/ --备份地址和名称可以自定义
pg_verifybackup backup/ --检查是否成功有效的备份(通过校验backup/backup_manifest)
more backup/backup_label --检查备份文件的相关起始状态
模拟误操作
drop table if exists pg_text;
create table pg_text(id int,remark varchar);
insert into pg_text(id ,remark) select n,'text' from generate_series(1,10) as n;
select pg_create_restore_point('restore_point'); --记录一个回放时间节点
drop table pg_text ; --模拟删表误操作
select pg_switch_wal(); --误操作后迅速切换wal日志 使其在wal_buffer中的记录落盘并归档。
修改备份相关参数
vim backup/postgresql.conf --编辑备份库的相关配置参数信息
restore_command = 'cp /home/postgres/archive_dir/%f %p' --在备份指定其回放时,从归档路径中寻找历史wal
recovery_target_name = 'restore2' --执行要回放到的节点名称
port = 5433 --指定不同于运行库的端口号
修改完参数后定义为回放启动模式
touch recovery.signal --启动回放模式
pg_ctl -D backup/ start -p 5433 --启动数据库
此时可以在备份库中看到被删除的数据
方法三(通过recovery_target_lsn
)
将此前的备份删除掉 再次执行一次备份
pg_basebackup -Fp -P -v -D backup/ --备份地址和名称可以自定义
pg_verifybackup backup/ --检查是否成功有效的备份(通过校验backup/backup_manifest)
more backup/backup_label --检查备份文件的相关起始状态
模拟误操作
drop table if exists pg_text;
create table pg_text(id int,remark varchar);
insert into pg_text(id ,remark) select n,'text' from generate_series(1,10) as n;
SELECT pg_current_wal_lsn(); --记录一个回放LSN节点
drop table pg_text ; --模拟删表误操作
select pg_switch_wal(); --误操作后迅速切换wal日志 使其在wal_buffer中的记录落盘并归档。
修改备份库相关参数
此时我们需要重放到LSN为 0/8601C350删表前的数据状态
vim backup/postgresql.conf --编辑备份库的相关配置参数信息
restore_command = 'cp /home/postgres/archive_dir/%f %p' --在备份指定其回放时,从归档路径中寻找历史wal
recovery_target_lsn = '0/8601C350' --执行要回放到的节点名称
port = 5433 --指定不同于运行库的端口号
修改参数后进行启动数据库
touch recovery.signal --启动回放模式
pg_ctl -D backup/ start -p 5433 --启动数据库
此时可以看到数据已经恢复。
方法四(通过recovery_target_time
)
删除备份,重新备份此处不在叙述
模拟误操作
drop table if exists pg_text;
create table pg_text(id int,remark varchar);
insert into pg_text(id ,remark) select n,'text' from generate_series(1,10) as n;
SELECT current_timestamp; --记录一个回放时间戳节点
drop table pg_text ; --模拟删表误操作
select pg_switch_wal(); --误操作后迅速切换wal日志 使其在wal_buffer中的记录落盘并归档。
备注:整文都是在已知确切的回放节点上进行回放,在实际生产中往往只知道大概时间,甚至不知道具体时间,此时记录数据变更语句就非常重要
详细介绍在以下blog中,将数据库所有数据变更操作语句记录到CSV文件中
后续集中恢复方式相似,修改对应的相关重放节点便可。
操作如有疑问,可以私信咨询。