Re: Recovering deleted or updated rows

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Recovering deleted or updated rows
Date: 2006-11-16 06:54:37
Message-ID: 13149.1163660077@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message SunWuKung 2006-11-16 08:48:08 calculated default
Previous Message John Sidney-Woollett 2006-11-16 06:43:40 Re: ORDER BY