PostgreSQL（常簡稱為Postgres）是一款功能強(qiáng)大、以擴(kuò)展性著稱的開源對(duì)象-關(guān)系型數(shù)據(jù)庫系統(tǒng)。它源于加州大學(xué)伯克利分校的POSTGRES項(xiàng)目，經(jīng)過三十多年的發(fā)展，已成為全球最受歡迎的企業(yè)級(jí)數(shù)據(jù)庫之一。

PostgreSQL 數(shù)據(jù)誤刪恢復(fù)技術(shù)指南

一、核心原理：為什么數(shù)據(jù)能恢復(fù)？

? 在 PostgreSQL 中，執(zhí)行 DELETE 操作后，數(shù)據(jù)并不會(huì)立即從磁盤上物理擦除。PostgreSQL 使用多版本并發(fā)控制（MVCC）機(jī)制，刪除操作僅僅是給數(shù)據(jù)行打上了一個(gè)“已刪除”的標(biāo)記（在事務(wù) ID 層面標(biāo)記為 xmax）。

只有當(dāng) VACUUM（自動(dòng)清理或手動(dòng)清理）進(jìn)程運(yùn)行并掃描該表時(shí)，這些被標(biāo)記為“已刪除”的物理空間才會(huì)被真正回收和覆蓋。因此，恢復(fù)的關(guān)鍵在于與 VACUUM 進(jìn)程賽跑。

二、緊急止損：黃金三步

一旦發(fā)現(xiàn)誤刪，必須立即執(zhí)行以下操作以鎖定現(xiàn)場，防止數(shù)據(jù)被徹底清理。

1. 立即停止應(yīng)用寫入
   防止新數(shù)據(jù)寫入覆蓋掉被標(biāo)記為刪除的舊數(shù)據(jù)頁。
2. 禁用自動(dòng)清理
   這是最關(guān)鍵的一步。必須針對(duì)受影響的表關(guān)閉 autovacuum。

-- 將 'your_table_name' 替換為實(shí)際表名
ALTER TABLE your_table_name SET (autovacuum_enabled = false);

? 3.鎖定表
防止其他會(huì)話對(duì)表進(jìn)行操作，確保數(shù)據(jù)文件處于靜止?fàn)顟B(tài)。

BEGIN;
LOCK TABLE your_table_name IN ACCESS EXCLUSIVE MODE;
-- 保持事務(wù)開啟，不要提交或回滾，直到恢復(fù)完成

三、恢復(fù)方案 A：使用 pg_dirtyread 插件（推薦）

如果數(shù)據(jù)庫允許安裝擴(kuò)展，這是最安全、最直觀的方法。該插件允許用戶讀取被標(biāo)記為刪除但仍存在于磁盤上的“臟”數(shù)據(jù)。

1. 安裝插件
   需在目標(biāo)數(shù)據(jù)庫中執(zhí)行（需超級(jí)用戶權(quán)限）：

CREATE EXTENSION pg_dirtyread;

2.查詢被刪數(shù)據(jù)
使用插件提供的函數(shù)讀取數(shù)據(jù)。你需要明確指定表的字段結(jié)構(gòu)。

SELECT * 
FROM pg_dirtyread('your_table_name') 
AS t(id int, name text, create_time timestamp) -- 必須與表結(jié)構(gòu)一致
WHERE (SELECT pg_xact_commit_timestamp(xmax)) IS NOT NULL; -- 篩選被刪除的行

• xmax：表示刪除該行的事務(wù) ID。如果 xmax 不為 0，說明該行已被刪除。
• pg_xact_commit_timestamp(xmax)：可選，用于查看刪除發(fā)生的時(shí)間。

? 3.數(shù)據(jù)回寫
確認(rèn)查詢到的數(shù)據(jù)無誤后，將其插回原表或新表。

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;

四、恢復(fù)方案 B：底層十六進(jìn)制解析（硬核方案）

如果無法安裝插件，可以通過查詢底層頁面數(shù)據(jù)來手動(dòng)還原。PostgreSQL 將數(shù)據(jù)存儲(chǔ)在 8KB 的頁面中，heap_page_items 函數(shù)可以讀取頁面的原始字節(jié)流。

1. 獲取原始數(shù)據(jù)
   查詢被刪除行的十六進(jìn)制數(shù)據(jù)。

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.解析十六進(jìn)制數(shù)據(jù)
查詢結(jié)果中的 t_attrs 字段通常以 \x 開頭，這是十六進(jìn)制編碼的文本。

• 文本字段：例如 \x48656c6c6f 對(duì)應(yīng) Hello。
• 整數(shù)字段：通常占用 4 字節(jié)，需注意大小端序（PostgreSQL 使用小端序）。例如 01 00 00 00 對(duì)應(yīng)整數(shù) 1。

為了簡化手動(dòng)解析的痛苦，建議創(chuàng)建一個(gè)輔助函數(shù)來批量轉(zhuǎn)換：

CREATE OR REPLACE FUNCTION hex_to_text(hex_str text)
RETURNS text AS $$
BEGIN
    -- 去除 \x 前綴并轉(zhuǎn)換
    RETURN convert_from(decode(substring(hex_str FROM 3), 'hex'), 'UTF8');
EXCEPTION WHEN OTHERS THEN
    RETURN hex_str; -- 轉(zhuǎn)換失敗返回原值
END;
$$ LANGUAGE plpgsql;

五、恢復(fù)方案 C：基于 WAL 日志的時(shí)間點(diǎn)恢復(fù)

如果數(shù)據(jù)已經(jīng)被 VACUUM 清理，或者上述方法無效，且數(shù)據(jù)庫開啟了歸檔模式，可以使用時(shí)間點(diǎn)恢復(fù)。

1. 確認(rèn)配置
   確保 postgresql.conf 中開啟了歸檔：

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

2.執(zhí)行恢復(fù)

• 停止數(shù)據(jù)庫服務(wù)。
• 使用 pg_basebackup 恢復(fù)基礎(chǔ)備份。
• 配置 recovery.signal 和 postgresql.auto.conf，指定恢復(fù)目標(biāo)時(shí)間：

restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2026-04-08 09:00:00' -- 誤刪前的時(shí)間點(diǎn)

• 啟動(dòng)數(shù)據(jù)庫，PG 將重放日志直到指定時(shí)間點(diǎn)。

六、善后工作：恢復(fù)配置

數(shù)據(jù)恢復(fù)完成后，務(wù)必記得重新開啟自動(dòng)清理，否則表膨脹會(huì)導(dǎo)致性能嚴(yán)重下降。

-- 重新開啟自動(dòng)清理
ALTER TABLE your_table_name RESET (autovacuum_enabled);

七、總結(jié)與建議

建議：生產(chǎn)環(huán)境應(yīng)始終開啟 WAL 歸檔，并定期驗(yàn)證備份的可用性。對(duì)于核心表，可考慮配置邏輯復(fù)制槽以保留變更歷史。

到此這篇關(guān)于PostgreSQL 數(shù)據(jù)誤刪止損操作指南的文章就介紹到這了,更多相關(guān)PostgreSQL 數(shù)據(jù)誤刪內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論