Sep 24 10:22:37 snafu postgres[18306]: [2-1] LOG:  database system was
interrupted while in recovery at 2004-09-24 10:21:41 MST
Sep 24 10:22:37 snafu postgres[18306]: [2-2] HINT:  This probably means
that some data is corrupted and you will have to use the last backup for
recovery.
Sep 24 10:22:37 snafu postgres[18306]: [3-1] LOG:  checkpoint record is
at 9A/C2022368
Sep 24 10:22:37 snafu postgres[18306]: [4-1] LOG:  redo record is at
9A/C2022368; undo record is at 0/0; shutdown FALSE
Sep 24 10:22:37 snafu postgres[18306]: [5-1] LOG:  next transaction ID:
197841225; next OID: 715436086
Sep 24 10:22:37 snafu postgres[18306]: [6-1] LOG:  database system was
not properly shut down; automatic recovery in progress
Sep 24 10:22:37 snafu postgres[18306]: [7-1] LOG:  redo starts at
9A/C20223B0
Sep 24 10:22:37 snafu postgres[18306]: [8-1] PANIC:  btree_insert_redo:
failed to add item
Sep 24 10:22:37 snafu postgres[18299]: [2-1] LOG:  startup process (PID
18306) was terminated by signal 6
Sep 24 10:22:37 snafu postgres[18299]: [3-1] LOG:  aborting startup due
to startup process failure


Any suggestions to recover?!  I'm dead in the water!  Please!!!




Fri, 24 Sep 2004 10:21:28 -0700
Cott Lang <cott@internetstaff.com>
CRITICAL HELP NEEDED!  DEAD DB!
For starters a little more detail would be helpful, for example:

What version of PostgreSQL?  What OS? What compiler?  What happened that 
caused this? Server Crash?

Matthew


Cott Lang wrote:

>Sep 24 10:22:37 snafu postgres[18306]: [2-1] LOG:  database system was
>interrupted while in recovery at 2004-09-24 10:21:41 MST
>Sep 24 10:22:37 snafu postgres[18306]: [2-2] HINT:  This probably means
>that some data is corrupted and you will have to use the last backup for
>recovery.
>Sep 24 10:22:37 snafu postgres[18306]: [3-1] LOG:  checkpoint record is
>at 9A/C2022368
>Sep 24 10:22:37 snafu postgres[18306]: [4-1] LOG:  redo record is at
>9A/C2022368; undo record is at 0/0; shutdown FALSE
>Sep 24 10:22:37 snafu postgres[18306]: [5-1] LOG:  next transaction ID:
>197841225; next OID: 715436086
>Sep 24 10:22:37 snafu postgres[18306]: [6-1] LOG:  database system was
>not properly shut down; automatic recovery in progress
>Sep 24 10:22:37 snafu postgres[18306]: [7-1] LOG:  redo starts at
>9A/C20223B0
>Sep 24 10:22:37 snafu postgres[18306]: [8-1] PANIC:  btree_insert_redo:
>failed to add item
>Sep 24 10:22:37 snafu postgres[18299]: [2-1] LOG:  startup process (PID
>18306) was terminated by signal 6
>Sep 24 10:22:37 snafu postgres[18299]: [3-1] LOG:  aborting startup due
>to startup process failure
>
>
>Any suggestions to recover?!  I'm dead in the water!  Please!!!
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>  
>


Fri, 24 Sep 2004 14:16:13 -0400
"Matthew T. O'Connor" <matthew@tocr.com>
Re: CRITICAL HELP NEEDED!  DEAD DB!
Cott Lang <cott@internetstaff.com> writes:
> Sep 24 10:22:37 snafu postgres[18306]: [2-1] LOG:  database system was
> interrupted while in recovery at 2004-09-24 10:21:41 MST
> Sep 24 10:22:37 snafu postgres[18306]: [2-2] HINT:  This probably means
> that some data is corrupted and you will have to use the last backup for
> recovery.
> Sep 24 10:22:37 snafu postgres[18306]: [3-1] LOG:  checkpoint record is
> at 9A/C2022368
> Sep 24 10:22:37 snafu postgres[18306]: [4-1] LOG:  redo record is at
> 9A/C2022368; undo record is at 0/0; shutdown FALSE
> Sep 24 10:22:37 snafu postgres[18306]: [5-1] LOG:  next transaction ID:
> 197841225; next OID: 715436086
> Sep 24 10:22:37 snafu postgres[18306]: [6-1] LOG:  database system was
> not properly shut down; automatic recovery in progress
> Sep 24 10:22:37 snafu postgres[18306]: [7-1] LOG:  redo starts at
> 9A/C20223B0
> Sep 24 10:22:37 snafu postgres[18306]: [8-1] PANIC:  btree_insert_redo:
> failed to add item
> Sep 24 10:22:37 snafu postgres[18299]: [2-1] LOG:  startup process (PID
> 18306) was terminated by signal 6
> Sep 24 10:22:37 snafu postgres[18299]: [3-1] LOG:  aborting startup due
> to startup process failure

> Any suggestions to recover?!  I'm dead in the water!  Please!!!

I think your only chance is pg_resetxlog.  Be aware that you won't
necessarily have a consistent database afterwards --- in particular,
whichever index that failure is about is certainly broken.  I'd
recommend a dump and reload, plus as much manual verification of data
consistency as you can manage.

How did you get into this state, anyway?

			regards, tom lane

Fri, 24 Sep 2004 14:43:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CRITICAL HELP NEEDED! DEAD DB! 
On Fri, 2004-09-24 at 11:43, Tom Lane wrote:
> 
> I think your only chance is pg_resetxlog.  Be aware that you won't
> necessarily have a consistent database afterwards --- in particular,
> whichever index that failure is about is certainly broken.  I'd
> recommend a dump and reload, plus as much manual verification of data
> consistency as you can manage.

That's what I've done, so far so good, although we are still checking
consistency against the last backup.  Thanks for the info. Luckily this
was one of our smaller databases ...

> How did you get into this state, anyway?

I wish I knew - this is what appeared to start it:

Sep 24 10:19:41 snafu postgres[18176]: [464-1] ERROR:  could not open
segment 1 of relation "idx_ordl_id" (target block 1719234412): No such
file or
Sep 24 10:19:41 snafu postgres[18176]: [464-2]  directory

I can't figure out what the exact problem is; there were no I/O errors
or any other relative messages at the time, the box was empty, and
nothing remarkable was going on.  :(

thanks,
Cott

PS: No, I don't think it's a PG problem. :)





Fri, 24 Sep 2004 12:18:00 -0700
Cott Lang <cott@internetstaff.com>
Re: CRITICAL HELP NEEDED! DEAD DB!
Cott Lang wrote:
> I wish I knew - this is what appeared to start it:
> 
> Sep 24 10:19:41 snafu postgres[18176]: [464-1] ERROR:  could not open
> segment 1 of relation "idx_ordl_id" (target block 1719234412): No such
> file or
> Sep 24 10:19:41 snafu postgres[18176]: [464-2]  directory
> 
> I can't figure out what the exact problem is; there were no I/O errors
> or any other relative messages at the time, the box was empty, and
> nothing remarkable was going on.  :(

I saw that exact error message, with no logged I/O system errors, when 
using SAN attached storage a month or so ago. It turned out to be the 
SAN silently corrupting files. We did eventually start to see scsi 
errors, but not at the beginning.

Joe

Fri, 24 Sep 2004 13:59:28 -0700
Joe Conway <mail@joeconway.com>
Re: CRITICAL HELP NEEDED! DEAD DB!