pg_filedump

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

使用介绍

  • 创建一张表插入几条记录
  1. postgres=# create table dhytest (id int , name char(20));
  2. CREATE TABLE
  3. postgres=# insert into dhytest values (10, '董红禹');
  4. INSERT 0 1
  5. postgres=# insert into dhytest values (20, 'PostgreSQL');
  6. INSERT 0 1
  7. postgres=# insert into dhytest values (30, 'MySQL');
  8. INSERT 0 1
  • 查找表对应的存储位置
  1. postgres=# select * from pg_relation_filepath('dhytest');
  2. pg_relation_filepath
  3. ----------------------
  4. base/12558/189038
  5. (1 row)
  • 将表中数据dump出来查看
  1. ./pg_filedump -D int,charN /pg_data/base/12558/189038
  2. *******************************************************************
  3. * PostgreSQL File/Block Formatted Dump Utility - Version 10.1
  4. *
  5. * File: /pg_data/base/12558/189038
  6. * Options used: -D int,charN
  7. *
  8. * Dump created on: Fri Oct 26 13:35:45 2018
  9. *******************************************************************
  10. Block 0 ********************************************************
  11. <Header> -----
  12. Block Offset: 0x00000000 Offsets: Lower 36 (0x0024)
  13. Block: Size 8192 Version 4 Upper 8024 (0x1f58)
  14. LSN: logid 1 recoff 0xde3679f8 Special 8192 (0x2000)
  15. Items: 3 Free Space: 7988
  16. Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
  17. Length (including item array): 36
  18. <Data> ------
  19. Item 1 -- Length: 55 Offset: 8136 (0x1fc8) Flags: NORMAL
  20. COPY: 10 董红禹
  21. Item 2 -- Length: 49 Offset: 8080 (0x1f90) Flags: NORMAL
  22. COPY: 20 PostgreSQL
  23. Item 3 -- Length: 49 Offset: 8024 (0x1f58) Flags: NORMAL
  24. COPY: 30 MySQL
  • 显示格式化的内容,可以看到每个字节对应的内容是什么
  1. ./pg_filedump -f /pg_data/base/12558/189038
  2. *******************************************************************
  3. * PostgreSQL File/Block Formatted Dump Utility - Version 10.1
  4. *
  5. * File: /pg_data/base/12558/189038
  6. * Options used: -f
  7. *
  8. * Dump created on: Fri Oct 26 13:39:20 2018
  9. *******************************************************************
  10. Block 0 ********************************************************
  11. <Header> -----
  12. Block Offset: 0x00000000 Offsets: Lower 36 (0x0024)
  13. Block: Size 8192 Version 4 Upper 8024 (0x1f58)
  14. LSN: logid 1 recoff 0xde3679f8 Special 8192 (0x2000)
  15. Items: 3 Free Space: 7988
  16. Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
  17. Length (including item array): 36
  18. 0000: 01000000 f87936de 00000000 2400581f .....y6.....$.X.
  19. 0010: 00200420 00000000 c89f6e00 909f6200 . . ......n...b.
  20. 0020: 589f6200 X.b.
  21. <Data> ------
  22. Item 1 -- Length: 55 Offset: 8136 (0x1fc8) Flags: NORMAL
  23. 1fc8: 66040000 00000000 00000000 00000000 f...............
  24. 1fd8: 01000200 02081800 0a000000 37e891a3 ............7...
  25. 1fe8: e7baa2e7 a6b92020 20202020 20202020 ......
  26. 1ff8: 20202020 202020
  27. Item 2 -- Length: 49 Offset: 8080 (0x1f90) Flags: NORMAL
  28. 1f90: 67040000 00000000 00000000 00000000 g...............
  29. 1fa0: 02000200 02081800 14000000 2b506f73 ............+Pos
  30. 1fb0: 74677265 53514c20 20202020 20202020 tgreSQL
  31. 1fc0: 20
  32. Item 3 -- Length: 49 Offset: 8024 (0x1f58) Flags: NORMAL
  33. 1f58: 68040000 00000000 00000000 00000000 h...............
  34. 1f68: 03000200 02081800 1e000000 2b4d7953 ............+MyS
  35. 1f78: 514c2020 20202020 20202020 20202020 QL
  36. 1f88: 20
  • 将数据块上一些细节内容打印出来
  1. ./pg_filedump -i /pg_data/base/12558/189038
  2. *******************************************************************
  3. * PostgreSQL File/Block Formatted Dump Utility - Version 10.1
  4. *
  5. * File: /pg_data/base/12558/189038
  6. * Options used: -i
  7. *
  8. * Dump created on: Fri Oct 26 13:41:27 2018
  9. *******************************************************************
  10. Block 0 ********************************************************
  11. <Header> -----
  12. Block Offset: 0x00000000 Offsets: Lower 36 (0x0024)
  13. Block: Size 8192 Version 4 Upper 8024 (0x1f58)
  14. LSN: logid 1 recoff 0xde3679f8 Special 8192 (0x2000)
  15. Items: 3 Free Space: 7988
  16. Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
  17. Length (including item array): 36
  18. <Data> ------
  19. Item 1 -- Length: 55 Offset: 8136 (0x1fc8) Flags: NORMAL
  20. XMIN: 1126 XMAX: 0 CID|XVAC: 0
  21. Block Id: 0 linp Index: 1 Attributes: 2 Size: 24
  22. infomask: 0x0802 (HASVARWIDTH|XMAX_INVALID)
  23. Item 2 -- Length: 49 Offset: 8080 (0x1f90) Flags: NORMAL
  24. XMIN: 1127 XMAX: 0 CID|XVAC: 0
  25. Block Id: 0 linp Index: 2 Attributes: 2 Size: 24
  26. infomask: 0x0802 (HASVARWIDTH|XMAX_INVALID)
  27. Item 3 -- Length: 49 Offset: 8024 (0x1f58) Flags: NORMAL
  28. XMIN: 1128 XMAX: 0 CID|XVAC: 0
  29. Block Id: 0 linp Index: 3 Attributes: 2 Size: 24
  30. infomask: 0x0802 (HASVARWIDTH|XMAX_INVALID)
0 评论  
添加一条新评论