{"id":10631,"date":"2017-11-08T10:34:10","date_gmt":"2017-11-08T09:34:10","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/"},"modified":"2017-11-08T10:34:10","modified_gmt":"2017-11-08T09:34:10","slug":"displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/","title":{"rendered":"Displaying the contents of a PostgreSQL data file with pg_filedump"},"content":{"rendered":"

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<\/a> which makes this pretty easy. Lets go …<\/p>\n

<\/p>\n

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:<\/p>\n

\npostgres@pgbox:\/home\/postgres\/ [PG10] tar -axf pg_filedump-REL_10_0-c0e4028.tar.gz \npostgres@pgbox:\/home\/postgres\/ [PG10] cd pg_filedump-REL_10_0-c0e4028\npostgres@pgbox:\/home\/postgres\/pg_filedump-REL_10_0-c0e4028\/ [PG10] make\npostgres@pgbox:\/home\/postgres\/pg_filedump-REL_10_0-c0e4028\/ [PG10] make install\n<\/pre>\n
If everything went fine the utility should be there:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] pg_filedump -h\n\nVersion 10.0 (for PostgreSQL 10.x)\nCopyright (c) 2002-2010 Red Hat, Inc.\nCopyright (c) 2011-2017, PostgreSQL Global Development Group\n\nUsage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file\n\nDisplay formatted contents of a PostgreSQL heap\/index\/control file\nDefaults are: relative addressing, range of the entire file, block\n               size as listed on block 0 in the file\n\nThe following options are valid for heap and index files:\n  -a  Display absolute addresses when formatting (Block header\n      information is always block relative)\n  -b  Display binary block images within a range (Option will turn\n      off all formatting options)\n  -d  Display formatted block content dump (Option will turn off\n      all other formatting options)\n  -D  Decode tuples using given comma separated list of types\n      Supported types:\n        bigint bigserial bool char charN date float float4 float8 int\n        json macaddr name oid real serial smallint smallserial text\n        time timestamp timetz uuid varchar varcharN xid xml\n      ~ ignores all attributes left in a tuple\n  -f  Display formatted block content dump along with interpretation\n  -h  Display this information\n  -i  Display interpreted item details\n  -k  Verify block checksums\n  -R  Display specific block ranges within the file (Blocks are\n      indexed from 0)\n        [startblock]: block to start at\n        [endblock]: block to end at\n      A startblock without an endblock will format the single block\n  -s  Force segment size to [segsize]\n  -n  Force segment number to [segnumber]\n  -S  Force block size to [blocksize]\n  -x  Force interpreted formatting of block items as index items\n  -y  Force interpreted formatting of block items as heap items\n\nThe following options are valid for control files:\n  -c  Interpret the file listed as a control file\n  -f  Display formatted content dump along with interpretation\n  -S  Force block size to [blocksize]\n\nReport bugs to \n<\/pre>\n
As we want to dump a file we obviously need a table with some data, so:<\/p>\n
\npostgres=# create table t1 ( a int, b varchar(50));\nCREATE TABLE\npostgres=# insert into t1 (a,b) select a, md5(a::varchar) from generate_series(1,10) a;\nINSERT 0 10\n<\/pre>\n
Get the name of the file:<\/p>\n
\npostgres=# select * from pg_relation_filenode('t1');\n pg_relation_filenode \n----------------------\n                24702\n(1 row)\n<\/pre>\n
Look it up in PGDATA:<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PG10] cd $PGDATA\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] find . -name 24702\n.\/base\/13212\/24702\n<\/pre>\n
… and dump it:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] pg_filedump .\/base\/13212\/24702\n\n*******************************************************************\n* PostgreSQL File\/Block Formatted Dump Utility - Version 10.0\n*\n* File: .\/base\/13212\/24702\n* Options used: None\n*\n* Dump created on: Wed Nov  8 10:39:33 2017\n*******************************************************************\nError: Unable to read full page header from block 0.\n  ===> Read 0 bytes\n<\/pre>\n
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):<\/p>\n
\npostgres=#  checkpoint;\nCHECKPOINT\nTime: 100.567 ms\n<\/pre>\n
Do it again:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] pg_filedump .\/base\/13212\/24702\n\n*******************************************************************\n* PostgreSQL File\/Block Formatted Dump Utility - Version 10.0\n*\n* File: .\/base\/13212\/24702\n* Options used: None\n*\n* Dump created on: Wed Nov  8 10:40:45 2017\n*******************************************************************\n\nBlock    0 ********************************************************\n
 -----\n Block Offset: 0x00000000         Offsets: Lower      64 (0x0040)\n Block: Size 8192  Version    4            Upper    7552 (0x1d80)\n LSN:  logid      0 recoff 0x478b2c48      Special  8192 (0x2000)\n Items:   10                      Free Space: 7488\n Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()\n Length (including item array): 64\n\n ------ \n Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL\n Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL\n Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL\n Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL\n Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL\n Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL\n Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL\n Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL\n Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL\n Item  10 -- Length:   61  Offset: 7552 (0x1d80)  Flags: NORMAL\n\n\n*** End of File Encountered. Last Block Read: 0 ***\n<\/pre>\n
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:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] pg_filedump -f .\/base\/13212\/24702\n\n*******************************************************************\n* PostgreSQL File\/Block Formatted Dump Utility - Version 10.0\n*\n* File: .\/base\/13212\/24702\n* Options used: -f \n*\n* Dump created on: Wed Nov  8 10:41:21 2017\n*******************************************************************\n\nBlock    0 ********************************************************\n
 -----\n Block Offset: 0x00000000         Offsets: Lower      64 (0x0040)\n Block: Size 8192  Version    4            Upper    7552 (0x1d80)\n LSN:  logid      0 recoff 0x478b2c48      Special  8192 (0x2000)\n Items:   10                      Free Space: 7488\n Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()\n Length (including item array): 64\n\n  0000: 00000000 482c8b47 00000000 4000801d  ....H,.G....@...\n  0010: 00200420 00000000 c09f7a00 809f7a00  . . ......z...z.\n  0020: 409f7a00 009f7a00 c09e7a00 809e7a00  @.z...z...z...z.\n  0030: 409e7a00 009e7a00 c09d7a00 809d7a00  @.z...z...z...z.\n\n ------ \n Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL\n  1fc0: 96020000 00000000 00000000 00000000  ................\n  1fd0: 01000200 02081800 01000000 43633463  ............Cc4c\n  1fe0: 61343233 38613062 39323338 32306463  a4238a0b923820dc\n  1ff0: 63353039 61366637 35383439 62        c509a6f75849b   \n\n Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL\n  1f80: 96020000 00000000 00000000 00000000  ................\n  1f90: 02000200 02081800 02000000 43633831  ............Cc81\n  1fa0: 65373238 64396434 63326636 33366630  e728d9d4c2f636f0\n  1fb0: 36376638 39636331 34383632 63        67f89cc14862c   \n\n Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL\n  1f40: 96020000 00000000 00000000 00000000  ................\n  1f50: 03000200 02081800 03000000 43656363  ............Cecc\n  1f60: 62633837 65346235 63653266 65323833  bc87e4b5ce2fe283\n  1f70: 30386664 39663261 37626166 33        08fd9f2a7baf3   \n\n Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL\n  1f00: 96020000 00000000 00000000 00000000  ................\n  1f10: 04000200 02081800 04000000 43613837  ............Ca87\n  1f20: 66663637 39613266 33653731 64393138  ff679a2f3e71d918\n  1f30: 31613637 62373534 32313232 63        1a67b7542122c   \n\n Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL\n  1ec0: 96020000 00000000 00000000 00000000  ................\n  1ed0: 05000200 02081800 05000000 43653464  ............Ce4d\n  1ee0: 61336237 66626263 65323334 35643737  a3b7fbbce2345d77\n  1ef0: 37326230 36373461 33313864 35        72b0674a318d5   \n\n Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL\n  1e80: 96020000 00000000 00000000 00000000  ................\n  1e90: 06000200 02081800 06000000 43313637  ............C167\n  1ea0: 39303931 63356138 38306661 66366662  9091c5a880faf6fb\n  1eb0: 35653630 38376562 31623264 63        5e6087eb1b2dc   \n\n Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL\n  1e40: 96020000 00000000 00000000 00000000  ................\n  1e50: 07000200 02081800 07000000 43386631  ............C8f1\n  1e60: 34653435 66636565 61313637 61356133  4e45fceea167a5a3\n  1e70: 36646564 64346265 61323534 33        6dedd4bea2543   \n\n Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL\n  1e00: 96020000 00000000 00000000 00000000  ................\n  1e10: 08000200 02081800 08000000 43633966  ............Cc9f\n  1e20: 30663839 35666239 38616239 31353966  0f895fb98ab9159f\n  1e30: 35316664 30323937 65323336 64        51fd0297e236d   \n\n Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL\n  1dc0: 96020000 00000000 00000000 00000000  ................\n  1dd0: 09000200 02081800 09000000 43343563  ............C45c\n  1de0: 34386363 65326532 64376662 64656131  48cce2e2d7fbdea1\n  1df0: 61666335 31633763 36616432 36        afc51c7c6ad26   \n\n Item  10 -- Length:   61  Offset: 7552 (0x1d80)  Flags: NORMAL\n  1d80: 96020000 00000000 00000000 00000000  ................\n  1d90: 0a000200 02081800 0a000000 43643364  ............Cd3d\n  1da0: 39343436 38303261 34343235 39373535  9446802a44259755\n  1db0: 64333865 36643136 33653832 30        d38e6d163e820   \n\n\n\n*** End of File Encountered. Last Block Read: 0 ***\n<\/pre>\n
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:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] pg_filedump -D int,varchar .\/base\/13212\/24702\n\n*******************************************************************\n* PostgreSQL File\/Block Formatted Dump Utility - Version 10.0\n*\n* File: .\/base\/13212\/24702\n* Options used: -D int,varchar \n*\n* Dump created on: Wed Nov  8 10:42:58 2017\n*******************************************************************\n\nBlock    0 ********************************************************\n
 -----\n Block Offset: 0x00000000         Offsets: Lower      64 (0x0040)\n Block: Size 8192  Version    4            Upper    7552 (0x1d80)\n LSN:  logid      0 recoff 0x478b2c48      Special  8192 (0x2000)\n Items:   10                      Free Space: 7488\n Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()\n Length (including item array): 64\n\n ------ \n Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL\nCOPY: 1\tc4ca4238a0b923820dcc509a6f75849b\n Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL\nCOPY: 2\tc81e728d9d4c2f636f067f89cc14862c\n Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL\nCOPY: 3\teccbc87e4b5ce2fe28308fd9f2a7baf3\n Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL\nCOPY: 4\ta87ff679a2f3e71d9181a67b7542122c\n Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL\nCOPY: 5\te4da3b7fbbce2345d7772b0674a318d5\n Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL\nCOPY: 6\t1679091c5a880faf6fb5e6087eb1b2dc\n Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL\nCOPY: 7\t8f14e45fceea167a5a36dedd4bea2543\n Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL\nCOPY: 8\tc9f0f895fb98ab9159f51fd0297e236d\n Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL\nCOPY: 9\t45c48cce2e2d7fbdea1afc51c7c6ad26\n Item  10 -- Length:   61  Offset: 7552 (0x1d80)  Flags: NORMAL\nCOPY: 10\td3d9446802a44259755d38e6d163e820\n<\/pre>\n
And now we can see it. This is the same data as if you’d do a select on the table:<\/p>\n
\npostgres=# select * from  t1;\n a  |                b                 \n----+----------------------------------\n  1 | c4ca4238a0b923820dcc509a6f75849b\n  2 | c81e728d9d4c2f636f067f89cc14862c\n  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3\n  4 | a87ff679a2f3e71d9181a67b7542122c\n  5 | e4da3b7fbbce2345d7772b0674a318d5\n  6 | 1679091c5a880faf6fb5e6087eb1b2dc\n  7 | 8f14e45fceea167a5a36dedd4bea2543\n  8 | c9f0f895fb98ab9159f51fd0297e236d\n  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26\n 10 | d3d9446802a44259755d38e6d163e820\n(10 rows)\n<\/pre>\n
What happens when we do an update?:<\/p>\n
\npostgres=# update t1 set b = 'a' where a = 4;\nUPDATE 1\npostgres=# checkpoint ;\nCHECKPOINT\n<\/pre>\n
How does it look like in the file?<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG10\/ [PG10] pg_filedump -D int,varchar .\/base\/13212\/24702\n\n*******************************************************************\n* PostgreSQL File\/Block Formatted Dump Utility - Version 10.0\n*\n* File: .\/base\/13212\/24702\n* Options used: -D int,varchar \n*\n* Dump created on: Wed Nov  8 11:12:35 2017\n*******************************************************************\n\nBlock    0 ********************************************************\n
 -----\n Block Offset: 0x00000000         Offsets: Lower      68 (0x0044)\n Block: Size 8192  Version    4            Upper    7520 (0x1d60)\n LSN:  logid      0 recoff 0x478c2998      Special  8192 (0x2000)\n Items:   11                      Free Space: 7452\n Checksum: 0x0000  Prune XID: 0x00000298  Flags: 0x0000 ()\n Length (including item array): 68\n\n ------ \n Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL\nCOPY: 1\tc4ca4238a0b923820dcc509a6f75849b\n Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL\nCOPY: 2\tc81e728d9d4c2f636f067f89cc14862c\n Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL\nCOPY: 3\teccbc87e4b5ce2fe28308fd9f2a7baf3\n Item   4 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL\nCOPY: 4\ta87ff679a2f3e71d9181a67b7542122c\n Item   5 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL\nCOPY: 5\te4da3b7fbbce2345d7772b0674a318d5\n Item   6 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL\nCOPY: 6\t1679091c5a880faf6fb5e6087eb1b2dc\n Item   7 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL\nCOPY: 7\t8f14e45fceea167a5a36dedd4bea2543\n Item   8 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL\nCOPY: 8\tc9f0f895fb98ab9159f51fd0297e236d\n Item   9 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL\nCOPY: 9\t45c48cce2e2d7fbdea1afc51c7c6ad26\n Item  10 -- Length:   61  Offset: 7552 (0x1d80)  Flags: NORMAL\nCOPY: 10\td3d9446802a44259755d38e6d163e820\n Item  11 -- Length:   30  Offset: 7520 (0x1d60)  Flags: NORMAL\nCOPY: 4\ta\n\n*** End of File Encountered. Last Block Read: 0 ***\n<\/pre>\n
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:<\/p>\n
\npostgres=# vacuum t1;\nVACUUM\npostgres=# checkpoint ;\nCHECKPOINT\n<\/pre>\n
Again (just displaying the data here):<\/p>\n
\n ------ \n Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL\nCOPY: 1\tc4ca4238a0b923820dcc509a6f75849b\n Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL\nCOPY: 2\tc81e728d9d4c2f636f067f89cc14862c\n Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL\nCOPY: 3\teccbc87e4b5ce2fe28308fd9f2a7baf3\n Item   4 -- Length:    0  Offset:   11 (0x000b)  Flags: REDIRECT\n Item   5 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL\nCOPY: 5\te4da3b7fbbce2345d7772b0674a318d5\n Item   6 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL\nCOPY: 6\t1679091c5a880faf6fb5e6087eb1b2dc\n Item   7 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL\nCOPY: 7\t8f14e45fceea167a5a36dedd4bea2543\n Item   8 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL\nCOPY: 8\tc9f0f895fb98ab9159f51fd0297e236d\n Item   9 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL\nCOPY: 9\t45c48cce2e2d7fbdea1afc51c7c6ad26\n Item  10 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL\nCOPY: 10\td3d9446802a44259755d38e6d163e820\n Item  11 -- Length:   30  Offset: 7584 (0x1da0)  Flags: NORMAL\nCOPY: 4\ta\n<\/pre>\n
… and “Item 4” is gone (somewhere else). The same happens when you delete data:<\/p>\n
\npostgres=# delete from t1 where a = 4;\nDELETE 1\npostgres=# vacuum t1;\nVACUUM\npostgres=# checkpoint;\nCHECKPOINT\n<\/pre>\n
You’ll notice that both, Items 4 and 11, are now gone (UNUSED):<\/p>\n
\n ------ \n Item   1 -- Length:   61  Offset: 8128 (0x1fc0)  Flags: NORMAL\nCOPY: 1\tc4ca4238a0b923820dcc509a6f75849b\n Item   2 -- Length:   61  Offset: 8064 (0x1f80)  Flags: NORMAL\nCOPY: 2\tc81e728d9d4c2f636f067f89cc14862c\n Item   3 -- Length:   61  Offset: 8000 (0x1f40)  Flags: NORMAL\nCOPY: 3\teccbc87e4b5ce2fe28308fd9f2a7baf3\n Item   4 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED\n Item   5 -- Length:   61  Offset: 7936 (0x1f00)  Flags: NORMAL\nCOPY: 5\te4da3b7fbbce2345d7772b0674a318d5\n Item   6 -- Length:   61  Offset: 7872 (0x1ec0)  Flags: NORMAL\nCOPY: 6\t1679091c5a880faf6fb5e6087eb1b2dc\n Item   7 -- Length:   61  Offset: 7808 (0x1e80)  Flags: NORMAL\nCOPY: 7\t8f14e45fceea167a5a36dedd4bea2543\n Item   8 -- Length:   61  Offset: 7744 (0x1e40)  Flags: NORMAL\nCOPY: 8\tc9f0f895fb98ab9159f51fd0297e236d\n Item   9 -- Length:   61  Offset: 7680 (0x1e00)  Flags: NORMAL\nCOPY: 9\t45c48cce2e2d7fbdea1afc51c7c6ad26\n Item  10 -- Length:   61  Offset: 7616 (0x1dc0)  Flags: NORMAL\nCOPY: 10\td3d9446802a44259755d38e6d163e820\n Item  11 -- Length:    0  Offset:    0 (0x0000)  Flags: UNUSED\n<\/pre>\n
So far for the introduction of pg_filedump, more to come in more detail.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-10631","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nDisplaying the contents of a PostgreSQL data file with pg_filedump - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Displaying the contents of a PostgreSQL data file with pg_filedump\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-11-08T09:34:10+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Displaying the contents of a PostgreSQL data file with pg_filedump\",\"datePublished\":\"2017-11-08T09:34:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\"},\"wordCount\":377,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\",\"name\":\"Displaying the contents of a PostgreSQL data file with pg_filedump - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-11-08T09:34:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Displaying the contents of a PostgreSQL data file with pg_filedump\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Displaying the contents of a PostgreSQL data file with pg_filedump - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/","og_locale":"en_US","og_type":"article","og_title":"Displaying the contents of a PostgreSQL data file with pg_filedump","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/","og_site_name":"dbi Blog","article_published_time":"2017-11-08T09:34:10+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Displaying the contents of a PostgreSQL data file with pg_filedump","datePublished":"2017-11-08T09:34:10+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/"},"wordCount":377,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/","url":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/","name":"Displaying the contents of a PostgreSQL data file with pg_filedump - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-11-08T09:34:10+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Displaying the contents of a PostgreSQL data file with pg_filedump"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10631"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10631\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10631"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}