Thread: Restore deleted rows

Hi!<br /><br />I need to recover deleted rows from table. After I delete those rows I stopped postgres immediately and
createtar archive of database. I found solution <a
href="http://archives.postgresql.org/pgsql-hackers/2005-03/msg00965.php">http://archives.postgresql.org/pgsql-hackers/2005-03/msg00965.php</a>,
butis there another (easyer) way to do it?<br /> 

Anton Egorov escribió:
> Hi!
> 
> 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.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.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.
        regards, tom lane

On Monday 27 April 2009 11:17:42 Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.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

On Wednesday 29 April 2009 14:03:14 Dimitri Fontaine wrote:
> Hi,
>
> On Tuesday 28 April 2009 20:43:38 Robert Treat wrote:
> > 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.
>
> There has been another idea proposed to solve this problem:
>   http://archives.postgresql.org/pgsql-hackers/2009-02/msg00117.php
>
> The idea is to have VACUUM not discard the no more visible tuples but store
> them on a specific fork (which you'll want to have on a WORM (cheap)
> tablespace, separate issue).

Sounds similar to Oracle's undo logs. 

> Then you want to be able to associate the tuple xid info with a timestamptz
> clock, which could be done thanks to txid and txid_snapshot by means of a
> ticker daemon. PGQ from Skytools has such a daemon, a C version is being
> prepared for the 3.0 release (alpha1 released).
>
> Hannu said:
> >Reintroducing keeping old tuples "forever" would also allow us to bring
> >back time travel feature, that is
> >
> >SELECT .... AS OF 'yesterday afternoon'::timestamp;
>
> It could be that there's a simpler way to implement the feature than
> provide a ticker daemon (one more postmaster child), but the linked thread
> show some other use cases of an integrated ticker. I know we use PGQ alone
> here to obtain reliable batches as presented at Prato:
>   http://wiki.postgresql.org/wiki/Image:Prato_2008_pgq_batches.pdf
>

Interesting.  

Something like flashback queries would certaily be nice, and it's interesting 
that we have most of the machinery to do this stuff already, we just need to 
spruce it up a little. 

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

Hi,

On Tuesday 28 April 2009 20:43:38 Robert Treat wrote:
> 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.

There has been another idea proposed to solve this problem:
http://archives.postgresql.org/pgsql-hackers/2009-02/msg00117.php

The idea is to have VACUUM not discard the no more visible tuples but store
them on a specific fork (which you'll want to have on a WORM (cheap)
tablespace, separate issue).
Then you want to be able to associate the tuple xid info with a timestamptz
clock, which could be done thanks to txid and txid_snapshot by means of a
ticker daemon. PGQ from Skytools has such a daemon, a C version is being
prepared for the 3.0 release (alpha1 released).

Hannu said:
>Reintroducing keeping old tuples "forever" would also allow us to bring
>back time travel feature, that is
>
>SELECT .... AS OF 'yesterday afternoon'::timestamp;

It could be that there's a simpler way to implement the feature than provide a
ticker daemon (one more postmaster child), but the linked thread show some
other use cases of an integrated ticker. I know we use PGQ alone here to
obtain reliable batches as presented at Prato: http://wiki.postgresql.org/wiki/Image:Prato_2008_pgq_batches.pdf

Regards,
--
dim

On Wed, Apr 29, 2009 at 7:03 PM, Dimitri Fontaine
<dfontaine@hi-media.com> wrote:
> The idea is to have VACUUM not discard the no more visible tuples but store
> them on a specific fork (which you'll want to have on a WORM (cheap)
> tablespace, separate issue).
> Then you want to be able to associate the tuple xid info with a timestamptz
> clock, which could be done thanks to txid and txid_snapshot by means of a
> ticker daemon. PGQ from Skytools has such a daemon, a C version is being
> prepared for the 3.0 release (alpha1 released).

I think you would use the xid "epoch" which the txid data type stores.
You would need a mapping somewhere to translate timestamps to
snapshots and the epoch to use for that snapshot.

There's a tricky problem of how to find the old tuple values in the
new fork. You can't just store them in the same ctid slot because you
could have many with the same ctid. I suspect you might be able to get
away with making the new fork a btree with the ctid as the key.

-- 
greg