【PostgreSQL使用pg_filedump工具解析数据文件以恢复数据】_pg数据库filedump的16进制转换-优快云博客

一、pg_filedump工具介绍

PostgreSQL数据库里如果只是一般的数据文件损坏,++首选通过备份恢复，做pitr等，最保险，最可靠++。
数据页损坏，可以使用zero_damaged_pages=on来跳过损坏的数据块来读取数据，然后将数据导到新表中，当然，这部分损坏的数据可能找不回来的。

++如果是没有可用备份的情况++，而且数据目录下的某些文件损坏，可能需要结合其他的环境拷贝相应文件尝试是否能拉起数据库，但是如果这种方式也无法拉起库，数据库无法启动了。这种情况我们可能需要通过工具直接从数据文件中读取数据，例如使用Oracle中的ODU等等的工具。PostgreSQL里有着类似的pg_filedump工具，读取物理数据文件，获取数据。

二、pg_filedump工具安装

下载pg_filedump工具

postgres@ubuntu-linux-22-04-desktop:~$ git clone git://git.postgresql.org/git/pg_filedump.git
Cloning into 'pg_filedump'...
remote: Enumerating objects: 442, done.
remote: Counting objects: 100% (442/442), done.
remote: Compressing objects: 100% (422/422), done.
remote: Total 442 (delta 267), reused 0 (delta 0), pack-reused 0
Receiving objects: 100% (442/442), 138.35 KiB | 1024 bytes/s, done.
Resolving deltas: 100% (267/267), done.

编译安装pg_filedump

postgres@ubuntu-linux-22-04-desktop:~/pg_filedump$ cd pg_filedump
...
postgres@ubuntu-linux-22-04-desktop:~/pg_filedump$ make -j 24
...
postgres@ubuntu-linux-22-04-desktop:~/pg_filedump$ make install -j 24
...

三、pg_filedump工具选项

postgres@ubuntu-linux-22-04-desktop:~/pg_filedump$ pg_filedump --help
Error: Missing file name to dump.

Usage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file

Display formatted contents of a PostgreSQL heap/index/control 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  Decode tuples using given comma separated list of types
      Supported types:
        bigint bigserial bool char charN date float float4 float8 int
        json macaddr name numeric oid real serial smallint smallserial text
        time timestamp timestamptz timetz uuid varchar varcharN xid xml
      ~ ignores all 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
  -o  Do not dump old values.
  -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]
  -t  Dump TOAST files
  -v  Ouput additional information about TOAST relations
  -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]
Additional functions:
  -m  Interpret file as pg_filenode.map file and print contents (all
      other options will be ignored)

Report bugs to <pgsql-bugs@postgresql.org>

命令解析如下：

  -a 格式化时显示绝对地址（块头
      信息总是块相关的）
  -b 显示一个范围内的二进制块图像（选项将打开
      关闭所有格式选项）
  -d 显示格式化的块内容转储（选项将关闭
      所有其他格式选项）
  -D 使用给定的逗号分隔类型列表解码元组。
      支持的类型列表：
        * 大整数
        * 大连载
        *布尔
        * 字符
        * 字符 N -- 字符(n)
        * 日期
        * 漂浮
        * 浮动4
        * 浮动8
        * 整数
        * JSON
        * MAC地址
        * 姓名
        * 数字
        * 对象
        * 真实的
        * 串行
        * smallint
        * 小系列
        * 文本
        * 时间
        * 时间戳
        *时间
        * uuid
        * 变量
        * 变量 N -- 变量(n)
        * xid
        * XML
        * ~ -- 忽略元组中剩余的所有属性
  -f 显示格式化的块内容转储以及解释
  -h 显示此信息
  -i 显示已解释的项目详细信息
  -k 验证块校验和
  -R 显示文件中特定的块范围（块是
      从 0 开始索引）
        [startblock]：开始的块
        [endblock]：块结束于
      没有结束块的起始块将格式化单个块
  -s 强制段大小为 [segsize]
  -n 强制段号为 [segnumber]
  -S 强制块大小为 [blocksize]
  -x 强制将块项的格式解释为索引项
  -y 强制将块项的格式解释为堆项

以下选项对控制文件有效：
  -c 解释列为控制文件的文件
  -f 显示格式化的内容转储以及解释
  -S 强块大小为 [blocksize]

附加功能：
	-m 将文件解释为 pg_filenode.map 文件并打印内容
      （所有其他选项将被忽略）

四、一些使用举例

4.1初始化测试环境

//创建测试数据

postgres=# create table tab_testdump(id int,info text,crt_time timestamp);
CREATE TABLE
postgres=# insert into tab_testdump select generate_series(1,10),left(md5(random()::text),8),clock_timestamp();
INSERT 0 10
postgres=# select * from tab_testdump;
 id |   info   |          crt_time
----+----------+----------------------------
  1 | 9514a4d8 | 2024-01-10 13:36:25.998179
  2 | 0b05a4c8 | 2024-01-10 13:36:25.998652
  3 | 5e3cac92 | 2024-01-10 13:36:25.998659
  4 | 26b50a38 | 2024-01-10 13:36:25.998662
  5 | f7dd5e12 | 2024-01-10 13:36:25.998666
  6 | 34916ff9 | 2024-01-10 13:36:25.998669
  7 | 15ae6253 | 2024-01-10 13:36:25.998672
  8 | db21b153 | 2024-01-10 13:36:25.998675
  9 | dd34afae | 2024-01-10 13:36:25.998678
 10 | 7f85d207 | 2024-01-10 13:36:25.998681
(10 rows)

//查看表对应的文件位置

postgres=# select pg_relation_filepath('tab_testdump');
 pg_relation_filepath
----------------------
 base/13008/33120
(1 row)

//确保数据刷到磁盘

postgres=# checkpoint;
CHECKPOINT

4.2 使用举例

4.2.1 直接使用

读取到的东西除了表头的相关结构，显示的是数据所处的偏移量，数据部分不能直接让我们知道所存储的具体数据。

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump base/13008/33120

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: base/13008/33120
* Options used: None
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      64 (0x0040)
 Block: Size 8192  Version    4            Upper    7712 (0x1e20)
 LSN:  logid      0 recoff 0x11286460      Special  8192 (0x2000)
 Items:   10                      Free Space: 7648
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 64

<Data> -----
 Item   1 -- Length:   48  Offset: 8144 (0x1fd0)  Flags: NORMAL
 Item   2 -- Length:   48  Offset: 8096 (0x1fa0)  Flags: NORMAL
 Item   3 -- Length:   48  Offset: 8048 (0x1f70)  Flags: NORMAL
 Item   4 -- Length:   48  Offset: 8000 (0x1f40)  Flags: NORMAL
 Item   5 -- Length:   48  Offset: 7952 (0x1f10)  Flags: NORMAL
 Item   6 -- Length:   48  Offset: 7904 (0x1ee0)  Flags: NORMAL
 Item   7 -- Length:   48  Offset: 7856 (0x1eb0)  Flags: NORMAL
 Item   8 -- Length:   48  Offset: 7808 (0x1e80)  Flags: NORMAL
 Item   9 -- Length:   48  Offset: 7760 (0x1e50)  Flags: NORMAL
 Item  10 -- Length:   48  Offset: 7712 (0x1e20)  Flags: NORMAL


*** End of File Encountered. Last Block Read: 0 ***

4.2.2 进一步带上 -D选项（读取所有的数据,包含未清理的死元组）

使用-D选项将其转换成可以直观读取的格式，COPY:XXX部分显示的便是表中的实际数据，但是PostgreSQL数据库的MVCC是基于多版本的,这样查看出的数据是包含死元组数据的，并不是数据库里查询的显示的数据。

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -D int,text,timestamp base/13008/33120

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: base/13008/33120
* Options used: -D int,text,timestamp
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      64 (0x0040)
 Block: Size 8192  Version    4            Upper    7712 (0x1e20)
 LSN:  logid      0 recoff 0x11286460      Special  8192 (0x2000)
 Items:   10                      Free Space: 7648
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 64

<Data> -----
 Item   1 -- Length:   48  Offset: 8144 (0x1fd0)  Flags: NORMAL
COPY: 1 9514a4d8        2024-01-10 13:36:25.998179
 Item   2 -- Length:   48  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 2 0b05a4c8        2024-01-10 13:36:25.998652
 Item   3 -- Length:   48  Offset: 8048 (0x1f70)  Flags: NORMAL
COPY: 3 5e3cac92        2024-01-10 13:36:25.998659
 Item   4 -- Length:   48  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 4 26b50a38        2024-01-10 13:36:25.998662
 Item   5 -- Length:   48  Offset: 7952 (0x1f10)  Flags: NORMAL
COPY: 5 f7dd5e12        2024-01-10 13:36:25.998666
 Item   6 -- Length:   48  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 6 34916ff9        2024-01-10 13:36:25.998669
 Item   7 -- Length:   48  Offset: 7856 (0x1eb0)  Flags: NORMAL
COPY: 7 15ae6253        2024-01-10 13:36:25.998672
 Item   8 -- Length:   48  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 8 db21b153        2024-01-10 13:36:25.998675
 Item   9 -- Length:   48  Offset: 7760 (0x1e50)  Flags: NORMAL
COPY: 9 dd34afae        2024-01-10 13:36:25.998678
 Item  10 -- Length:   48  Offset: 7712 (0x1e20)  Flags: NORMAL
COPY: 10        7f85d207        2024-01-10 13:36:25.998681


*** End of File Encountered. Last Block Read: 0 ***

4.2.3 筛选实际数据部分（读取所有的数据,包含未清理的死元组）

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -D int,text,timestamp base/13008/33120  | grep 'COPY'
COPY: 1 9514a4d8        2024-01-10 13:36:25.998179
COPY: 2 0b05a4c8        2024-01-10 13:36:25.998652
COPY: 3 5e3cac92        2024-01-10 13:36:25.998659
COPY: 4 26b50a38        2024-01-10 13:36:25.998662
COPY: 5 f7dd5e12        2024-01-10 13:36:25.998666
COPY: 6 34916ff9        2024-01-10 13:36:25.998669
COPY: 7 15ae6253        2024-01-10 13:36:25.998672
COPY: 8 db21b153        2024-01-10 13:36:25.998675
COPY: 9 dd34afae        2024-01-10 13:36:25.998678
COPY: 10        7f85d207        2024-01-10 13:36:25.998681
postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -D int,text,timestamp base/13008/33120  | grep 'COPY'| awk '{print $2,$3,$4}'
1 9514a4d8 2024-01-10
2 0b05a4c8 2024-01-10
3 5e3cac92 2024-01-10
4 26b50a38 2024-01-10
5 f7dd5e12 2024-01-10
6 34916ff9 2024-01-10
7 15ae6253 2024-01-10
8 db21b153 2024-01-10
9 dd34afae 2024-01-10
10 7f85d207 2024-01-10

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

4.2.4 读取表中数据（排除死元组）

上面已经提到了，如果仅仅使用 -D选项，是会包含live_tup和dead_tup的，这样我们就没办法得到准确的表里的数据。

所以我们需要过滤出死元组。

4.2.4.1 创造含死元组的表

先进行一个更新，创造死元组的数据。

postgres=# update tab_testdump set info='ysladeadtup' where id=10;
UPDATE 1
postgres=# select * from tab_testdump;
 id |    info     |          crt_time
----+-------------+----------------------------
  1 | 9514a4d8    | 2024-01-10 13:36:25.998179
  2 | 0b05a4c8    | 2024-01-10 13:36:25.998652
  3 | 5e3cac92    | 2024-01-10 13:36:25.998659
  4 | 26b50a38    | 2024-01-10 13:36:25.998662
  5 | f7dd5e12    | 2024-01-10 13:36:25.998666
  6 | 34916ff9    | 2024-01-10 13:36:25.998669
  7 | 15ae6253    | 2024-01-10 13:36:25.998672
  8 | db21b153    | 2024-01-10 13:36:25.998675
  9 | dd34afae    | 2024-01-10 13:36:25.998678
 10 | ysladeadtup | 2024-01-10 13:36:25.998681
(10 rows)

postgres=# select n_tup_upd,n_live_tup,n_dead_tup from pg_stat_all_tables where relname='tab_testdump';
 n_tup_upd | n_live_tup | n_dead_tup
-----------+------------+------------
         1 |         10 |          1
(1 row)

此时用上边pg_filedump去看包含死元组的数据，这样其实无法分出死元组和活跃的元组。无法找到真实的数据。

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -D int,text,timestamp base/13008/33120  | grep 'COPY'
COPY: 1 9514a4d8        2024-01-10 13:36:25.998179
COPY: 2 0b05a4c8        2024-01-10 13:36:25.998652
COPY: 3 5e3cac92        2024-01-10 13:36:25.998659
COPY: 4 26b50a38        2024-01-10 13:36:25.998662
COPY: 5 f7dd5e12        2024-01-10 13:36:25.998666
COPY: 6 34916ff9        2024-01-10 13:36:25.998669
COPY: 7 15ae6253        2024-01-10 13:36:25.998672
COPY: 8 db21b153        2024-01-10 13:36:25.998675
COPY: 9 dd34afae        2024-01-10 13:36:25.998678
COPY: 10        7f85d207        2024-01-10 13:36:25.998681
COPY: 10        ysladeadtup     2024-01-10 13:36:25.998681

4.2.4.2 pg_filedump查看解析后的元组的xmin和xmax

这个时候使用pg_filedump的时候需要带上 -i选项。这个时候

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -i -D int,text,timestamp base/13008/33120

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: base/13008/33120
* Options used: -i -D int,text,timestamp
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      68 (0x0044)
 Block: Size 8192  Version    4            Upper    7664 (0x1df0)
 LSN:  logid      0 recoff 0x112868f0      Special  8192 (0x2000)
 Items:   11                      Free Space: 7596
 Checksum: 0x0000  Prune XID: 0x000006c6  Flags: 0x0000 ()
 Length (including item array): 68

<Data> -----
 Item   1 -- Length:   48  Offset: 8144 (0x1fd0)  Flags: NORMAL
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 1   Attributes: 3   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)

COPY: 1 9514a4d8        2024-01-10 13:36:25.998179
 Item   2 -- Length:   48  Offset: 8096 (0x1fa0)  Flags: NORMAL
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 2   Attributes: 3   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)

COPY: 2 0b05a4c8        2024-01-10 13:36:25.998652
 Item   3 -- Length:   48  Offset: 8048 (0x1f70)  Flags: NORMAL
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 3   Attributes: 3   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)

4.2.4.3 命令行语句过滤

暂时处理

 10 ysladeadtup 2024-01-10 13:36:25.998681
postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -i -D int,text,timestamp base/13008/33120 |grep  -v 'infomask'| egrep 'COPY|XMAX'
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 1 9514a4d8        2024-01-10 13:36:25.998179
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 2 0b05a4c8        2024-01-10 13:36:25.998652
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 3 5e3cac92        2024-01-10 13:36:25.998659
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 4 26b50a38        2024-01-10 13:36:25.998662
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 5 f7dd5e12        2024-01-10 13:36:25.998666
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 6 34916ff9        2024-01-10 13:36:25.998669
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 7 15ae6253        2024-01-10 13:36:25.998672
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 8 db21b153        2024-01-10 13:36:25.998675
  XMIN: 1733  XMAX: 0  CID|XVAC: 0
COPY: 9 dd34afae        2024-01-10 13:36:25.998678
  XMIN: 1733  XMAX: 1734  CID|XVAC: 0
COPY: 10        7f85d207        2024-01-10 13:36:25.998681
  XMIN: 1734  XMAX: 0  CID|XVAC: 0
COPY: 10        ysladeadtup     2024-01-10 13:36:25.998681

去掉COPY字段。

 pg_filedump -i -D int,text,timestamp base/13008/33120 |grep  -v 'infomask'| egrep 'COPY|XMAX' |awk '{$1=null;print $0}'
 1733 XMAX: 0 CID|XVAC: 0
 1 9514a4d8 2024-01-10 13:36:25.998179
 1733 XMAX: 0 CID|XVAC: 0
 2 0b05a4c8 2024-01-10 13:36:25.998652
 1733 XMAX: 0 CID|XVAC: 0
 3 5e3cac92 2024-01-10 13:36:25.998659
 1733 XMAX: 0 CID|XVAC: 0
 4 26b50a38 2024-01-10 13:36:25.998662
 1733 XMAX: 0 CID|XVAC: 0
 5 f7dd5e12 2024-01-10 13:36:25.998666
 1733 XMAX: 0 CID|XVAC: 0
 6 34916ff9 2024-01-10 13:36:25.998669
 1733 XMAX: 0 CID|XVAC: 0
 7 15ae6253 2024-01-10 13:36:25.998672
 1733 XMAX: 0 CID|XVAC: 0
 8 db21b153 2024-01-10 13:36:25.998675
 1733 XMAX: 0 CID|XVAC: 0
 9 dd34afae 2024-01-10 13:36:25.998678
 1733 XMAX: 1734 CID|XVAC: 0
 10 7f85d207 2024-01-10 13:36:25.998681
 1734 XMAX: 0 CID|XVAC: 0
 10 ysladeadtup 2024-01-10 13:36:25.998681

合并到一行

pg_filedump -i -D int,text,timestamp base/13008/33120 |grep  -v 'infomask'| egrep 'COPY|XMAX' |awk '{$1=null;print $0}'|awk 'ORS=NR%2?" ":"\n"{print}'
 1733 XMAX: 0 CID|XVAC: 0  1 9514a4d8 2024-01-10 13:36:25.998179
 1733 XMAX: 0 CID|XVAC: 0  2 0b05a4c8 2024-01-10 13:36:25.998652
 1733 XMAX: 0 CID|XVAC: 0  3 5e3cac92 2024-01-10 13:36:25.998659
 1733 XMAX: 0 CID|XVAC: 0  4 26b50a38 2024-01-10 13:36:25.998662
 1733 XMAX: 0 CID|XVAC: 0  5 f7dd5e12 2024-01-10 13:36:25.998666
 1733 XMAX: 0 CID|XVAC: 0  6 34916ff9 2024-01-10 13:36:25.998669
 1733 XMAX: 0 CID|XVAC: 0  7 15ae6253 2024-01-10 13:36:25.998672
 1733 XMAX: 0 CID|XVAC: 0  8 db21b153 2024-01-10 13:36:25.998675
 1733 XMAX: 0 CID|XVAC: 0  9 dd34afae 2024-01-10 13:36:25.998678
 1733 XMAX: 1734 CID|XVAC: 0  10 7f85d207 2024-01-10 13:36:25.998681
 1734 XMAX: 0 CID|XVAC: 0  10 ysladeadtup 2024-01-10 13:36:25.998681

分别查看数据部分和区分

postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -i -D int,text,timestamp base/13008/33120 |grep  -v 'infomask'| egrep 'COPY|XMAX' |awk '{$1=null;print $0}'|awk 'ORS=NR%2?" ":"\n"{print}'| awk '{print $1,$2,$3,$4,$5}'
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 0 CID|XVAC: 0
1733 XMAX: 1734 CID|XVAC: 0
1734 XMAX: 0 CID|XVAC: 0
postgres@ubuntu-linux-22-04-desktop:~/data-16$ pg_filedump -i -D int,text,timestamp base/13008/33120 |grep  -v 'infomask'| egrep 'COPY|XMAX' |awk '{$1=null;print $0}'|awk 'ORS=NR%2?" ":"\n"{print}'| awk '{print $6,$7,$8}'
1 9514a4d8 2024-01-10
2 0b05a4c8 2024-01-10
3 5e3cac92 2024-01-10
4 26b50a38 2024-01-10
5 f7dd5e12 2024-01-10
6 34916ff9 2024-01-10
7 15ae6253 2024-01-10
8 db21b153 2024-01-10
9 dd34afae 2024-01-10
10 7f85d207 2024-01-10
10 ysladeadtup 2024-01-10

过滤语句如下，这部分根