Did you ever wonder what exactly is in a PostgreSQL data file? Usually you don’t care, I agree. But there might be situations where knowing how you can do this might be a great help. Maybe your file is corrupted and you want to recover as much data as possible? Maybe you just want to do some research. There is a utility called pg_filedump which makes this pretty easy. Lets go …

Before you try to install pg_filedump you’ll need to make sure that all the header files are there in your PostgreSQL installation. Once you have that the installation is as simple as:

postgres@pgbox:/home/postgres/ [PG10] tar -axf pg_filedump-REL_10_0-c0e4028.tar.gz 
postgres@pgbox:/home/postgres/ [PG10] cd pg_filedump-REL_10_0-c0e4028
postgres@pgbox:/home/postgres/pg_filedump-REL_10_0-c0e4028/ [PG10] make
postgres@pgbox:/home/postgres/pg_filedump-REL_10_0-c0e4028/ [PG10] make install

If everything went fine the utility should be there:

postgres@pgbox:/u02/pgdata/PG10/ [PG10] pg_filedump -h

Version 10.0 (for PostgreSQL 10.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2017, 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
  -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]
  -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]

Report bugs to 

As we want to dump a file we obviously need a table with some data, so:

postgres=# create table t1 ( a int, b varchar(50));
CREATE TABLE
postgres=# insert into t1 (a,b) select a, md5(a::varchar) from generate_series(1,10) a;
INSERT 0 10

Get the name of the file:

postgres=# select * from pg_relation_filenode('t1');
 pg_relation_filenode 
----------------------
                24702
(1 row)

Look it up in PGDATA:

postgres@pgbox:/home/postgres/ [PG10] cd $PGDATA
postgres@pgbox:/u02/pgdata/PG10/ [PG10] find . -name 24702
./base/13212/24702

… and dump it:

postgres@pgbox:/u02/pgdata/PG10/ [PG10] pg_filedump ./base/13212/24702

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.0
*
* File: ./base/13212/24702
* Options used: None
*
* Dump created on: Wed Nov  8 10:39:33 2017
*******************************************************************
Error: Unable to read full page header from block 0.
  ===> Read 0 bytes

Hm, nothing in there. Why? The reasons is easy: The data is there in PostgreSQL but it is only WAL logged at the moment and not yet in the datafile as no checkpoint happened (in this case):

postgres=#  checkpoint;
CHECKPOINT
Time: 100.567 ms

Do it again:

postgres@pgbox:/u02/pgdata/PG10/ [PG10] pg_filedump ./base/13212/24702

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.0
*
* File: ./base/13212/24702
* Options used: None
*
* Dump created on: Wed Nov  8 10:40:45 2017
*******************************************************************

Block    0 ********************************************************
----- Block Offset: 0x00000000 Offsets: Lower 64 (0x0040) Block: Size 8192 Version 4 Upper 7552 (0x1d80) LSN: logid 0 recoff 0x478b2c48 Special 8192 (0x2000) Items: 10 Free Space: 7488 Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () Length (including item array): 64 ------ Item 1 -- Length: 61 Offset: 8128 (0x1fc0) Flags: NORMAL Item 2 -- Length: 61 Offset: 8064 (0x1f80) Flags: NORMAL Item 3 -- Length: 61 Offset: 8000 (0x1f40) Flags: NORMAL Item 4 -- Length: 61 Offset: 7936 (0x1f00) Flags: NORMAL Item 5 -- Length: 61 Offset: 7872 (0x1ec0) Flags: NORMAL Item 6 -- Length: 61 Offset: 7808 (0x1e80) Flags: NORMAL Item 7 -- Length: 61 Offset: 7744 (0x1e40) Flags: NORMAL Item 8 -- Length: 61 Offset: 7680 (0x1e00) Flags: NORMAL Item 9 -- Length: 61 Offset: 7616 (0x1dc0) Flags: NORMAL Item 10 -- Length: 61 Offset: 7552 (0x1d80) Flags: NORMAL *** End of File Encountered. Last Block Read: 0 ***

Here we go. What can we learn from that output. This is not really human readable but at least we see that there are ten rows. We can also list the actual contents of the rows:

postgres@pgbox:/u02/pgdata/PG10/ [PG10] pg_filedump -f ./base/13212/24702

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.0
*
* File: ./base/13212/24702
* Options used: -f 
*
* Dump created on: Wed Nov  8 10:41:21 2017
*******************************************************************

Block    0 ********************************************************
----- Block Offset: 0x00000000 Offsets: Lower 64 (0x0040) Block: Size 8192 Version 4 Upper 7552 (0x1d80) LSN: logid 0 recoff 0x478b2c48 Special 8192 (0x2000) Items: 10 Free Space: 7488 Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () Length (including item array): 64 0000: 00000000 482c8b47 00000000 4000801d ....H,.G....@... 0010: 00200420 00000000 c09f7a00 809f7a00 . . ......z...z. 0020: 409f7a00 009f7a00 c09e7a00 809e7a00 @.z...z...z...z. 0030: 409e7a00 009e7a00 c09d7a00 809d7a00 @.z...z...z...z. ------ Item 1 -- Length: 61 Offset: 8128 (0x1fc0) Flags: NORMAL 1fc0: 96020000 00000000 00000000 00000000 ................ 1fd0: 01000200 02081800 01000000 43633463 ............Cc4c 1fe0: 61343233 38613062 39323338 32306463 a4238a0b923820dc 1ff0: 63353039 61366637 35383439 62 c509a6f75849b Item 2 -- Length: 61 Offset: 8064 (0x1f80) Flags: NORMAL 1f80: 96020000 00000000 00000000 00000000 ................ 1f90: 02000200 02081800 02000000 43633831 ............Cc81 1fa0: 65373238 64396434 63326636 33366630 e728d9d4c2f636f0 1fb0: 36376638 39636331 34383632 63 67f89cc14862c Item 3 -- Length: 61 Offset: 8000 (0x1f40) Flags: NORMAL 1f40: 96020000 00000000 00000000 00000000 ................ 1f50: 03000200 02081800 03000000 43656363 ............Cecc 1f60: 62633837 65346235 63653266 65323833 bc87e4b5ce2fe283 1f70: 30386664 39663261 37626166 33 08fd9f2a7baf3 Item 4 -- Length: 61 Offset: 7936 (0x1f00) Flags: NORMAL 1f00: 96020000 00000000 00000000 00000000 ................ 1f10: 04000200 02081800 04000000 43613837 ............Ca87 1f20: 66663637 39613266 33653731 64393138 ff679a2f3e71d918 1f30: 31613637 62373534 32313232 63 1a67b7542122c Item 5 -- Length: 61 Offset: 7872 (0x1ec0) Flags: NORMAL 1ec0: 96020000 00000000 00000000 00000000 ................ 1ed0: 05000200 02081800 05000000 43653464 ............Ce4d 1ee0: 61336237 66626263 65323334 35643737 a3b7fbbce2345d77 1ef0: 37326230 36373461 33313864 35 72b0674a318d5 Item 6 -- Length: 61 Offset: 7808 (0x1e80) Flags: NORMAL 1e80: 96020000 00000000 00000000 00000000 ................ 1e90: 06000200 02081800 06000000 43313637 ............C167 1ea0: 39303931 63356138 38306661 66366662 9091c5a880faf6fb 1eb0: 35653630 38376562 31623264 63 5e6087eb1b2dc Item 7 -- Length: 61 Offset: 7744 (0x1e40) Flags: NORMAL 1e40: 96020000 00000000 00000000 00000000 ................ 1e50: 07000200 02081800 07000000 43386631 ............C8f1 1e60: 34653435 66636565 61313637 61356133 4e45fceea167a5a3 1e70: 36646564 64346265 61323534 33 6dedd4bea2543 Item 8 -- Length: 61 Offset: 7680 (0x1e00) Flags: NORMAL 1e00: 96020000 00000000 00000000 00000000 ................ 1e10: 08000200 02081800 08000000 43633966 ............Cc9f 1e20: 30663839 35666239 38616239 31353966 0f895fb98ab9159f 1e30: 35316664 30323937 65323336 64 51fd0297e236d Item 9 -- Length: 61 Offset: 7616 (0x1dc0) Flags: NORMAL 1dc0: 96020000 00000000 00000000 00000000 ................ 1dd0: 09000200 02081800 09000000 43343563 ............C45c 1de0: 34386363 65326532 64376662 64656131 48cce2e2d7fbdea1 1df0: 61666335 31633763 36616432 36 afc51c7c6ad26 Item 10 -- Length: 61 Offset: 7552 (0x1d80) Flags: NORMAL 1d80: 96020000 00000000 00000000 00000000 ................ 1d90: 0a000200 02081800 0a000000 43643364 ............Cd3d 1da0: 39343436 38303261 34343235 39373535 9446802a44259755 1db0: 64333865 36643136 33653832 30 d38e6d163e820 *** End of File Encountered. Last Block Read: 0 ***

But this does not help much either. When you want to see the contents in human readable format use the “-D” switch and provide the list of data types you want to decode:

postgres@pgbox:/u02/pgdata/PG10/ [PG10] pg_filedump -D int,varchar ./base/13212/24702

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.0
*
* File: ./base/13212/24702
* Options used: -D int,varchar 
*
* Dump created on: Wed Nov  8 10:42:58 2017
*******************************************************************

Block    0 ********************************************************
----- Block Offset: 0x00000000 Offsets: Lower 64 (0x0040) Block: Size 8192 Version 4 Upper 7552 (0x1d80) LSN: logid 0 recoff 0x478b2c48 Special 8192 (0x2000) Items: 10 Free Space: 7488 Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () Length (including item array): 64 ------ Item 1 -- Length: 61 Offset: 8128 (0x1fc0) Flags: NORMAL COPY: 1 c4ca4238a0b923820dcc509a6f75849b Item 2 -- Length: 61 Offset: 8064 (0x1f80) Flags: NORMAL COPY: 2 c81e728d9d4c2f636f067f89cc14862c Item 3 -- Length: 61 Offset: 8000 (0x1f40) Flags: NORMAL COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3 Item 4 -- Length: 61 Offset: 7936 (0x1f00) Flags: NORMAL COPY: 4 a87ff679a2f3e71d9181a67b7542122c Item 5 -- Length: 61 Offset: 7872 (0x1ec0) Flags: NORMAL COPY: 5 e4da3b7fbbce2345d7772b0674a318d5 Item 6 -- Length: 61 Offset: 7808 (0x1e80) Flags: NORMAL COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc Item 7 -- Length: 61 Offset: 7744 (0x1e40) Flags: NORMAL COPY: 7 8f14e45fceea167a5a36dedd4bea2543 Item 8 -- Length: 61 Offset: 7680 (0x1e00) Flags: NORMAL COPY: 8 c9f0f895fb98ab9159f51fd0297e236d Item 9 -- Length: 61 Offset: 7616 (0x1dc0) Flags: NORMAL COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26 Item 10 -- Length: 61 Offset: 7552 (0x1d80) Flags: NORMAL COPY: 10 d3d9446802a44259755d38e6d163e820

And now we can see it. This is the same data as if you’d do a select on the table:

postgres=# select * from  t1;
 a  |                b                 
----+----------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b
  2 | c81e728d9d4c2f636f067f89cc14862c
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
  4 | a87ff679a2f3e71d9181a67b7542122c
  5 | e4da3b7fbbce2345d7772b0674a318d5
  6 | 1679091c5a880faf6fb5e6087eb1b2dc
  7 | 8f14e45fceea167a5a36dedd4bea2543
  8 | c9f0f895fb98ab9159f51fd0297e236d
  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
 10 | d3d9446802a44259755d38e6d163e820
(10 rows)

What happens when we do an update?:

postgres=# update t1 set b = 'a' where a = 4;
UPDATE 1
postgres=# checkpoint ;
CHECKPOINT

How does it look like in the file?

postgres@pgbox:/u02/pgdata/PG10/ [PG10] pg_filedump -D int,varchar ./base/13212/24702

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.0
*
* File: ./base/13212/24702
* Options used: -D int,varchar 
*
* Dump created on: Wed Nov  8 11:12:35 2017
*******************************************************************

Block    0 ********************************************************
----- Block Offset: 0x00000000 Offsets: Lower 68 (0x0044) Block: Size 8192 Version 4 Upper 7520 (0x1d60) LSN: logid 0 recoff 0x478c2998 Special 8192 (0x2000) Items: 11 Free Space: 7452 Checksum: 0x0000 Prune XID: 0x00000298 Flags: 0x0000 () Length (including item array): 68 ------ Item 1 -- Length: 61 Offset: 8128 (0x1fc0) Flags: NORMAL COPY: 1 c4ca4238a0b923820dcc509a6f75849b Item 2 -- Length: 61 Offset: 8064 (0x1f80) Flags: NORMAL COPY: 2 c81e728d9d4c2f636f067f89cc14862c Item 3 -- Length: 61 Offset: 8000 (0x1f40) Flags: NORMAL COPY: 3 eccbc87e4b5ce2fe28308fd9f2a7baf3 Item 4 -- Length: 61 Offset: 7936 (0x1f00) Flags: NORMAL COPY: 4 a87ff679a2f3e71d9181a67b7542122c Item 5 -- Length: 61 Offset: 7872 (0x1ec0) Flags: NORMAL COPY: 5 e4da3b7fbbce2345d7772b0674a318d5 Item 6 -- Length: 61 Offset: 7808 (0x1e80) Flags: NORMAL COPY: 6 1679091c5a880faf6fb5e6087eb1b2dc Item 7 -- Length: 61 Offset: 7744 (0x1e40) Flags: NORMAL COPY: 7 8f14e45fceea167a5a36dedd4bea2543 Item 8 -- Length: 61 Offset: 7680 (0x1e00) Flags: NORMAL COPY: 8 c9f0f895fb98ab9159f51fd0297e236d Item 9 -- Length: 61 Offset: 7616 (0x1dc0) Flags: NORMAL COPY: 9 45c48cce2e2d7fbdea1afc51c7c6ad26 Item 10 -- Length: 61 Offset: 7552 (0x1d80) Flags: NORMAL COPY: 10 d3d9446802a44259755d38e6d163e820 Item 11 -- Length: 30 Offset: 7520 (0x1d60) Flags: NORMAL COPY: 4 a *** End of File Encountered. Last Block Read: 0 ***

The a=4 row is still there but we got a new one (Item 11) which is our update. Remember that it is the job of vacuum to recycle the dead/old rows:

postgres=# vacuum t1;
VACUUM
postgres=# checkpoint ;
CHECKPOINT

Again (just displaying the data here):

 ------ 
 Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 1	c4ca4238a0b923820dcc509a6f75849b
 Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 2	c81e728d9d4c2f636f067f89cc14862c
 Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 3	eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:    0  Offset:   11 (0x000b)  Flags: REDIRECT
 Item   5 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 5	e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 6	1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 7	8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 8	c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 9	45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 10	d3d9446802a44259755d38e6d163e820
 Item  11 -- Length:   30  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 4	a

… and “Item 4” is gone (somewhere else). The same happens when you delete data:

postgres=# delete from t1 where a = 4;
DELETE 1
postgres=# vacuum t1;
VACUUM
postgres=# checkpoint;
CHECKPOINT

You’ll notice that both, Items 4 and 11, are now gone (UNUSED):

 ------ 
 Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL
COPY: 1	c4ca4238a0b923820dcc509a6f75849b
 Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL
COPY: 2	c81e728d9d4c2f636f067f89cc14862c
 Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 3	eccbc87e4b5ce2fe28308fd9f2a7baf3
 Item   4 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED
 Item   5 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL
COPY: 5	e4da3b7fbbce2345d7772b0674a318d5
 Item   6 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL
COPY: 6	1679091c5a880faf6fb5e6087eb1b2dc
 Item   7 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL
COPY: 7	8f14e45fceea167a5a36dedd4bea2543
 Item   8 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL
COPY: 8	c9f0f895fb98ab9159f51fd0297e236d
 Item   9 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL
COPY: 9	45c48cce2e2d7fbdea1afc51c7c6ad26
 Item  10 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 10	d3d9446802a44259755d38e6d163e820
 Item  11 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED

So far for the introduction of pg_filedump, more to come in more detail.