Skip site navigation (1) Skip section navigation (2)

Re: 8.1.8 autovacuum missing databases

From: Ian Westmacott <ianw(at)intellivid(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: 8.1.8 autovacuum missing databases
Date: 2008-05-01 13:01:22
Message-ID: 1209646882.16174.15.camel@vega.intellivid.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Wed, 2008-04-30 at 16:43 -0400, Tom Lane wrote:
> Ian Westmacott <ianw(at)intellivid(dot)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



In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2008-05-01 13:57:28
Subject: Re: 8.1.8 autovacuum missing databases
Previous:From: Jignesh K. ShahDate: 2008-05-01 13:00:25
Subject: Re: help about replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group