Read PostgreSQL data directory files

Question

How can i view and read files in PostgreSQL "data\base" directory. I try to use "pg_ctl" to import this directory to my server as a database but It failed. i try to make a tar archive and restore it using pgadmin, but it return toc heading error. I know that each folder in "data/base" is a database OID and each file is a table and relative data. these files like a binary or dat file like this:(16384/1174)

€ëÌ 0 ðð b1 0ëÌ Ô ð àŸ ÐŸ ÀŸ °Ÿ  Ÿ Ÿ €Ÿ pŸ Ÿ PŸ @Ÿ 0Ÿ Ÿ Ÿ Ÿ ðž àž Ðž Àž °ž  ž ž €ž pžž Pž @ž 0ž ž ž ž ð à Ð À °   € p P @ 0 ðœ àœ Ðœ Àœ °œ  œ œ €œ pœœ Pœ @œ 0œ œ œ œ ð› à› Ð› À› °›  › › €› p› `› P› @› 0› › › › ðš àš Ðš Àš °š  š

Is there any way to view and read?? I want to retrieve tables and data in it. I searched a lot and not found any true solution. please help. thanks a lot.

Answer 1

neither pg_ctl. nor pgAdmin, nor any SQL client can work with database files themselves. If you want to read the file, you sould study the page structure (link below) and how to use that knowledge. So answering Is there any way to view and read?? yes, basically you quote contents in your question. But you can't use it to costruct table or index data. Unless you backingeneer the postgres. Or try to hack it to make it think your 16384/1174 file is existing relation - both are out of SO scope I believe.

below some reference on the structure, that you try to read

https://www.postgresql.org/docs/current/static/storage-page-layout.html

Every table and index is stored as an array of pages of a fixed size

and further:

The first 24 bytes of each page consists of a page header (PageHeaderData). Its format is detailed in Table 66.3. The first field tracks the most recent WAL entry related to this page. The second field contains the page checksum if data checksums are enabled. Next is a 2-byte field containing flag bits. This is followed by three 2-byte integer fields (pd_lower, pd_upper, and pd_special). These contain byte offsets from the page start to the start of unallocated space, to the end of unallocated space, and to the start of the special space. The next 2 bytes of the page header, pd_pagesize_version, store both the page size and a version indicator. Beginning with PostgreSQL 8.3 the version number is 4; PostgreSQL 8.1 and 8.2 used version number 3; PostgreSQL 8.0 used version number 2; PostgreSQL 7.3 and 7.4 used version number 1; prior releases used version number 0. (The basic page layout and header format has not changed in most of these versions, but the layout of heap row headers has.) The page size is basically only present as a cross-check; there is no support for having more than one page size in an installation. The last field is a hint that shows whether pruning the page is likely to be profitable: it tracks the oldest un-pruned XMAX on the page.

Answer 2

We can use Visual Code to open files in the base directory. 1). Install Visual Code 2). Install Hex editor extension 3). Go to the base directory, choose the file you want, to 'Open With', choose Visual Code, choose 'Open Anyway', then at the left top corner, choose 'Hex Editor'. Enjoy!