pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedaccidental drop table recoverable?
5+ messages / 4 participants
[nested] [flat]
* accidental drop table recoverable?
@ 2008-07-14 13:13 Paul Libbrecht <paul@activemath.org>
2008-07-14 14:47 ` Re: accidental drop table recoverable? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Paul Libbrecht @ 2008-07-14 13:13 UTC (permalink / raw)
To: pgsql-admin
Hello fellow Postgres administrators,
right now we experienced the loss of backups (due to wrong
communication) and a list of accidental drop table statements (due to
wrong database choice).
We sure have clues how to avoid that in the future but right now we do
not seem to find the solution to actually rollback this accidental
drop table.
Having searched around, the only recipe I found was:
http://archives.postgresql.org/pgsql-docs/2004-12/msg00027.php
which is pretty convoluted but seems like it could do the job.
Now... is there a simpler way?
I seem to see a fixed set of files in data/base that seemed to have
been changed at the right time of the drop db... any chance to fiddle
with them?
thanks for hints.
paul
Attachments:
smime.p7s (application/pkcs7-signature, 2.2K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: accidental drop table recoverable?
2008-07-14 13:13 accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
@ 2008-07-14 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-14 15:29 ` Re: accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-07-14 14:47 UTC (permalink / raw)
To: Paul Libbrecht <paul@activemath.org>; +Cc: pgsql-admin
Paul Libbrecht <paul@activemath.org> writes:
> right now we experienced the loss of backups (due to wrong
> communication) and a list of accidental drop table statements (due to
> wrong database choice).
Ouch.
> We sure have clues how to avoid that in the future but right now we do
> not seem to find the solution to actually rollback this accidental
> drop table.
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.
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) 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.)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: accidental drop table recoverable?
2008-07-14 13:13 accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
2008-07-14 14:47 ` Re: accidental drop table recoverable? Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-07-14 15:29 ` Paul Libbrecht <paul@activemath.org>
2008-07-14 16:54 ` Re: accidental drop table recoverable? Carol Walter <walterc@indiana.edu>
2008-07-14 17:09 ` Re: accidental drop table recoverable? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Paul Libbrecht @ 2008-07-14 15:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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!=
Attachments:
smime.p7s (application/pkcs7-signature, 2.2K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: accidental drop table recoverable?
2008-07-14 13:13 accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
2008-07-14 14:47 ` Re: accidental drop table recoverable? Tom Lane <tgl@sss.pgh.pa.us>
2008-07-14 15:29 ` Re: accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
@ 2008-07-14 16:54 ` Carol Walter <walterc@indiana.edu>
1 sibling, 0 replies; 5+ messages in thread
From: Carol Walter @ 2008-07-14 16:54 UTC (permalink / raw)
To: Paul Libbrecht <paul@activemath.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: accidental drop table recoverable?
2008-07-14 13:13 accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
2008-07-14 14:47 ` Re: accidental drop table recoverable? Tom Lane <tgl@sss.pgh.pa.us>
2008-07-14 15:29 ` Re: accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
@ 2008-07-14 17:09 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2008-07-14 17:09 UTC (permalink / raw)
To: Paul Libbrecht <paul@activemath.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Mon, Jul 14, 2008 at 9:29 AM, Paul Libbrecht <paul@activemath.org> 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.
First off, if you're gonna run 7.4.x then you should REALLY be running
the latest 7.4 version, which is 7.4.21. You are missing almost 4
years worth of updates, and there ARE dataloss bugs in 7.4.5 which
could bite you. But that's not the core of your issue here.
When you delete tuples in a table, postgresql doesn't delete the
actual row, it just marks it as deleted / not visible anymore. Same
goes for the corresponding entries in an index.
Dropping or truncating a table is something else. Now, if you do:
begin;
drop table yada;
rollback;
you'll still have your table. Same thing goes for truncate. But once
you commit such a transaction, the table is gone. Keep in mind that
if you don't start a transaction explicitly, then each command you
type in is a transaction unto itself, and that means that a simple
drop table statement commits as soon as it finishes.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-07-14 17:09 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-14 13:13 accidental drop table recoverable? Paul Libbrecht <paul@activemath.org>
2008-07-14 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-14 15:29 ` Paul Libbrecht <paul@activemath.org>
2008-07-14 16:54 ` Carol Walter <walterc@indiana.edu>
2008-07-14 17:09 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox