Re: restoring from dump

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.

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Jeff Frost [mailto:jeff(at)frostconsultingllc(dot)com]
Sent: Fri 8/22/2008 10:28 PM
To: Tena Sakai
Cc: pgsql-admin(at)postgresql(dot)org
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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032