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.