一、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