postgresql error PANIC: could not locate a valid checkpoint record

When I load up the postgres server (v9.0.1) i get a panic that prevents it from starting:

PANIC: could not locate a valid checkpoint record

How can I fix this?

Solution 1:

It's looking for a checkpoint record in the transaction log that probably doesn't exist or is corrupted. You can determine if this is the case by running:

# Postgres >= 10
pg_resetwal DATADIR

# Postgres < 10
pg_resetxlog DATADIR

If the transaction log is corrupt, you'll see a message like:

The database server was not shut down cleanly. Resetting the transaction log might cause data to be lost. If you want to proceed anyway, use -f to force reset.

You can then follow the instructions and run with -f to force the update:

# Postgres >= 10
pg_resetwal -f DATADIR

# Postgres < 10
pg_resetxlog -f DATADIR 

That should reset the transaction log, however it could leave your database in an indeterminate state as explained in the PostgreSQL documentation on pg_resetwal:

If pg_resetwal complains that it cannot determine valid data for pg_control, you can force it to proceed anyway by specifying the -f (force) option. In this case plausible values will be substituted for the missing data. Most of the fields can be expected to match, but manual assistance might be needed for the next OID, next transaction ID and epoch, next multitransaction ID and offset, and WAL starting location fields. These fields can be set using the options discussed below. If you are not able to determine correct values for all these fields, -f can still be used, but the recovered database must be treated with even more suspicion than usual: an immediate dump and reload is imperative. Do not execute any data-modifying operations in the database before you dump, as any such action is likely to make the corruption worse.

Solution 2:

I'm running 9.1.7 and i find ran the following successfully:

/usr/lib/postgresql/9.1/bin/pg_resetxlog -f /var/lib/postgresql/9.1/main

Your final argument to the pg_resetxlog command should be the location on disk where postgres stores your database data.

Solution 3:

As indicated here pg_resetxlog should not be run. The answers that refer to this is bad advice. Assuming the error occured in a context of copy/replication instance, the link provides a more succinct way of doing copy/replication with pg_basebackup

Solution 4:

Do you do continuous archiving? If you are backing up at the time, you may find it more prudent to remove backup_label. pg_resetxlog is a severe thing.

Solution 5:

I came across here with a Docker Postgresql-13 which did not start again. I fixed it by finding the volume (for the data) and running

Being in the volume data folder, e.g., /var/lib/docker/volumes/c4c8d637d9eee086265d732b2974690b731abcb23f47ca61bf75fe28526e31ce/_data

run as owner of the directory (for me it was the systemd-coredump user)

sudo -u systemd-coredump /usr/lib/postgresql/13/bin/pg_resetwal -f .

For sure you need the same Postgresql version installed (if the pg_resetwal is not part of the volume)

worked