Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 69DE86501B5 for ; Mon, 25 Aug 2008 11:06:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54094-01 for ; Mon, 25 Aug 2008 11:06:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from app1.ihackr.net (app1.ihackr.net [209.61.157.9]) by postgresql.org (Postfix) with ESMTP id 4307864FD2A for ; Mon, 25 Aug 2008 11:06:36 -0300 (ADT) Received: by app1.ihackr.net (Postfix, from userid 515) id 1061610134D; Mon, 25 Aug 2008 09:06:33 -0500 (CDT) Received: from office100-240.sat.rackspace.com (nat-vlan200.sat.rackspace.com [64.39.1.10]) by app1.ihackr.net (Postfix) with ESMTP id 96DB810086A; Mon, 25 Aug 2008 09:06:31 -0500 (CDT) Cc: Message-Id: From: Marcelo Martins To: "Tena Sakai" In-Reply-To: Content-Type: multipart/alternative; boundary=Apple-Mail-2-847822458 Mime-Version: 1.0 (Apple Message framework v926) Subject: Re: restoring from dump Date: Mon, 25 Aug 2008 09:06:30 -0500 References: <48AF9E8B.9020508@frostconsultingllc.com> <48AFA015.2000909@frostconsultingllc.com> <22746678-86BF-4F63-9F3D-6B822B9FFC47@zeroaccess.org> X-Mailer: Apple Mail (2.926) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/149 X-Sequence-Number: 29994 --Apple-Mail-2-847822458 Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit I never tried that and I would be reluctant on doing so since you will be getting status of another transactions. Marcelo Linux/Solaris System Administrator http://www.zeroaccess.org On Aug 24, 2008, at 4:24 PM, Tena Sakai wrote: > 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 > > 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 > > >>> then repeat what I did from shell prompt, ie., > > >>> zcat | 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 > > Frost Consulting, LLC http://www.frostconsultingllc.com/ > > Phone: 916-647-6411 FAX: 916-405-4032 > > > > > > > --Apple-Mail-2-847822458 Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I never tried that and I would = be reluctant on doing so since you will be getting status of another = transactions.

Marcelo
Linux/Solaris System = Administrator

On Aug 24, 2008, at 4:24 PM, = Tena Sakai wrote:

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]<= br> 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=3D/dev/zero of=3D/var/lib/pgsql/data/pg_clog/0085 bs=3D256K 
= count=3D1

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

=

= --Apple-Mail-2-847822458--