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

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

PostgreSQL 本身不具備數(shù)據(jù)閃回和數(shù)據(jù)誤刪除保護(hù)功能，但在不同場景下也有對應(yīng)的解決方案。本文由作者在 2021 PCC 大會(huì)的演講主題《PostgreSQL 數(shù)據(jù)找回》整理而來，介紹了常見 數(shù)據(jù)恢復(fù)和 預(yù)防數(shù)據(jù)丟失的相關(guān)工具實(shí)現(xiàn)原理及使用示例。

在盤點(diǎn)數(shù)據(jù)恢復(fù)方案之前，先簡單了解一下數(shù)據(jù)丟失的原因。

數(shù)據(jù)丟失的原因

數(shù)據(jù)丟失通常是由 DDL 與 DML 兩種操作引起。

DDL

在 PostgreSQL 數(shù)據(jù)庫中，表以文件的形式，采用 OID 命名規(guī)則存儲(chǔ)于 PGDATA/base/DatabaseId/relfilenode 目錄中。當(dāng)進(jìn)行 DROP TABLE 操作時(shí)，會(huì)將文件整體刪除。

由于在操作系統(tǒng)中表文件已經(jīng)不存在，所以只能采用恢復(fù)磁盤的方法進(jìn)行數(shù)據(jù)恢復(fù)。但這種方式找回?cái)?shù)據(jù)的概率非常小，尤其是云數(shù)據(jù)庫，恢復(fù)磁盤數(shù)據(jù)幾乎不可能。

DML

DML 包含 UPDATE、DELETE 操作。根據(jù) MVCC 的實(shí)現(xiàn)，DML 操作并不是在操作系統(tǒng)磁盤中將數(shù)據(jù)刪除，因此數(shù)據(jù)可以通過參數(shù)vacuum_defer_cleanup_age 來調(diào)整 Dead 元組在數(shù)據(jù)庫中的數(shù)量，以便恢復(fù)誤操作的數(shù)據(jù)。

數(shù)據(jù)恢復(fù)方案

pg_resetwal

pg_resetwal[1] 是 PostgreSQL 自帶的工具（9.6 及以前版本叫 pg_resetxlog）?？汕宄A(yù)寫式日志（WAL）并且可以重置 pg_control 文件中的一些信息。也可以修改當(dāng)前事務(wù) ID，從而使數(shù)據(jù)庫可以訪問到未被 Vacuum 掉的 Dead 元組。

使用示例

pg_resetwal 通過設(shè)置事務(wù)號的方式來恢復(fù)數(shù)據(jù)，因此必須提前獲取待恢復(fù)數(shù)據(jù)的事務(wù)號。

1. 查看當(dāng)前 lsn 位置

-- 在線查詢
select pg_current_wal_lsn();
-- 離線查詢
./pg_controldata -D dj | grep 'checkpoint location'

通過查詢來確定 lsn 的大致的位置。

2. 獲取事務(wù)號

./pg_waldump -b -s 0/2003B58 -p dj
rmgr: Heap        len (rec/tot):     59/   299， tx:        595， lsn: 0/030001B8， prev 0/03000180， desc: DELETE off 5 KEYS_UPDATED ， blkref #0: rel 1663/16392/16393 blk 0 FPW
rmgr: Heap        len (rec/tot):     54/    54， tx:        595， lsn: 0/030002E8， prev 0/030001B8， desc: DELETE off 6 KEYS_UPDATED ， blkref #0: rel 1663/16392/16393 blk 0
rmgr: Transaction len (rec/tot):     34/    34， tx:        595， lsn: 0/03000320， prev 0/030002E8， desc: COMMIT 2019-03-26 11:00:23。410557 CST

3. 設(shè)置事務(wù)號

-- 關(guān)閉數(shù)據(jù)
./pg_resetwal -D dj -x 595
-- 啟動(dòng)數(shù)據(jù)庫

4. 查看所需數(shù)據(jù)

select * from xx

小結(jié)

• pg_resetwal 恢復(fù)數(shù)據(jù)操作及時(shí)，數(shù)據(jù)絕對可恢復(fù)。
• 在 SERVER 端操作所需權(quán)限較高，云數(shù)據(jù)庫可能無法使用。
• 若 DDL 數(shù)據(jù)無法找回，雖然元信息已經(jīng)恢復(fù)，但數(shù)據(jù)已經(jīng)不在磁盤上。 ERROR: could not open file "base/16392/16396" 表明文件或目錄已經(jīng)不存在了。
• 啟動(dòng)數(shù)據(jù)庫后，不可以進(jìn)行任何影響事務(wù)號的操作。否則提升事務(wù)號將導(dǎo)致數(shù)據(jù)再次不可見。
• 通過 pg_resetwal 恢復(fù)數(shù)據(jù)前，需將數(shù)據(jù) PGDATA 目錄進(jìn)行全量備份，只恢復(fù)所需數(shù)據(jù)
• pg_resetwal 操作難度大，需要掌握的 PG 知識較多。

pg_dirtyread

pg_dirtyread[2] 利用 MVCC 機(jī)制讀取 Dead 元組。因此可以恢復(fù) UPDATE、DELETE、DROPCOLUMN、ROLLBACK 等 MVCC 機(jī)制操作的數(shù)據(jù)。pg_dirtyread 不存在于 contrib 目錄下，因此需要單獨(dú)編譯。

使用示例

CREATE TABLE foo (bar bigint， baz text);
INSERT INTO foo VALUES (1， 'Test')， (2， 'New Test');
DELETE FROM foo WHERE bar = 1;
SELECT * FROM pg_dirtyread('foo') as t(bar bigint， baz text);
   bar │   baz
  ─────┼──────────
     1 │ Test
     2 │ New Test

小結(jié)

• pg_dirtyread 使用非常方便，僅需要安裝一個(gè)插件便可以找回?cái)?shù)據(jù)。
• pg_dirtyread 會(huì)返回全部數(shù)據(jù)，包含未被刪除的數(shù)據(jù)。例如示例中 bar=2 的數(shù)據(jù)。
• 基于 MVCC 機(jī)制的操作只能實(shí)現(xiàn) DML 的數(shù)據(jù)找回。

pg_recovery

pg_recovery[3] 與 pg_dirtyread 類似，但是使用更靈活。目前的版本中默認(rèn)只返回需要找回的數(shù)據(jù) 。pg_recovery 的目標(biāo)致力于數(shù)據(jù)的找回，而不僅僅是讀取 Dead 元組，在后續(xù)的版本中，會(huì)增加一些輔助數(shù)據(jù)找回的調(diào)試信息，來幫助用戶更快的在眾多數(shù)據(jù)中找到自己需要找回的數(shù)據(jù)。pg_recovery 不存在于 contrib 目錄下，因此需要單獨(dú)編譯。

使用示例

CREATE TABLE foo (bar bigint， baz text);
INSERT INTO foo VALUES (1， 'Test')， (2， 'New Test');
DELETE FROM foo WHERE bar = 1;
SELECT * FROM pg_recovery('foo') as t(bar bigint， baz text);
   bar │   baz
  ─────┼──────────
     1 │ Test

小結(jié)

• pg_recovery 的目標(biāo)是用于數(shù)據(jù)找回，因此使用起來更方便。在未來的版本中，也會(huì)加入更多輔助數(shù)據(jù)找回的功能。
• pg_recovery(recoveryrow => false) 可以讀取出全部數(shù)據(jù)。
• pg_recovery 只能找回 DML 的數(shù)據(jù)。

pg_filedump

pg_filedump[4] 是一款命令行工具， 因此只能在服務(wù)端執(zhí)行，并且不需要連接數(shù)據(jù)庫。該工具可以分析出數(shù)據(jù)文件中數(shù)據(jù)的詳細(xì)數(shù)據(jù)，內(nèi)容格式與 pageinspect 類似。

使用示例

./pg_filedump -D int，varchar dj/base/24679/24777
 Item   1 -- Length:   30  Offset: 8160 (0x1fe0)  Flags: NORMAL
COPY: 1  a
 Item   2 -- Length:  113  Offset: 8040 (0x1f68)  Flags: NORMAL
COPY: 2  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 Item   3 -- Length:  203  Offset: 7832 (0x1e98)  Flags: NORMAL
COPY: 2  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

小結(jié)

• pg_filedump 可以直接讀取文件，無需連接數(shù)據(jù)庫，適用于嚴(yán)重災(zāi)難的情況。但是需要知道具體的文件位置，適用性不強(qiáng)。
• pg_filedump 可直接通過 SQL 將數(shù)據(jù)一鍵找回，需要編譯找回?cái)?shù)據(jù)方法。
• pg_filedump 無法找回自定義數(shù)據(jù)類型的數(shù)據(jù)。
• pg_filedump 由于只能在服務(wù)端執(zhí)行，不適用于用于云數(shù)據(jù)庫的數(shù)據(jù)找回。

WalMiner

WalMiner[5] 是從 PostgreSQL 的 WAL（write ahead logs）日志的解析工具，旨在挖掘 WAL 日志所有的有用信息，從而提供 PG 的數(shù)據(jù)恢復(fù)支持。目前主要有如下功能：

從 WAL 日志中解析出 SQL，包括 DML 和少量 DDL

解析出執(zhí)行的 SQL 語句的工具，并能生成對應(yīng)的 UNDO SQL語句。與傳統(tǒng)的 logical decode 插件相比，WalMiner 不要求 logical 日志級別且解析方式較為靈活。

數(shù)據(jù)頁挽回

當(dāng)數(shù)據(jù)庫被執(zhí)行了 TRUNCATE 等不被 WAL 記錄的數(shù)據(jù)清除操作或者發(fā)生磁盤頁損壞時(shí)，可使用此功能從 WAL 日志中搜索數(shù)據(jù)，盡量挽回?cái)?shù)據(jù)。

使用示例

postgres=# select record_database，record_user，op_text，op_undo from walminer_contents;
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------
record_database | postgres
record_user     | lichuancheng
op_text         | INSERT INTO "public"。"t2"("i"， "j"， "k") VALUES(1， 1， 'qqqqqq');
op_undo         | DELETE FROM "public"。"t2" WHERE "i"=1 AND "j"=1 AND "k"='qqqqqq' AND ctid = '(0，1)';

小結(jié)

• WalMiner 通過 WAL 日志進(jìn)行找回，只要日志保存量足夠，便可以找回?cái)?shù)據(jù)。
• WalMiner 可以通過與存儲(chǔ)過程的結(jié)合，來實(shí)現(xiàn)一鍵數(shù)據(jù)找回的功能。

pageinspect

pageinspect[6] 是 PostgreSQL 自帶的插件，存在于源碼 contrib 目錄中，具備更高的穩(wěn)定。

pageinspace 可以查看數(shù)據(jù)二進(jìn)制的存儲(chǔ)方式，并且可以讀取 Dead 元組，因此可以用于數(shù)據(jù)找回和查看所需找回的數(shù)據(jù)是否存在。

數(shù)據(jù)結(jié)構(gòu)

struct varlena
 {
     char        vl_len_[4];     /* Do not touch this field directly! */
     char        vl_dat[FLEXIBLE_ARRAY_MEMBER];  /* Data content is here */
 };

使用示例

test=# SELECT tuple_data_split('lzzhang'::regclass， t_data， t_infomask， t_infomask2， t_bits) FROM heap_page_items(get_raw_page('lzzhang'， 0));
tuple_data_split          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"\\x01000000"，"\\x0561"} {"\\x02000000"，"\\xab616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161"}
{"\\x02000000"，"\\xbc020000616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161"}
(3 行記錄)

小結(jié)

• pageinspacet 通常用于底層數(shù)據(jù)存儲(chǔ)的分析，極難恢復(fù)數(shù)據(jù)，復(fù)雜的自定義數(shù)據(jù)類型，恢復(fù)更加困難。雖然可以找回?cái)?shù)據(jù)，但不推薦。
• 數(shù)據(jù)不直觀，例如 {"\\x01000000"，"\\x0561"} 。
• 數(shù)據(jù)的先后順序，需要參考 pg_attribute 來獲知返回的數(shù)據(jù)對應(yīng)的列。
• 需要對 PG 源碼深度掌握，同一數(shù)據(jù)類型不同長度數(shù)據(jù)格式不同。例如"\\x0561"， "\\xab6161"， "\\xbc020000616161”，61 代表字母 a 。

小貼士：保留多少 Dead 元組最合適？

因?yàn)?MVCC 機(jī)制，PG 本身自帶 autovacuum，通常情況下無需手動(dòng)維護(hù) MVCC 。但autovacuum 的觸發(fā)需要一定條件，數(shù)據(jù)庫至少有 10% 以上的數(shù)據(jù)膨脹，嚴(yán)重的可能超過數(shù)據(jù)本身。

通過設(shè)置參數(shù) vacuum_defer_cleanup_age 可保留部分 Dead 元組，減少數(shù)據(jù)膨脹對數(shù)據(jù)庫產(chǎn)生的影響。若需要立即清理數(shù)據(jù)，可在數(shù)據(jù)存儲(chǔ)過程調(diào)用 select * from txid_current(); 增加事務(wù)號，清空 Dead 元組。

但即使沒有設(shè)置 vacuum_defer_cleanup_age ，由于 vacuum 不及時(shí)，及時(shí)操作也可以恢復(fù)出數(shù)據(jù)。

PG 數(shù)據(jù)恢復(fù)方案總結(jié)

不同方案適合的場景不同，從使用難易角度大致做了以下排名（個(gè)人建議）：

• pg_recovery 使用簡單，默認(rèn)只有待找回?cái)?shù)據(jù)；
• pg_dirtyread 使用簡單，默認(rèn)返回全部數(shù)據(jù)；
• WalMiner 需要對 walminer 全面掌握，并做好系統(tǒng)預(yù)設(shè)；
• pg_resetwal 需要了解的內(nèi)容較多；
• pg_filedump 需要單獨(dú)寫一些腳本或工具來配合使用；
• pageinspect 難度極大。

若無任何準(zhǔn)備，如何恢復(fù)數(shù)據(jù)？推薦以下方法：

• 及時(shí)設(shè)置 vacuum_defer_cleanup_age
• 安裝 pg_recover 或者 pg_dirtyread
• 無法安裝插件可以采用 pg_resetwal ，無需任何額外工具

掌握數(shù)據(jù)恢復(fù)工具使用是必不可少的，但在事故發(fā)生前采取預(yù)防數(shù)據(jù)丟失的方案更有必要。下一期我們將從 DDL 和 DML 兩類操作分別介紹如何預(yù)防數(shù)據(jù)丟失的方案。

參考引用

[1]：pg_resetwal：https://www.postgresql.org/docs/10/app-pgresetwal.html
[2]：pg_dirtyread：https://github.com/df7cb/pg_dirtyread
[3]：pg_recovery：https://github.com/radondb/pg_recovery
[4]：pg_filedump：https://github.com/ChristophBerg/pg_filedump
[5]：WalMiner：https://gitee.com/movead/XLogMiner
[6]：pageinspect：https://www.postgresql.org/docs/10/pageinspect.html

到此這篇關(guān)于常用 PostgreSQL 數(shù)據(jù)恢復(fù)方案概覽【建議收藏】的文章就介紹到這了,更多相關(guān)PostgreSQL 數(shù)據(jù)恢復(fù)內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

國外服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。