Thread: big problem

Hi all,

I couldn't find anything related to my problem on web or irc, so i'm
posting here.

I deleted valuable data from wrong table :) pretty common problem i
think. Guy on #postgresql at freenode told me that my data is still
there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on
fbsd, i dont' use any special config and pg_xlog is fine. I hope there
is a solution :)

Thanks for answer and best regards,
Bostjan

> I deleted valuable data from wrong table :) pretty common problem i
> think. Guy on #postgresql at freenode told me that my data is still
> there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on
> fbsd, i dont' use any special config and pg_xlog is fine. I hope there
> is a solution :)

That guy on IRC is probably me.  I told him to take a copy of his data 
dir for safekeeping, and not to vacuum.  I don't know how to get a look 
at old row versions, however.  It's new data with no backup, and no PITR 
running.

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> I deleted valuable data from wrong table :) pretty common problem i
>> think. Guy on #postgresql at freenode told me that my data is still
>> there, but tricky part is how to undo my delete. I'm using pg 7.4.7 on
>> fbsd, i dont' use any special config and pg_xlog is fine. I hope there
>> is a solution :)

> That guy on IRC is probably me.  I told him to take a copy of his data 
> dir for safekeeping, and not to vacuum.  I don't know how to get a look 
> at old row versions, however.  It's new data with no backup, and no PITR 
> running.

The easiest avenue might be to back up the XID counter with
pg_resetxlog.  You'd have to dump and restore the table of course, else
the rows will go missing again as soon as the XID climbs up past the
transaction that deleted 'em.
        regards, tom lane