> 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,
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?
From: Marcelo Martins [mailto:pglists(at)zeroaccess(dot)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
Linux/Solaris System Administrator
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
> 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
> -----Original Message-----
> From: Jeff Frost [mailto:jeff(at)frostconsultingllc(dot)com]
> Sent: Sat 8/23/2008 10:29 PM
> To: Tena Sakai
> Cc: pgsql-admin(at)postgresql(dot)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
> '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(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 >
> >>> Would you mind confirming if I am understanding you correctly?
> >> Yes, based on the information you've given us, you should be able
> >> restore the entire database (and any other databases that were in
> >> cluster) by first dropping those databases and then issuing the
> >> 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
> >> 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
In response to
pgsql-admin by date
|Next:||From: Bob Lunney||Date: 2008-08-25 05:17:12|
|Subject: PITR wrm-standby startup fails|
|Previous:||From: Tena Sakai||Date: 2008-08-24 17:07:32|
|Subject: Re: restoring from dump|