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

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

PostgreSQL 本身不具備資料閃回和資料誤洗掉保護功能，但在不同場景下也有對應的解決方案，本文由作者在 2021 PCC 大會的演講主題《PostgreSQL 資料找回》整理而來，介紹了常見 資料恢復和 預防資料丟失的相關工具實作原理及使用示例，

在盤點資料恢復方案之前，先簡單了解一下資料丟失的原因，

資料丟失的原因

資料丟失通常是由 DDL 與 DML 兩種操作引起，

DDL

在 PostgreSQL 資料庫中，表以檔案的形式，采用 OID 命名規則存盤于 PGDATA/base/DatabaseId/relfilenode 目錄中，當進行 DROP TABLE 操作時，會將檔案整體洗掉，

由于在作業系統中表檔案已經不存在，所以只能采用恢復磁盤的方法進行資料恢復，但這種方式找回資料的概率非常小，尤其是云資料庫，恢復磁盤資料幾乎不可能，

DML

DML 包含 UPDATE、DELETE 操作，根據 MVCC 的實作，DML 操作并不是在作業系統磁盤中將資料洗掉，因此資料可以通過引數vacuum_defer_cleanup_age 來調整 Dead 元組在資料庫中的數量，以便恢復誤操作的資料，

資料恢復方案

pg_resetwal

pg_resetwal[1] 是 PostgreSQL 自帶的工具（9.6 及以前版本叫 pg_resetxlog），可清除預寫式日志（WAL）并且可以重置 pg_control 檔案中的一些資訊，也可以修改當前事務 ID，從而使資料庫可以訪問到未被 Vacuum 掉的 Dead 元組，

使用示例

pg_resetwal 通過設定事務號的方式來恢復資料，因此必須提前獲取待恢復資料的事務號，

1. 查看當前 lsn 位置

-- 在線查詢
select pg_current_wal_lsn();

-- 離線查詢
./pg_controldata -D dj | grep 'checkpoint location'

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

2. 獲取事務號

./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. 設定事務號

-- 關閉資料
./pg_resetwal -D dj -x 595
-- 啟動資料庫

4. 查看所需資料

select * from xx

小結

pg_resetwal 恢復資料操作及時，資料絕對可恢復，
在 SERVER 端操作所需權限較高，云資料庫可能無法使用，
若 DDL 資料無法找回，雖然元資訊已經恢復，但資料已經不在磁盤上， ERROR: could not open file "base/16392/16396" 表明檔案或目錄已經不存在了，
啟動資料庫后，不可以進行任何影響事務號的操作，否則提升事務號將導致資料再次不可見，
通過 pg_resetwal 恢復資料前，需將資料 PGDATA 目錄進行全量備份，只恢復所需資料
pg_resetwal 操作難度大，需要掌握的 PG 知識較多，

pg_dirtyread

pg_dirtyread[2] 利用 MVCC 機制讀取 Dead 元組，因此可以恢復 UPDATE、DELETE、DROPCOLUMN、ROLLBACK 等 MVCC 機制操作的資料，pg_dirtyread 不存在于 contrib 目錄下，因此需要單獨編譯，

使用示例

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

小結

pg_dirtyread 使用非常方便，僅需要安裝一個插件便可以找回資料，
pg_dirtyread 會回傳全部資料，包含未被洗掉的資料，例如示例中 bar=2 的資料，
基于 MVCC 機制的操作只能實作 DML 的資料找回，

pg_recovery

pg_recovery[3] 與 pg_dirtyread 類似，但是使用更靈活，目前的版本中默認只回傳需要找回的資料，pg_recovery 的目標致力于資料的找回，而不僅僅是讀取 Dead 元組，在后續的版本中，會增加一些輔助資料找回的除錯資訊，來幫助用戶更快的在眾多資料中找到自己需要找回的資料，pg_recovery 不存在于 contrib 目錄下，因此需要單獨編譯，

使用示例

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

小結

pg_recovery 的目標是用于資料找回，因此使用起來更方便，在未來的版本中，也會加入更多輔助資料找回的功能，
pg_recovery(recoveryrow => false) 可以讀取出全部資料，
pg_recovery 只能找回 DML 的資料，

pg_filedump

pg_filedump[4] 是一款命令列工具，因此只能在服務端執行，并且不需要連接資料庫，該工具可以分析出資料檔案中資料的詳細資料，內容格式與 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

小結

pg_filedump 可以直接讀取檔案，無需連接資料庫，適用于嚴重災難的情況，但是需要知道具體的檔案位置，適用性不強，
pg_filedump 可直接通過 SQL 將資料一鍵找回，需要編譯找回資料方法，
pg_filedump 無法找回自定義資料型別的資料，
pg_filedump 由于只能在服務端執行，不適用于用于云資料庫的資料找回，

WalMiner

WalMiner[5] 是從 PostgreSQL 的 WAL（write ahead logs）日志的決議工具，旨在挖掘 WAL 日志所有的有用資訊，從而提供 PG 的資料恢復支持，目前主要有如下功能：

從 WAL 日志中決議出 SQL，包括 DML 和少量 DDL

決議出執行的 SQL 陳述句的工具，并能生成對應的 UNDO SQL陳述句，與傳統的 logical decode 插件相比，WalMiner 不要求 logical 日志級別且決議方式較為靈活，

資料頁挽回

當資料庫被執行了 TRUNCATE 等不被 WAL 記錄的資料清除操作或者發生磁盤頁損壞時，可使用此功能從 WAL 日志中搜索資料，盡量挽回資料，

使用示例

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)';

小結

WalMiner 通過 WAL 日志進行找回，只要日志保存量足夠，便可以找回資料，
WalMiner 可以通過與存盤程序的結合，來實作一鍵資料找回的功能，

pageinspect

pageinspect[6] 是 PostgreSQL 自帶的插件，存在于原始碼 contrib 目錄中，具備更高的穩定，

pageinspace 可以查看資料二進制的存盤方式，并且可以讀取 Dead 元組，因此可以用于資料找回和查看所需找回的資料是否存在，

資料結構

 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 行記錄)

小結

pageinspacet 通常用于底層資料存盤的分析，極難恢復資料，復雜的自定義資料型別，恢復更加困難，雖然可以找回資料，但不推薦，
資料不直觀，例如 {"\\x01000000"，"\\x0561"} ，
資料的先后順序，需要參考 pg_attribute 來獲知回傳的資料對應的列，
需要對 PG 原始碼深度掌握，同一資料型別不同長度資料格式不同，例如"\\x0561"， "\\xab6161"， "\\xbc020000616161”，61 代表字母 a ，

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

因為 MVCC 機制，PG 本身自帶 autovacuum，通常情況下無需手動維護 MVCC ，但autovacuum 的觸發需要一定條件，資料庫至少有 10% 以上的資料膨脹，嚴重的可能超過資料本身，

通過設定引數 vacuum_defer_cleanup_age 可保留部分 Dead 元組，減少資料膨脹對資料庫產生的影響，若需要立即清理資料，可在資料存盤程序呼叫 select * from txid_current(); 增加事務號，清空 Dead 元組，

但即使沒有設定 vacuum_defer_cleanup_age ，由于 vacuum 不及時，及時操作也可以恢復出資料，

PG 資料恢復方案總結

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

pg_recovery 使用簡單，默認只有待找回資料；
pg_dirtyread 使用簡單，默認回傳全部資料；
WalMiner 需要對 walminer 全面掌握，并做好系統預設；
pg_resetwal 需要了解的內容較多；
pg_filedump 需要單獨寫一些腳本或工具來配合使用；
pageinspect 難度極大，

若無任何準備，如何恢復資料？推薦以下方法：

及時設定 vacuum_defer_cleanup_age
安裝 pg_recover 或者 pg_dirtyread
無法安裝插件可以采用 pg_resetwal ，無需任何額外工具

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

參考參考

[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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/413162.html

標籤：MySQL

上一篇：誰再說學不會 MySQL 資料庫，就把這個給他扔過去！

下一篇：【SQL實戰】一個SQL統計全國各地疫情分布情況

盤點 | 常用 PG 資料恢復方案概覽【建議收藏】

資料丟失的原因

DDL

DML

資料恢復方案

pg_resetwal

使用示例

小結

pg_dirtyread

使用示例

小結

pg_recovery

使用示例

小結

pg_filedump

使用示例

小結

WalMiner

使用示例

小結

pageinspect

資料結構

使用示例

小結

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

PG 資料恢復方案總結

參考參考