标签
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
AI 代码解读
命令帮助如下，通常来说，你只需要指定需要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.
AI 代码解读
pg_filedump使用举例
1. 创建测试表
postgres=# create table digoal(id int, info text, crt_time timestamp);
CREATE TABLE
AI 代码解读
2. 插入测试数据
postgres=# insert into digoal select generate_series(1,1000000),md5(random()::text), clock_timestamp();
INSERT 0 1000000
AI 代码解读
3. 找出表对应的数据文件
postgres=# select pg_relation_filepath('digoal');
pg_relation_filepath
----------------------
base/13269/173369
(1 row)
AI 代码解读
4. 调用checkpoint，把数据刷盘，便于我们接下来的观察
checkpoint;
AI 代码解读
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
......
AI 代码解读
为了得到记录，需要提供一下字段类型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
..........
AI 代码解读
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)
AI 代码解读
记录头的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 */
AI 代码解读
观察deadtuple
postgres=# update digoal set info='new' where id=1;
UPDATE 1
postgres=# checkpoint;
CHECKPOINT
AI 代码解读
观察
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
AI 代码解读
查看新版本（编号为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
AI 代码解读
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)
AI 代码解读
每个块内的组织，与对象类型有关，比如堆表，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"
AI 代码解读
防止脱裤
我们已经看到，使用pg_filedump可直接decode数据文件的内容，因此泄露数据文件其实是比较危险的。
那么如何防止脱裤呢？TDE是一个很好的手段，即数据文件透明加密。你可以参考我末尾的文章。
另外还有加密方法，比如对敏感数据，使用加密字段存储。加解密交给程序完成。彻底杜绝因泄露文件导致的数据泄露。
