Re: restoring from dump - Mailing list pgsql-admin

Hi Marcelo,

> What happened to that clog file? was it deleted?
I assume you mean 0088.  I think it got created as
a result of my executing (and failing) "dropdb"
command.  Judging from the timestamp, to say otherwise
is not plausible.

> where is 0086, 0087?
I have no idea.  We have had a series of power outages
and maybe they got lost as a result.  I am not convinced,
however.

Instead of your dd suggestion, I thought of copying
0084 and calling it 0085.  I have no idea what consiquence
there might be.  Perhaps, you can comment?

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu




-----Original Message-----
From: Marcelo Martins [mailto:pglists@zeroaccess.org]
Sent: Sun 8/24/2008 12:58 PM
To: Tena Sakai
Subject: Re: [ADMIN] restoring from dump


What happened to that clog file ? was it deleted ?
You could try re-creating it with zero contents as a last resort 
though ..  the transaction will be lost too.

hmm where is 0086, 0087 ?

postgres~$ dd if=/dev/zero of=/var/lib/pgsql/data/pg_clog/0085 bs=256K 
count=1

Marcelo
Linux/Solaris System Administrator
http://www.zeroaccess.org

On Aug 24, 2008, at 12:07 PM, Tena Sakai wrote:

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