Thread

1. Restore deleted rows

   Anton Egorov <anton.egoroff@gmail.com> — 2009-04-26T18:41:15Z

   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?
   

2. Re: Restore deleted rows

   Alvaro Herrera <alvherre@commandprompt.com> — 2009-04-27T14:46:54Z

   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.
   
   

3. Re: Restore deleted rows

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-04-27T15:17:42Z

   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
   
   

4. Re: Restore deleted rows

   Robert Treat <xzilla@users.sourceforge.net> — 2009-04-28T18:43:38Z

   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
   
   

5. Re: Restore deleted rows

   Dimitri Fontaine <dfontaine@hi-media.com> — 2009-04-29T18:03:14Z

   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
   

6. Re: Restore deleted rows

   Robert Treat <xzilla@users.sourceforge.net> — 2009-05-01T03:00:55Z

   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
   
   

7. Re: Restore deleted rows

   Gregory Stark <stark@enterprisedb.com> — 2009-05-01T10:01:34Z

   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