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:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=EJT4dL2WMvMhBGEtjUADPazo9EJMH90yeqHOPNRDV8e9LM37OO1nveD4I9RHu9yf9vtYZREbHwuVRNgJ0AvK5s4enkvSMQVoHtRcx7K0bNmI+UwOr/OxIuMv0if/KmRfISLzaYAsigaBzP3Qmbz0KE4ZbRRWkfwzDAQrLL8Mrsg=;
References: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
	<5064D7BC.1000709@pinpointresearch.com>
Message-ID: <1348797684.2846.YahooMailNeo@web125904.mail.ne1.yahoo.com>
Date: Thu, 27 Sep 2012 19:01:24 -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
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <5064D7BC.1000709@pinpointresearch.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Steve (and others who replied):=0A=0A=0A----- Original Message -----=0A> Fr=
om: Steve Crawford <scrawford@pinpointresearch.com>=0A> To: Greg Williamson=
 <gwilliamson39@yahoo.com>=0A> Cc: "pgsql-admin@postgresql.org" <pgsql-admi=
n@postgresql.org>=0A> Sent: Thursday, September 27, 2012 3:48 PM=0A> Subjec=
t: Re: [ADMIN] Database size stays constant but disk space keeps shrinking =
-- postgres 9.1=0A> =0A> On 09/27/2012 03:05 PM, Greg Williamson wrote:=0A>=
>  Dear list,=0A>> =0A>>  I have a postgres database, 9.1.3, which shows a =
fairly constant amount of =0A> space used by postgres, but total disk space=
 kees shrinking.=0A>>  If I restart postgres the space on my file system re=
turns.=0A>> =0A>>  This cluster is replicated to another; that custer does =
not show this =0A> problem.=0A>> =0A>>  There are two main databases on thi=
s cluster, both using logging. One is =0A> used to accumulate some stats on=
 our systen.=0A>> =0A>>  The other is a slimmed-down version of our product=
ion database, which gets =0A> recreated hourly by a shell script which pull=
s data from remote servers, does a =0A> pg_dump of the resulting 3 gig data=
base, and then drops it.=0A>> =0A>>  I posted a message about ths a few day=
s and got no responses. I am hoping =0A> for better luck this time as this =
is a serious issue,not relayed t vacuuming or =0A> routine admin tasks whic=
h may or may not be happening.=0A>> =0A>> =0A> No answer but some questions=
.=0A> =0A> How are you determining space used by PostgreSQL? From the OS (i=
.e. du on =0A> PostgreSQL's directories or similar) or from PostgreSQL itse=
lf (pg_ system =0A> tables, \dt+, etc.)?=0A>=A0=0A=0Adf -h and the postgres=
 internal functions that show size of relations.=0A=0A> What method of repl=
ication are you using?=0A=0A=0Astreaming replication w/ synchronous commit=
=0A=0A> =0A> Do you have any indication if the issue is only related to whi=
ch database is =0A> causing the issue? Can you stop activity on one of them=
 and see if the problem =0A> persists?=0A=0A=0AIf I suspend the build / dro=
p database process the shrinking of available disk space slows dramatically=
.=0A=0A> =0A> Have you checked to see if there are any processes that have =
open handles to =0A> deleted files (lsof -X | grep deleted). Deleted files =
won't show up in du =0A> but won't release their disk space until the proce=
ss exits. Perhaps a script =0A> or scripts, even one of your hourly ones, t=
hat terminate when the server =0A> restarts? You could save the output of l=
sof and ps immediately before and after =0A> a restart and compare them.=0A=
>=A0=0A=0Alsof -X | grep deleted | wc -l=0A=0Ashows: 835 such files.=0A=0AA=
 couple:=0Apostgres =A0 2540 postgres =A0 50u =A0 =A0 =A0REG =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A08,3 =A0 =A0 409600 =A0 =A0 =A093429 /var/lib/postgresql/=
9.1/main/base/2789=0A200/11816 (deleted)=0Apostgres =A0 2540 postgres =A0 5=
1u =A0 =A0 =A0REG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08,3 =A0 18112512 =A0 49694=
570 /var/lib/postgresql/9.1/main/base/2789=0A200/2791679 (deleted)=0A<...>=
=0A=0Awhile ls shows:=0Aroot@db11:~# ls /var/lib/postgresql/9.1/main/base/ =
| more=0A1=0A11938=0A11946=0A1418400=0A16387=0A16392=0A16402=0A16424=0A1644=
9=0A2047839=0Apgsql_tmp=0A=0AWe've a planned restart scheduled soon which w=
ill let me find any scripts that might be keeping things open, and I'll rev=
iew my script that creates / populates / drops the database hourly.=0A=A0=
=0A=0AThis does not seem to be related to temp tables. Something in the scr=
ipt, perhaps ...=0A=0AFurther suggestions and =A0/ or questions welcome.=0A=
=0AThanks !=0A=0AGreg W.=0A