Обсуждение: how can i view deleted records?
Hello, everybody! How can I view deleted records in table? -- Verba volent, scripta manent Dan Black
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]
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
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
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.
Вложения
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 >
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
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.