Re: broken xlog - recovery plan check

On 24 March 2012 00:45, Colin Taylor <colin(dot)taylor(at)gmail(dot)com> wrote:
> Hi I seem to have an 8.3.9 database with a broken xlog,
>
> PANIC:  heap_insert_redo: invalid max offset number
>
> My plan is to run pg_resetxlog.
> Hopefully it then starts up.
> Test recent data as thoroughly as possible - (script some Select * ' s?)
> If ok -> curse ops and their raid caches
> If not -> curse ops and tell them to recover from backup (v. large and
> therefore very slow process).
>
> Can anyone give me feedback on this plan?

Yes, it's almost certainly corrupted. How old is the backup? I ask
this because if you use pg_resetxlog, it would be a good idea to dump
and restore the database once you get it up and running anyway. This
is because you can't trust that your database will be consistent. I
guess technically it *might* be fine, but you wouldn't know this
unless you went through verifying all your data made sense from a
referential integrity perspective. So it will be a trade-off between
one of:

- restore from an existing backup, losing the data since you last backed up
- doing a dump/restore after resetting xlog to ensure your database is
consistent
- running full checks once you've got your database up and running (or
ignore it and possibly find weird problems later)

Also, PostgreSQL 8.3.9 is over 2 years out of date. I'd recommend
bringing it up to 8.3.18 to take advantage of the hundreds of bug
fixes that have since gone in.

--
Thom