“All in Cloud”之后,和你聊聊「云原生DevOps的Kubernetes技巧」 了解详情
写点什么

PostgreSQL 误删数据怎么办?

  • 2019 年 8 月 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 年 8 月 28 日 11:334470

评论

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

数字新金融到底是什么样的金融

CECBC

🏆「推荐收藏」【Spring源码专题】彻底让你明白【IOC容器初始化】运行原理和源码流程

浩宇天尚

spring 容器 源码分析 7月日更

Vitalik Buterin眼中的区块链信任模型

CECBC

Python协程 & 异步编程(asyncio) 入门介绍

行者AI

#python

2022秋春招/提前批面经分享总结(字节、腾讯、阿里)

学无止境的阿奔

腾讯 面试 阿里 字节 校招

强!上线3天获10w浏览量,京东T8纯手码Redis缓存手册,我粉了

Java 数据库 redis 架构 面试

为技术系统打“疫苗”,爱奇艺攻防演练平台的探索实践

爱奇艺技术产品团队

架构 安全攻防 开发 混沌工程

字节跳动实习生删库高操作

学神来啦

Linux 程序员 职场搞笑 linux运维

[TcaplusDB]祖国富强统一,未来一路同行

TcaplusDB

数据库 nosql 后端 tencentdb TcaplusDB

国内市场主流音视频产品主要功能分析

anyRTC开发者

音视频 WebRTC 语音通话 实时通讯 视频通话

如何实现跨设备的双向连接? Labo涂鸦鸿蒙亲子版分布式开发技术分享

科技汇

新能源车的发展趋势

石云升

学习 新能源汽车 7月日更

建党纪念日| TcaplusDB为祖国喝彩,向党员致敬!

TcaplusDB

数据库 后端 tencentdb TcaplusDB

免费分享入门学习Java的优秀图书

Java入门到架构

Java 书籍 Java入门

科创人|容联七陌CEO陈光:90后街舞少年的CEO修炼手册

科创人

人工智能 创业 程序员

iOS 15隐私升级及注意事项

阿里巴巴淘系技术

ios WWDC21

云原生负载均衡和网关应用实践

火山引擎开发者社区

云原生 后端

花2个月备战字节,3轮面试拿下总包60W Offer!

Java架构师迁哥

CVPR2021竞赛结果出炉,阿里淘系多媒体算法包揽3项国际冠军

阿里巴巴淘系技术

CVPR

[建党纪念日] TcaplusDB为祖国喝彩,向党员致敬!

数据人er

数据库 nosql tencentdb TcaplusDB

模块1

cherrycheek

什么是数据仓库

奔向架构师

数据库 数据仓库 数据架构 话题讨论 7月日更

Flink知识点总结

五分钟学大数据

flink 7月日更

全表遍历并处理数据有点慢?放开!我来!

林一

jpa Stram @QueryHints

TcaplusDB君 · 行业新闻汇编(7月2日)

TcaplusDB

数据库 nosql 后端 TcaplusDB tencendb

将 FeignClient 的请求记录成 cURL 格式

哈德韦

微服务 前后端分离 log4j curl Spring boot Feign

大专的我,闭关苦学56天,含泪拿下阿里offer,五轮面试,六个小时灵魂拷问

Java架构师迁哥

[TcaplusDB] 行业新闻汇编(6月29日)

数据人er

数据库 nosql 游戏 tencentdb TcaplusDB

Linux常用命令-文件操作

正向成长

linux命令

[TcaplusDB 香港回归日]祝祖国富强统一,未来一路同行

数据人er

数据库 nosql tencentdb TcaplusDB

AQS介绍和原理分析(下)

追风少年

并发编程 AQS

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