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 [EMAIL PROTECTED] -----Original Message----- From: Marcelo Martins [mailto:[EMAIL PROTECTED] 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 > [EMAIL PROTECTED] > > > > -----Original Message----- > From: Jeff Frost [mailto:[EMAIL PROTECTED] > 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 > > [EMAIL PROTECTED] > > > > -----Original Message----- > > From: Jeff Frost [mailto:[EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 916-647-6411 FAX: 916-405-4032 > >
