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

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

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

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

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

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

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

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

DDL 操作

事件觸發(fā)器

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

當(dāng)表被刪除后，可以通過(guò) 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ā)器，無(wú)法修改 drop 的任何行為，因此只能拒絕，來(lái)確保數(shù)據(jù)不被刪除，由其他擁有更高權(quán)限的數(shù)據(jù)庫(kù)管理員刪除。

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

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

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

回收站

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

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

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

通過(guò)其代碼示例可以看出， DROP TABLE 操作被轉(zhuǎn)換成了 ALTER 操作。

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

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

DML 操作

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

流復(fù)制延遲恢復(fù)

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

找回?cái)?shù)據(jù)的具體操作步驟如下:

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

備份恢復(fù)

從備份模式的角度來(lái)說(shuō)，備份主要包括以下兩種：

• 邏輯備份

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

• 物理備份

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

物理備份與 PITR 結(jié)合，可恢復(fù)數(shù)據(jù)到任意時(shí)間點(diǎn)。可選用工具有很多，如下幾種是常用的恢復(fù)工具。

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

總結(jié)

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

參考引用

[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

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