PostgreSQL插件——pg_filedump

一、pg_filedump简介

数据库难免会遇到因为某些故障导致数据丢失的情况，此时便需要进行数据恢复。一般情况有备份的话可以直接恢复，但是如果恰好没有备份或者数据文件出现损坏那就比较麻烦了。

在PostgreSQL中如果只是一般的数据文件损坏，我们可以直接使用zero_damaged_pages=on来跳过损坏的数据块来读取数据，然后将数据导到新表中即可。但是如果元数据都损坏了，数据库已经无法启动了呢？这种情况我们便需要通过工具直接从数据文件中读取数据，例如Oracle中的DUL、ODU这类的工具。

在pg中，我们可以使用pg_filedump这个工具来实现类似的功能。

二、安装

1.下载地址：

https://github.com/df7cb/pg_filedump

2.解压

[pg14@node1 pgsoft]$ unzip pg_filedump-master.zip 
Archive:  pg_filedump-master.zip
37e3c1023d583658d351e51a6d3ece54e00089b5
   creating: pg_filedump-master/
  inflating: pg_filedump-master/.gitignore  
  inflating: pg_filedump-master/Makefile  
  inflating: pg_filedump-master/Makefile.contrib  
  inflating: pg_filedump-master/README.pg_filedump  
  inflating: pg_filedump-master/decode.c  
  inflating: pg_filedump-master/decode.h  
   creating: pg_filedump-master/expected/
  inflating: pg_filedump-master/expected/pg_filedump.out  
  inflating: pg_filedump-master/expected/pg_filedump_-Dinttext.out  
  inflating: pg_filedump-master/expected/pg_filedump_-Dinttext_3.out  
  inflating: pg_filedump-master/expected/pg_filedump_3.out  
  inflating: pg_filedump-master/expected/testfile.out  
  inflating: pg_filedump-master/pg_filedump.c  
  inflating: pg_filedump-master/pg_filedump.h  
   creating: pg_filedump-master/sql/
  inflating: pg_filedump-master/sql/pg_filedump.sql  
  inflating: pg_filedump-master/sql/pg_filedump_-Dinttext.sql  
  inflating: pg_filedump-master/sql/testfile.sql  
  inflating: pg_filedump-master/stringinfo.c  

3.编译安装

[pg14@node1 pgsoft]$ cd pg_filedump-master/
[pg14@node1 pg_filedump-master]$ ls
decode.c  expected  Makefile.contrib  pg_filedump.h       sql
decode.h  Makefile  pg_filedump.c     README.pg_filedump  stringinfo.c
[pg14@node1 pg_filedump-master]$ make && make install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g -I. -I./ -I/pgsoft/pg14/include/postgresql/server -I/pgsoft/pg14/include/postgresql/internal  -D_GNU_SOURCE   -c -o pg_filedump.o pg_filedump.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g -I. -I./ -I/pgsoft/pg14/include/postgresql/server -I/pgsoft/pg14/include/postgresql/internal  -D_GNU_SOURCE   -c -o decode.o decode.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g -I. -I./ -I/pgsoft/pg14/include/postgresql/server -I/pgsoft/pg14/include/postgresql/internal  -D_GNU_SOURCE   -c -o stringinfo.o stringinfo.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g pg_filedump.o decode.o stringinfo.o  -L/pgsoft/pg14/lib   -Wl,--as-needed -Wl,-rpath,'/pgsoft/pg14/lib',--enable-new-dtags   -L/pgsoft/pg14/lib -lpgcommon -lpgport -L/pgsoft/pg14/lib -lpq -o pg_filedump
/bin/mkdir -p '/pgsoft/pg14/bin'
/bin/install -c  pg_filedump '/pgsoft/pg14/bin'

4.使用概览

[pg14@node1 pg_filedump-master]$ pg_filedump 

Version 14.0 (for PostgreSQL 8.x .. 14.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2021, PostgreSQL Global Development Group

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 oid real serial smallint smallserial text
        time timestamp 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>
[pg14@node1 pg_filedump-master]$ 

三、使用案例

1.创建测试数据

test2=# create table t1(id int,name varchar(10));
CREATE TABLE
test2=# insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc');
INSERT 0 3
test2=# select * from t1;
 id | name 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

2.查看关系对应路径

test2=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/16385/16384
(1 row)

确保数据刷盘
test2=# checkpoint;
CHECKPOINT

3.file_dump读取数据

[pg14@node1 16385]$ pg_filedump /pgdata02/base/16385/16384

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /pgdata02/base/16385/16384
* Options used: None
*******************************************************************

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

<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
 Item   2 -- Length:   32  Offset: 8128 (0x1fc0)  Flags: NORMAL
 Item   3 -- Length:   32  Offset: 8096 (0x1fa0)  Flags: NORMAL


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

使用-D选项将其转换成可以直观读取的格式
[pg14@node1 16385]$ pg_filedump -D int,varchar /pgdata02/base/16385/16384

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /pgdata02/base/16385/16384
* Options used: -D int,varchar
*******************************************************************

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

<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
COPY: 1 aaa
 Item   2 -- Length:   32  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 2 bbb
 Item   3 -- Length:   32  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 3 ccc


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

4.查看数据文件中的dead元组

test2=# update t1 set name='AAA' where id=1;
UPDATE 1
test2=# select * from t1;
 id | name 
----+------
  2 | bbb
  3 | ccc
  1 | AAA
(3 rows)

[pg14@node1 16385]$ pg_filedump -D int,varchar -i  /pgdata02/base/16385/16384

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /pgdata02/base/16385/16384
* Options used: -D int,varchar -i
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      40 (0x0028)
 Block: Size 8192  Version    4            Upper    8064 (0x1f80)
 LSN:  logid      0 recoff 0x0301cc88      Special  8192 (0x2000)
 Items:    4                      Free Space: 8024
 Checksum: 0x0000  Prune XID: 0x00100005  Flags: 0x0000 ()
 Length (including item array): 40

<Data> -----
 Item   1 -- Length:   32  Offset: 8160 (0x1fe0)  Flags: NORMAL
  XMIN: 1048580  XMAX: 1048581  CID|XVAC: 0
  Block Id: 0  linp Index: 4   Attributes: 2   Size: 24
  infomask: 0x0502 (HASVARWIDTH|XMIN_COMMITTED|XMAX_COMMITTED|HOT_UPDATED) 

COPY: 1 aaa
 Item   2 -- Length:   32  Offset: 8128 (0x1fc0)  Flags: NORMAL
  XMIN: 1048580  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 2   Attributes: 2   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 

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

COPY: 3 ccc
 Item   4 -- Length:   32  Offset: 8064 (0x1f80)  Flags: NORMAL
  XMIN: 1048581  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 4   Attributes: 2   Size: 24
  infomask: 0x2902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID|UPDATED|HEAP_ONLY) 

COPY: 1 AAA


*** End of File Encountered. Last Block Read: 0 ***
[pg14@node1 16385]$ 

5.pg_filedump恢复数据

test2=# insert into t1 values(1,'aaa'),(2,'bbb'),(3,'ccc');
INSERT 0 3
test2=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/16385/16387
(1 row)

test2=# checkpoint;
CHECKPOINT

pg_filedump -D int,varchar /pgdata02/base/16385/16387| grep COPY > /home/pg14/pg_rec
[pg14@node1 ~]$  awk -F ' ' 'OFS=","{print $2,$3}'  /home/pg14/pg_rec >/home/pg14/pg_rec1
[pg14@node1 ~]$ more pg_rec1
1,aaa
2,bbb
3,ccc


test2=#  truncate table t1;
TRUNCATE TABLE
test2=# select * from t1;
 id | name 
----+------
(0 rows)
test2=# copy t1 from '/home/pg14/pg_rec1'(DELIMITER ',');
COPY 3
test2=# select * from t1;
 id | name 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

参考：https://blog.csdn.net/jycjyc/article/details/125145027