Postgres down after Time Machine restore

A few days ago my MacBook crashed. I had to erase the harddisk and restore the data from the latest Time Machine backup. After a few hours reading a book my MacBook worked as though nothing had happened – except the postgres server. On OS X the log files can be found in /Library/PostgreSQL/8.4/data/pg_log

I looked in the newest log file with

sudo less /Library/PostgreSQL/8.4/data/pg_log/postgresql-2014-01-14_152823.log

and found a PANIC log message

2014-01-14 15:28:23 CET LOG:  record with zero length at 19/3DA0E2C0
2014-01-14 15:28:23 CET LOG:  invalid primary checkpoint record
2014-01-14 15:28:23 CET LOG:  record with zero length at 19/3DA0E27C
2014-01-14 15:28:23 CET LOG:  invalid secondary checkpoint record
2014-01-14 15:28:23 CET PANIC:  could not locate a valid checkpoint record

If you want to analyse the transaction log switch user to postgres and use

sudo su - postgres
/Library/PostgreSQL/8.4/bin/pg_resetxlog /Library/PostgreSQL/8.4/data

In my case the transaction log was corrupt and the data didn’t matter. So I used -f to force reset

/Library/PostgreSQL/8.4/bin/pg_resetxlog -f /Library/PostgreSQL/8.4/data

See also this discussion at stackoverflow

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s