pg_filedump工具的使用

duqk1982

已于 2023-01-07 17:55:02 修改

阅读量504

点赞数

分类专栏： PostgreSQL 文章标签：数据库 postgresql

于 2023-01-07 17:53:23 首次发布

本文链接：https://blog.csdn.net/dqk1982/article/details/128593388

版权

PostgreSQL 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

pg_filedump是一个用于PostgreSQL的数据文件、索引文件和控制文件格式化输出的工具，帮助理解内部存储格式。当数据库损坏时，此工具可用于数据恢复。文章展示了如何安装、使用pg_filedump以及如何通过它来导入和导出数据，强调了其在数据恢复和理解数据库结构中的作用。

摘要由CSDN通过智能技术生成

pg_filedump简介

pg_filedump 工具可以针对数据文件、索引文件、控制文件进行dump格式化输出，方便我们学习查看文件中格式及内容还有数据块上一些细节内容。如果你想更加了解PG内部存储格式这个工具是你很好的选择。
如果postgresql数据库彻底损坏无法启动，也可以通过pg_filedump尽可能的从文件中抽取数据进行恢复。

安装

下载地址：https://github.com/df7cb/pg_filedump

用root用户安装：
source /home/postgres/.bash_profile
unzip pg_filedump-REL_14_1.zip
cd pg_filedump-REL_14_1
make
make install

基本使用

创建测试数据：

[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# create table t(id int,name varchar(20));
CREATE TABLE
postgres=# insert into t values(1,'a'),(2,'b'),(3,'c');
INSERT 0 3
postgres=# select * from t;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13892/16445
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# \q
[postgres@duqk04 ~]$

将表中数据dump出来查看

[postgres@duqk04 ~]$ pg_filedump -D int,charN $PGDATA/base/13892/16445

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /data/pgdata/base/13892/16445
* Options used: -D int,charN
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      36 (0x0024)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid      0 recoff 0x0aa02470      Special  8192 (0x2000)
 Items:    3                      Free Space: 8060
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 36

<Data> -----
 Item   1 -- Length:   30  Offset: 8160 (0x1fe0)  Flags: NORMAL
COPY: 1 a
 Item   2 -- Length:   30  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 2 b
 Item   3 -- Length:   30  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 3 c


*** End of File Encountered. Last Block Read: 0 ***
[postgres@duqk04 ~]$

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

[postgres@duqk04 ~]$ pg_filedump -f $PGDATA/base/13892/16445

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /data/pgdata/base/13892/16445
* Options used: -f
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      36 (0x0024)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid      0 recoff 0x0aa02470      Special  8192 (0x2000)
 Items:    3                      Free Space: 8060
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 36

  0000: 00000000 7024a00a 00000000 2400a01f  ....p$......$...
  0010: 00200420 00000000 e09f3c00 c09f3c00  . . ......<...<.
  0020: a09f3c00                             ..<.            

<Data> -----
 Item   1 -- Length:   30  Offset: 8160 (0x1fe0)  Flags: NORMAL
  1fe0: f9020000 00000000 00000000 00000000  ................
  1ff0: 01000200 02091800 01000000 0561      .............a  

 Item   2 -- Length:   30  Offset: 8128 (0x1fc0)  Flags: NORMAL
  1fc0: f9020000 00000000 00000000 00000000  ................
  1fd0: 02000200 02091800 02000000 0562      .............b  

 Item   3 -- Length:   30  Offset: 8096 (0x1fa0)  Flags: NORMAL
  1fa0: f9020000 00000000 00000000 00000000  ................
  1fb0: 03000200 02091800 03000000 0563      .............c  


*** End of File Encountered. Last Block Read: 0 ***
[postgres@duqk04 ~]$

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

[postgres@duqk04 ~]$ pg_filedump -i $PGDATA/base/13892/16445

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /data/pgdata/base/13892/16445
* Options used: -i
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      36 (0x0024)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid      0 recoff 0x0aa02470      Special  8192 (0x2000)
 Items:    3                      Free Space: 8060
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 36

<Data> -----
 Item   1 -- Length:   30  Offset: 8160 (0x1fe0)  Flags: NORMAL
  XMIN: 761  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:   30  Offset: 8128 (0x1fc0)  Flags: NORMAL
  XMIN: 761  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 2   Attributes: 2   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 

 Item   3 -- Length:   30  Offset: 8096 (0x1fa0)  Flags: NORMAL
  XMIN: 761  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 3   Attributes: 2   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 


*** End of File Encountered. Last Block Read: 0 ***
[postgres@duqk04 ~]$

数据恢复

利用pg_filedump生成数据文件

[postgres@duqk04 ~]$ pg_filedump -D int,charn $PGDATA/base/13892/16445|grep COPY |awk '{$1=null;print $0}'|sed 's/^[ ]*//g'> /home/postgres/t.txt
[postgres@duqk04 ~]$ cat /home/postgres/t.txt
1 a
2 b
3 c
[postgres@duqk04 ~]$

导入数据

[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# truncate table t;
TRUNCATE TABLE
postgres=# copy t from '/home/postgres/t.txt' with DELIMITER ' ';
COPY 3
postgres=# select * from t;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

postgres=# checkpoint;
CHECKPOINT
postgres=# delete from t where id=3;
DELETE 1
postgres=# select * from t;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

postgres=# checkpoint;                      
CHECKPOINT
postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13892/16448
(1 row)

postgres=# \q
[postgres@duqk04 ~]$ pg_filedump -D int,charn $PGDATA/base/13892/****16448****|grep COPY |awk '{$1=null;print $0}'|sed 's/^[ ]*//g'> /home/postgres/dt.txt 
[postgres@duqk04 ~]$ cat /home/postgres/dt.txt 
1 a
2 b
3 c
[postgres@duqk04 ~]$ 


[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# select * from t;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

postgres=# insert into t values(3,'cc');
INSERT 0 1
postgres=# insert into t values(4,'d');
INSERT 0 1
postgres=# update t set name='upadate' where id=1;
UPDATE 1
postgres=# select * from t;
 id |  name   
----+---------
  2 | b
  3 | cc
  4 | d
  1 | upadate
(4 rows)

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13892/16448
(1 row)

postgres=# \q
[postgres@duqk04 ~]$ 

[postgres@duqk04 ~]$ pg_filedump -D int,charn $PGDATA/base/13892/16448|grep COPY |awk '{$1=null;print $0}'|sed 's/^[ ]*//g'> /home/postgres/tt.txt
[postgres@duqk04 ~]$ cat /home/postgres/tt.txt
1 a
2 b
3 c
3 cc
4 d
1 upadate
[postgres@duqk04 ~]$ 
---如果有重复唯一值，则最后面的为当前可见数据。

对表执行vacuum和vacuum full 后对比

[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.
 
postgres=# vacuum t; 
VACUUM
postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13892/16448
(1 row)

postgres=# \q
[postgres@duqk04 ~]$ 
[postgres@duqk04 ~]$ pg_filedump -D int,charn $PGDATA/base/13892/16448|grep COPY |awk '{$1=null;print $0}'|sed 's/^[ ]*//g'> /home/postgres/vt.txt                                  
[postgres@duqk04 ~]$ cat /home/postgres/vt.txt
1 a
2 b
3 c
3 cc
4 d
1 upadate
[postgres@duqk04 ~]$
--vacuum并没有回收文件的中dead tuple。

[postgres@duqk04 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# vacuum full t;
VACUUM
postgres=# select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13892/16449
(1 row)

postgres=# \q
[postgres@duqk04 ~]$
[postgres@duqk04 ~]$ pg_filedump -D int,charn $PGDATA/base/13892/16449|grep COPY |awk '{$1=null;print $0}'|sed 's/^[ ]*//g'> /home/postgres/vft.txt
[postgres@duqk04 ~]$ cat /home/postgres/vft.txt
2 b
3 cc
4 d
1 upadate
[postgres@duqk04 ~]$