PostgreSQL数据闪回工具Pg_Dirtyread使用实践-51CTO.COM

作者 | 吴守阳

审校 | 重楼

1、概述

2、恢复机制

3、优缺点

4、安装

5、操作实例

1）基于表数据Delete闪回

2）基于表字段数据闪回

3）基于时间点闪回

6、支持查询被删除列的历史数据

7、注意事项

8、总结

概述

pg_dirtyread 是一个PostgreSQL 扩展，它利用了PostgreSQL 的多版本并发控制（MVCC）机制来读取未提交的事务所影响的数据。此插件可以用于在开发和测试环境中快速查看事务对数据的即时影响，当关键表中的数据被意外删除后，业务系统可能会出现服务中断、数据丢失、数据不一致等问题，影响用户服务。通过使用pg_dirtyread，你可以快速查看未提交的删除或更新事务，恢复数据，从而恢复正常服务、确保数据完整、数据一致。

恢复机制

在 PostgreSQL 中，事务提交后，如果数据尚未被 VACUUM 清理，仍有可能通过某些方式恢复这些数据。这是因为 PostgreSQL 采用了多版本并发控制（MVCC）机制，允许旧的元组（称为 Dead 元组）在事务提交后继续保留在系统中，直到被 VACUUM 清除。具体来说：

MVCC 机制：即使事务已提交，删除或更新的行仍作为旧版本存在，允许在 VACUUM 运行之前进行恢复。
VACUUM 的作用：VACUUM 操作会清理表中的 Dead 元组，回收存储空间并重置行标识符（TID），以便新行可以重用这些空间。一旦执行了 VACUUM，Dead 元组被清除，数据就无法再通过常规手段恢复。

优缺点

pg_dirtyread 插件非常方便，它可以通过安装来找回数据。
该插件会返回所有数据，包括那些未被删除的数据。
由于 PostgreSQL 的 MVCC 机制，pg_dirtyread 只能用于找回 DML 操作的数据。
基于时间点恢复要配置参数track_commit_timestamp = on
支持PostgreSQL16及以前版本

安装

#安装依赖
yum install postgresql16-devel clang
 
wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.7.tar.gz
tar xvf 2.7.tar.gz
mv pg_dirtyread-2.7/ /jesong/pgdata/contrib/
cd /jesong/pgdata/contrib/
make PG_CONFIG=/usr/pgsql-16/bin/pg_config
make install PG_CONFIG=/usr/pgsql-16/bin/pg_config
 
# 登陆数据库 安装插件
postgres=# CREATE EXTENSION pg_dirtyread;
postgres=# select * from pg_available_extensions;
postgres=# \dx1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

数据恢复前置条件

#如果发现错误操作，导致数据意外删除，要第一时间关闭表上的vacuum
#关闭vacuum
ALTER TABLE foo SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false);
 
#表未被vacuum
postgres=# select * from pg_stat_all_tables where relname='foo';
-[ RECORD 1 ]-------+-----------------------------
last_vacuum | 
last_autovacuum | 
vacuum_count | 0
autovacuum_count | 01.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

操作示例

1)基于表数据Delete闪回

--创建测试表
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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

2)基于表字段数据闪回

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

指定列的规则：使用dropped_N来访问第N列，从1开始计数。

局限性：

由于 PostgreSQL 删除操作会移除原始列的元数据信息，因此在使用 pg_dirtyread 时，需要在表列名中显式指定正确的数据类型。这包括类型长度、对齐方式和类型修饰符，并且数据是按值传递的。

1.显式指定类型:

在读取 Dead 元组时，需要明确指定列的数据类型，包括长度和其他修饰符。

2.完整性检查:

为了进行有效的数据恢复和完整性检查，必须确保类型信息的准确性。

3)基于时间点闪回

如果你想恢复到某个特定时间点的数据，可以通过 pg_xact_commit_timestamp 系统函数来获取每个事务的提交时间。

具体步骤如下：

1.获取提交时间:

使用 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)
 
--查询某个时间点的数据
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 ) 
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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

支持查询被删除列的历史数据

--创建表
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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

注意事项

适用范围: pg_dirtyread 主要适用于 MVCC 机制下的 DML 操作（如 DELETE 和 UPDATE），对于 DDL 操作（如 DROP TABLE）导致的数据丢失则无能为力。
事务提交后: 一旦事务提交，数据恢复变得更加困难。如果 VACUUM 没有运行，未提交的事务产生的 Dead 元组仍然存在，可以通过 pg_dirtyread 查看这些数据。但是一旦 VACUUM 清除了 Dead 元组，数据就无法通过 pg_dirtyread 恢复。
安全性问题:使用 pg_dirtyread 读取未提交的数据可能会带来数据一致性和安全性问题。因此，在生产环境中应谨慎使用，并确保数据安全。

总结

pg_dirtyread 是一个 PostgreSQL 扩展，它主要用于开发和测试环境中快速查看和恢复由于误操作导致的数据丢失。它利用 MVCC 机制来读取未提交的数据，适用于 DML 操作。在事务提交前，pg_dirtyread 提供了一种便捷的数据恢复方法。然而，在事务提交后，如果没有及时运行 VACUUM，仍然有机会恢复数据，但一旦 VACUUM 清除了 Dead 元组，数据恢复将变得不可行。因此，在生产环境中应谨慎使用 pg_dirtyread，并依赖备份和 WAL 日志归档等更为可靠的恢复机制。

作者介绍

吴守阳，51CTO社区编辑，拥有8年DBA工作经验，熟练管理MySQL、Redis、MongoDB等开源数据库。精通性能优化、备份恢复和高可用性架构设计。善于故障排除和自动化运维，保障系统稳定可靠。具备良好的团队合作和沟通能力，致力于为企业提供高效可靠的数据库解决方案。