作者：張連壯 PostgreSQL 研發負責人

從事多年 PostgreSQL 資料庫內核開發，對 Citus 有非常深入的研究，

PostgreSQL 本身不具備資料閃回和資料誤洗掉保護功能，但在不同場景下也有對應的解決方案，

本文由作者在 2021 PCC 大會的演講主題《PostgreSQL 資料找回》整理而來，上一篇《盤點 | 常用 PG 資料恢復方案概覽》介紹了 PostgreSQL 常見的 資料恢復方案，本篇將介紹 預防資料丟失方案的實作原理及使用示例，

預防資料丟失方案

前文提到資料丟失的主要操作為 DDL 和 DML ，

本篇主要介紹關于 DDL 和 DML 操作，如何預防資料丟失的方案，

DDL 操作

事件觸發器

當事件以其定義的方式在資料庫中相關的發生時，觸發事件觸發器，主要可預防以下四種 DDL 事件，

當表被洗掉后，可以通過 ddl_command_start 事件組織洗掉操作，

CREATE OR REPLACE FUNCTION disable_drops()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
     RAISE EXCEPTION 'drop table denied';
END
$$; -- 創建事件觸發器函式
 
CREATE EVENT TRIGGER event_trigger_disable_drops
    ON ddl_command_start WHEN TAG in('drop table')
    EXECUTE PROCEDURE disable_drops(); -- 創建事件觸發器，禁止drop table操作

事件觸發器，無法修改 drop 的任何行為，因此只能拒絕，來確保資料不被洗掉，由其他擁有更高權限的資料庫管理員洗掉，

test=# \dy
                                        事件觸發器串列
            名稱             |       Event       | 擁有者  | 使能 |     函式      |    標簽    
-----------------------------+-------------------+---------+------+---------------+------------
 event_trigger_disable_drops | ddl_command_start | lzzhang | 啟用 | disable_drops | DROP TABLE
(1 行記錄)
 
test=# drop table lzzhang;
ERROR:  drop table denied
CONTEXT:  PL/pgSQL function disable_drops() line 3 at RAISE

洗掉表的操作由擁有更高級權限的資料庫管理員操作，

BEGIN;
ALTER EVENT TRIGGER event_trigger_disable_drops DISABLE;
DROP TABLE lzzhang;
ALTER EVENT TRIGGER event_trigger_disable_drops ENABLE;
COMMIT;

回收站

DDL 會將檔案從作業系統中完全洗掉，因此唯一的辦法是將洗掉改為換一個"位置"，類似 Windows 中回收站，

pgtanshscan[1] 便是一種回收站工具，并且只能通過插件采用 hook 的方式來實作，

if (nodeTag(parsetree) == T_DropStmt)
{
                if (stmt->removeType == OBJECT_TABLE)
{
AlterObjectSchemaStmt *newstmt = makeNode(AlterObjectSchemaStmt);
newstmt->newschema = pstrdup(trashcan_nspname);

通過其代碼示例可以看出， DROP TABLE 操作被轉換成了 ALTER 操作，

由于 pgtrashcan 代碼陳舊，已經有 8 年未更新，不適配新版本 PG，且僅支持移動功能，并不支持徹底清除功能，由此，pgtrashcan 做了很多優化，

• 支持新版本 PG 14/13/12
• 通過插件的 depend 功能，依賴 pg_cron
• 自動設定 pg_cron 將其回收站中超過 1 天的資料清除

DML 操作

通過引數 vacuum_defer_cleanup_age 來調整 Dead 元組在資料庫中的量，以便恢復誤操作的資料，接下來將根據 流復制延遲恢復和 備份恢復兩種設計方案來具體介紹：

流復制延遲恢復

PostgreSQL 流復制時可以通過 recovery_min_apply_delay 設定相應的延遲時間，例如設定 5 小時，備庫可以延遲應用最近 5 小時的日志，提供最多 5 小時的資料恢復視窗，延遲的應用日志的同時并不影響日志的接受，源庫的日志仍然是實時的被延遲恢復節點接受，

找回資料的具體操作步驟如下:

1. 暫停延遲恢復 pg_wal_replay_pause() ；
2. 通過 pg_dump 或 copy 操作將其需要的資料找出來；
3. 通過 psql、copy、pg_restore 等操作將資料匯入源庫中；
4. 繼續延遲 pg_wal_replay_resume() ，

備份恢復

從備份模式的角度來說，備份主要包括以下兩種：

• 邏輯備份
  不能進行實時備份，因此不太適用于資料找回，會丟失很多資料，

• 物理備份
  物理備份擁有與源集群完全一致的資料，因此可以持續使用源集群的 WAL 日志，達到資料找回的目標，原理上也是延遲恢復，

物理備份與 PITR 結合，可恢復資料到任意時間點，可選用工具有很多，如下幾種是常用的恢復工具，

• pg_basebackup[2]
• pg_probackup[3]
• pgbackrest[4]
• barman[5]
• pg_rman[6]

總結

1. 注意權限劃分，危險操作或是 DDL 等影響大的操作，一定要由第二個資料庫管理員操作，
2. 提前做好資料找回和資料安全的方案規劃，
3. 流復制延遲恢復，同樣需要設定 recovery_target_xid 、recovery_target_time 或recovery_target_lsn 來精準的定位到完整的資料集，
4. pg_waldump 是資料找回必備的一個功能，
5. 如果方案是重型的，輕型的插件有時會是更好的選擇，
6. 若無任何準備，且不能安裝任何插件，可第一時間將資料庫關機！！！防止 Dead 元組被清理，拷貝整個集群，使用拷貝后的集群用 pg_resetwal 進行資料恢復，

參考參考

[1] ：pgtrashcan：https://github.com/petere/pgtrashcan

[2]：pg_basebackup：https://www.postgresql.org/docs/10/app-pgbasebackup.html

[3]：pg_probackup：https://github.com/postgrespro/pg_probackup

[4]：pgbackrest：https://github.com/pgbackrest/pgbackrest

[5]：barman：https://github.com/EnterpriseDB/barman

[6]：pg_rman：https://github.com/ossc-db/pg_rman

標籤：MySQL

上一篇：kafka學習筆記

下一篇：如何在IntellijIDEA中突出顯示本地提交的代碼？