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.