PG 8.1.4 not clearing pg_clog

From: "Scott Whitney" <swhitney(at)journyx(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: PG 8.1.4 not clearing pg_clog
Date: 2007-10-09 17:19:50
Message-ID: 20071009171615.E8AF83F2398@mail.int.journyx.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Browse pgsql-admin by date

  From Date Subject
Next Message Lucas Martino 2007-10-09 20:04:35 Schema objects
Previous Message Carol Walter 2007-10-09 16:34:49 pghotbackup