工具 | 常用 PostgreSQL

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

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

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

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

預防資料丢失方案

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

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

DDL 操作

事件觸發器

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

事件	說明
ddl_command_start	DDL 執行前執行
ddl_command_end	DDL 執行後執行， 通過 pg_event_trigger_ddl_commands() 可以擷取操作的對象
sql_drop	DDL 執行後執行， 通過 pg_event_trigger_dropped_objects() 可以擷取所有被删除的對象
table_rewrite	DDL 執行前執行， 例如 ALTER TABLE、ALTER TYPE 等

當表被删除後，可以通過 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