Recover anything from dropped database?

-----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-----

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

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

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

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/

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.