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 ***
References
- git repository
- README
- pg_filedump in the PostgreSQL Wiki
Useful links
- Displaying the contents of a PostgreSQL data file with pg_filedump - blog article by Daniel Westermann
- Physical recovery with pg_filedump - blog article by Alexey Chernyshov
See also
pageinspect, pg_hexdump