From scott@journyx.com Tue Dec  21 20:34:27 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D07F21337B4F
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 16:34:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 76530-06 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 20:34:21 +0000 (UTC)
X-Greylist: delayed 00:09:20.918444 by SQLgrey-1.7.6
Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227])
	by mail.postgresql.org (Postfix) with ESMTP id 516721337990
	for <pgsql-admin@postgresql.org>; Tue, 21 Dec 2010 16:34:20 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.int.journyx.com (Postfix) with ESMTP id 5CFA07E45A6
	for <pgsql-admin@postgresql.org>; Tue, 21 Dec 2010 14:24:58 -0600 (CST)
X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com
Received: from mail.int.journyx.com ([127.0.0.1])
	by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id yi9iDbesEcuQ for <pgsql-admin@postgresql.org>;
	Tue, 21 Dec 2010 14:24:53 -0600 (CST)
Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5])
	by mail.int.journyx.com (Postfix) with ESMTP id 3A25AC407B
	for <pgsql-admin@postgresql.org>; Tue, 21 Dec 2010 14:24:53 -0600 (CST)
Date: Tue, 21 Dec 2010 14:24:53 -0600 (CST)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: pgsql-admin@postgresql.org
Message-ID: <3784661.2704.1292963093042.JavaMail.root@zimbra.int.journyx.com>
In-Reply-To: 
 <16384123.2701.1292963013966.JavaMail.root@zimbra.int.journyx.com>
Subject: pg_clog not getting cleared
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_2703_1683237.1292963093039"
X-Originating-IP: [192.168.100.188]
X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0
	(Win)/6.0.6_GA_2331.RHEL4)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/151
X-Sequence-Number: 36380

------=_Part_2703_1683237.1292963093039
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Hello. I posted this once before, but I'm encountering it again. 

Each Saturday, I run: vacuumdb -a -v. I have autovac on all the time. 

However, my pg_clog directory lists clog files going back to July. 

This is pg 8.4.4 on Linux (CentOS 5.5). 

I know this isn't a whole lot of information at this time. What more would be useful in finding out what's failing and where? 

I seem to recall that doing vacuumdb -f -a -v resolved it, which would seem to point to a database not being vacuumed, but that's sort of the point of the -a switch, right? I'm running this as the "postgres" user himself. 



------=_Part_2703_1683237.1292963093039
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: Times New Roman; font-size: 12pt; color: #000000'>Hello. I posted this once before, but I'm encountering it again.<br><br>Each Saturday, I run: vacuumdb -a -v. I have autovac on all the time.<br><br>However, my pg_clog directory lists clog files going back to July.<br><br>This is pg 8.4.4 on Linux (CentOS 5.5).<br><br>I know this isn't a whole lot of information at this time. What more would be useful in finding out what's failing and where?<br><br>I seem to recall that doing vacuumdb -f -a -v resolved it, which would seem to point to a database not being vacuumed, but that's sort of the point of the -a switch, right? I'm running this as the "postgres" user himself.<br><br><br></div></body></html>
------=_Part_2703_1683237.1292963093039--



From Kevin.Grittner@wicourts.gov Tue Dec  21 21:22:10 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 32BDC1337B5B
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 17:22:10 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 82184-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Tue, 21 Dec 2010 21:22:02 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 5DA7C1337B53
	for <pgsql-admin@postgresql.org>; Tue, 21 Dec 2010 17:22:02 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Tue, 21 Dec 2010 15:22:01 -0600
Message-Id: <4D10C61202000025000388ED@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Tue, 21 Dec 2010 15:21:54 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Scott Whitney" <swhitney@journyx.com>,
	 <pgsql-admin@postgresql.org>
Subject: Re: pg_clog not getting cleared
References: <16384123.2701.1292963013966.JavaMail.root@zimbra.int.journyx.com>
	<3784661.2704.1292963093042.JavaMail.root@zimbra.int.journyx.com>
In-Reply-To: <3784661.2704.1292963093042.JavaMail.root@zimbra.int.journyx.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/152
X-Sequence-Number: 36381

Scott Whitney <scott@journyx.com> wrote:
 
> Each Saturday, I run: vacuumdb -a -v. I have autovac on all the
> time.
> 
> However, my pg_clog directory lists clog files going back to
> July.
> 
> This is pg 8.4.4 on Linux (CentOS 5.5).
> 
> I know this isn't a whole lot of information at this time. What
> more would be useful in finding out what's failing and where?
 
Nothing is failing -- clog is needed to track what has committed
until transactions are "frozen".
 
> I seem to recall that doing vacuumdb -f -a -v resolved it
 
Are you sure that wasn't -F rather than -f ?  The uppercase switch
freezes tuples, which would allow clog to be cleaned up.  This
shouldn't normally be needed, unless you have some other reason to
need aggressive freezing.
 
-Kevin



From scott@journyx.com Wed Dec  22 21:13:30 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 237B81337CDF
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 22 Dec 2010 17:13:30 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 10844-02-3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 22 Dec 2010 21:13:22 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227])
	by mail.postgresql.org (Postfix) with ESMTP id 9B0421337BE5
	for <pgsql-admin@postgresql.org>; Wed, 22 Dec 2010 17:12:59 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.int.journyx.com (Postfix) with ESMTP id 9616AC411D;
	Wed, 22 Dec 2010 15:12:44 -0600 (CST)
X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com
Received: from mail.int.journyx.com ([127.0.0.1])
	by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id C3CdfYeJWBnJ; Wed, 22 Dec 2010 15:12:39 -0600 (CST)
Received: from mail.int.journyx.com (zimbra.int.journyx.com [192.168.100.5])
	by mail.int.journyx.com (Postfix) with ESMTP id 3225EC4081;
	Wed, 22 Dec 2010 15:12:39 -0600 (CST)
Date: Wed, 22 Dec 2010 15:12:39 -0600 (CST)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Scott Whitney <swhitney@journyx.com>, pgsql-admin@postgresql.org
Message-ID: <21700506.3248.1293052359046.JavaMail.root@zimbra.int.journyx.com>
In-Reply-To: <3773635.3245.1293052309856.JavaMail.root@zimbra.int.journyx.com>
Subject: Re: pg_clog not getting cleared
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3247_1608438.1293052359044"
X-Originating-IP: [173.174.48.227]
X-Mailer: Zimbra 6.0.6_GA_2331.RHEL4 (ZimbraWebClient - FF3.0
	(Win)/6.0.6_GA_2331.RHEL4)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/166
X-Sequence-Number: 36395

------=_Part_3247_1608438.1293052359044
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

I'm certain it was -f, not -F, since it's still in the script I use, albeit commented out. 

I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I've got about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted. 

I'll try the -F on Sat and see if that resolves the issue. I'll also have logs available at that time, assuming all goes well. 

Thanks for the advice. 




Scott Whitney <scott@journyx.com> wrote: 

> Each Saturday, I run: vacuumdb -a -v. I have autovac on all the 
> time. 
> 
> However, my pg_clog directory lists clog files going back to 
> July. 
> 
> This is pg 8.4.4 on Linux (CentOS 5.5). 
> 
> I know this isn't a whole lot of information at this time. What 
> more would be useful in finding out what's failing and where? 

Nothing is failing -- clog is needed to track what has committed 
until transactions are "frozen". 

> I seem to recall that doing vacuumdb -f -a -v resolved it 

Are you sure that wasn't -F rather than -f ? The uppercase switch 
freezes tuples, which would allow clog to be cleaned up. This 
shouldn't normally be needed, unless you have some other reason to 
need aggressive freezing. 

-Kevin 


------=_Part_3247_1608438.1293052359044
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: Times New Roman; font-size: 12pt; color: #000000'=
>I'm certain it was -f, not -F, since it's still in the script I use, albei=
t commented out.<br><br>I understand the purpose of the clogs, but I would =
think that the transactions would have been frozen on all dbs (I've got abo=
ut 300 in my cluster) by now. My logs go back to July 13th which, I think, =
is when the server was last restarted.<br><br>I'll try the -F on Sat and se=
e if that resolves the issue. I'll also have logs available at that time, a=
ssuming all goes well.<br><br>Thanks for the advice.<br><br><hr><blockquote=
 style=3D"border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; paddin=
g-left: 5px;">Scott Whitney &lt;scott@journyx.com&gt; wrote:<br>&nbsp;<br>&=
gt; Each Saturday, I run: vacuumdb -a -v. I have autovac on all the<br>&gt;=
 time.<br>&gt; <br>&gt; However, my pg_clog directory lists clog files goin=
g back to<br>&gt; July.<br>&gt; <br>&gt; This is pg 8.4.4 on Linux (CentOS =
5.5).<br>&gt; <br>&gt; I know this isn't a whole lot of information at this=
 time. What<br>&gt; more would be useful in finding out what's failing and =
where?<br>&nbsp;<br>Nothing is failing -- clog is needed to track what has =
committed<br>until transactions are "frozen".<br>&nbsp;<br>&gt; I seem to r=
ecall that doing vacuumdb -f -a -v resolved it<br>&nbsp;<br>Are you sure th=
at wasn't -F rather than -f ? &nbsp;The uppercase switch<br>freezes tuples,=
 which would allow clog to be cleaned up. &nbsp;This<br>shouldn't normally =
be needed, unless you have some other reason to<br>need aggressive freezing=
.<br>&nbsp;<br>-Kevin<br></blockquote><br></div></body></html>
------=_Part_3247_1608438.1293052359044--



From tgl@sss.pgh.pa.us Wed Dec  22 21:38:33 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C3BAA1337B9C
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 22 Dec 2010 17:38:33 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 89943-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 22 Dec 2010 21:38:26 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 427A01337B96
	for <pgsql-admin@postgresql.org>; Wed, 22 Dec 2010 17:38:25 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oBMLcCmC010706;
	Wed, 22 Dec 2010 16:38:12 -0500 (EST)
To: Scott Whitney <swhitney@journyx.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>, pgsql-admin@postgresql.org
Subject: Re: pg_clog not getting cleared 
In-reply-to: 
 <21700506.3248.1293052359046.JavaMail.root@zimbra.int.journyx.com>
References: <21700506.3248.1293052359046.JavaMail.root@zimbra.int.journyx.com>
Comments: In-reply-to Scott Whitney <scott@journyx.com>
	message dated "Wed, 22 Dec 2010 15:12:39 -0600"
Date: Wed, 22 Dec 2010 16:38:12 -0500
Message-ID: <10705.1293053892@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/167
X-Sequence-Number: 36396

Scott Whitney <scott@journyx.com> writes:
> I understand the purpose of the clogs, but I would think that the transactions would have been frozen on all dbs (I've got about 300 in my cluster) by now. My logs go back to July 13th which, I think, is when the server was last restarted. 

It doesn't really try to remove clog entries that are younger than
vacuum_freeze_table_age (see also vacuum_freeze_min_age).  At two bits
per transaction, the general feeling is that eating the disk space is
better than forcing full-database vacuums more often.  But if you're
hot to have the space released sooner, those are the knobs to frob.

			regards, tom lane