Subject: Re: 8.1.8 autovacuum missing databases
From: Ian Westmacott <ianw@intellivid.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@commandprompt.com>, pgsql-admin@postgresql.org
In-Reply-To: <22093.1209588237@sss.pgh.pa.us>
References: <1209499373.18622.47.camel@vega.intellivid.com>
	<20080429203335.GK5652@alvh.no-ip.org>
	<1209503103.18622.68.camel@vega.intellivid.com>
	<20080429211244.GM5652@alvh.no-ip.org>
	<1209560546.5209.12.camel@vega.intellivid.com>
	<20080430161942.GC21286@alvh.no-ip.org>
	<19778.1209572869@sss.pgh.pa.us>
	<1209574908.22509.6.camel@vega.intellivid.com>
	<20185.1209575233@sss.pgh.pa.us>
	<1209576488.22509.9.camel@vega.intellivid.com>
	<22093.1209588237@sss.pgh.pa.us>
Content-Type: text/plain
Organization: IntelliVid Corp.
Date: Thu, 01 May 2008 09:01:22 -0400
Message-Id: <1209646882.16174.15.camel@vega.intellivid.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2008-04-30 at 16:43 -0400, Tom Lane wrote:
> Ian Westmacott <ianw@intellivid.com> writes:
> 499 is the value that those columns would have immediately after initdb,
> in an 8.1 database.  So what this says is that vacuum has never
> succeeded in updating the values at all, in any of your databases.
> It definitely *should* be doing that given the size of the age() values
> you're reporting.  Moreover, after a look through the 8.1.8 source code
> I cannot see how it would not update the values without throwing an
> ERROR or at least a WARNING into the postmaster log.  (What have you got
> log_min_messages set to, anyway?  Maybe the complaint is getting
> suppressed?)

log_min_messages is set to the default (notice), until I reset it to
debug1 a couple of days ago.  I combed back through the logs to initial
installation in Feb.  There are no ERRORs or WARNINGs that are obviously
related.  However, what I did find was a couple of small log files with
timestamps in the future (Jan 2009).

Baffled, I did some digging around and it looks like the user initially
installed this system with the system clock set in Jan 2009.  The system
ran for about 20 minutes, during which time two of our application
databases were created and processed by autovacuum.  Then the user
corrected the time problem.  During that 20 minutes in the future,
autovacuum processed postgres, template1, and two of our databases
(itvtrackdata and itvtrackdatauser).  autovacuum hasn't touched our two
since time was corrected.

I imagine if a database's last process time is in the future, that would
mess up autovacuum's choice algorithm (at the least).  Can I confirm
this is the problem?  Is there a way to fix it short of dump/restore?
Anything else I should worry about in this installation?

I mentioned earlier that I had two installations like this.  The second
one doesn't seem to have any time issues.  But in that case the only
database being skipped is postgres.  Do I need to worry about that?

Thanks,

	--Ian