Skip site navigation (1) Skip section navigation (2)

Re: accidental drop table recoverable?

From: Carol Walter <walterc(at)indiana(dot)edu>
To: Paul Libbrecht <paul(at)activemath(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-admin(at)postgresql(dot)org
Subject: Re: accidental drop table recoverable?
Date: 2008-07-14 16:54:46
Message-ID: E319316C-559E-4D3C-AC23-269AAFE2AD0D@indiana.edu (view raw or flat)
Thread:
Lists: pgsql-admin
I think what you're talking about here is physical delete vs. logical  
delete.  I may be blowing smoke here because I haven't been using  
postgres that long.  Other systems I've worked with mark a table or  
file as deleted, but some operation like vacuum in this case is  
required to actually delete the data and in all actuality it is still  
really there till you overwrite it.  When it's just marked as deleted  
the system will overwrite other areas first but will use the deleted  
file or tables space as a last resort.  If you have the right fancy  
software you can get it back.  When it's physically deleted the  
system will use the space as needed and overwrite as needed.  You can  
still get it back with the right fancy software, but it takes fancier  
software and it's fraught with a lot more hazards.

Carol

On Jul 14, 2008, at 11:29 AM, Paul Libbrecht wrote:

>
> Le 14-juil.-08 à 16:47, Tom Lane a écrit :
>
>> You can't really "rollback" a DROP TABLE --- that corresponds  
>> directly
>> to a filesystem remove() call, and no amount of fooling around  
>> with the
>> database state will undo that.
>
> That is dark.
> I read yesterday night that actually a vacuum was advised everyday  
> since otherwise there was no actual deletion. So you are telling me  
> that, however, drop-table does really go to deletion right away?
>
> I'm running 7.4.5 btw.
>
>> If you have filesystem tools that will resurrect the deleted files  
>> for
>> you, you could probably put them back into the database.  My  
>> inclination
>> would be not to try to "roll back" anything, but create new tables  
>> with
>> the identical column sets to the old ones (but no indexes)
>
> this can be done easily.
> But the filesystem resurrect I am doubting of. I'll hunt.
>
> thanks!
>
> paul
>
>> and then rename the recovered files into place to match the new  
>> tables'
>> relfilenode values.
>> After which, a dump and reload would be prudent to
>> make sure everything's really kosher.  (Actually, copying the data  
>> into
>> newly created tables should be enough for that.)
>
> sure!


In response to

pgsql-admin by date

Next:From: Scott MarloweDate: 2008-07-14 17:09:39
Subject: Re: accidental drop table recoverable?
Previous:From: Paul LibbrechtDate: 2008-07-14 15:29:08
Subject: Re: accidental drop table recoverable?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group