腳本之家服務器常用軟件

快捷導航

常用?PostgreSQL?數(shù)據恢復方案及使用示例

更新時間：2022年01月19日 09:34:25 作者：RadonDB開源社區(qū)

這篇文章主要介紹了常用?PostgreSQL?數(shù)據恢復方案概覽,數(shù)據丟失通常是由?DDL?與?DML?兩種操作引起，由于在操作系統(tǒng)中表文件已經不存在，所以只能采用恢復磁盤的方法進行數(shù)據恢復，需要的朋友可以參考下

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

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

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

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

數(shù)據丟失的原因

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

DDL

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

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

DML

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

數(shù)據恢復方案

pg_resetwal

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

使用示例

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

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

-- 關閉數(shù)據
./pg_resetwal -D dj -x 595
-- 啟動數(shù)據庫

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

select * from xx

小結

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

pg_dirtyread

pg_dirtyread[2] 利用 MVCC 機制讀取 Dead 元組。因此可以恢復 UPDATE、DELETE、DROPCOLUMN、ROLLBACK 等 MVCC 機制操作的數(shù)據。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 使用非常方便，僅需要安裝一個插件便可以找回數(shù)據。
pg_dirtyread 會返回全部數(shù)據，包含未被刪除的數(shù)據。例如示例中 bar=2 的數(shù)據。
基于 MVCC 機制的操作只能實現(xiàn) DML 的數(shù)據找回。

pg_recovery

pg_recovery[3] 與 pg_dirtyread 類似，但是使用更靈活。目前的版本中默認只返回需要找回的數(shù)據。pg_recovery 的目標致力于數(shù)據的找回，而不僅僅是讀取 Dead 元組，在后續(xù)的版本中，會增加一些輔助數(shù)據找回的調試信息，來幫助用戶更快的在眾多數(shù)據中找到自己需要找回的數(shù)據。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 的目標是用于數(shù)據找回，因此使用起來更方便。在未來的版本中，也會加入更多輔助數(shù)據找回的功能。
pg_recovery(recoveryrow => false) 可以讀取出全部數(shù)據。
pg_recovery 只能找回 DML 的數(shù)據。

pg_filedump

pg_filedump[4] 是一款命令行工具，因此只能在服務端執(zhí)行，并且不需要連接數(shù)據庫。該工具可以分析出數(shù)據文件中數(shù)據的詳細數(shù)據，內容格式與 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 可以直接讀取文件，無需連接數(shù)據庫，適用于嚴重災難的情況。但是需要知道具體的文件位置，適用性不強。
pg_filedump 可直接通過 SQL 將數(shù)據一鍵找回，需要編譯找回數(shù)據方法。
pg_filedump 無法找回自定義數(shù)據類型的數(shù)據。
pg_filedump 由于只能在服務端執(zhí)行，不適用于用于云數(shù)據庫的數(shù)據找回。

WalMiner

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

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

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

數(shù)據頁挽回

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

使用示例

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 日志進行找回，只要日志保存量足夠，便可以找回數(shù)據。
WalMiner 可以通過與存儲過程的結合，來實現(xiàn)一鍵數(shù)據找回的功能。

pageinspect

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

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

數(shù)據結構

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

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

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

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

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