Обсуждение: Recover anything from dropped database?
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
-----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.