PostgreSQL数据闪回工具Pg

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等开源数据库。知晓性能提升、备份复原和高可用性架构设计。擅长缺点扫除和智能化运维，保证系统稳固牢靠。具有良好的团队协作和沟通才干，努力于为企业提供高效牢靠的数据库处置打算。