Re: Recovering deleted or updated rows

----- Original Message -----
From: "Florian G. Pflug" <fgp(at)phlo(dot)org>
To: <pgsql-general(at)postgresql(dot)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