极客资料网推荐PostgreSQL 数据误删 止损操作这篇文章给大家，欢迎收藏极客资料网享受知识的乐趣

PostgreSQL 数据误删恢复技术指南

💡 作者：古渡蓝按

个人微信公众号：微信公众号（深入浅出谈java）
感觉本篇对你有帮助可以关注一下，会不定期更新知识和面试资料、技巧！！！

一、核心原理：为什么数据能恢复？

​ 在 PostgreSQL 中，执行 DELETE 操作后，数据并不会立即从磁盘上物理擦除。PostgreSQL 使用多版本并发控制（MVCC）机制，删除操作仅仅是给数据行打上了一个“已删除”的标记（在事务 ID 层面标记为 xmax）。

只有当 VACUUM（自动清理或手动清理）进程运行并扫描该表时，这些被标记为“已删除”的物理空间才会被真正回收和覆盖。因此，恢复的关键在于与 VACUUM 进程赛跑。

二、紧急止损：黄金三步

一旦发现误删，必须立即执行以下操作以锁定现场，防止数据被彻底清理。

1. 立即停止应用写入
   防止新数据写入覆盖掉被标记为删除的旧数据页。
2. 禁用自动清理
   这是最关键的一步。必须针对受影响的表关闭 autovacuum。

-- 将 'your_table_name' 替换为实际表名
ALTER TABLE your_table_name SET (autovacuum_enabled = false);

​ 3.锁定表
防止其他会话对表进行操作，确保数据文件处于静止状态。

BEGIN;
LOCK TABLE your_table_name IN ACCESS EXCLUSIVE MODE;
-- 保持事务开启，不要提交或回滚，直到恢复完成

三、恢复方案 A：使用 pg_dirtyread 插件（推荐）

如果数据库允许安装扩展，这是最安全、最直观的方法。该插件允许用户读取被标记为删除但仍存在于磁盘上的“脏”数据。

1. 安装插件
   需在目标数据库中执行（需超级用户权限）：

CREATE EXTENSION pg_dirtyread;

2.查询被删数据
使用插件提供的函数读取数据。你需要明确指定表的字段结构。

SELECT * 
FROM pg_dirtyread('your_table_name') 
AS t(id int, name text, create_time timestamp) -- 必须与表结构一致
WHERE (SELECT pg_xact_commit_timestamp(xmax)) IS NOT NULL; -- 筛选被删除的行

• xmax：表示删除该行的事务 ID。如果 xmax 不为 0，说明该行已被删除。
• pg_xact_commit_timestamp(xmax)：可选，用于查看删除发生的时间。

​ 3.数据回写
确认查询到的数据无误后，将其插回原表或新表。

INSERT INTO your_table_name (id, name, create_time)
SELECT id, name, create_time
FROM pg_dirtyread('your_table_name') 
AS t(id int, name text, create_time timestamp)
WHERE (SELECT pg_xact_commit_timestamp(xmax)) IS NOT NULL;

四、恢复方案 B：底层十六进制解析（硬核方案）

如果无法安装插件，可以通过查询底层页面数据来手动还原。PostgreSQL 将数据存储在 8KB 的页面中，heap_page_items 函数可以读取页面的原始字节流。

1. 获取原始数据
   查询被删除行的十六进制数据。

SELECT lp, t_attrs 
FROM heap_page_item_attrs(get_raw_page('your_table_name', 0), 'your_table_name'::regclass)
WHERE t_xmax != 0; -- 筛选已删除行

2.解析十六进制数据
查询结果中的 t_attrs 字段通常以 \x 开头，这是十六进制编码的文本。

• 文本字段：例如 \x48656c6c6f 对应 Hello。
• 整数字段：通常占用 4 字节，需注意大小端序（PostgreSQL 使用小端序）。例如 01 00 00 00 对应整数 1。

为了简化手动解析的痛苦，建议创建一个辅助函数来批量转换：

CREATE OR REPLACE FUNCTION hex_to_text(hex_str text)
RETURNS text AS $$
BEGIN
    -- 去除 \x 前缀并转换
    RETURN convert_from(decode(substring(hex_str FROM 3), 'hex'), 'UTF8');
EXCEPTION WHEN OTHERS THEN
    RETURN hex_str; -- 转换失败返回原值
END;
$$ LANGUAGE plpgsql;

五、恢复方案 C：基于 WAL 日志的时间点恢复

如果数据已经被 VACUUM 清理，或者上述方法无效，且数据库开启了归档模式，可以使用时间点恢复。

1. 确认配置
   确保 postgresql.conf 中开启了归档：

archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

2.执行恢复

• 停止数据库服务。
• 使用 pg_basebackup 恢复基础备份。
• 配置 recovery.signal 和 postgresql.auto.conf，指定恢复目标时间：

restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2026-04-08 09:00:00' -- 误删前的时间点

• 启动数据库，PG 将重放日志直到指定时间点。

六、善后工作：恢复配置

数据恢复完成后，务必记得重新开启自动清理，否则表膨胀会导致性能严重下降。

-- 重新开启自动清理
ALTER TABLE your_table_name RESET (autovacuum_enabled);

七、总结与建议

方案	适用场景	难度	风险
pg_dirtyread	未执行 VACUUM，可安装插件	低	低
底层解析	未执行 VACUUM，无法安装插件	高	中（需人工解析）
WAL 日志	已执行 VACUUM，有归档配置	极高	高（需停机恢复）
备份还原	有定期 pg_dump 备份	中	中（数据可能回退）

建议：生产环境应始终开启 WAL 归档，并定期验证备份的可用性。对于核心表，可考虑配置逻辑复制槽以保留变更历史。

文章来源:https://www.cnblogs.com/blbl-blog/p/19833639
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！