pg_resetwal

A utility to reset potentially corrupted WAL files

pg_resetwal (PostgreSQL 9.6 and earlier: pg_resetxlog) is a utility to reset potentially corrupted WAL files.

pg_resetwal was added (as pg_resetxlog) in PostgreSQL 7.3.

IMPORTANT: pg_resetwal is an "option of last resort" when dealing with corrupted WAL files. If you are not familiar with this utility (particularly if you are arriving from a desperate web search looking for a quick solution), use of pg_resetwal is likely to make things worse.

If you do have a valid reason to use pg_resetwal, be sure to use it extreme care and caution; it is highly recommended to make a full backup of the database at file system level before doing anything further.

Implementation

The source code (src/bin/pg_resetwal/pg_resetwal.c) explains the theory of operation as follows:

  1. Read the existing pg_control (which will include the last checkpoint record). If it is an old format then update to current format. (NB a later comment in the code states this is not actually done)
  2. If pg_control is corrupt, attempt to intuit reasonable values, by scanning the old xlog if necessary.
  3. Modify pg_control to reflect a "shutdown" state with a checkpoint record at the start of xlog.
  4. Flush the existing xlog files and write a new segment with just a checkpoint record in it. The new segment is positioned just past the end of the old xlog, so that existing LSNs in data pages will appear to be "in the past".

This is followed by the comment "This is all pretty straightforward except for the intuition part of step 2 ..."

Change history

pg_resetwal has remained, from a functionality point of view, largely unchanged since its introduction, though many internal fixes have been made.

Categories

Core utility, Dangerous thing, PostgreSQL internals, Storage, WAL