How to recover when can't start database

Hi!
(Hope this is the right place to post)

I crashed the postmaster and cannot start it anymore, with the error

LOG: database system was interrupted while in recovery at 2005-04-01 11:04:33 CEST
HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.
LOG: checkpoint record is at 5/6F00C540
LOG: redo record is at 5/6F000ABC; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 599824; next OID: 147679259
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 5/6F000ABC
PANIC: btree_split_redo: lost left sibling
LOG: startup process (PID 5603) was terminated by signal 6
LOG: aborting startup due to startup process failure

Is there a way to recover from that?

I don't have a fresh backup, but loosing some couple of days won't be a
problem.

I use PG 8.0 on a linux box, with standard postgresq.conf (except some
increased memory settings).

thanks,
L.B.

"L.Boldareva" <pg(at)pierro(dot)dds(dot)nl> writes:
> LOG: database system was not properly shut down; automatic recovery in
> progress
> LOG: redo starts at 5/6F000ABC
> PANIC: btree_split_redo: lost left sibling
> LOG: startup process (PID 5603) was terminated by signal 6

Hmm. AFAICS that could only happen if a page split record is pointing
at an "original" page that's not there anymore; that is, the page is
past what the kernel says is the end of the file. Exactly how did you
get into this state ... was there a system-level crash involved? ISTM
this cannot happen unless the filesystem has dropped data.

You could probably get it to start by changing the "false" to "true"
in this call of XLogReadBuffer

/* Left (original) sibling */
buffer = XLogReadBuffer(false, reln, leftsib);
if (!BufferIsValid(buffer))
elog(PANIC, "btree_split_%s: lost left sibling", op);

in src/backend/access/nbtree/nbtxlog.c (it's line 261 in CVS tip,
possibly a little different in 8.0). Let us know if that helps.

I'd be a bit suspicious of the contents of the index, if not the
whole database, so an immediate dump,reinitdb,reload might be your
most prudent course of action after you get it to start.

Plan B would be to wipe out the WAL log with pg_resetxlog. This will
allow you to start but the odds of having corrupt data afterwards would
be about 100% ... you *must* dump and reload if you go that way.

regards, tom lane

On Fri, 2005-04-01 at 04:02, L.Boldareva wrote:
> Hi!
> (Hope this is the right place to post)
>
> I crashed the postmaster and cannot start it anymore, with the error

In addition to what everyone's posted already, I would suggest you spend
some time figuring out what got you here in the first place. Bad
hardware, misconfigured postgresql.conf (specifically fsync=false) or
other possibilities.

Under normal circumstances, a crashing postmaster should not cause these
problems.

I have kind of fixed the problem (hopefully)

I turned out I used plan B, dump/reload will be my next step then.
What is meant by the "corrupt data", is this about the data or the things
around it, like indexes, system tables?

Here is how I got it crashed:
I compiled a c-procedure and copied the .so file to its place exactly at a
time when (quite unfortunately) another query was running, that used that
library.

I usually do pg_ctl reload right after that, and it seems to be enough,
but not this time.

The c-function contained code that would send a query to populate a table
(most likely one of the 2 bad ones), but I am not sure this matters since
there were 2 tables out of order, and
only one at a time is touched by my script.

That's it. I just checked, if that matters, that fsync = true on the
config file.

On Fri, 1 Apr 2005, Tom Lane wrote:

> Hmm. AFAICS that could only happen if a page split record is pointing
> at an "original" page that's not there anymore; that is, the page is
> past what the kernel says is the end of the file.

Something like that was mentioned in the WARNING message when I tried to
drop the table, but I got that warning only once, further actions just
raised the error about relid.

Thank you for your help,
LB

> You could probably get it to start by changing the "false" to "true"
> in this call of XLogReadBuffer
>
> /* Left (original) sibling */
> buffer = XLogReadBuffer(false, reln, leftsib);
> if (!BufferIsValid(buffer))
> elog(PANIC, "btree_split_%s: lost left sibling", op);
>
> in src/backend/access/nbtree/nbtxlog.c (it's line 261 in CVS tip,
> possibly a little different in 8.0). Let us know if that helps.
>
> I'd be a bit suspicious of the contents of the index, if not the
> whole database, so an immediate dump,reinitdb,reload might be your
> most prudent course of action after you get it to start.
>

> Plan B would be to wipe out the WAL log with pg_resetxlog. This will
> allow you to start but the odds of having corrupt data afterwards would
> be about 100% ... you *must* dump and reload if you go that way.
>
> regards, tom lane
>

"L.Boldareva" <pg(at)pierro(dot)dds(dot)nl> writes:
> Here is how I got it crashed:
> I compiled a c-procedure and copied the .so file to its place exactly at a
> time when (quite unfortunately) another query was running, that used that
> library.

Hmm, at worst that should only crash one backend, not result in bad data
getting into the WAL log. If there was no filesystem crash then I have
to suppose that the page number entered in the WAL page split record was
wrong ... but how could that happen if the same page number had just
been used successfully to read the page?

regards, tom lane

On Fri, 1 Apr 2005, Tom Lane wrote:

> "L.Boldareva" <pg(at)pierro(dot)dds(dot)nl> writes:
> > Here is how I got it crashed:
> > I compiled a c-procedure and copied the .so file to its place exactly at a
> > time when (quite unfortunately) another query was running, that used that
> > library.
>

> Hmm, at worst that should only crash one backend, not result in bad data
> getting into the WAL log. If there was no filesystem crash then I have
> to suppose that the page number entered in the WAL page split record was
> wrong ... but how could that happen if the same page number had just
> been used successfully to read the page?
>
> regards, tom lane

I have no idea
Tell me where to look, I'll have a look in the tar. I'd
rather not reproduce the thing, but if someone is interested, I can try
that, too (later).

regards,
LB

>