Re: restoring from dump - Mailing list pgsql-admin

Hi Jeff,

Quagmire deepens, it seems...

I tried:
  dropdb <myDB>
and it told me:
  dropdb: database removal failed: ERROR:  could not access status of transaction 139602298
  DETAIL:  Could not open file "pg_clog/0085": No such file or directory.

I went into pg_clog directory and issued:
  ls -lt | head
and it told me:
  -rw-------   1 postgres postgres 163840 Aug 24 09:57 0088
  drwx------  11 postgres postgres   4096 Aug 22 13:56 ..
  drwx------   2 postgres postgres   4096 Jun 30 16:03 .
  -rw-------   1 postgres postgres 262144 Jun  1 20:04 0084
  -rw-------   1 postgres postgres 262144 Apr  4 15:48 0083
  -rw-------   1 postgres postgres 262144 Mar 26 18:25 0082
  -rw-------   1 postgres postgres 262144 Mar 26 01:28 0081
  -rw-------   1 postgres postgres 262144 Mar 25 23:05 0080
  -rw-------   1 postgres postgres 262144 Mar 25 20:39 007F

It seems that 0088 was generated at the time very close to
my issuing dropdb.

What are my options now?

Thank you.

Tena Sakai
tsakai@gallo.ucsf.edu



-----Original Message-----
From: Jeff Frost [mailto:jeff@frostconsultingllc.com]
Sent: Sat 8/23/2008 10:29 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] restoring from dump

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