Recover PostgreSQL from corrupted WAL

Symptom

PostgreSQL cannot start and its deployment logs contain a checkpoint error such as:

FATAL: could not locate a valid checkpoint record

The logs might also contain invalid primary checkpoint record or the same checkpoint message at the PANIC level.

Cause

PostgreSQL uses its write-ahead log (WAL) to recover committed changes after a restart. A damaged WAL segment or control file can leave PostgreSQL without a valid checkpoint from which to start.

Solution

If point-in-time recovery is enabled, restore the database to a new service. Otherwise, restore a volume backup when one is available. Both options are safer than resetting the WAL because they don't modify the source database.

Create a volume backup

Preserve the current state before changing the database files:

1. Open the PostgreSQL service in Railway.
2. Select the Backups tab.
3. Create a manual backup of the database volume.

Stop PostgreSQL

Prevent PostgreSQL from running while you reset the WAL:

1. Open the PostgreSQL service Settings.
2. Set the Custom Start Command to sleep infinity.
3. Deploy the change.

The new deployment keeps the container running without starting PostgreSQL.

Connect to the container

In a terminal linked to the Railway project and PostgreSQL service, connect to the deployment:

railway ssh

Confirm that PGDATA points to the PostgreSQL data directory and that its major version matches the pg_resetwal major version:

printf '%s\n' "$PGDATA"
cat "$PGDATA/PG_VERSION"
pg_resetwal --version

Reset the WAL

Preview the values that pg_resetwal will use without changing any files:

su postgres -c "pg_resetwal --dry-run '$PGDATA'"

Review the output, then force the reset:

su postgres -c "pg_resetwal --force '$PGDATA'"

pg_resetwal must run as the postgres user while the server is stopped. The command must come from the same PostgreSQL major version as the data directory.

Restart PostgreSQL

Return the service to its normal start command:

1. Remove sleep infinity from Custom Start Command.
2. Deploy the change.
3. Check the deployment logs to confirm that PostgreSQL starts.

Rebuild the database

Treat the recovered database as inconsistent. Don't run data-modifying queries. Immediately create a logical dump, initialize a new PostgreSQL service, restore the dump into it, and check the restored data for inconsistencies.

For the required recovery steps and caveats, see the PostgreSQL pg_resetwal documentation.