DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Y8yyUFq6ZLDpf+wiGXBk3UB5xLJvqzgIduCrwgg+EkA6DuF7UBoDWxD6jW1ANucb23N7ylh1doxWg8vkHCPpC3F0g3OS837CduP0Tby4IeYaNE5JASh8dLiiahL9Tzz/sfMpyJPMJvXp6v81Q7drmnsIVDLPAxHSY5U265UahmE=;
References: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
	<5064D7BC.1000709@pinpointresearch.com>
	<1348797684.2846.YahooMailNeo@web125904.mail.ne1.yahoo.com>
	<10417.1348798479@sss.pgh.pa.us>
Message-ID: <1348799034.68119.YahooMailNeo@web125902.mail.ne1.yahoo.com>
Date: Thu, 27 Sep 2012 19:23:54 -0700 (PDT)
From: Greg Williamson <gwilliamson39@yahoo.com>
Reply-To: Greg Williamson <gwilliamson39@yahoo.com>
Subject: Re: Database size stays constant but disk space keeps shrinking --
	postgres 9.1
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <10417.1348798479@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Tom --=0A=0A----- Original Message -----=0A> From: Tom Lane <tgl@sss.pgh.pa=
.us>=0A> To: Greg Williamson <gwilliamson39@yahoo.com>=0A> Cc: "pgsql-admin=
@postgresql.org" <pgsql-admin@postgresql.org>=0A> Sent: Thursday, September=
 27, 2012 7:14 PM=0A> Subject: Re: [ADMIN] Database size stays constant but=
 disk space keeps shrinking -- postgres 9.1=0A> =0A>G reg Williamson <gwill=
iamson39@yahoo.com> writes:=0A>>>  Have you checked to see if there are any=
 processes that have open =0A> handles to=0A>>>  deleted files (lsof -X | g=
rep deleted).=0A> =0A>>  lsof -X | grep deleted | wc -l=0A> =0A>>  shows: 8=
35 such files.=0A> =0A>>  A couple:=0A>>  postgres=A0  2540 postgres=A0  50=
u=A0 =A0 =A0 REG=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 8,3=A0 =A0  409600=A0 =A0 =
=A0 =0A> 93429 /var/lib/postgresql/9.1/main/base/2789=0A>>  200/11816 (dele=
ted)=0A>>  postgres=A0  2540 postgres=A0  51u=A0 =A0 =A0 REG=A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 8,3=A0  18112512=A0 =0A> 49694570 /var/lib/postgresql/9.1/=
main/base/2789=0A>>  200/2791679 (deleted)=0A>>  <...>=0A> =0A> So, which p=
rocesses are holding these open, and what are they doing=0A> exactly?=A0 Le=
t's see output from ps and pg_stat_activity, maybe even=0A> attach to them =
with gdb and get stack traces.=0A> =0A>>  We've a planned restart scheduled=
 soon which will let me find any=0A>>  scripts that might be keeping things=
 open,=0A> =0A> A restart will destroy all the evidence, so let's not be in=
 a hurry=0A> to do that before we've identified what's happening.=0A> =0A> =
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane=0A> =0A=0AThanks for the su=
ggestions -- I'll post back when I have more info. Many of these do not see=
m to have a link to any identifiable process that is still running, but som=
e do and they have pointed me away from the hourly drop / rebuild, at least=
 for now. Looks like the stats database may be the issue.=0A=0AGreg W.=0A