Recovering deleted or updated rows

----- Original Message -----
From: "Florian G. Pflug" <fgp@phlo.org>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, November 15, 2006 11:19 AM
Subject: [GENERAL] Recovering deleted or updated rows

Hi

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?

If you have any other ideas, please tell me - I'm quite
desperate ;-)

be sure to backup the data files before trying any of my ideas

1) pgfsck - last supported version was iirc 7.3, but with some hacking it
may work for You (When I tried it with 7.4 , it gave some errors about
unknown data types)

2) pg_resetxlog
-select xmin from table where id=id_of_a_badly_updated_row (if that was
updated in one transaction. If not, try to find the minimum xmin)
-stop postmaster
- reset the transaction counter to a value a hundred less than what You get.
-start postmaster, and You should see the old data.
-pg_dump the table . There may be some strange things in the dump - review
it manually.
-stop postmaster
-restore datafiles from backup (pg_resetxlog may have messed up your data)

Greetings
Marcin

"Florian G. Pflug" <fgp@phlo.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

Tom Lane wrote:

"Florian G. Pflug" <fgp@phlo.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 :-(

Thanks for you comments - they have been enlighting as usual ;-)

Fortunatly, the customer changed his mind, and now will manually
reenter the lost data.

Thanks anyways,
Florian Pflug