On Sat, 23 Aug 2008, Tena Sakai wrote:
Hi,
At psql prompt, I tried:
drop database myDB;
and it told me:
ERROR: cannot drop the currently open database
Does this mean I have to issue
pg_ctl stop
before I issue
"drop database myDB;"?
But if I do so, then how would I get to psql prompt
at all?
How would I get around this catch-22 situation?
Any advice appreciated.
Just connect to a different database to do your drop. This is what the
'postgres' database is often used for. That's why it is sometimes referred to
as the maintenance DB.
Or you can use the dropdb command. Of course I'd still recommend you rename
the DB till you're sure the restore was successful.
Tena Sakai
tsakai@xxxxxxxxxxxxxx
-----Original Message-----
From: Jeff Frost [mailto:jeff@xxxxxxxxxxxxxxxxxxxxxx]
Sent: Fri 8/22/2008 10:28 PM
To: Tena Sakai
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] restoring from dump
Jeff Frost wrote:
Tena Sakai wrote:
If you want to restore all the databases that were in this
postgresql installation at the time of the backup, then the
best thing to do is drop all those DBs before attempting the
restore.
Yes, the database in question is built and updated continuously
from a several sources. There was a massive power failure,
a series of them, and things got to be a very inconsistent
state and therefore we need to go back to a reliable, trustworthy
backup and then rebuild from there.
What I gather, from your comments, all I have to do would to issue
a psql command:
drop database <dbname>
then repeat what I did from shell prompt, ie.,
zcat <compressed_file> | psql postgres > restore.out 2 > restore.err
Would you mind confirming if I am understanding you correctly?
Yes, based on the information you've given us, you should be able to
restore the entire database (and any other databases that were in the
cluster) by first dropping those databases and then issuing the above
command.
BTW, if you find yourself with an older version of postgresql, this
could be a good opportunity to upgrade. I'm not sure if you mentioned
what version you were using in your original post.
I should also note that you could rename the database instead of
dropping it outright, to make sure your restore is effective before
dropping it.
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032
