Restore deleted rows

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

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 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

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