Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 97E382E0044 for ; Wed, 30 Apr 2008 14:01:52 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 23223-04 for ; Wed, 30 Apr 2008 14:01:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mailhost.intellivid.com (mailhost.intellivid.com [64.32.200.11]) by developer.postgresql.org (Postfix) with ESMTP id B37252E0037 for ; Wed, 30 Apr 2008 14:01:48 -0300 (ADT) Received: from [192.168.2.80] (vega.intellivid.com [192.168.2.80]) by newmail.intellivid.com (Postfix) with ESMTP id E82446C637; Wed, 30 Apr 2008 13:01:49 -0400 (EDT) Subject: Re: 8.1.8 autovacuum missing databases From: Ian Westmacott To: Tom Lane Cc: Alvaro Herrera , pgsql-admin@postgresql.org In-Reply-To: <19778.1209572869@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> Content-Type: text/plain Organization: IntelliVid Corp. Date: Wed, 30 Apr 2008 13:01:48 -0400 Message-Id: <1209574908.22509.6.camel@vega.intellivid.com> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/311 X-Sequence-Number: 29075 On Wed, 2008-04-30 at 12:27 -0400, Tom Lane wrote: > Alvaro Herrera writes: > > I still think that the autovac is dying before completing the task. Did > > you investigate whether there are "ERROR" messages coming from > > autovacuum? No PG crashes would happen. There are no ERROR messages at all. Sample log snippet: 2008-04-30 12:03:36 EDT LOG: autovacuum: processing database "template1" 2008-04-30 12:04:36 EDT LOG: autovacuum: processing database "itvtrackdatapos" 2008-04-30 12:05:36 EDT LOG: autovacuum: processing database "template1" 2008-04-30 12:06:36 EDT LOG: autovacuum: processing database "itvtrackdatapos" 2008-04-30 12:07:36 EDT LOG: autovacuum: processing database "template1" 2008-04-30 12:08:36 EDT LOG: autovacuum: processing database "itvtrackdatapos" 2008-04-30 12:09:36 EDT LOG: autovacuum: processing database "template1" 2008-04-30 12:10:36 EDT LOG: autovacuum: processing database "itvtrackdatapos" > I'm noticing though that the 8.1 logic pays attention to both > datvacuumxid and datfrozenxid. Could we see the age() of both of > those columns for all the databases. The are all identical: itvtrackdata=> select datname,age(datfrozenxid),age(datvacuumxid) from pg_database; datname | age | age ------------------+-----------+----------- postgres | 295995059 | 295995059 itvtrackdata | 295995059 | 295995059 itvtrackdatauser | 295995059 | 295995059 itvtrackdatapos | 295995059 | 295995059 template1 | 295995059 | 295995059 template0 | 295995059 | 295995059 (6 rows) itvtrackdata=> --Ian