pg_filedump

Utility to display the formatted contents of a PostgreSQL heap, index, or control file

pg_filedump is a utility to display the formatted contents of a PostgreSQL heap, index, or control file.

pg_filedump is available for all PostgreSQL versions from PostgreSQL 9.0 onwards. Note that while it is provided by the PostgreSQL project, it is not a core utility and needs to be compiled or installed separately.

Release history

Examples

Create a table with some arbitrary data:

CREATE TABLE foo (id INT, val TEXT);
INSERT INTO foo VALUES(generate_series(1,5), md5(clock_timestamp()::TEXT));

CHECKPOINT; -- ensure contents are written from shared_buffers to disk

Determine the location of the table's datafile (see filenode for further information on this):

SELECT pg_relation_filepath('foo'::regclass);

Dump the contents in human-readble format with the -D option (note: you'll need to know the column types and order for this):

postgres $ pg_filedump -D int,text ./base/16385/32784

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: ./base/16385/32784
* Options used: -D int,text
*
* Dump created on: Fri Dec  7 18:47:46 2018
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      44 (0x002c)
 Block: Size 8192  Version    4            Upper    7872 (0x1ec0)
 LSN:  logid      0 recoff 0x018dacb0      Special  8192 (0x2000)
 Items:    5                      Free Space: 7828
 Checksum: 0xd874  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 44

<Data> ------
 Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 1	eb4f2363f1f90f13c31d18ec9ecb683e
 Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 2	1cdce4984a6002b1ae1bafffc1eb31db
 Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 3	8764bdccfbd32aaf0ae3ffb077f48d56
 Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 4	fb37467829dc3f7a4124d6ea1f0fb77b
 Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 5	7398f49dc8c7027f73d75a6081b415bc


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

Categories

Data consistency and forensics, PostgreSQL internals, Storage, Utility program

See also

pageinspect, pg_hexdump