Hi, I downloaded the latest release (8.3.3), followed the instruction on the manual to install and restored the file I had made by pg_dumpall, thereby bypassing the trouble with files in pg_clog directory.
I just read a bit about these files. They are made as a result of autovacuum. Does anybody have notion as to how long they should be kept, what recycling policy there ought to be, etc? Regards, Tena Sakai [EMAIL PROTECTED] -----Original Message----- From: Marcelo Martins [mailto:[EMAIL PROTECTED] Sent: Mon 8/25/2008 7:06 AM To: Tena Sakai Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] restoring from dump 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 > [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 > > > > > > >