标签归档：pg恢复

当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数据库出现极端情况下，可以使用该方法进行最后的数据恢复,减少因为数据丢失带来的损失.