云贝教育 |【技术文章】PostgreSQL中误删除数据怎么办（一）

2023-12-08 22:28
文章标签 数据 技术 postgresql 文章 教育 误删除 云贝

本文主要是介绍云贝教育 |【技术文章】PostgreSQL中误删除数据怎么办（一），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

原文链接：【PostgreSQL】PostgreSQL中误删除数据怎么办（一） - 课程体系 - 云贝教育 (yunbee.net)

在我们学习完PG的MVCC机制之后，对于DML操作，被操作的行其实并未被删除，只能手工vacuum或自动vacuum触发才会清理掉这些无效数据，也就是死元组。

基于这种机制，在发生死元组清理动作之前，只需要将其中不可见的行中的数据解析出来，或者发生脏读，就可以获取到误删除的数据。虽然PG不支持脏读，但今天介绍的pg_dirtyread插件，可以实现脏读。

一、安装pg_dirtyread

下载地址

GitHub - df7cb/pg_dirtyread: Read dead but unvacuumed tuples from a PostgreSQL relation

这个网页上有详细的安装说明

编译安装

unzip pg_dirtyread-master.zip
cd pg_dirtyread-master/
make
make install

二、使用示例

2.1、在对应库创建EXTENSION
create extension pg_dirtyread ;

2.2、创建表并禁用autovacuum
testdb=# CREATE TABLE t1 (id int, name text);
CREATE TABLE
2.3、插入并通过heap_page_items查看数据
testdb=# INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'),(3,'ccc');
INSERT 0 3
testdb=#
testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0));tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------------1 |   1104 |      0 |     0 | (0,1)  | \x0100000000000000096161612 |   1104 |      0 |     0 | (0,2)  | \x0200000000000000096262623 |   1104 |      0 |     0 | (0,3)  | \x030000000000000009636363
(3 rows)
2.4、删除数据
testdb=# DELETE FROM t1 WHERE id = 1;
DELETE 1
testdb=# DELETE FROM t1 WHERE id = 2;
DELETE 1testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0));tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------------1 |   1104 |   1105 |     0 | (0,1)  | \x0100000000000000096161612 |   1104 |   1106 |     0 | (0,2)  | \x0200000000000000096262623 |   1104 |      0 |     0 | (0,3)  | \x030000000000000009636363
(3 rows)

这里发现被删除的数据还在块中

2.5、 发现数据被误删除后第一时间关掉表上的vacuum

这一步很关键！！！

这一步很关键！！！

这一步很关键！！！

ALTER TABLE t1 SET (
autovacuum_enabled = false, toast.autovacuum_enabled = false
);

2.6、查看表vacuum情况
testdb=# \x
Expanded display is on.
testdb=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+-------
relid               | 49546
schemaname          | public
relname             | t1
seq_scan            | 3
seq_tup_read        | 6
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 3
n_tup_upd           | 0
n_tup_del           | 2
n_tup_hot_upd       | 0
n_live_tup          | 1
n_dead_tup          | 2
n_mod_since_analyze | 5
n_ins_since_vacuum  | 3
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

last_vacuum和last_autovacuum都是空的，表示还未被vacuum过。

2.7、使用pg_dirtyread查看表，dead为t表示数据已 经删除
testdb=# SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boole an,id int, name text);tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name
----------+-------+------+------+------+------+------+----+------49546 | (0,1) | 1104 | 1105 |    0 |    0 | t    |  1 | aaa49546 | (0,2) | 1104 | 1106 |    0 |    0 | t    |  2 | bbb49546 | (0,3) | 1104 |    0 |    0 |    0 | f    |  3 | ccc
(3 rows)

删除数据后，需要查询一下，pg_dirtyread中的dead列才会更新。也就是pg_dirtyread需要扫描一次表中的page才知道该行是不是被修改过。

testdb=# delete from t1;
DELETE 3testdb=# SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text);
tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | name
----------+-------+------+------+------+------+------+-----+-----
49541 | (0,1) | 1102 | 1108 | 0 | 0 | f | 1 | aaa
49541 | (0,2) | 1102 | 1108 | 0 | 0 | f | 2 | bbb
49541 | (0,3) | 1102 | 1108 | 0 | 0 | f | 3 | ccc
(3 rows)testdb=# select * from t1;
id | name
-----+-----
(0 rows)testdb=# SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text);
tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | name
----------+-------+------+------+------+------+------+-----+-----
49541 | (0,1) | 1102 | 1108 | 0 | 0 | t | 1 | aaa
49541 | (0,2) | 1102 | 1108 | 0 | 0 | t | 2 | bbb
49541 | (0,3) | 1102 | 1108 | 0 | 0 | t | 3 | ccc
(3 rows)
2.8 恢复到某个时间

如果做不完全恢复，即恢复数据到某个时刻，需要使用函数pg_xact_commit_timestamp将事务ID进行转换。

testdb=# alter system set track_commit_timestamp=on;
ALTER SYSTEM#删除一条数据
testdb=# select * from t1;
id | name
----+------
5 | EEE
(1 row)testdb=# delete from t1;
DELETE 1#查看删除时间
testdb=# SELECT pg_xact_commit_timestamp(xmin) xmin_time
,pg_xact_commit_timestamp(xmax) xmax_time
,*
FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text)
where xmax<>0;
-[ RECORD 1 ]----------------------------
xmin_time | 2023-12-03 16:27:03.830358+08
xmax_time | 2023-12-06 10:10:29.115887+08
tableoid | 49776
ctid | (0,2)
xmin | 7207
xmax | 7235
cmin | 0
cmax | 0
dead | f
id | 5
name | EEE

xmax_time 就是数据具体删除时间

2.9、pg_dirtyread还支持被删除的列
testdb=# select * from t1;
id | name
----+------
3 | ccc
(1 row)testdb=#
testdb=# ALTER TABLE t1 DROP COLUMN name;
ALTER TABLE
testdb=# SELECT * FROM pg_dirtyread('t1') t1(id int, dropped_2 text);
id | dropped_2
----+-----------
1 | aaa
2 | bbb
3 | ccc
3 | ccc
(4 rows)testdb=# select * from t1;
id
----
3
(1 row)

三、如果表上已经发生了vacuum

3.1、对表进行vacuum回收死元组
postgres=# vacuum t1;
VACUUM
3.2、查看块中的数据被清理
testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data F
ROM heap_page_items(get_raw_page('t1', 0));
tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data
-------+--------+--------+-------+--------+----------------------------1 |        |        |       |        |2 |        |        |       |        |3 |        |        |       |        |4 |   1110 |      0 |     0 |  (0,4) | \x030000000000000009636363
(4 rows)

3.3、再次用pg_dirtyread查看死元组的数据已经被清理了

testdb=# \x
Expanded display is on.
testdb=# select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid | 49546
schemaname | public
relname | t1
seq_scan | 8
seq_tup_read | 33
idx_scan |
idx_tup_fetch |
n_tup_ins | 4
n_tup_upd | 0
n_tup_del | 3
n_tup_hot_upd | 0
n_live_tup | 1
n_dead_tup | 0
n_mod_since_analyze | 7
n_ins_since_vacuum | 0
last_vacuum | 2023-12-01 14:55:44.099392+0821 last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 1
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0testdb=# SELECT * FROM pg_dirtyread('t1') t1(id int, dropped_2 text);
id | dropped_2
----+-----------
3 | ccc
(1 row)

这种场景下，就无法通过脏块或解析死元组中的数据信息来恢复数据库，下一篇介绍WALMINER恢复误删除的数据。

总结

如果不小心误删除了数据，可以通过特殊手段来恢复数据的，具体恢复步骤如下：

1. 对表执行禁用vacuum（特别强调，这一步非常重要）

ALTER TABLE t1 SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

2. 使用pg_dirtyread插件查询被删除的数据，同时将数据抽取到中间表

create table t1_bak select id ,name from ((SELECT * FROM pg_dirtyread('t1')
AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text))) as foo;

另外，如何要找的数据己被vacuum，还可以通过分析数据具体被删除的时间，然后通过WalMiner解析wal日志，找到对应的时间点，生成undo sql(如果执行的delete，undo sql就是insert语句)。

这篇关于云贝教育 |【技术文章】PostgreSQL中误删除数据怎么办（一）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/471503。

相关文章

大模型研发全揭秘：客服工单数据标注的完整攻略

大模型研发全揭秘：客服工单数据标注的完整攻略

在人工智能（AI）领域，数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者，掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中，工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注，不仅能够帮助提升客服自动化系统的智能化水平，还能优化客户服务流程，提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
关于数据埋点，你需要了解这些基本知识

关于数据埋点，你需要了解这些基本知识

产品汪每天都在和数据打交道，你知道数据来自哪里吗？ 移动app端内的用户行为数据大多来自埋点，了解一些埋点知识，能和数据分析师、技术侃大山，参与到前期的数据采集，更重要是让最终的埋点数据能为我所用，否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式，可以区分为： 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理：自动程度高的，能解决通用统计，便于统一化管理，但个性化定
阅读更多...
使用SecondaryNameNode恢复NameNode的数据

使用SecondaryNameNode恢复NameNode的数据

1）需求： NameNode进程挂了并且存储的数据也丢失了，如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2）故障模拟 （1）kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 （2）删除NameNode存储的数据（/opt/module/hadoop-3.1.4/data/tmp/dfs/na
阅读更多...
异构存储（冷热数据分离）

异构存储（冷热数据分离）

异构存储主要解决不同的数据，存储在不同类型的硬盘中，达到最佳性能的问题。 异构存储Shell操作 （1）查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies （2）为指定路径（数据存储目录）设置指定的存储策略 hdfs storagepolicies -setStoragePo
阅读更多...
Hadoop集群数据均衡之磁盘间数据均衡

Hadoop集群数据均衡之磁盘间数据均衡

生产环境，由于硬盘空间不足，往往需要增加一块硬盘。刚加载的硬盘没有数据时，可以执行磁盘数据均衡命令。（Hadoop3.x新特性） plan后面带的节点的名字必须是已经存在的，并且是需要均衡的节点。 如果节点不存在，会报如下错误： 如果节点只有一个硬盘的话，不会创建均衡计划： （1）生成均衡计划 hdfs diskbalancer -plan hadoop102 （2）执行均衡计划 hd
阅读更多...
Andrej Karpathy最新采访：认知核心模型10亿参数就够了，AI会打破教育不公的僵局

Andrej Karpathy最新采访：认知核心模型10亿参数就够了，AI会打破教育不公的僵局

夕小瑶科技说 原创  作者 | 海野 AI圈子的红人，AI大神Andrej Karpathy，曾是OpenAI联合创始人之一，特斯拉AI总监。上一次的动态是官宣创办一家名为 Eureka Labs 的人工智能+教育公司 ，宣布将长期致力于AI原生教育。 近日，Andrej Karpathy接受了No Priors（投资博客）的采访，与硅谷知名投资人 Sara Guo 和 Elad G
阅读更多...
【专题】2024飞行汽车技术全景报告合集PDF分享（附原数据表）

【专题】2024飞行汽车技术全景报告合集PDF分享（附原数据表）

原文链接： https://tecdat.cn/?p=37628 6月16日，小鹏汇天旅航者X2在北京大兴国际机场临空经济区完成首飞，这也是小鹏汇天的产品在京津冀地区进行的首次飞行。小鹏汇天方面还表示，公司准备量产，并计划今年四季度开启预售小鹏汇天分体式飞行汽车，探索分体式飞行汽车城际通勤。阅读原文，获取专题报告合集全文，解锁文末271份飞行汽车相关行业研究报告。 据悉，业内人士对飞行汽车行业
阅读更多...
【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

✨✨ 欢迎大家来到景天科技苑✨✨ 🎈🎈 养成好习惯，先赞后看哦~🎈🎈 🏆 作者简介：景天科技苑 🏆《头衔》：大厂架构师，华为云开发者社区专家博主，阿里云开发者社区专家博主，CSDN全栈领域优质创作者，掘金优秀博主，51CTO博客专家等。 🏆《博客》：Python全栈，前后端开发，小程序开发，人工智能，js逆向，App逆向，网络系统安全，数据分析，Django，fastapi
阅读更多...
【区块链 + 人才服务】可信教育区块链治理系统 | FISCO BCOS应用案例

【区块链 + 人才服务】可信教育区块链治理系统 | FISCO BCOS应用案例

伴随着区块链技术的不断完善，其在教育信息化中的应用也在持续发展。利用区块链数据共识、不可篡改的特性， 将与教育相关的数据要素在区块链上进行存证确权，在确保数据可信的前提下，促进教育的公平、透明、开放，为教育教学质量提升赋能，实现教育数据的安全共享、高等教育体系的智慧治理。 可信教育区块链治理系统的顶层治理架构由教育部、高校、企业、学生等多方角色共同参与建设、维护，支撑教育资源共享、教学质量评估、
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2