Date: Thu, 10 Mar 2011 09:18:02 -0600 (CST)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-admin@postgresql.org, Scott Whitney <swhitney@journyx.com>
Message-ID: 
 <31022840.14155.1299770282363.JavaMail.root@zimbra.int.journyx.com>
In-Reply-To: 
 <30674693.14151.1299770218945.JavaMail.root@zimbra.int.journyx.com>
Subject: Re: pg_clogs hanging around
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

> > > > > If you have hardware problems like that you have way more
> > > > > problems. You could have corruption (silent) occurring in any
> > > > > of the other
> > > > > database files. Good luck.
> > > >
> > > > I am, in fact, aware of that, but every single machine ever
> > > > manufactured will have hardware problems such at this at some
> > > > point. It stems quite simply from Ohm's Law, one gross
> > > > over-simplification of which is as simple as "if it's got a
> > > > resistor in it, it's going
> > > > to fail at some point", as I'm sure you know. It's merely a
> > > > matter of whether proactive replacement, backups, standby
> > > > systems, etc
> > > > ameliorate that risk. When we had our failure a couple of years
> > > > ago, it did not.
> > > >
> > > > Regardless, my question still stands, and I do, in fact, care
> > > > about ANY database blocking cleanup of clogs (or anything else).
> > > > There's this concept of "if this then what else," and if
> > > > template0 (or
> > > > anyone else) is blocking that ability to properly clean those
> > > > up, what else is possibly screwed up in a similar fashion.
> > > >
> > > > So, what can I do to resolve this issue?
> > > >
> > >
> > > True, entropy rules. I think that you can use "VACUUM FREEZE"
> > > to allow the clogs to be cleaned up.
> > >
> > I thought I was. I've got -F in there. vacuumdb -a -v -F
> >
> 
> Sorry about that, you are. I have never done it from the command-line
> just from psql. I was thinking F for FULL... :)
> 

This is also interesting. I just allowed connections to template0 for the express purpose of vacuuming it, did a full vac on template0, and that did NOT clear up the hanging clogs.

Thanks for the help so far, everyone. I'm going to assuming something is going wrong with my vacuuming on Saturdays (which I can't PROVE until my Saturday window), and take it from there.

I'll reply next week with more info if needed.