Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 321
nathan_scott Avatar asked Jan 10 '12 06:01

nathan_scott


3 Answers

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.

like image 157
7 revs, 4 users 67% Avatar answered Nov 02 '22 16:11

7 revs, 4 users 67%


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.

like image 25
limlam Avatar answered Nov 02 '22 18:11

limlam


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

like image 11
Jerome Avatar answered Nov 02 '22 16:11

Jerome