作者：張連壯 PostgreSQL 研發(fā)負責人

從事多年 PostgreSQL 數(shù)據(jù)庫內(nèi)核開發(fā)，對 Citus 有非常深入的研究。

PostgreSQL是一種特性非常齊全的自由軟件的對象-關系型數(shù)據(jù)庫管理系統(tǒng)（ORDBMS），是以加州大學計算機系開發(fā)的POSTGRES，4.2版本為基礎的對象關系型數(shù)據(jù)庫管理系統(tǒng)。POSTGRES的許多領先概念只是在比較遲的時候才出現(xiàn)在商業(yè)網(wǎng)站數(shù)據(jù)庫中。PostgreSQL支持大部分的SQL標準并且提供了很多其他現(xiàn)代特性，如復雜查詢、外鍵、觸發(fā)器、視圖、事務完整性、多版本并發(fā)控制等。同樣，PostgreSQL也可以用許多方法擴展，例如通過增加新的數(shù)據(jù)類型、函數(shù)、操作符、聚集函數(shù)、索引方法、過程語言等。另外，因為許可證的靈活，任何人都可以以任何目的免費使用、修改和分發(fā)PostgreSQL。下面看下常用 PostgreSQL 預防數(shù)據(jù)丟失解決方案。

PostgreSQL 本身不具備數(shù)據(jù)閃回和數(shù)據(jù)誤刪除保護功能，但在不同場景下也有對應的解決方案。

本文由作者在 2021 PCC 大會的演講主題《PostgreSQL 數(shù)據(jù)找回》整理而來，上一篇《盤點 | 常用 PG 數(shù)據(jù)恢復方案概覽》介紹了 PostgreSQL 常見的 數(shù)據(jù)恢復方案。本篇將介紹 預防數(shù)據(jù)丟失方案的實現(xiàn)原理及使用示例。

預防數(shù)據(jù)丟失方案

前文提到數(shù)據(jù)丟失的主要操作為 DDL 和 DML 。

本篇主要介紹關于 DDL 和 DML 操作，如何預防數(shù)據(jù)丟失的方案。

DDL 操作

事件觸發(fā)器

當事件以其定義的方式在數(shù)據(jù)庫中相關的發(fā)生時，觸發(fā)事件觸發(fā)器。主要可預防以下四種 DDL 事件。

當表被刪除后，可以通過 ddl_command_start 事件組織刪除操作。

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

事件觸發(fā)器，無法修改 drop 的任何行為，因此只能拒絕，來確保數(shù)據(jù)不被刪除，由其他擁有更高權限的數(shù)據(jù)庫管理員刪除。

test=# \dy
                                        事件觸發(fā)器列表
            名稱             |       Event       | 擁有者  | 使能 |     函數(shù)      |    標簽    
-----------------------------+-------------------+---------+------+---------------+------------
 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

刪除表的操作由擁有更高級權限的數(shù)據(jù)庫管理員操作。

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

回收站

DDL 會將文件從操作系統(tǒng)中完全刪除，因此唯一的辦法是將刪除改為換一個"位置"，類似 Windows 中回收站。

pgtanshscan[1] 便是一種回收站工具，并且只能通過插件采用 hook 的方式來實現(xiàn)。

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

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

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

• 支持新版本 PG 14/13/12
• 通過插件的 depend 功能，依賴 pg_cron
• 自動設置 pg_cron 將其回收站中超過 1 天的數(shù)據(jù)清除

DML 操作

通過參數(shù) vacuum_defer_cleanup_age 來調(diào)整 Dead 元組在數(shù)據(jù)庫中的量，以便恢復誤操作的數(shù)據(jù)。接下來將根據(jù) 流復制延遲恢復和 備份恢復兩種設計方案來具體介紹：

流復制延遲恢復

PostgreSQL 流復制時可以通過 recovery_min_apply_delay 設置相應的延遲時間。例如設置 5 小時，備庫可以延遲應用最近 5 小時的日志，提供最多 5 小時的數(shù)據(jù)恢復窗口，延遲的應用日志的同時并不影響日志的接受，源庫的日志仍然是實時的被延遲恢復節(jié)點接受。

找回數(shù)據(jù)的具體操作步驟如下:

• 暫停延遲恢復 pg_wal_replay_pause() ；
• 通過 pg_dump 或 copy 操作將其需要的數(shù)據(jù)找出來；
• 通過 psql、copy、pg_restore 等操作將數(shù)據(jù)導入源庫中；
• 繼續(xù)延遲 pg_wal_replay_resume() 。

備份恢復

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

• 邏輯備份

不能進行實時備份，因此不太適用于數(shù)據(jù)找回，會丟失很多數(shù)據(jù)。

• 物理備份

物理備份擁有與源集群完全一致的數(shù)據(jù)，因此可以持續(xù)使用源集群的 WAL 日志，達到數(shù)據(jù)找回的目標，原理上也是延遲恢復。

物理備份與 PITR 結合，可恢復數(shù)據(jù)到任意時間點?？蛇x用工具有很多，如下幾種是常用的恢復工具。

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

總結

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

參考引用

[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

到此這篇關于常用 PostgreSQL 預防數(shù)據(jù)丟失方案的文章就介紹到這了,更多相關PostgreSQL數(shù)據(jù)丟失內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論