Re: Recovering deleted or updated rows

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> I'm looking for a way to recover deleted or old versions of
> accidentally updated rows from a postgres 7.4 database. I've
> verified that the relevant tables haven't been vacuumed since
> the accident took place.

> I was thinking that it might work to patch the clog so that
> the offending transactions look like they have never been
> comitted? Would that work? How could I patch the clog?

By this point the lost rows are no doubt marked HEAP_XMAX_COMMITTED,
which means that hacking the clog entries wouldn't accomplish anything
by itself --- you'd have to go and unset those hint bits, too.

Given that, you might as well not bother with patching clog; it
wouldn't be any more trouble to unset HEAP_XMAX_COMMITTED and set
HEAP_XMAX_INVALID in each tuple you needed to resurrect.

You'd need a custom tool to do either though :-(

regards, tom lane