PostgreSQL Will Not Start: Before You Reset the WAL

A PostgreSQL server that will not start after a crash usually says why in its log: an invalid checkpoint record, a missing or unreadable WAL segment, a PANIC while replaying the log, or a message about an invalid page in a data file. These point to different problems. Damage to the write-ahead log (WAL) stops the server from finishing crash recovery. Damage to a data file can leave the server running but make individual tables fail to read. The first is fixed with different tools than the second, and using the wrong one can make things worse.

Before running any repair tool, stop PostgreSQL and copy the entire data directory, including the pg_wal directory and any tablespaces stored outside it, to a separate volume. If the disk itself looks unreliable, take a block-level image rather than a file copy. Recovery tools change the files they run against, so the only way to keep every option open is to run them on a copy and leave the original untouched.

The pg_resetwal tool clears the write-ahead log and resets the control information so the server can start. PostgreSQL's own documentation describes it as a last resort, for cases where the log is damaged and the server cannot start otherwise, and warns that it can leave the database in an inconsistent state. Transactions that were committed but not yet fully written to the data files can be lost, and the result may contain partly applied changes.

If it is used, the safe way to treat the result is as a source to extract data from: start the server, dump what can be read, and load the dump into a freshly initialized cluster on healthy storage. A cluster that was started after a WAL reset should not go back into normal use.

Deleting files from the pg_wal directory by hand is a common mistake. Those files are the log the server needs to reach a consistent state, and removing them is not a way to free space or to make the server start. Others to avoid are re-running initdb over the existing directory, restoring an old base backup on top of the damaged one before copying it, and running VACUUM FULL or REINDEX against tables that are already reporting errors.

If the server starts but pg_dump fails on a particular table with an invalid page error, that table has damaged pages. PostgreSQL has a zero_damaged_pages setting that lets a read continue past a damaged page by treating it as empty, which allows the rest of the table to be dumped, but the rows on that page are lost. That trade-off should only be made on a copy, and only after the alternatives, such as restoring from a backup or replaying archived WAL, have been checked.

Most PostgreSQL corruption comes from the storage layer: a failing disk, a controller problem, a power loss during a write, or a volume that filled up. Running with fsync turned off, or on storage that does not honour flush requests, can also leave a database unrecoverable after a crash. Rebuilding onto the same storage without finding the cause is likely to bring the problem back, so check the disks and the system logs as part of the recovery.

If the server will not start, if dumps are missing tables, or if the system catalogs are damaged, recovery has to work from the data files themselves. We have recovered data in many cases like this, and we tell you exactly what is recoverable after a free assessment. At that point, keep working from the copy or image taken at the start, and avoid running further tools against the original files.