Обсуждение: Recover anything from dropped database?

Поиск
Список
Период
Сортировка

Recover anything from dropped database?

От
"John D. Burger"
Дата:
With a slip of the keyboard, I just dropped a database I'd like to
have back.  I don't have PITR or anything turned on - if nothing else
has been done to the cluster since then, is there any way to recover
anything at all?

Thanks.

- John Burger
   MITRE

Re: Recover anything from dropped database?

От
Ron Johnson
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 14:59, John D. Burger wrote:
> With a slip of the keyboard, I just dropped a database I'd like to have
> back.  I don't have PITR or anything turned on - if nothing else has

And no backups?

> been done to the cluster since then, is there any way to recover
> anything at all?


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF03qdS9HxQb37XmcRAjt3AKCsk20FV9UJCCz/65MTcK8pB8fyDACgk/qi
gyfeMr0Ba5yff+Sx08kRoVk=
=uGmg
-----END PGP SIGNATURE-----

Re: Recover anything from dropped database?

От
"John D. Burger"
Дата:
> John D. Burger wrote:
>> With a slip of the keyboard, I just dropped a database I'd like to
>> have
>> back.  I don't have PITR or anything turned on - if nothing else has
>
> And no backups?
>
>> been done to the cluster since then, is there any way to recover
>> anything at all?

I have a backup as of last night, but I'd like to recover something
more recent if I can.  If I'm stuck with the backup, I can just stop
the postmaster, drop the whole PG directory into place from the
backup, and restart, yes?

Any advice welcome.  By the way, this is PG 7.4.

Thanks.

- John Burger

Re: Recover anything from dropped database?

От
"John D. Burger"
Дата:
> I have a backup as of last night, but I'd like to recover something
> more recent if I can.  If I'm stuck with the backup, I can just
> stop the postmaster, drop the whole PG directory into place from
> the backup, and restart, yes?

I presume from the near-deafening silence there's nothing else I can
do, which is no surprise, but I'd still like confirmation about how
to restore the backup.

(It turns out I can recover the changes since the backup annother
way, since they all happened through interaction with CGI scripts,
luckily GET rather than POST - I can "replay" the relevant URLs
grepped from the web server log.)

Still, I'm trying to figure out how to avoid my screw-up in the
future.  I'm not that familiar with the permissions stuff - is there
any way to take away "drop DB privileges" from a user, or even all
users?  Again, this is PG 7.4.

Thanks.

- John Burger
   MITRE

Re: Recover anything from dropped database?

От
Erik Jones
Дата:
John D. Burger wrote:
>> I have a backup as of last night, but I'd like to recover something
>> more recent if I can.  If I'm stuck with the backup, I can just stop
>> the postmaster, drop the whole PG directory into place from the
>> backup, and restart, yes?
>
> I presume from the near-deafening silence there's nothing else I can
> do, which is no surprise, but I'd still like confirmation about how to
> restore the backup.
>
> (It turns out I can recover the changes since the backup annother way,
> since they all happened through interaction with CGI scripts, luckily
> GET rather than POST - I can "replay" the relevant URLs grepped from
> the web server log.)
>
> Still, I'm trying to figure out how to avoid my screw-up in the
> future.  I'm not that familiar with the permissions stuff - is there
> any way to take away "drop DB privileges" from a user, or even all
> users?  Again, this is PG 7.4.
AFAIK, once a database is dropped, it's gone.  That's kinda the point in
dropping it.  So, yeah, you're backup is the way to go.  Also, since
you're going to have start from a fresh restore of your backup, I'd
recommend upgrading to a more recent version of Postgres, at least 8.1.x
if not the most recent which is 8.2.3.

As  far as your questions regarding limiting users' permissions, read
the chapter on Database Roles and Priveledges from the docs:
http://www.postgresql.org/docs/8.2/interactive/user-manag.html


Re: Recover anything from dropped database?

От
Peter Eisentraut
Дата:
John D. Burger wrote:
> Still, I'm trying to figure out how to avoid my screw-up in the
> future.

The good old "don't do everything as root" comes to mind.

> I'm not that familiar with the permissions stuff - is there
> any way to take away "drop DB privileges" from a user, or even all
> users?  Again, this is PG 7.4.

There is no specific "drop database" privilege, but only owners or
superusers can drop things.  So do your general transactional
operations as some other user.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Recover anything from dropped database?

От
Bruno Wolff III
Дата:
On Thu, Feb 15, 2007 at 10:53:48 -0500,
  "John D. Burger" <john@mitre.org> wrote:
>
> I presume from the near-deafening silence there's nothing else I can
> do, which is no surprise, but I'd still like confirmation about how
> to restore the backup.
>
> (It turns out I can recover the changes since the backup annother
> way, since they all happened through interaction with CGI scripts,
> luckily GET rather than POST - I can "replay" the relevant URLs
> grepped from the web server log.)

It wasn't entirely clear what you wanted to accomplish. If you had mentioned
needing find at least some of the transactions that occured, then you might
have got some suggestions along the lines of imaging the disk to capture
data from teh recently freed blocks. There wouldn't be an automated way to
get the data back into the database, but you might have been able to find
some things out.

However, the web server logs are probably going to give you what you want
more reliably than grepping through the freed blocks, so there isn't any point
in going there.