pg_resetwal

Reset the WAL and control information for a specific data directory

$ pg_resetwal [[-D|--pgdata]] [path/to/data]

$ pg_resetwal [[-D|--pgdata]] [path/to/data] [[-n|--dry-run]]

$ pg_resetwal [[-D|--pgdata]] [path/to/data] [[-f|--force]]

$ pg_resetwal [[-?|--help]]

$ pg_resetwal [[-V|--version]]

pg_resetwal [-f | --force] [-n | --no-sync] [-o OID] [-x XID] [-l WALFILE] [-m MXID] [-e MXOFFSET] [-D DATADIR] [--help | -V]

DATADIR
    Specifies the PostgreSQL data directory. This is a mandatory argument.

-f, --force
    Forces pg_resetwal to proceed even if it cannot determine valid data from the control file. Use with extreme caution as it bypasses critical safety checks.

-n, --no-sync
    Disables syncing of output files to disk. This makes the operation faster but increases the risk of data loss if the system crashes immediately after running the command.

-o OID, --next-oid=OID
    Manually sets the next object identifier (OID) for new objects. Defaults to the largest OID currently in the database, plus one.

-x XID, --next-transaction-id=XID
    Manually sets the next transaction ID. Defaults to the largest known XID plus one. It is often necessary to increase this significantly to prevent transaction ID wraparound issues.

-l WALFILE, --next-wal-file=WALFILE
    Manually sets the next WAL file name to be used. This allows specifying a WAL file name that is greater than any currently existing one, preventing PostgreSQL from trying to read older, potentially corrupted, WAL segments.

-m MXID, --next-multi-xid=MXID
    Sets the next multixact ID. Defaults to the largest known multixact ID plus one.

-e MXOFFSET, --next-multi-xact-offset=MXOFFSET
    Sets the next multixact offset. Defaults to the largest known multixact offset plus one.

-D DATADIR, --pgdata=DATADIR
    Specifies the data directory. This can also be given as a non-option argument.

--help
    Shows help about pg_resetwal command line arguments.

-V, --version
    Prints the pg_resetwal version and exits.

pg_resetwal is a PostgreSQL utility designed for emergency recovery when the database write-ahead log (WAL) files become corrupted or unrecoverable, preventing the PostgreSQL server from starting. It forcibly resets the WAL and control file information, allowing the server to restart, though potentially at the cost of losing some data that was not yet flushed to disk. This command should only be used as a last resort when all other recovery methods have failed, and after careful consideration of the risks, as it can lead to data inconsistencies. It is crucial to understand that pg_resetwal does not repair data corruption; it merely bypasses the WAL-related startup checks, effectively telling PostgreSQL to start with a 'clean slate' for WAL, which might leave the underlying data files in an inconsistent state relative to the point of crash. After using this utility, a full database dump and restore, or a fresh `initdb` and restore from backup, is highly recommended.

pg_resetwal is an emergency tool and should be used with extreme caution. It can lead to irreversible data loss or inconsistencies if not used correctly or if the underlying data files are themselves corrupted. It should only be attempted when the PostgreSQL server fails to start due to WAL-related errors and and other recovery options (like restoring from a recent backup) are not feasible. After using pg_resetwal, it is highly recommended to immediately perform a full database dump (pg_dumpall) and then initialize a new database cluster (initdb) and restore the data from the dump. Do not rely on a cluster reset with this tool for long-term production use without thorough validation.

Use pg_resetwal when the PostgreSQL server fails to start due to corrupted WAL files, typically manifesting as errors like 'invalid primary checkpoint record' or 'WAL contains unexpected data'. This happens when the server crashed badly and the WAL files are left in an unrecoverable state, or if disk corruption affects the WAL segments.

After successfully running pg_resetwal and restarting the server, the database might be in an inconsistent state. The recommended actions are:
1. Immediately take a full logical backup using pg_dumpall.
2. Evaluate the integrity of the data.
3. If data integrity is critical, consider initializing a new database cluster and restoring from the backup, or restoring from a prior physical backup if available. This ensures a clean and consistent database state.

The pg_resetwal utility was introduced in PostgreSQL 10, replacing the earlier utility named pg_resetxlog. The name change was made to better reflect its actual purpose: resetting only the Write-Ahead Log (WAL) and associated control file information, rather than suggesting it resets the entire transaction log, which could be misleading. This renaming aimed to improve clarity and avoid confusion among database administrators. Functionality largely remained consistent, adapting to internal WAL format changes.

pg_controldata(1), initdb(1), pg_basebackup(1), pg_dumpall(1)