Re: Restore deleted rows

On Monday 27 April 2009 11:17:42 Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Anton Egorov escribió:
> >> I need to recover deleted rows from table. After I delete those rows I
> >> stopped postgres immediately and create tar archive of database. I found
> >> solution
> >> http://archives.postgresql.org/pgsql-hackers/2005-03/msg00965.php, but
> >> is there another (easyer) way to do it?
> >
> > I guess you could figure out the Xid of the transaction that deleted the
> > tuples, and mark it as aborted in pg_clog; you'd also need to reset the
> > hint bits on the tuples themselves. Not necessarily any easier than the
> > above, but at least you don't have to patch Postgres code.
>
> The solution recommended in that message doesn't work anyway --- it will
> consider *all* tuples visible, even ones you don't want. Reversing a
> single transaction, or small number of transactions, as Alvaro suggests
> is much less likely to create a huge mess.
>

We had started down the path of making a function to read deleted tuples from
a table for a DR scenario we were involved with once. The idea was that you
could do something like select * from viewdeletedpages('tablename') t (table
type), which would allow you to see the dead rows. It ended up unnessesary,
so we never finished it, but I still think the utility of such a function
would be high... for most people, if you told them that they could do create
table as select * from viewdeletedttuples(...) t(...) after doing a
mis-placed delete/update, at the cost of having to sift through extra data,
they would make that trade in a heartbeat.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com