Thread: how can i view deleted records?

how can i view deleted records?

From
"Dan Black"
Date:
Hello,  everybody!
How  can  I  view  deleted  records  in  table?

--
Verba volent, scripta manent
Dan Black

Re: how can i view deleted records?

From
Robby Russell
Date:
On Apr 29, 2006, at 12:49 PM, Dan Black wrote:

> Hello,  everybody!
> How  can  I  view  deleted  records  in  table?

SELECT * FROM recycle_bin;

;-)

Just kidding... once you delete your records... they are gone.

You could restore your database... assuming that you have a backup.

-Robby


Robby Russell
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting

www.planetargon.com
www.robbyonrails.com

+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4968 [fax]



Re: how can i view deleted records?

From
Steve Atkins
Date:
On Apr 29, 2006, at 4:18 PM, Robby Russell wrote:

>
> On Apr 29, 2006, at 12:49 PM, Dan Black wrote:
>
>> Hello,  everybody!
>> How  can  I  view  deleted  records  in  table?
>
> SELECT * FROM recycle_bin;
>
> ;-)
>
> Just kidding... once you delete your records... they are gone.

That's.... not true.

Deleted (or modified) records don't go away until the space
they use is recycled by the VACUUM command.

However, there's no support in postgresql for any sort of
"time travel", including viewing deleted tuples. The data
is there on the disk, but there is no clean way to view it
via the database.

It's certainly not something a DBA should even think about
(outside of security issues) but deleted tuples are available
in a forensics situation, as long as vacuum hasn't been run.

Cheers,
   Steve


Ответ: how can i view deleted records?

From
"Dan Black"
Date:
Thanks.  I  thought  that  there  are some  standard  utilities  or
sql  request  in  postgres  to  view  deleted  or  modified  tuples.

2006/4/30, Steve Atkins <steve@blighty.com>:
>
> On Apr 29, 2006, at 4:18 PM, Robby Russell wrote:
>
> >
> > On Apr 29, 2006, at 12:49 PM, Dan Black wrote:
> >
> >> Hello,  everybody!
> >> How  can  I  view  deleted  records  in  table?
> >
> > SELECT * FROM recycle_bin;
> >
> > ;-)
> >
> > Just kidding... once you delete your records... they are gone.
>
> That's.... not true.
>
> Deleted (or modified) records don't go away until the space
> they use is recycled by the VACUUM command.
>
> However, there's no support in postgresql for any sort of
> "time travel", including viewing deleted tuples. The data
> is there on the disk, but there is no clean way to view it
> via the database.
>
> It's certainly not something a DBA should even think about
> (outside of security issues) but deleted tuples are available
> in a forensics situation, as long as vacuum hasn't been run.
>
> Cheers,
>    Steve
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


--
Verba volent, scripta manent
Dan Black

Re: how can i view deleted records?

From
Martijn van Oosterhout
Date:
On Sat, Apr 29, 2006 at 07:05:35PM -0700, Steve Atkins wrote:
> >Just kidding... once you delete your records... they are gone.
>
> That's.... not true.
>
> Deleted (or modified) records don't go away until the space
> they use is recycled by the VACUUM command.

Well yes, but with autovacuum you don't know when that might be.

> However, there's no support in postgresql for any sort of
> "time travel", including viewing deleted tuples. The data
> is there on the disk, but there is no clean way to view it
> via the database.

Well, there is a timetravel module which you can enable per table. Just
showing deleted records in general doesn't work well because it
violates all sorts of constraints. If you show deleted records, all of
a sudden your unique indexes arn't unique anymore. Timetravel is
expensive though, which is why it's not by default.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: how can i view deleted records?

From
"Ian Harding"
Date:
There used to be a knob that would allow you to temporarily see
deleted tuples.  Don't know if it's still there.  Sounded kinda
dangerous.

http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php

Also, you could start (now) using PITR so you could simply restore to
the moment before the records you are interested in were deleted.

Good luck,

- Ian

On 4/29/06, Dan Black <fireworker@gmail.com> wrote:
> Hello,  everybody!
> How  can  I  view  deleted  records  in  table?
>
> --
> Verba volent, scripta manent
> Dan Black
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Re: how can i view deleted records?

From
"Jim C. Nasby"
Date:
On Sun, Apr 30, 2006 at 12:33:04PM +0200, Martijn van Oosterhout wrote:
> On Sat, Apr 29, 2006 at 07:05:35PM -0700, Steve Atkins wrote:
> > >Just kidding... once you delete your records... they are gone.
> >
> > That's.... not true.
> >
> > Deleted (or modified) records don't go away until the space
> > they use is recycled by the VACUUM command.
>
> Well yes, but with autovacuum you don't know when that might be.
>
> > However, there's no support in postgresql for any sort of
> > "time travel", including viewing deleted tuples. The data
> > is there on the disk, but there is no clean way to view it
> > via the database.
>
> Well, there is a timetravel module which you can enable per table. Just
> showing deleted records in general doesn't work well because it
> violates all sorts of constraints. If you show deleted records, all of
> a sudden your unique indexes arn't unique anymore. Timetravel is
> expensive though, which is why it's not by default.

There is? The only time travel capability I know of is
http://www.varlena.com/GeneralBits/122.php. There is also the idea of
having vacuum move old tuples to some form of secondary storage instead
of sending them to the bit-bucket.

An interesting alternative would be to allow for starting a transaction
that uses a different XID for reading data than what it would normally
use. Provided vacuum hasn't nuked anything that old you should
theoretically be able to get a consistent view of data, excluding some
things like TRUNCATE.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: how can i view deleted records?

From
Martijn van Oosterhout
Date:
On Thu, May 04, 2006 at 02:42:29PM -0500, Jim C. Nasby wrote:
> There is? The only time travel capability I know of is
> http://www.varlena.com/GeneralBits/122.php. There is also the idea of
> having vacuum move old tuples to some form of secondary storage instead
> of sending them to the bit-bucket.

There's the contrib/spi/timetravel module, though maybe it does
something slightly different.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment