WAL backup recovery is not working because of invalid checkpoint

From: Omar Siddiqui <omar(at)grabhalo(dot)com>
To: mumbai-pug(at)postgresql(dot)org
Subject: WAL backup recovery is not working because of invalid checkpoint
Date: 2013-08-16 10:14:16
Message-ID: CAPzPNmNtifKdyTTdsjjpzBQNXQZe9fEVQAo-ja-HrOrBh7kkLQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: mumbai-pug

Hi

Unfortunately, my machine crashed, and with it went out data. Fortunately,
we had instituted WAL backups. I am now rebuilding our environment on a new
machine and would like to populate our postgres DB with the data we have
saved from the old machine.

But when I am starting postgres according to the
documentation<http://www.postgresql.org/docs/9.2/interactive/continuous-archiving.html#BACKUP-PITR-RECOVERY>
I
get the following error:

* Starting PostgreSQL 9.2 database server

* The PostgreSQL server failed
to start. Please check the log output:
2013-08-16 09:45:24 UTC LOG: database system shutdown was interrupted;
last known up at 2013-08-16 09:13:48 UTC
cp: cannot stat `/var/lib/pgsql/pg_log_archive/00000010.history': No such
file or directory
2013-08-16 09:45:24 UTC LOG: starting point-in-time recovery to 2013-08-11
07:00:00+00
2013-08-16 09:45:24 UTC LOG: could not open file
"pg_xlog/000000100000009400000045" (log file 148, segment 69): No such file
or directory
2013-08-16 09:45:24 UTC LOG: invalid primary checkpoint record
2013-08-16 09:45:24 UTC LOG: could not open file
"pg_xlog/000000100000009400000045" (log file 148, segment 69): No such file
or directory
2013-08-16 09:45:24 UTC LOG: invalid secondary checkpoint record
2013-08-16 09:45:24 UTC PANIC: could not locate a valid checkpoint record
2013-08-16 09:45:25 UTC LOG: startup process (PID 2061) was terminated by
signal 6: Aborted
2013-08-16 09:45:25 UTC LOG: aborting startup due to startup process
failure

Basically the local DB was not in existence when our WAL files were
generated. To the DB, these are obsolete and it is looking for new ones
which don't have.

My question(s):
1) how can I tell postgresql to start with the backup and WAL files I have
and ignore the newer files it has? Am I configuring the recovery.conf
incorrectly? I set the time to: 2013-08-11 07:00:00+00 (as logged above)
which is after the crash on the original machine but before this DB was
made. I did this so that Postgres may try to recover the data up to that
point and look at the older backup/WAL file. Why is it still looking for
the newer 000000100000009400000045 file? (fyi that file was generated when
postgres was installed and started on the new machine. I do not care about
this recent data at all). But no matter what recovery time I choose, I see
the same errors so OITR is not working. If I do pg_resetxlog then the error
is gone but it starts postgres without loading the old data.

2) We do not have any of the original configuration files (pg_control etc.)
from PGDATA of the old machine. Is there anyway around this?

I have looked online and see a similar question raised before, but no
solution. Kindly help!

Omar Siddiqui*
*
grabHalo Inc.
www.grabhalo.com

[image: Facebook] <http://www.facebook.com/GrabHalo> [image:
Twitter]<http://htmlsig.com/@grabhalo>
[image: Linkedin] <http://www.linkedin.com/company/grabhalo-inc->

Browse mumbai-pug by date

  From Date Subject
Next Message PARLIKAR, Sandip 2016-04-18 12:26:48 Postgres Help
Previous Message Sandy Joseph Padala 2013-05-08 10:37:35 About Instant PostgreSQL Starter book from Packt