本文经授权转载自 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
更多内容推荐
2022 最新 MySQL 高频面试题汇总
本文已经收录到Github仓库，该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点，欢迎star~
2023-01-20
OceanBase 信息技术服务管理体系通过 ISO20000 认证和 ITSS 认证
近日，北京奥星贝斯科技有限公司（OceanBase 数据库）在获得 ISO9001、ISO14001、ISO45001 及 ISO27001 等认证后，又于技术服务方面通过了 ISO20000 认证和 ITSS 认证。
2023-03-30
VLDB'22 HiEngine 极致 RTO 论文解读
《Index Checkpoints for Instant Recovery in In-Memory Database Systems》是由华为云数据库创新Lab一作发表在数据库领域顶级会议VLDB'2022的学术论文。
2022-09-09
【我和 openGauss 的故事】使用 Ora2Pg 迁移 oracle 数据到 openGauss
DemonCharm [openGauss](javascript:void(0);) 2023-08-04 18:01 发表于四川
2023-08-07
20｜数据库方案设计：如何设计运营搭建平台的数据库？
数据库的设计范式，就是为了让数据的关系清晰，结构精简和减少冗余。不过，实际项目的数据库设计，与范式之间会有一些矛盾。
2023-01-09
Innodb 的 RR 是否彻底解决了幻读？
Innodb 的幻读问题
2023-02-13
Day433
每一个要使用分布式事务的数据库都需要一个 UNDO_LOG 表。
2022-05-08
12｜数据库锁：明明有行锁，怎么突然就加了表锁？
数据库锁
2023-07-12
4、事务底层原理 ReadView 案例
2023-09-27
21｜数据库 ORM 对象关系映射（一）：数据库连接与必备操作
这节课，我们一起来学习在线视频平台的数据管理基石——数据库。
2023-06-09
数字经济时代的开源数据库创新 | 2022 开放原子全球开源峰会数据库分论坛圆满召开
7 月 27 日，2022 开放原子全球开源峰会数据库分论坛在北京成功举办。论坛以 “数字经济时代的开源数据库创新” 为主题，围绕数据库前沿技术、数据库开源生态建设、数据库应用实践等方面开展技术交流和经验分享，促进数字经济产业快速发展。
2022-07-28
GreatSQL 社区月报 | 2023.04
GreatSQL 是一个开源的 MySQL 技术路线数据库社区，社区致力于通过开放的社区合作，构建国内自主 MySQL 版本及开源数据库技术，推动中国开源数据库及应用生态繁荣发展。
2023-05-12
10 分钟带你搞懂 MySQL 读写分离
2021-11-19
活动预告 | 中国数据库联盟（ACDU）中国行第二站定档杭州，邀您探讨数据库技术与实践！
8月19日（周六），OceanBase资深研发总监庄明强、PG中文社区常委唐成、亚信安慧副总裁张桦、阿里云资深技术专家陈宗志、沃趣科技CTO魏兴华与代维科技CTO周亮邀您参与【ACDU中国行·杭州站】，面对面聊聊数据库那些事儿！
2023-08-02
MySQL 查询数据库表记录数
最近在做统计想查找一个数据库里基本所有的表数据量,下面这篇文章主要给大家介绍了关于MySQL如何统计一个数据库所有表的数据量的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考下
2022-09-28
HTAP 的下一步？SoTP 初探（上）：从 “大” 数据到 “小” 而 “宽” 数据 —— 第七届中国开源年会（COSCon'22）
在今年的第七届中国开源年会上，StoneDB 团队在大数据分论坛发表了《HTAP 的下一步？SoTP 初探》主题演讲，在本次演讲中，我们首次正式对外阐释了“SoTP 数据库”的技术理念，本系列是演讲实录+小编补充版，权当抛砖引玉，供大家批评指正。由于内容比较多，本
2022-11-22
GreatSQL 社区月报 | 2023.03
GreatSQL 是一个开源的 MySQL 技术路线数据库社区，社区致力于通过开放的社区合作，构建国内自主 MySQL 版本及开源数据库技术，推动中国开源数据库及应用生态繁荣发展。
2023-04-10
数据库 CI/CD 工具 -- Bytebase 介绍
Bytebase 作为一款开源的数据库 CI/CD 工具，为 DBA 和开发人员提供了一个基于 Web 的工作区，以安全高效的方式协作和管理数据库的变更。
2023-03-29
02｜缓存一致：读多写少时，如何解决数据更新缓存不同步？
这节课，我会结合用户中心的一些业务场景，带你看看如何使用临时缓存或长期缓存应对高并发查询，帮你掌握高并发流量下缓存数据一致性的相关技巧。
2022-10-26
推荐阅读
中国数据库前世今生第 3 集：2000 年代 / 数据库分型及国产数据库开端
数据库系统概述之国产数据库
2023-11-29
InnoDB 存储引擎篇｜课后题答疑
2024-12-16
可跨平台数据库多连管理工具：Navicat Premium 16 中文版
2023-11-03
25｜数据库无法启动，如何读取 InnoDB 文件中的数据？（上）
2024-10-18
基础篇｜课后题答疑
2024-12-11
精彩回顾｜【ACDU 中国行·杭州站】数据库主题交流活动成功举办！
2023-08-23
电子书
大厂实战PPT下载
换一换 
邰翀 | Databend 研发工程师
毕鸣一 | 腾讯 CSIG 质量部 高级工程师、APM 项目技术负责人
熊飞 | 经纬中国 合伙人
评论