PostgreSQL | 数据库无法启动，pg_dumpfile可以帮我们读数据

pg_dumpfile是一种很有用的工具。当数据库不能打开或文件部分被破坏时，它可以帮助我们直接从数据库文件中读取数据，这很有用，尤其是在勒索病毒泛滥的今天。直接删除您系统的基表文件。你没有备份或者灾备环境的话，就只能从底层数据文件中恢复数据。pg_dumpfile当前已经支持PostgreSQL 13.1版本。

测试pg_dumpfile直读文件

下面测试它是否能够读取数据库文件中的数据，首先需要安装pg_dumpfile。

git clone git://git.postgresql.org/git/pg_filedump.git
cd pg_filedump
make
make install

在安装之后，测试工具是否能正常使用。

[postgres@centos8 12711]$ pg_filedump -h

Version 13.1 (for PostgreSQL 8.x .. 13.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2020, PostgreSQL Global Development Group

Usage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file

接下来，我们将创建一个表，其中包含 int, varchar, timestamp, text类型。

postgres=# create table t1 (id int,name varchar,birth timestamp,description text);
CREATE TABLE
postgres=# insert into t1 values(1,'aaa',now(),'我爱中国');
INSERT 0 1
postgres=# insert into t1 values(2,'bbb',now(),'postgresql recover');
INSERT 0 1

postgres=# select * from t1;
 id | name |           birth            |    description     
----+------+----------------------------+--------------------
  1 | aaa  | 2021-02-05 08:28:48.317408 | 我爱中国
  2 | bbb  | 2021-02-05 08:28:52.292864 | postgresql recover
(2 rows)

接下来在pg_class中查找表的relfilenode，然后根据文件号在数据文件夹中查找相应的文件。

postgres=# select relfilenode from pg_class where relname = 't1';
 relfilenode 
-------------
      164112
(1 row)

[postgres@centos8 pgdata]$ find . -name 164106 -print
./base/12711/164112

之后，使用pg_dump_file读取这个文件。在这里使用- D选项，它用一个给定的逗号来分隔字段类型。需求类型要与表中的列一致。这里其实涉及到一个问题，如果发生了异常宕机，无法知道表的relfilenode和列的类型，我们该怎么来挖掘数据呢？

[postgres@centos8 pgdata]$ pg_filedump -D int,varchar,timestamp,text ./base/12711/164112

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: ./base/12711/164112
* Options used: -D int,varchar,timestamp,text
*******************************************************************
Notice: Block size determined from reading block 0 is zero, using default 8192 instead.
Hint: Use -S <size> to specify the size manually.

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower       0 (0x0000)
 Block: Size    0  Version    0            Upper       0 (0x0000)
 LSN:  logid      0 recoff 0x00000000      Special     0 (0x0000)
 Items:    0                      Free Space:    0
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 24

 Error: Invalid header information.

<Data> -----
 Empty block - no items listed 

<Special Section> -----
 Error: Invalid special section encountered.

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

由于插入数据之后，PostgreSQL尚未将数据回写到磁盘上，所以无法显示此数据。在检查点完成之后，才会真正写入磁盘，我们要执行一个checkpoint操作。

postgres=# checkpoint;
CHECKPOINT

[postgres@centos8 pgdata]$ pg_filedump -D int,varchar,timestamp,text ./base/12711/164112

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: ./base/12711/164112
* Options used: -D int,varchar,timestamp,text
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8072 (0x1f88)
 LSN:  logid      6 recoff 0x2452d3c8      Special  8192 (0x2000)
 Items:    2                      Free Space: 8040
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

<Data> -----
 Item   1 -- Length:   53  Offset: 8136 (0x1fc8)  Flags: NORMAL
COPY: 1 aaa     2021-02-05 08:28:48.317408      我爱中国
 Item   2 -- Length:   59  Offset: 8072 (0x1f88)  Flags: NORMAL
COPY: 2 bbb     2021-02-05 08:28:52.292864      postgresql recover


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

做完checkpoint之后，我们可以在< Data>这里看到表中的数据。通过使用grep，我们可以过滤出数据。

[postgres@centos8 pgdata]$ pg_filedump -D int,varchar,timestamp,text ./base/12711/164112 | grep COPY
COPY: 1 aaa     2021-02-05 08:28:48.317408      我爱中国
COPY: 2 bbb     2021-02-05 08:28:52.292864      postgresql recover

上面多出个COPY:可以使用sed替换成空

[postgres@centos8 pgdata]$ pg_filedump -D int,varchar,timestamp,text ./base/12711/164112 | grep COPY  | sed 's/COPY: //g ' 
1       aaa     2021-02-05 08:28:48.317408      我爱中国
2       bbb     2021-02-05 08:28:52.292864      postgresql recover

还可以使用perl来替换。

[postgres@centos8 pgdata]$ pg_filedump -D int,varchar,timestamp,text ./base/12711/164112 | grep COPY  | perl -lne 's/^COPY: //g; print;'
1       aaa     2021-02-05 08:28:48.317408      我爱中国
2       bbb     2021-02-05 08:28:52.292864      postgresql recover

可以把这些数据重定向到txt文件，然后使用 copy进行恢复。

[postgres@centos8 pgdata]$ pg_filedump -D int,varchar,timestamp,text ./base/12711/164112 | grep COPY  | sed 's/COPY: //g ' > /tmp/copy.txt

postgres=# create table t2 (id int,name varchar,birth timestamp,description text);
CREATE TABLE
postgres=# copy t2 from '/tmp/copy.txt';
COPY 2
postgres=# select * from t2;
 id | name |           birth            |    description     
----+------+----------------------------+--------------------
  1 | aaa  | 2021-02-05 08:28:48.317408 | 我爱中国
  2 | bbb  | 2021-02-05 08:28:52.292864 | postgresql recover
(2 rows)

结尾

在遇到损坏的环境，这样的工具还是很有作用的。