联系：手机/微信(+86 17813235971) QQ(107644445)
标题：PostgreSQL恢复系列:pg_filedump基本使用
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
当PostgreSQL遇到重大故障,使用各种方法都无法直接启动数据库，可以考虑使用类似oracle dul工具,直接离线方式读取文件进行恢复.这个工具为pg_filedump
pg_filedump安装
[root@xifenfei ~]# yum install pg_filedump_14.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pg_filedump_14.x86_64 0:14.1-1.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
======================================================================================================================
Package Arch Version Repository Size
======================================================================================================================
Installing:
pg_filedump_14 x86_64 14.1-1.rhel7 pgdg14 43 k
Transaction Summary
======================================================================================================================
Install 1 Package
Total download size: 43 k
Installed size: 81 k
Is this ok [y/d/N]: y
Downloading packages:
pg_filedump_14-14.1-1.rhel7.x86_64.rpm | 43 kB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pg_filedump_14-14.1-1.rhel7.x86_64 1/1
Verifying : pg_filedump_14-14.1-1.rhel7.x86_64 1/1
Installed:
pg_filedump_14.x86_64 0:14.1-1.rhel7
Complete!
-bash-4.2$ pg_filedump
Version 14.1 (for PostgreSQL 8.x .. 14.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2022, 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 numeric oid real serial smallint smallserial text
time timestamp timestamptz 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>
创建测试表
-bash-4.2$ psql psql (14.3) Type "help" for help. postgres=# create table pg_xifenfei(id int,name varchar(100)); CREATE TABLE postgres=# insert into pg_xifenfei values(1,'www.xifenfei.com'); INSERT 0 1 postgres=# insert into pg_xifenfei values(2,'xienfei_pg_recovery'); INSERT 0 1 postgres=# select * from pg_xifenfei; id | name ----+--------------------- 1 | www.xifenfei.com 2 | xienfei_pg_recovery (2 rows) postgres=#
pg_filedump恢复数据
-bash-4.2$ pg_filedump /var/lib/pgsql/14/data/base/14487/16384
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /var/lib/pgsql/14/data/base/14487/16384
* Options used: None
*******************************************************************
Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 32 (0x0020)
Block: Size 8192 Version 4 Upper 8096 (0x1fa0)
LSN: logid 0 recoff 0x16299cf0 Special 8192 (0x2000)
Items: 2 Free Space: 8064
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
Length (including item array): 32
<Data> -----
Item 1 -- Length: 45 Offset: 8144 (0x1fd0) Flags: NORMAL
Item 2 -- Length: 48 Offset: 8096 (0x1fa0) Flags: NORMAL
*** End of File Encountered. Last Block Read: 0 ***
-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
COPY: 1 www.xifenfei.com
COPY: 2 xienfei_pg_recovery
-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
> |awk '{$1=null;print $0}'>/tmp/pg_xifenfei_rec
-bash-4.2$ sed -i 's/^[ ]*//g' /tmp/pg_xifenfei_rec
导入数据验证
postgres=# truncate table pg_xifenfei; TRUNCATE TABLE postgres=# select * from pg_xifenfei; id | name ----+------ (0 rows) postgres=# copy pg_xifenfei from '/tmp/pg_xifenfei_rec'(DELIMITER ' '); COPY 2 postgres=# select * from pg_xifenfei; id | name ----+--------------------- 1 | www.xifenfei.com 2 | xienfei_pg_recovery (2 rows)
通过上述简单测试证明,在PG数据库出现极端情况下，可以使用该方法进行最后的数据恢复,减少因为数据丢失带来的损失.
