PG 8.1.4 not clearing pg_clog - Mailing list pgsql-admin

From Scott Whitney
Subject PG 8.1.4 not clearing pg_clog
Date
Msg-id 20071009171615.E8AF83F2398@mail.int.journyx.com
Whole thread Raw
List 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.


pgsql-admin by date:

Previous
From: Carol Walter
Date:
Subject: pghotbackup
Next
From: "Lucas Martino"
Date:
Subject: Schema objects