本文经授权转载自 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
更多内容推荐
全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
今天，我跟你介绍了MySQL的全局锁和表级锁。
2018 年 11 月 26 日
增删改查：如何操作表中的数据？
我就给你讲讲表中数据的操作，也就是常说的“增删改查”。
2021 年 3 月 16 日
误删数据后除了跑路，还能怎么办？
今天，我和你讨论了误删数据的几种可能，以及误删后的处理方法。但，我要强调的是，预防远比处理的意义来得大。
2019 年 1 月 23 日
Docker 与 PostgreSQL 11.5 系列文章（三）：数据持久化
本文主要讨论容器的持久化。“持久化” 简单理解，就是容器被关闭后PostgreSQL数据库的数据是否还存在？
如何将关系型数据导入 MongoDB？
按照本文提供的方法和步骤，项目团队可以在数据迁移中减少不必要的时间和错误的操作。
一次大量删除导致 MySQL 慢查的分析
本文来自《2019年有赞技术大礼包》系列。
事务机制：Redis 能实现 ACID 属性吗？
如果有些属性在一些场景下不能保证的话，很可能会导致数据出错。
2020 年 10 月 30 日
分析型嵌入式键值数据库 hamsterdb
近日， Christoph Rupp在highscalability.com上发表了一篇文章，介绍由他创建的分析型嵌入式键值数据库hamsterdb。它用C/C++编写，是一个速度非常快的轻量级NoSQL数据库引擎，支持事务、数据库游标、内存数据库和远程网络访问，类似谷歌的leveldb和甲骨文的BerkeleyDB。
每个工程师都应该了解的：数据库知识
数据库可以说是互联网公司最宝贵的资产，这块不出问题也罢，一出问题，即是见血封喉，服务直接宕机。
2017 年 12 月 8 日
Tokutek 发布新版本的 TokuDB 和 TokuMX
Tokutek已经发布了其存储引擎产品的新版本。TokuDB for MySQL已经到了7.5版本，包含两项重要的性能特性。TokuMX for MongoDB已经到了2.0版本，包含各种新特性，其中包括其本身性能的提升。
为什么选择 PostgreSQL 而不是 MySQL
David Bolton使用PostgreSQL和MySQL都已有超过十年的时间。他认为，MySQL之所以仍然如此流行是因为每个Linux Web托管软件包中都包含它。但被Oracle收购后，MySQL的开源程度大不如前。而PostgreSQL不仅发展更快，还加入了JSON支持，成为少数几个支持NoSQL的关系型数据库之一。
三万倍提升，起飞的 PostgreSQL 主从优化实践
某些业务场景安全性要求很高，核心空间的数据不能随意修改，本文介绍腾讯云数据库PostgreSQL在大量drop业务场景下主从复制产生的性能问题，为大家完整剖析此次内核优化的原理和方案，最终让主从同步性能增强了3W多倍，并解决了社区一直悬而未决的问题。
MongoDB 4.0 有望支持跨文档事务
自MongoDB并购了WiredTiger及其关系数据库存储引擎以来，很多技术专家一直翘首以待MongoDB何时提供对跨文档事务（multi-document transaction）的支持。MongoDB在本周宣布，跨文档事务有望于今年夏天加入到MongoDB 4.0中。
十年难得一遇！从数据误删到全量恢复的惊险记录
本文介绍在线下自搭建环境的一次数据误删除事件。
Hive 事务管理避坑指南
Hive作为Hadoop家族历史最悠久的组件之一，一直以其优秀的兼容性支持和稳定性而著称，越来越多的企业将业务数据从传统数据库迁移至Hadoop平台，并通过Hive来进行数据分析。但是我们在迁移的过程中难免会碰到如何将传统数据库的功能也迁移到Hadoop的问题，比如说事务。事务作为传统数据库很重要的一个功能，在Hive中是如何实现的呢？Hive的实现有什么不一样的地方呢？我们将传统数据库的应用迁移到Hive如果有事务相关的场景我们该如何去转换并要注意什么问题呢？ 本文会通过很多真实测试案例来比较Hive与传统数据库事务的区别，并在文末给出一些在Hive平台上使用事务相关的功能时的指导和建议。
如何利用 PostgreSQL 的延迟复制实现灾备
GitLab网站的运营工作由GitLab基础设施团队负责，同时这也是GitLab目前最大的实例：拥有约300万用户和近700万个项目，是互联网上最大的单租户开源SaaS站点之一。
MySQL 数据库安全之审计
自建和云服务
MongoDB 安全加固实践
2020 年 1 月 15 日
「SQL 数据分析系列」12. 事务
写在前面： 大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与AI相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。
2021 年 7 月 30 日
MongoDB 发布 4.0 版本，支持 ACID 事务
MongoDB最近发布了最新的4.0版本。毫无疑问，这一版本的主要特性是支持多文档ACID事务，同时还提供了MongoDB Stitch、MongoDB Mobile和Mobile Sync、聚合管道中的新类型转换以及与Kubernetes的集成。
推荐阅读
CockroachDB 是如何实现分布式原子事务的？
为什么表数据删掉一半，表文件大小不变？
2018 年 12 月 12 日
MongoDB 索引机制（一）
2020 年 1 月 15 日
3 年部署 3000 套 PG 实例的架构设计与踩坑经验
数据备份：异常情况下，如何确保数据安全？
2021 年 4 月 27 日
PostgreSQL 9.4 版本增强了灵活性、扩展性、性能
高可用数据库 UDB 主从复制延时的解决
电子书
大厂实战PPT下载
换一换 
Jack Works | Sujitech 前端工程师
陆传胜 | 字节跳动 架构-编程语言 团队负责人
周志明 | 华为 SaaS 首席软件教练
评论