6、支持查询被删除列的历史数据
）机制来读取未提交的事务所影响的数据。此插件可以用于在开发和测试环境中快速查看事务对数据的即时影响，当关键表中的数据被意外删除后，业务系统可能会出现服务中断、数据丢失、数据不一致等问题，影响用户服务。通过使用 可以快速查看未提交的删除或更新事务，恢复数据，从而恢复正常服务、确保数据完整、数据一致。
在 PostgreSQL 中，事务提交后，如果数据尚未被 VACUUM 清理，仍有可能通过某些方式恢复这些数据。这是因为 PostgreSQL 采用了多版本并发控制（MVCC）机制，允许旧的元组（称为 Dead 元组）在事务提交后继续保留在系统中，直到被 VACUUM 清除。具体来说：
#安装依赖yum install postgresql16-devel clangwgetxvf 2.7.tar.gzmv pg_dirtyread-2.7/ /jesong/pgdata/contrib/cd /jesong/pgdata/contrib/make PG_CONFIG=/usr/pgsql-16/bin/pg_configmake install PG_CONFIG=/usr/pgsql-16/bin/pg_config# 登陆数据库 安装插件postgres=# CREATE EXTENSION pg_dirtyread;postgres=# select * from pg_available_extensions;postgres=# \dx#如果发现错误操作，导致数据意外删除，要第一时间关闭表上的vacuum#关闭vacuumALTER TABLE foo SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false);#表未被vacuumpostgres=# select * from pg_stat_all_tables where relname='foo';-[ RECORD 1 ]-------+-----------------------------last_vacuum |last_autovacuum |vacuum_count | 0autovacuum_count | 0--创建测试表CREATE TABLE saas (id bigint, name text, mail text);-- 测试方便，先把自动vacuum关闭掉。ALTER TABLE saas SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false);--插入数据INSERT INTO saas VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');--删除所有数据DELETE FROM saas ;postgres=# select * from saas;--获取到了已删除的数据postgres=# SELECT * FROM pg_dirtyread('saas') as t(id bigint, name text, mail text); id | name | mail----+-----------+------------ 1 | Test1 | 111@qq.com 2 | New Test2 | 111@qq.com(2 rows)CREATE TABLE saas1 (id bigint, name text, mail text);INSERT INTO saas1 VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');ALTER TABLE saas1 DROP COLUMN mail ;DELETE FROM saas1;postgres=# select * from saas1;--获取到了已删除列的数据postgres=# SELECT * FROM pg_dirtyread('saas1') t(id bigint, name text, dropped_3 text); id | name | dropped_3----+-----------+------------ 1 | Test1 | 111@qq.com 2 | New Test2 | 111@qq.com(2 rows)指定列的规则：使用dropped_N来访问第N列，从1开始计数。
由于 PostgreSQL 删除操作会移除原始列的元数据信息，因此在使用 pg_dirtyread 时，需要在表列名中显式指定正确的数据类型。这包括类型长度、对齐方式和类型修饰符，并且数据是按值传递的。
在读取 Dead 元组时，需要明确指定列的数据类型，包括长度和其他修饰符。
为了进行有效的数据恢复和完整性检查，必须确保类型信息的准确性。
如果你想恢复到某个特定时间点的数据，可以通过 pg_xact_commit_timestamp 系统函数来获取每个事务的提交时间。
使用 pg_xact_commit_timestamp 函数来获取每个事务的提交时间。这包括写入事务的提交时间（xmin）和删除或更新事务的提交时间（xmax）。
2.基于时间点的闪回查询:
有了这些提交时间，你可以根据需要恢复到的具体时间点来过滤数据，从而实现基于时间点的数据恢复。
--参数配置track_commit_timestamp = on--模拟数据 CREATE TABLE saas2 (id bigint, name text, mail text);INSERT INTO saas2 VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');INSERT INTO saas2 VALUES (3, 'Test3','111@qq.com'), (4, 'New Test4','111@qq.com');DELETE FROM saas2;--查询数据历史版本select pg_xact_commit_timestamp(xmin) as xmin_time,pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*from pg_dirtyread('saas2') as t(tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,id bigint, name text, mail text); xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | mail-------------------------------+------------------------------+----------+-------+------+------+------+------+----+-----------+------------ 2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,1) | 822 | 824 | 0 | 0 | 1 | Test1 | 111@qq.com 2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,2) | 822 | 824 | 0 | 0 | 2 | New Test2 | 111@qq.com 2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,3) | 823 | 824 | 0 | 0 | 3 | Test3 | 111@qq.com 2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,4) | 823 | 824 | 0 | 0 | 4 | New Test4 | 111@qq.com(4 rows)--查询某个时间点的数据SELECTpg_xact_commit_timestamp ( xmin ) AS xmin_time,pg_xact_commit_timestamp ( CASE xmax WHEN 0 THEN NULL ELSE xmax END ) AS xmax_time,*FROMpg_dirtyread ( 'saas2' ) AS t ( tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, id bigint, name text, mail text )WHERE pg_xact_commit_timestamp ( xmin ) <= '2024-09-13 16:51:10' and pg_xact_commit_timestamp ( CASE xmax WHEN 0 THEN NULL ELSE xmax END ) <= '2024-09-13 16:51:10'; xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | mail-------------------------------+------------------------------+----------+-------+------+------+------+------+----+-----------+------------ 2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,1) | 822 | 824 | 0 | 0 | 1 | Test1 | 111@qq.com 2024-09-13 16:49:10.096164+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,2) | 822 | 824 | 0 | 0 | 2 | New Test2 | 111@qq.com 2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,3) | 823 | 824 | 0 | 0 | 3 | Test3 | 111@qq.com 2024-09-13 16:50:10.699546+08 | 2024-09-13 16:50:15.77861+08 | 16465 | (0,4) | 823 | 824 | 0 | 0 | 4 | New Test4 | 111@qq.com(4 rows)支持查询被删除列的历史数据
--创建表CREATE TABLE saas3 (id bigint, name text, mail text);INSERT INTO saas3 VALUES (1, 'Test1','111@qq.com'), (2, 'New Test2','111@qq.com');INSERT INTO saas3 VALUES (3, 'Test3','111@qq.com'), (4, 'New Test4','111@qq.com');--添加字段alter table saas3 add column qq text;--写入数据postgres=# INSERT INTO saas3 VALUES (5, 'Test3','111@qq.com','qq1223'), (6, 'New Test4','111@qq.com','qq234');--删除列postgres=# alter table saas3 drop column mail;--删除某条数据postgres=# delete from saas3 where id=6;--获取被删除的列数据postgres=# select pg_xact_commit_timestamp(xmin) as xmin_time,pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*from pg_dirtyread('saas3') as t(tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,id bigint, name text, dropped_3 text,qq text) xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | dropped_3 | qq-------------------------------+-------------------------------+----------+-------+------+------+------+------+----+-----------+------------+-------- 2024-09-13 17:26:43.600404+08 | | 16480 | (0,1) | 839 | 0 | 0 | 0 | 1 | Test1 | 111@qq.com |2024-09-13 17:26:43.600404+08 | | 16480 | (0,2) | 839 | 0 | 0 | 0 | 2 | New Test2 | 111@qq.com |2024-09-13 17:26:45.03857+08 | | 16480 | (0,3) | 840 | 0 | 0 | 0 | 3 | Test3 | 111@qq.com |2024-09-13 17:26:45.03857+08 | | 16480 | (0,4) | 840 | 0 | 0 | 0 | 4 | New Test4 | 111@qq.com |2024-09-13 17:28:47.577755+08 | | 16480 | (0,5) | 842 | 0 | 0 | 0 | 5 | Test3 | 111@qq.com | qq1223 2024-09-13 17:28:47.577755+08 | 2024-09-13 17:31:58.565021+08 | 16480 | (0,6) | 842 | 844 | 0 | 0 | 6 | New Test4 | 111@qq.com | qq234(6 rows)--按时间获取被删除的列数据postgres=# select pg_xact_commit_timestamp(xmin) as xmin_time,pg_xact_commit_timestamp(case xmax when 0 then null else xmax end) as xmax_time,*from pg_dirtyread('saas3') as t(tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,id bigint, name text, dropped_3 text,qq text)WHERE pg_xact_commit_timestamp ( xmin ) <= '2024-09-13 17:28:48' xmin_time | xmax_time | tableoid | ctid | xmin | xmax | cmin | cmax | id | name | dropped_3 | qq-------------------------------+-------------------------------+----------+-------+------+------+------+------+----+-----------+------------+-------- 2024-09-13 17:26:43.600404+08 | | 16480 | (0,1) | 839 | 0 | 0 | 0 | 1 | Test1 | 111@qq.com |2024-09-13 17:26:43.600404+08 | | 16480 | (0,2) | 839 | 0 | 0 | 0 | 2 | New Test2 | 111@qq.com |2024-09-13 17:26:45.03857+08 | | 16480 | (0,3) | 840 | 0 | 0 | 0 | 3 | Test3 | 111@qq.com |2024-09-13 17:26:45.03857+08 | | 16480 | (0,4) | 840 | 0 | 0 | 0 | 4 | New Test4 | 111@qq.com |2024-09-13 17:28:47.577755+08 | | 16480 | (0,5) | 842 | 0 | 0 | 0 | 5 | Test3 | 111@qq.com | qq1223 2024-09-13 17:28:47.577755+08 | 2024-09-13 17:31:58.565021+08 | 16480 | (0,6) | 842 | 844 | 0 | 0 | 6 | New Test4 | 111@qq.com | qq234(6 rows)pg_dirtyread 是一个 PostgreSQL 扩展，它主要用于开发和测试环境中快速查看和恢复由于误操作导致的数据丢失。它利用 MVCC 机制来读取未提交的数据，适用于 DML 操作。在事务提交前，pg_dirtyread 提供了一种便捷的数据恢复方法。然而，在事务提交后，如果没有及时运行 VACUUM，仍然有机会恢复数据，但一旦 VACUUM 清除了 Dead 元组，数据恢复将变得不可行。因此，在生产环境中应谨慎使用 pg_dirtyread，并依赖备份和 WAL 日志归档等更为可靠的恢复机制。
吴守阳，社区编辑，拥有8年DBA工作经验，熟练管理MySQL、Redis、MongoDB等开源数据库。精通性能优化、备份恢复和高可用性架构设计。善于故障排除和自动化运维，保障系统稳定可靠。具备良好的团队合作和沟通能力，致力于为企业提供高效可靠的数据库解决方案。
© 版权声明