Re: crash / data recovery issues

Robert Treat wrote:
> I'm trying to do some data recovery on an 8.1.9 system. The brief history is
> the system crashed, attempted to do xlog replay but that failed. I did a
> pg_resetxlog to get something that would startup, and it looks as if the
> indexes on pg_class have become corrupt. (ie. reindex claimes duplicate rows,
> which do not show up when doing count() manipulations on the data). As it
> turns out, I can't drop these indexes either (system refuses with message
> indexes are needed by the system). This has kind of let the system in an
> unworkable state.

You can work out of it by starting a standalone server with system
indexes disabled (postgres -O -P, I think) and do a REINDEX on it (the
form of it that reindexes all system indexes -- I think it's REINDEX
DATABASE).

> I also did some digging to find the original error on xlog replay and it
> was "failed to re-find parent key in "763769" for split pages 21032/21033".
> I'm wondering if this is actually something you can push past with
> pg_resetxlog, or if I need to do a pg_resetxlog and pass in values prior to
> that error point (i guess essentially letting pg_resetxlog do a lookup)...
> thoughts?

You should be able to get out of that by reindexing that index.
(Actually, after you do a pg_resetxlog I think the best is to pg_dump
the whole thing and reload it. That gives you at least the assurance
that your FKs are not b0rked)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support