Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B518C9F99E0 for ; Wed, 10 Oct 2007 13:46:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 92227-07 for ; Wed, 10 Oct 2007 13:46:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227]) by postgresql.org (Postfix) with ESMTP id B28B49FA53D for ; Wed, 10 Oct 2007 13:46:09 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.int.journyx.com (Postfix) with ESMTP id 6F1923F2361 for ; Wed, 10 Oct 2007 11:46:06 -0500 (CDT) X-Virus-Scanned: amavisd-new at Received: from mail.int.journyx.com ([127.0.0.1]) by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id EixfqhwExHKt for ; Wed, 10 Oct 2007 11:46:00 -0500 (CDT) Received: from whitneys (dhcp-100-201.int.journyx.com [192.168.100.201]) by mail.int.journyx.com (Postfix) with ESMTP id 7FA2B3F2325 for ; Wed, 10 Oct 2007 11:46:00 -0500 (CDT) From: "Scott Whitney" To: Subject: PG 8.1.4 not clearing pg_clog Date: Wed, 10 Oct 2007 11:49:35 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 Thread-Index: AcgKmJkLvJT3IwRlSAivyTx+CSZCcgAxNynQ Message-Id: <20071010164600.7FA2B3F2325@mail.int.journyx.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/79 X-Sequence-Number: 26993 Sorry if this one is a repost, folks. I didn't see it come to my box or show up on the archive on the web. I've seen several posts on this issue in the past, but none seems to address my situation. In my pg_clog directory, I have 225 files dating back to August 8th, when I installed this PostgreSQL server. In my pg_xlog directory, I have 128 files dating back to October 8th. Autovacuum is on. Settings are as follows: autovacuum = on # enable autovacuum subprocess? autovacuum_naptime = 3 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 400 # min # of tuple updates before # vacuum autovacuum_analyze_threshold = 200 # min # of tuple updates before # analyze autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze Using: SELECT datname, age(datfrozenxid) FROM pg_database order by age; The ages of my frozenxids are in a range from: 6/9/1977 (this is strange..I have 4 dbs listing that, including template0 and 2 of my own. The difference of those databases is that those 3 (not including template0) are owned by a different user. Then a range from 1/11/2004 through 6/2/2004. There are 217 of these. Specifically, some examples are: datname | age -------------------+------------ template0 | 234824829 postgres | 1073922288 template1 | 1073923008 Which I assume to be epoch dates and thusly converted. If "today" is Saturday, a cronjob runs this command runs on each database: PGCMD = 'vacuumdb -f -v -z %s' where %s is each database in the system. I don't have the log at the moment to prove it ran Saturday, but, yeah it does. So... I'm performing a full vacuum on each database in the system every Saturday. My pg_clog files date back to August 8th. What am I doing wrong? Thanks, Scott Whitney Journyx, Inc.