----- Original Message ----- From: "Florian G. Pflug" <fgp@xxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> 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
