PostgreSQL Data Recovery

Unlike SQL Server or Oracle, PostgreSQL does not use a single monolithic database file. The cluster is a directory tree managed by the postmaster process. Understanding this layout is the first step in recovery.

• base/ contains one subdirectory per database, named by OID. Each table and index is stored as one or more 1GB segment files (e.g., base/16384/24576, base/16384/24576.1).
• pg_wal/ (pg_xlog in versions before 10) holds 16MB write-ahead log segment files. Each segment is named by timeline and LSN position (e.g., 000000010000000000000042). PostgreSQL replays these segments during crash recovery to bring the cluster to a consistent state.
• global/pg_control is an 8KB file containing the cluster's checkpoint location, database system identifier, catalog version, WAL segment size, and timeline ID. If this file is missing or corrupted, PostgreSQL will not start.
• pg_tblspc/ contains symlinks to tablespace directories on other filesystems or drives. Tablespace-resident tables will be missing from the recovery if the target drive is not also recovered.
• pg_xact/ (pg_clog before version 10) stores transaction commit status bitmaps. Each page tracks 32,768 transaction IDs. Corruption here causes PostgreSQL to report transactions as "in progress" indefinitely, making affected rows invisible to queries.

pg_resetwal (pg_resetxlog before version 10) forces PostgreSQL to start by writing a new pg_control file and discarding all existing WAL history. The PostgreSQL documentation warns that after running this command, the database should be dumped and reloaded immediately, and that data may be silently lost.

The mechanism behind the data loss: pg_resetwal creates a new checkpoint record and advances the transaction counter past any in-flight transactions. Committed transactions that were recorded in WAL but not yet flushed to the heap files are permanently lost. The database will start, but the data on disk may not match what was committed by the application.

When the corruption originates from a physical drive failure, the situation is worse. pg_resetwal operates on whatever data the operating system can read from the degraded drive. Sectors with read errors are silently skipped or filled with zeros. Running pg_resetwal on this partial data locks in the loss. Imaging the drive with PC-3000 using adaptive read parameters first recovers more sectors, producing a more complete data directory to work with.

PostgreSQL's TOAST (The Oversized-Attribute Storage Technique) mechanism automatically compresses and splits column values that exceed approximately 2KB. The overflow data is stored in a separate TOAST table (pg_toast.pg_toast_NNNNN) with chunk_id and chunk_seq columns linking back to the parent row.

TOAST tables are the most fragile part of a PostgreSQL cluster during drive failures. Because TOAST data is distributed across separate heap files from the parent table, a partial drive failure can leave the main table intact while destroying the TOAST relation. Queries against affected rows return: "ERROR: missing chunk number 0 for toast value NNNNN in pg_toast.pg_toast_XXXXX."

Standard recovery tools (pg_dump, pg_restore) cannot handle this failure mode because they attempt to read the TOAST data through the normal SQL interface, which throws an error. We bypass the SQL layer and read the TOAST heap files directly from the recovered image, extracting valid chunks and mapping them back to parent rows by OID. Columns with unrecoverable TOAST chunks are flagged in the export rather than causing the entire table dump to fail.

PostGIS stores geometry and geography values as binary data within standard PostgreSQL heap pages. Point, polygon, linestring, and raster columns follow the same on-disk format as any bytea column. The spatial data itself does not require special handling during recovery.

Spatial indexes (GiST, SP-GiST, BRIN) are stored in separate relation files and are fully rebuildable. After the cluster starts, running REINDEX on spatial indexes restores query performance without any data loss. If the extension shared library files (postgis.so, postgis_raster.so) are missing from the recovered image, we install the matching PostGIS version in the sandboxed recovery environment.

Other extensions with on-disk state (pg_trgm indexes, hstore columns, JSONB GIN indexes) follow the same pattern: the data is in heap pages, the indexes are rebuildable, and the extension libraries are installed in the recovery environment to match the original cluster configuration.