PostgreSQL資料檔案災難恢復-解析與資料dump
標籤
PostgreSQL , 資料檔案 , pg_filedump , 安全 , TDE
背景
俗話說常在河邊站哪有不溼鞋，作為一名戰鬥在一線的DBA或者開發者，可能有遇到過磁碟損壞，磁碟陣列損壞，如果有備份或者備庫的話，還好。
如果沒有備份，或者沒有備庫（通常有一些小型或者創業型的企業），那麼遇到磁碟損壞或者其他原因（比如掉電檔案系統損壞），導致資料庫的資料檔案並不完整時，如何從有限的資料中找出資料呢?
比如PostgreSQL，如果讀到壞塊，會報塊不可讀的錯誤，這種情況下通過設定zero_damaged_pages=on可以跳過損壞的資料塊。
如果連後設資料都損壞了，又或者壞了一些磁碟，只有某些表空間被倖免於難，這些情況下你的資料庫都已經無法啟動時，如何能從有限的資料檔案中找回資料呢？
資料檔案解析pg_filedump
pg_filedump是PostgreSQL社群託管的一個專案，類似於pg_xlogdump，不需要開啟資料庫，可以直接從資料檔案中將資料dump出來。
pg_filedump實際上可以DUMP 堆表、索引資料檔案，控制檔案的內容。（從pg_filedump引用的標頭檔案也能看出端倪）
安裝很簡單
git clone git://git.postgresql.org/git/pg_filedump.git
cd pg_filedump
export PATH=/home/digoal/pgsql9.6/bin:$PATH
make ; make install
命令幫助如下，通常來說，你只需要指定需要DUMP的檔案即可。
如果檔案的塊頭損壞了，那麼你可以手工指定一些資訊，包括塊大小，段大小，解析哪個塊，根據什麼格式解析（欄位型別列表）等。
pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file
Defaults are: relative addressing, range of the entire file, block size
as listed on block 0 in the file
The following options are valid for heap and index files:
-a Display absolute addresses when formatting (Block header
information is always block relative)
-b Display binary block images within a range (Option will turn
off all formatting options)
-d Display formatted block content dump (Option will turn off
all other formatting options)
-D Try to decode tuples using given comma separated list of types.
List of supported types:
* bigint
* bigserial
* bool
* char
* charN -- char(n)
* date
* float
* float4
* float8
* int
* json
* macaddr
* name
* oid
* real
* serial
* smallint
* smallserial
* text
* time
* timestamp
* timetz
* uuid
* varchar
* varcharN -- varchar(n)
* xid
* xml
* ~ -- ignores are attributes left in a tuple
-f Display formatted block content dump along with interpretation
-h Display this information
-i Display interpreted item details
-k Verify block checksums
-R Display specific block ranges within the file (Blocks are
indexed from 0)
[startblock]: block to start at
[endblock]: block to end at
A startblock without an endblock will format the single block
-s Force segment size to [segsize]
-n Force segment number to [segnumber]
-S Force block size to [blocksize]
-x Force interpreted formatting of block items as index items
-y Force interpreted formatting of block items as heap items
The following options are valid for control files:
-c Interpret the file listed as a control file
-f Display formatted content dump along with interpretation
-S Force block size to [blocksize]
In most cases it`s recommended to use the -i and -f options to get
the most useful dump output.
pg_filedump使用舉例
1. 建立測試表
postgres=# create table digoal(id int, info text, crt_time timestamp);
CREATE TABLE
2. 插入測試資料
postgres=# insert into digoal select generate_series(1,1000000),md5(random()::text), clock_timestamp();
INSERT 0 1000000
3. 找出表對應的資料檔案
postgres=# select pg_relation_filepath(`digoal`);
pg_relation_filepath
----------------------
base/13269/173369
(1 row)
4. 呼叫checkpoint，把資料刷盤，便於我們接下來的觀察
checkpoint;
5. 使用pg_filedump直接讀取資料檔案，匯出資料
-> cd $PGDATA/base/13269
-> pg_filedump -i -f ./173369
輸出擷取
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 9.6.0
*
* File: ./173369
* Options used: -i -f
*
* Dump created on: Sun Mar 12 00:28:56 2017
*******************************************************************
首先是塊的頭部內容
Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 452 (0x01c4)
Block: Size 8192 Version 4 Upper 488 (0x01e8)
LSN: logid 61 recoff 0xe69d6490 Special 8192 (0x2000)
Items: 107 Free Space: 36
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
Length (including item array): 452
...
然後是DATA部分，
<Data> ------
Item 1 -- Length: 72 Offset: 8120 (0x1fb8) Flags: NORMAL
XMIN: 88072212 XMAX: 88072214 CID|XVAC: 0
Block Id: 9345 linp Index: 86 Attributes: 3 Size: 24
infomask: 0x0102 (HASVARWIDTH|XMIN_COMMITTED)
1fb8: 14e03f05 16e03f05 00000000 00008124 ..?...?........$
1fc8: 56000300 02011800 01000000 43363134 V...........C614
1fd8: 62653439 31616339 65356636 64633136 be491ac9e5f6dc16
1fe8: 35653065 31323162 36316563 33000000 5e0e121b61ec3...
1ff8: 791cce69 7ced0100 y..i|...
COPY: 1 614be491ac9e5f6dc165e0e121b61ec3 2017-03-12 00:26:23.553657
Item 2 -- Length: 72 Offset: 8048 (0x1f70) Flags: NORMAL
XMIN: 88072212 XMAX: 0 CID|XVAC: 0
Block Id: 0 linp Index: 2 Attributes: 3 Size: 24
infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)
1f70: 14e03f05 00000000 00000000 00000000 ..?.............
1f80: 02000300 02091800 02000000 43383335 ............C835
1f90: 39653064 31623462 61323261 64336139 9e0d1b4ba22ad3a9
1fa0: 65386634 38316231 61633336 31000000 e8f481b1ac361...
1fb0: df1cce69 7ced0100 ...i|...
COPY: 2 8359e0d1b4ba22ad3a9e8f481b1ac361 2017-03-12 00:26:23.553759
......
為了得到記錄，需要提供一下欄位型別LIST，必須保證與表結構一致
-> pg_filedump -D int,text,timestamp ./173369|less
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 9.6.0
*
* File: ./173369
* Options used: -D int,text,timestamp
*
* Dump created on: Sun Mar 12 00:31:25 2017
*******************************************************************
Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 452 (0x01c4)
Block: Size 8192 Version 4 Upper 488 (0x01e8)
LSN: logid 61 recoff 0xe69d6490 Special 8192 (0x2000)
Items: 107 Free Space: 36
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
Length (including item array): 452
<Data> ------
Item 1 -- Length: 72 Offset: 8120 (0x1fb8) Flags: NORMAL
COPY: 1 614be491ac9e5f6dc165e0e121b61ec3 2017-03-12 00:26:23.553657
Item 2 -- Length: 72 Offset: 8048 (0x1f70) Flags: NORMAL
COPY: 2 8359e0d1b4ba22ad3a9e8f481b1ac361 2017-03-12 00:26:23.553759
Item 3 -- Length: 72 Offset: 7976 (0x1f28) Flags: NORMAL
COPY: 3 0dc8c441e91217897f994ae163510653 2017-03-12 00:26:23.553764
..........
COPY得到的就是使用-D提供的型別列表decode拼裝的記錄。
是不是可以從檔案中DUMP資料了呢？莫急，還要看看掩碼哦，否則你不知道這條記錄是否為你需要的，因為它可能是DEAD TUPLE。
例子
-> pg_filedump -D int,text,timestamp -i -f ./173369|less
COPY: 1 614be491ac9e5f6dc165e0e121b61ec3 2017-03-12 00:26:23.553657
Item 2 -- Length: 72 Offset: 8048 (0x1f70) Flags: NORMAL
XMIN: 88072212 XMAX: 0 CID|XVAC: 0
Block Id: 0 linp Index: 2 Attributes: 3 Size: 24
infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)
記錄頭的infomask解釋
每條記錄，頭部都有infomask, infomask2掩碼，掩碼錶示的意思，可以參考標頭檔案
比如什麼是DEAD TUPLE呢？
src/include/access/htup_details.h
/*
* information stored in t_infomask:
*/
#define HEAP_HASNULL 0x0001 /* has null attribute(s) */
#define HEAP_HASVARWIDTH 0x0002 /* has variable-width attribute(s) */
#define HEAP_HASEXTERNAL 0x0004 /* has external stored attribute(s) */
#define HEAP_HASOID 0x0008 /* has an object-id field */
#define HEAP_XMAX_KEYSHR_LOCK 0x0010 /* xmax is a key-shared locker */
#define HEAP_COMBOCID 0x0020 /* t_cid is a combo cid */
#define HEAP_XMAX_EXCL_LOCK 0x0040 /* xmax is exclusive locker */
#define HEAP_XMAX_LOCK_ONLY 0x0080 /* xmax, if valid, is only a locker */
/* xmax is a shared locker */
#define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK |
HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */
#define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */
#define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED 0x0400 /* t_xmax committed */
#define HEAP_XMAX_INVALID 0x0800 /* t_xmax invalid/aborted */
#define HEAP_XMAX_IS_MULTI 0x1000 /* t_xmax is a MultiXactId */
#define HEAP_UPDATED 0x2000 /* this is UPDATEd version of row */
#define HEAP_MOVED_OFF 0x4000 /* moved to another place by pre-9.0
* VACUUM FULL; kept for binary
* upgrade support */
#define HEAP_MOVED_IN 0x8000 /* moved from another place by pre-9.0
* VACUUM FULL; kept for binary
* upgrade support */
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)
#define HEAP_XACT_MASK 0xFFF0 /* visibility-related bits */
觀察deadtuple
postgres=# update digoal set info=`new` where id=1;
UPDATE 1
postgres=# checkpoint;
CHECKPOINT
觀察
pg_filedump -D int,text,timestamp -i -f ./173369|less
Item 1 -- Length: 72 Offset: 8120 (0x1fb8) Flags: NORMAL
包含了xmax，說明是更新過的記錄
XMIN: 88072212 XMAX: 88072214 CID|XVAC: 0
blockid表示記錄指向，即新版本記錄在哪個新資料塊，Linp指新資料塊的第幾條記錄。
Block Id: 9345 linp Index: 86 Attributes: 3 Size: 24
infomask 參考前面的標頭檔案，解讀
infomask: 0x0102 (HASVARWIDTH|XMIN_COMMITTED)
1fb8: 14e03f05 16e03f05 00000000 00008124 ..?...?........$
1fc8: 56000300 02011800 01000000 43363134 V...........C614
1fd8: 62653439 31616339 65356636 64633136 be491ac9e5f6dc16
1fe8: 35653065 31323162 36316563 33000000 5e0e121b61ec3...
1ff8: 791cce69 7ced0100 y..i|...
COPY: 1 614be491ac9e5f6dc165e0e121b61ec3 2017-03-12 00:26:23.553657
檢視新版本（編號為9345資料塊，第86條記錄）
pg_filedump -D int,text,timestamp -i -f -R 9345 ./173369|less
Item 86 -- Length: 40 Offset: 2032 (0x07f0) Flags: NORMAL
XMIN: 88072214 XMAX: 0 CID|XVAC: 0
Block Id: 9345 linp Index: 86 Attributes: 3 Size: 24
infomask: 0x2802 (HASVARWIDTH|XMAX_INVALID|UPDATED)
07f0: 16e03f05 00000000 00000000 00008124 ..?............$
0800: 56000300 02281800 01000000 096e6577 V....(.......new
0810: 791cce69 7ced0100 y..i|...
COPY: 1 new 2017-03-12 00:26:23.553657
PostgreSQL資料塊的簡介
對於資料檔案的組織形式，可以參考標頭檔案
src/include/storage
/*
* BlockNumber:
*
* each data file (heap or index) is divided into postgres disk blocks
* (which may be thought of as the unit of i/o -- a postgres buffer
* contains exactly one disk block). the blocks are numbered
* sequentially, 0 to 0xFFFFFFFE.
*
* InvalidBlockNumber is the same thing as P_NEW in buf.h.
*
* the access methods, the buffer manager and the storage manager are
* more or less the only pieces of code that should be accessing disk
* blocks directly.
*/
typedef uint32 BlockNumber;
#define InvalidBlockNumber ((BlockNumber) 0xFFFFFFFF)
#define MaxBlockNumber ((BlockNumber) 0xFFFFFFFE)
每個塊內的組織，與物件型別有關，比如堆表，B-TREE，HASH等索引，TOAST, FSM等。
可以參考資料layout介紹
https://www.postgresql.org/docs/9.6/static/storage.html
也可以參考對應型別的標頭檔案
閱讀pg_filedump的原始碼，也有助於你對PostgreSQL儲存構造的理解
不妨仔細閱讀以下標頭檔案
#include "access/gin_private.h"
#include "access/gist.h"
#include "access/hash.h"
#include "access/htup.h"
#include "access/htup_details.h"
#include "access/itup.h"
#include "access/nbtree.h"
#include "access/spgist_private.h"
#include "catalog/pg_control.h"
#include "storage/bufpage.h"
防止脫褲
我們已經看到，使用pg_filedump可直接decode資料檔案的內容，因此洩露資料檔案其實是比較危險的。
那麼如何防止脫褲呢？TDE是一個很好的手段，即資料檔案透明加密。你可以參考我末尾的文章。
另外還有加密方法，比如對敏感資料，使用加密欄位儲存。加解密交給程式完成。徹底杜絕因洩露檔案導致的資料洩露。
參考
《PostgreSQL 透明加密(TDE,FDE) – 塊級加密》
相關文章
- MySQL資料災難挽救之ibdata檔案誤刪恢復MySql
- 伺服器資料恢復-UNIX類檔案系統資料災難的資料恢復可能性分析伺服器資料恢復
- 【資料庫資料恢復】ORACLE常見資料災難&資料恢復可能性資料庫資料恢復Oracle
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- postgresql備份與恢復資料庫SQL資料庫
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer
- 【伺服器資料恢復】StorNext檔案系統資料恢復案例伺服器資料恢復
- 資料恢復新姿勢——通過ibd和frm檔案恢復資料資料恢復
- 資料庫資料恢復-SQL SERVER資料庫檔案大小變為“0”的資料恢復方案資料庫資料恢復SQLServer
- PostgreSQL DBA(30) - Backup&Recovery#3(資料檔案損壞恢復)SQL
- pg_dump 備份，恢復資料庫資料庫
- 【儲存資料恢復】WAFL檔案系統下raid資料恢復案例資料恢復AI
- 【資料庫資料恢復】EXT3檔案系統下MYSQL資料庫恢復案例資料庫資料恢復MySql
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 【伺服器資料恢復】xfs檔案系統資料丟失的資料恢復案例伺服器資料恢復
- 【北亞資料恢復】MongoDB資料遷移檔案丟失的MongoDB資料恢復案例資料恢復MongoDB
- MSSQL資料庫資料恢復案例:ndf檔案大小變為0KB恢復資料SQL資料庫資料恢復
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 怎樣恢復Mac檔案及資料夾資料？BackupLoupe for mac(資料恢復備份助手)3.5.4Mac資料恢復
- oracle資料庫災難挽救應急方案之DML誤操作恢復Oracle資料庫
- Veeam助力TrendMicro解決資料保護和災難恢復挑戰
- PG-pg_dump備份/恢復資料庫資料庫
- 電腦檔案丟失資料恢復資料恢復
- Linux伺服器資料恢復案例；ocfs2檔案系統資料恢復Linux伺服器資料恢復
- 伺服器資料恢復-ext3檔案系統下oracle資料庫資料恢復案例伺服器資料恢復Oracle資料庫
- Oracle & MySQL & PostgreSQL資料庫恢復支援OracleMySql資料庫
- oracle資料庫災難挽救應急方案之DDL誤操作恢復（drop）Oracle資料庫
- oracle資料庫災難挽救應急方案之DDL誤操作恢復（truncate）Oracle資料庫
- MongoDB資料庫報錯，資料庫檔案丟失資料恢復案例MongoDB資料庫資料恢復
- 【伺服器資料恢復】linux ext3檔案系統下mysql資料庫資料恢復案例伺服器資料恢復LinuxMySql資料庫
- 【伺服器資料恢復】reiserfs檔案系統下RAID5資料恢復案例伺服器資料恢復AI
- 【伺服器資料恢復】ZFS檔案系統下伺服器資料恢復案例伺服器資料恢復
- 資料恢復：AMDU資料抽取恢復資料恢復
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- SQL SEVER 缺少LOG檔案資料庫恢復SQL資料庫
- mysql通過frm、idb檔案恢復資料MySql
- 【分散式儲存資料恢復】hbase和hive資料庫底層檔案誤刪的資料恢復案例分散式資料恢復Hive資料庫