写点什么

PostgreSQL 误删数据怎么办?

  • 2019-08-28
  • 本文字数:3623 字

    阅读完需:约 12 分钟

PostgreSQL 误删数据怎么办?

本文经授权转载自 PostgreSQL 中文社区。


PostgreSQL 并未有闪回和数据误删除保护的功能,但是在一些场景下也可以实现。


此方法仅仅针对 DML 操作有效,DDL 操作(drop/truncate 等会将数据页面删除)无法找回数据。由于 PG 是多版本实现机制,因此数据仍然都在,只是不可见而已,vacuum_defer_cleanup_age 可以防止最近这些事务的元祖被删除,即保留这些事务操作的元组,可以闪回到这些操作的任意时间点

开始准备数据

test=# select pg_current_wal_lsn();
pg_current_wal_lsn
0/2003B58(1 row)
test=# create table lzzhang(id int);
CREATE TABLE
test=# insert into lzzhang values(1);
INSERT 0 1
test=# insert into lzzhang values(2);
INSERT 0 1
test=# insert into lzzhang values(3);
INSERT 0 1
test=# insert into lzzhang values(4);
INSERT 0 1
test=# insert into lzzhang values(5);
INSERT 0 1
test=# insert into lzzhang values(6);
INSERT 0 1
test=# select xmin, xmax, cmin, cmax, * from lzzhang;xmin | xmax | cmin | cmax | id——+——+——+——+—-
588 | 0 | 0 | 0 | 1
589 | 0 | 0 | 0 | 2
590 | 0 | 0 | 0 | 3
591 | 0 | 0 | 0 | 4
592 | 0 | 0 | 0 | 5
593 | 0 | 0 | 0 | 6
(6 rows)
复制代码

回滚 Insert

分析 redo,根据时间找到自己的错误操作,并找到对应的事务号


./pg_waldump -b -s 0/2003B58 -p dj
rmgr: Transaction len (rec/tot): 34/ 34, tx: 589, lsn: 0/0201A660, prev 0/0201A620, desc: COMMIT 2019-03-26 10:55:05.685536 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 590, lsn: 0/0201A688, prev 0/0201A660, desc: INSERT off 3
blkref #0: rel 1663/16392/16393 fork main blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 590, lsn: 0/0201A6C8, prev 0/0201A688, desc: COMMIT 2019-03-26 10:55:07.749260 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 591, lsn: 0/0201A6F0, prev 0/0201A6C8, desc: INSERT off 4
blkref #0: rel 1663/16392/16393 fork main blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 591, lsn: 0/0201A730, prev 0/0201A6F0, desc: COMMIT 2019-03-26 10:55:09.893856 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 592, lsn: 0/0201A758, prev 0/0201A730, desc: INSERT off 5
blkref #0: rel 1663/16392/16393 fork main blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 592, lsn: 0/0201A798, prev 0/0201A758, desc: COMMIT 2019-03-26 10:55:11.917570 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 593, lsn: 0/0201A7C0, prev 0/0201A798, desc: INSERT off 6
复制代码


此示例中,我们回滚 5 和 6 的数据。根据 wal 信息找到事务号为 592。


关闭数据库[lzzhang@lzzhang-pc bin]$ ./pg_resetwal -D dj -x 592启动数据库
复制代码


查询


test=# select xmin, xmax, cmin, cmax, * from lzzhang;
xmin | xmax | cmin | cmax | id------+------+------+------+----588 | 0 | 0 | 0 | 1589 | 0 | 0 | 0 | 2590 | 0 | 0 | 0 | 3591 | 0 | 0 | 0 | 4
复制代码


5/6 的数据已经没有了。增长事务号,看看情况!!!


test=# select * from txid_current(); txid_current--------------592(1 row)
复制代码


test=# select * from txid_current();txid_current-------------- 593(1 row)
复制代码


test=# select * from txid_current();txid_current--------------594
复制代码


test=# select xmin, xmax, cmin, cmax, * from lzzhang;xmin | xmax | cmin | cmax | id------+------+------+------+----588 |    0 |    0 |    0 |  1 589 |    0 |    0 |    0 |  2590 |    0 |    0 |    0 |  3591 |    0 |    0 |    0 |  4592 |    0 |    0 |    0 |  5593 |    0 |    0 |    0 |  6
复制代码


由于 5/6 仍然在数据库中,所以 5/6 又可见了。

回滚 delete

删除数据


test=# select pg_current_wal_lsn();pg_current_wal_lsn--------------------0/3000190(1 row)

复制代码


test=# delete from lzzhang where id = 5 or id = 6;DELETE 2
复制代码


找到事物号(595)


rmgr: Heap        len (rec/tot):     59/   299, tx:        595, lsn: 0/030001B8, prev 0/03000180, desc: DELETE off 5 KEYS_UPDATED , blkref #0: rel 1663/16392/16393 blk 0 FPW
复制代码


rmgr: Heap        len (rec/tot):     54/    54, tx:        595, lsn: 0/030002E8, prev 0/030001B8, desc: DELETE off 6 KEYS_UPDATED , blkref #0: rel 1663/16392/16393 blk 0
复制代码


rmgr: Transaction len (rec/tot):     34/    34, tx:        595, lsn: 0/03000320, prev 0/030002E8, desc: COMMIT 2019-03-26 11:00:23.410557 CST
复制代码


回滚数据


关闭数据库[lzzhang@lzzhang-pc bin]$ ./pg_resetwal -D dj -x 595启动数据库
复制代码


查看数据


test=# select xmin, xmax, cmin, cmax, * from lzzhang;xmin | xmax | cmin | cmax | id------+------+------+------+----588 |    0 |    0 |    0 |  1589 |    0 |    0 |    0 |  2590 |    0 |    0 |    0 |  3591 |    0 |    0 |    0 |  4592 |  595 |    0 |    0 |  5593 |  595 |    0 |    0 |  6
复制代码


提升事务号,5/6 又被删除


test=# select * from txid_current();txid_current--------------595(1 row)
复制代码


test=# select * from txid_current();txid_current--------------596(1 row)
复制代码


test=# select xmin, xmax, cmin, cmax, * from lzzhang;xmin | xmax | cmin | cmax | id------+------+------+------+----588 |    0 |    0 |    0 |  1589 |    0 |    0 |    0 |  2590 |    0 |    0 |    0 |  3591 |    0 |    0 |    0 |  4(4 rows)
复制代码

回滚 update

回滚 update 的操作类似,就不在讲述。

回滚 drop table

回滚后表可见,但是数据已经没有了 test=# select * from zz;ERROR: could not open file “base/16392/16396”: 没有那个文件或目录根据你的需要回滚/闪回指定操作,然后使用 txid_current()提升事物号,并不影响数据库整体的可用性。

确定数据仍然存在

安装 pageinspect 插件

create extension pageinspect

检查数据是否存在

查看目前表中的内容


test=# select xmin, xmax, cmin, cmax, * from lzzhang;xmin | xmax | cmin | cmax | id——+——+——+——+—–588 | 0 | 0 | 0 | 1589 | 0 | 0 | 0 | 2590 | 0 | 0 | 0 | 3597 | 0 | 0 | 0 | 100
复制代码


查看表的 page 内容(无效/不可见的元组都可以查看到)


test=# select * from heap_page_items(get_raw_page(‘lzzhang’, ‘main’, 0));lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data—-+——–+———-+——–+——–+——–+———-+——–+————-+————+——–+——–+——-+————1 | 8160 | 1 | 28 | 588 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x010000002 | 8128 | 1 | 28 | 589 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x020000003 | 8096 | 1 | 28 | 590 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x030000004 | 8064 | 1 | 28 | 591 | 597 | 0 | (0,7) | 16385 | 1280 | 24 | | | \x040000005 | 8032 | 1 | 28 | 592 | 595 | 0 | (0,5) | 8193 | 1280 | 24 | | | \x050000006 | 8000 | 1 | 28 | 593 | 595 | 0 | (0,6) | 8193 | 1280 | 24 | | | \x060000007 | 7968 | 1 | 28 | 597 | 0 | 0 | (0,7) | 32769 | 10496 | 24 | | | \x64000000
复制代码


vacuum 后查看 page 数据 vacuum lzzhang;


test=# select * from heap_page_items(get_raw_page(‘lzzhang’, ‘main’, 0));lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data—-+——–+———-+——–+——–+——–+———-+——–+————-+————+——–+——–+——-+————1 | 8160 | 1 | 28 | 588 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x010000002 | 8128 | 1 | 28 | 589 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x020000003 | 8096 | 1 | 28 | 590 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x030000004 | 7 | 2 | 0 | | | | | | | | | |5 | 0 | 0 | 0 | | | | | | | | | |6 | 0 | 0 | 0 | | | | | | | | | |7 | 8064 | 1 | 28 | 597 | 0 | 0 | (0,7) | 32769 | 10496 | 24 | | | \x64000000(7 rows)
复制代码


4/5/6 已经被 vacuum 掉,通过 waldump 可以找到对应的事务号的元祖已经不在,所有无法找回。


作者简介:


张连壮,多年 PostgreSQL 数据库内核研发经验,高可用/数据复制方面经验较为丰富,目前主要从事分布式数据库 Citus 相关工作,CitusDB 中国【站主】专注于 Citus 技术分享的全信息平台。


原文链接:


https://mp.weixin.qq.com/s/FbA8haWVOwRcw4CKNP0Vig


2019-08-28 11:336233

评论

发布
暂无评论
发现更多内容

两万字带你了解Java多线程(详细大总结)

Java快了!

中国的时区为什么是Asia/Shanghai,而不是Asia/Beijing?

Sher10ck

ApacheCon Asia 2022 精彩回顾 | 如何让更多人从大数据中获益?

白鲸开源

Redis数据倾斜与JD开源hotkey源码分析揭秘

京东科技开发者

数据库 数据倾斜 key Redis 数据结构 redis\

如何利用OpenHarmony ArkUI的Canvas组件实现涂鸦功能?

OpenHarmony开发者

OpenHarmony

Java之static关键字的应用【工具类、代码块和单例】

Fire_Shield

static 9月月更 实际应用

SQL为什么历经半个世纪却经久不衰?

雨果

sql

2022年8月中国网约车领域月度观察

易观分析

网约车

3个轻量级物联网新品实验,带您深度体验IoT开发

华为云开发者联盟

物联网 沙箱实验 企业号九月金秋榜

一文了解循环神经网络

华为云开发者联盟

人工智能 语音识别 企业号九月金秋榜

新书上市|一位家长的忠告:长大后不成才的孩子,父母都忽视了这个点!

图灵社区

育儿 教育 脑科学 基因

新书上市|一位家长的忠告:长大后不成才的孩子,父母都忽视了这个点!

图灵教育

育儿 教育 脑科学 基因

大佬就是强!意外收获史诗级分布式资源,从基础到进阶,干货满满!

收到请回复

Java 云计算 开源 架构 编程语言

云原生底座之上,顺丰智慧供应链领跑的秘密

华为云开发者联盟

云计算 云原生 后端 企业号九月金秋榜

SAP Cloud Application Programming 编程模型(CAP)的设计准则

汪子熙

CAP Cloud SAP Cloud Studio 9月月更

区块链追溯:让冷链物流“热”起来!

旺链科技

区块链 产业区块链 企业号九月金秋榜 冷链物流

聊聊数据库主键那点事儿

Steven

TiFlash 源码阅读(九)TiFlash 中常用算子的设计与实现

PingCAP

#TiDB TiDB 源码解读

面了个阿里拿38k出来的,让我见识到了基础顶端

程序知音

Java java面试 后端技术 秋招 八股文

技术分享| 分布式系统中服务注册发现组件的原理及比较

anyRTC开发者

音视频 分布式系统

【微信小程序】页面导航详解

陈橘又青

9月月更

为啥是SQL?互联网投资回报比最高的技能是什么?

雨果

sql

马蹄链Dapp系统开发(智能合约)

薇電13242772558

如何进行 Apache Doris 集群 Docker 快速部署

SelectDB

数据库 Doris Docker 镜像 安装 & 部署 企业号九月金秋榜

提高数据可视化效果的五个原则

博文视点Broadview

MobTech短信验证ApiCloud端SDK

MobTech袤博科技

API 短信验证

漏洞管理流程

SEAL安全

漏洞修复 漏洞管理 企业号九月金秋榜

DataLeap的Catalog系统近实时消息同步能力优化

字节跳动数据平台

大数据 kafka 数据治理 实时同步 数据研发

融云云盘,不止于存储

融云 RongCloud

云盘 云存储

PostgreSQL 误删数据怎么办?_数据库_张连壮_InfoQ精选文章