pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Whitney <swhitney@journyx.com>
To: pgsql-admin@postgresql.org
Subject: PG 8.1.4 not clearing pg_clog
Date: Wed, 10 Oct 2007 11:49:35 -0500
Message-ID: <20071010164600.7FA2B3F2325@mail.int.journyx.com> (raw)

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.




view thread (3+ messages)  latest in thread

Message-ID: <20071010164600.7FA2B3F2325@mail.int.journyx.com>
Permalink:  ../20071010164600.7FA2B3F2325@mail.int.journyx.com/
Also on:    postgresql.org/message-id/20071010164600.7FA2B3F2325@mail.int.journyx.com

 · 

reply

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: <20071010164600.7FA2B3F2325@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