From: Scott Whitney <swhitney@journyx.com>
To: pgsql-admin@postgresql.org
Subject: PG 8.1.4 not clearing pg_clog
Date: Tue, 9 Oct 2007 12:19:50 -0500
Message-ID: <20071009171615.E8AF83F2398@mail.int.journyx.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: swhitney@journyx.com
Subject: Re: PG 8.1.4 not clearing pg_clog
In-Reply-To: <20071009171615.E8AF83F2398@mail.int.journyx.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox