pg_filedump

pg_filedump 工具可以针对数据文件、索引文件、控制文件进行dump格式化输出，方便我们学习查看文件中格式及内容还有数据块上一些细节内容。如果你想更加了解PG内部存储格式这个工具是你很好的选择。

• 项目地址:https://github.com/ChristophBerg/pg_filedump

使用介绍

• 创建一张表插入几条记录

postgres=# create table dhytest (id int , name char(20));
CREATE TABLE
postgres=# insert into dhytest values (10, '董红禹');
INSERT 0 1
postgres=# insert into dhytest values (20, 'PostgreSQL');
INSERT 0 1
postgres=# insert into dhytest values (30, 'MySQL');
INSERT 0 1

• 查找表对应的存储位置

postgres=# select * from pg_relation_filepath('dhytest');
 pg_relation_filepath
----------------------
 base/12558/189038
(1 row)

• 将表中数据dump出来查看

./pg_filedump -D int,charN  /pg_data/base/12558/189038
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.1
*
* File: /pg_data/base/12558/189038
* Options used: -D int,charN
*
* Dump created on: Fri Oct 26 13:35:45 2018
*******************************************************************
Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      36 (0x0024)
 Block: Size 8192  Version    4            Upper    8024 (0x1f58)
 LSN:  logid      1 recoff 0xde3679f8      Special  8192 (0x2000)
 Items:    3                      Free Space: 7988
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 36
<Data> ------
 Item   1 -- Length:   55  Offset: 8136 (0x1fc8)  Flags: NORMAL
COPY: 10    董红禹
 Item   2 -- Length:   49  Offset: 8080 (0x1f90)  Flags: NORMAL
COPY: 20    PostgreSQL
 Item   3 -- Length:   49  Offset: 8024 (0x1f58)  Flags: NORMAL
COPY: 30    MySQL

• 显示格式化的内容，可以看到每个字节对应的内容是什么

./pg_filedump -f    /pg_data/base/12558/189038
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.1
*
* File: /pg_data/base/12558/189038
* Options used: -f
*
* Dump created on: Fri Oct 26 13:39:20 2018
*******************************************************************
Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      36 (0x0024)
 Block: Size 8192  Version    4            Upper    8024 (0x1f58)
 LSN:  logid      1 recoff 0xde3679f8      Special  8192 (0x2000)
 Items:    3                      Free Space: 7988
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 36
  0000: 01000000 f87936de 00000000 2400581f  .....y6.....$.X.
  0010: 00200420 00000000 c89f6e00 909f6200  . . ......n...b.
  0020: 589f6200                             X.b.
<Data> ------
 Item   1 -- Length:   55  Offset: 8136 (0x1fc8)  Flags: NORMAL
  1fc8: 66040000 00000000 00000000 00000000  f...............
  1fd8: 01000200 02081800 0a000000 37e891a3  ............7...
  1fe8: e7baa2e7 a6b92020 20202020 20202020  ......
  1ff8: 20202020 202020
 Item   2 -- Length:   49  Offset: 8080 (0x1f90)  Flags: NORMAL
  1f90: 67040000 00000000 00000000 00000000  g...............
  1fa0: 02000200 02081800 14000000 2b506f73  ............+Pos
  1fb0: 74677265 53514c20 20202020 20202020  tgreSQL
  1fc0: 20
 Item   3 -- Length:   49  Offset: 8024 (0x1f58)  Flags: NORMAL
  1f58: 68040000 00000000 00000000 00000000  h...............
  1f68: 03000200 02081800 1e000000 2b4d7953  ............+MyS
  1f78: 514c2020 20202020 20202020 20202020  QL
  1f88: 20

• 将数据块上一些细节内容打印出来

./pg_filedump -i    /pg_data/base/12558/189038
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.1
*
* File: /pg_data/base/12558/189038
* Options used: -i
*
* Dump created on: Fri Oct 26 13:41:27 2018
*******************************************************************
Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      36 (0x0024)
 Block: Size 8192  Version    4            Upper    8024 (0x1f58)
 LSN:  logid      1 recoff 0xde3679f8      Special  8192 (0x2000)
 Items:    3                      Free Space: 7988
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 36
<Data> ------
 Item   1 -- Length:   55  Offset: 8136 (0x1fc8)  Flags: NORMAL
  XMIN: 1126  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 1   Attributes: 2   Size: 24
  infomask: 0x0802 (HASVARWIDTH|XMAX_INVALID)
 Item   2 -- Length:   49  Offset: 8080 (0x1f90)  Flags: NORMAL
  XMIN: 1127  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 2   Attributes: 2   Size: 24
  infomask: 0x0802 (HASVARWIDTH|XMAX_INVALID)
 Item   3 -- Length:   49  Offset: 8024 (0x1f58)  Flags: NORMAL
  XMIN: 1128  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 3   Attributes: 2   Size: 24
  infomask: 0x0802 (HASVARWIDTH|XMAX_INVALID)