pg_filedump

一、pg_filedump简介

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

参考：pg_filedump

二、安装

参考：pg_filedump安装

yum -y install git
su - postgres
git clone https://git.postgresql.org/git/pg_filedump.git
git branch -a
git tag -l
cd pg_filedump/
make -f Makefile.contrib USE_PGXS=1
# 安装完成后查看 pg_filedump 目录
[postgres@node1 pg_filedump]$ ll
total 280
-rw-rw-r--. 1 postgres postgres 31161 Jan 21 17:05 decode.c
-rw-rw-r--. 1 postgres postgres   351 Jan 21 17:05 decode.h
-rw-rw-r--. 1 postgres postgres 19168 Jan 21 17:09 decode.o
drwxrwxr-x. 2 postgres postgres   142 Jan 21 17:05 expected
-rw-rw-r--. 1 postgres postgres   919 Jan 21 17:05 Makefile
-rw-rw-r--. 1 postgres postgres   541 Jan 21 17:05 Makefile.contrib
-rwxrwxr-x. 1 postgres postgres 81696 Jan 21 17:09 pg_filedump
-rw-rw-r--. 1 postgres postgres 62135 Jan 21 17:05 pg_filedump.c
-rw-rw-r--. 1 postgres postgres  5687 Jan 21 17:05 pg_filedump.h
-rw-rw-r--. 1 postgres postgres 42928 Jan 21 17:09 pg_filedump.o
-rw-rw-r--. 1 postgres postgres  4513 Jan 21 17:05 README.pg_filedump
drwxrwxr-x. 2 postgres postgres    82 Jan 21 17:05 sql
-rw-rw-r--. 1 postgres postgres  4440 Jan 21 17:05 stringinfo.c
-rw-rw-r--. 1 postgres postgres  3432 Jan 21 17:09 stringinfo.o

三、使用

lmj_test=# select pg_relation_filepath('tab_test');
 pg_relation_filepath
----------------------
 base/16385/16451
(1 row)

lmj_test=# select * from tab_test ;
 id |    name
----+------------
  1 | lmj
  1 | PostgreSQL
(2 rows)

• 将表中数据dump出来查看

[postgres@node1 pg_filedump]$ ./pg_filedump -D int,charN $PGDATA/base/16385/16451

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /soft/pg14/data6000/base/16385/16451
* Options used: -D int,charN
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8120 (0x1fb8)
 LSN:  logid      0 recoff 0x01dbd388      Special  8192 (0x2000)
 Items:    2                      Free Space: 8088
 Checksum: 0x479f  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
COPY: 1 lmj
 Item   2 -- Length:   39  Offset: 8120 (0x1fb8)  Flags: NORMAL
COPY: 1 PostgreSQL


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

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

[postgres@node1 pg_filedump]$ ./pg_filedump -f $PGDATA/base/16385/16451

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /soft/pg14/data6000/base/16385/16451
* Options used: -f
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8120 (0x1fb8)
 LSN:  logid      0 recoff 0x01dbd388      Special  8192 (0x2000)
 Items:    2                      Free Space: 8088
 Checksum: 0x479f  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

  0000: 00000000 88d3db01 9f470000 2000b81f  .........G.. ...
  0010: 00200420 00000000 e09f4000 b89f4e00  . . ......@...N.

<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
  1fe0: fc020000 00000000 00000000 00000000  ................
  1ff0: 01000200 02091800 01000000 096c6d6a  .............lmj

 Item   2 -- Length:   39  Offset: 8120 (0x1fb8)  Flags: NORMAL
  1fb8: fd020000 00000000 00000000 00000000  ................
  1fc8: 02000200 02091800 01000000 17506f73  .............Pos
  1fd8: 74677265 53514c                      tgreSQL



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

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

[postgres@node1 pg_filedump]$ ./pg_filedump -i $PGDATA/base/16385/16451

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /soft/pg14/data6000/base/16385/16451
* Options used: -i
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8120 (0x1fb8)
 LSN:  logid      0 recoff 0x01dbd388      Special  8192 (0x2000)
 Items:    2                      Free Space: 8088
 Checksum: 0x479f  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
  XMIN: 764  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 1   Attributes: 2   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)

 Item   2 -- Length:   39  Offset: 8120 (0x1fb8)  Flags: NORMAL
  XMIN: 765  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 2   Attributes: 2   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)



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