Recovering a Corrupted PostgreSQL Database with pg_resetwal: A Step‑by‑Step Guide

Background and Motivation

IT operations often feel frightening because practitioners lack insight into the underlying mechanisms of the systems they manage. The author recounts a late‑1990s Oracle incident where an operating‑system crash rendered the database unusable; through diligent study of Oracle internals the instance was eventually recovered, highlighting the value of deep technical knowledge.

Applying Oracle Lessons to PostgreSQL

The same principles of redo and undo apply to PostgreSQL. As long as the data files remain intact, a corrupted or inconsistent PostgreSQL cluster can often be revived by resetting its write‑ahead log (WAL), much like Oracle’s redo logs.

Simple WAL‑Reset Example

When all WAL files are lost, the pg_resetwal (or pg_resetxlog in pre‑10 versions) tool can generate a fresh WAL to force the database to start. After running the tool, the instance starts, though any transactions recorded only in the missing WAL are lost (e.g., one row after the last checkpoint).

Recovering a Missing pg_control File

If the critical pg_control file is absent, a more elaborate use of pg_resetwal is required. The tool accepts several parameters that must match the original cluster’s state: -l, --next-wal-file=WALFILE: set the next WAL file name (use the highest existing WAL ID + 1). -x, --next-transaction-id=XID: set the next transaction ID (derived from files in pg_xact, expressed in hexadecimal, e.g., -x 0x039300000). -m, --multixact-ids=MXID1,MXID2: supply multixact IDs calculated from pg_multixact/offsets (e.g., -m 0x00570000,0x00080000). -O, --multixact-offset=OFFSET: provide the multixact offset from pg_multixact/members (e.g., -O 0x00BAED00).

Full Recovery Command

Combining the parameters yields a command such as:

pg_resetwal -O 0x00BAED00 -m 0x00570000,0x00080000 -x 0x039300000 -l 000000010000099B00000037 $PGDATA

Preparation and Execution Steps

If the server crashed, manually delete $PGDATA/postmaster.pid if it still exists.

Create an empty pg_control file in $PGDATA/global (e.g., touch pg_control).

Run the assembled pg_resetwal command, adding -f to force execution when necessary.

After the command finishes, restart the PostgreSQL instance.

Successful start does not guarantee that the pg_control file is fully correct; incorrect parameters can still leave the cluster inconsistent. Always back up the data directory before attempting such recovery.

Cautions

The pg_resetwal tool should be a last‑resort measure. Misuse can cause data loss or hidden corruption. Verify all parameter values carefully, and perform a complete backup before proceeding.

Illustrative Images