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=B8xqaFIxZqtNpP80/dXvz6wromnxeGiJ3lGm80X3xmQEytB6u86EQOFetw0VD67BPz7lgTxC98ZGUNGT0oVYoLsK3xyE15NHmYCos8kMVk0KtxLuqthrixHyrXysBPh0vNwlbVQax8CFj6oLcoZMF4qYlkUndjpVMMtcrOyFhnw=;
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>
	<1348799034.68119.YahooMailNeo@web125902.mail.ne1.yahoo.com>
Message-ID: <1349215343.78143.YahooMailNeo@web125901.mail.ne1.yahoo.com>
Date: Tue, 2 Oct 2012 15:02:23 -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: <1348799034.68119.YahooMailNeo@web125902.mail.ne1.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I've done some more testing and the problem seems to be repmgr itself.=0A=
=0AA few details below...=0A=0A=0A----- Original Message -----=0A> From: Gr=
eg Williamson <gwilliamson39@yahoo.com>=0A> To: Tom Lane <tgl@sss.pgh.pa.us=
>=0A> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>=0A> Sen=
t: Thursday, September 27, 2012 7:23 PM=0A> Subject: Re: [ADMIN] Database s=
ize stays constant but disk space keeps shrinking -- postgres 9.1=0A> =0A>T=
 om --=0A> =0A> ----- Original Message -----=0A>>  From: Tom Lane <tgl@sss.=
pgh.pa.us>=0A>>  To: Greg Williamson <gwilliamson39@yahoo.com>=0A>>  Cc: "p=
gsql-admin@postgresql.org" =0A> <pgsql-admin@postgresql.org>=0A>>  Sent: Th=
ursday, September 27, 2012 7:14 PM=0A>>  Subject: Re: [ADMIN] Database size=
 stays constant but disk space keeps =0A> shrinking -- postgres 9.1=0A>> =
=0A>> G reg Williamson <gwilliamson39@yahoo.com> writes:=0A>>>> =A0 Have yo=
u checked to see if there are any processes that have open =0A>>  handles t=
o=0A>>>> =A0 deleted files (lsof -X | grep deleted).=0A>> =0A>>> =A0 lsof -=
X | grep deleted | wc -l=0A>> =0A>>> =A0 shows: 835 such files.=0A>> =0A>>>=
 =A0 A couple:=0A>>> =A0 postgres=A0=A0 2540 postgres=A0=A0 50u=A0 =A0 =A0 =
REG=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 8,3=A0 =A0=A0 409600=A0 =0A> =A0 =A0 =0A=
>>  93429 /var/lib/postgresql/9.1/main/base/2789=0A>>> =A0 200/11816 (delet=
ed)=0A>>> =A0 postgres=A0=A0 2540 postgres=A0=A0 51u=A0 =A0 =A0 REG=A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 8,3=A0=A0 18112512=A0 =0A> =0A>>  49694570 /var/lib=
/postgresql/9.1/main/base/2789=0A>>> =A0 200/2791679 (deleted)=0A>>> =A0 <.=
..>=0A>> =0A>>  So, which processes are holding these open, and what are th=
ey doing=0A>>  exactly?=A0 Let's see output from ps and pg_stat_activity, m=
aybe even=0A>>  attach to them with gdb and get stack traces.=0A>> =0A>>> =
=A0 We've a planned restart scheduled soon which will let me find any=0A>>>=
 =A0 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 befor=
e we've identified what's happening.=0A>> =0A>>  =A0=A0=A0 =A0=A0=A0 =A0=A0=
=A0 regards, tom lane=0A>> =0A> =0A> Thanks for the suggestions -- I'll pos=
t back when I have more info. Many of =0A> these do not seem to have a link=
 to any identifiable process that is still =0A> running, but some do and th=
ey have pointed me away from the hourly drop / =0A> rebuild, at least for n=
ow. Looks like the stats database may be the issue.=0A> =0A> Greg W.=0A=0AI=
 turned off the cronjob that did the hourly database create / drop and am s=
till leaking disk space, but a but slower -- only lost 2 gigs overnight.=0A=
=0AWhile running this process I see these data directories:=0Apostgres@db11=
:~$ ls -lrt 9.1/main/base=0Atotal 200=0Adrwx------ 2 postgres postgres =A0 =
=A0 6 2012-09-21 16:36 pgsql_tmp=0Adrwx------ 2 postgres postgres =A08192 2=
012-10-01 00:26 16387=0Adrwx------ 2 postgres postgres 16384 2012-10-01 00:=
26 1418400=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 00:26 204783=
9=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 00:26 11946=0Adrwx---=
--- 2 postgres postgres =A08192 2012-10-01 00:27 16449=0Adrwx------ 2 postg=
res postgres =A08192 2012-10-01 00:27 16392=0Adrwx------ 2 postgres postgre=
s =A08192 2012-10-01 00:27 16402=0Adrwx------ 2 postgres postgres =A08192 2=
012-10-01 00:27 11938=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 0=
0:27 1=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 08:17 16424=0Adr=
wx------ 2 postgres postgres 32768 2012-10-01 19:20 3171846=0A=0AWhen it is=
 done (note the last directory is now gone):=0Apostgres@db11:~$ ls -lrt 9.1=
/main/base=0Atotal 140=0Adrwx------ 2 postgres postgres =A0 =A0 6 2012-09-2=
1 16:36 pgsql_tmp=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 00:26=
 16387=0Adrwx------ 2 postgres postgres 16384 2012-10-01 00:26 1418400=0Adr=
wx------ 2 postgres postgres =A08192 2012-10-01 00:26 2047839=0Adrwx------ =
2 postgres postgres =A08192 2012-10-01 00:26 11946=0Adrwx------ 2 postgres =
postgres =A08192 2012-10-01 00:27 16449=0Adrwx------ 2 postgres postgres =
=A08192 2012-10-01 00:27 16392=0Adrwx------ 2 postgres postgres =A08192 201=
2-10-01 00:27 16402=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 00:=
27 11938=0Adrwx------ 2 postgres postgres =A08192 2012-10-01 00:27 1=0Adrwx=
------ 2 postgres postgres =A08192 2012-10-01 08:17 16424=0A=0AWhen I run l=
sof -X and grep for deleted files I see these 4 new entries added since the=
 last database create/drop:=0Aase/3167420/3169915 (deleted)=0Apostgres =A02=
1116 postgres =A0 66u =A0 =A0 =A0REG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08,3 =A0=
 19709952 =A0136501576 /var/lib/postgresql/9.1/main/base/3171846/3174279 (d=
eleted)=0Apostgres =A021116 postgres =A0 67u =A0 =A0 =A0REG =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A08,3 =A0 15450112 =A0136501574 /var/lib/postgresql/9.1/main/=
base/3171846/3174278 (deleted)=0Apostgres =A021116 postgres =A0 68u =A0 =A0=
 =A0REG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08,3 =A0 28344320 =A0136410873 /var/l=
ib/postgresql/9.1/main/base/3171846/3172541 (deleted)=0Apostgres =A021116 p=
ostgres =A0 69u =A0 =A0 =A0REG =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08,3 =A0 82452=
480 =A0144333458 /var/lib/postgresql/9.1/main/base/3171846/3174341 (deleted=
)=0Aroot@db11:~#=A0=0Aroot@db11:~# ps auxww | grep 21116=0Apostgres 21116 =
=A00.0 =A00.1 100416 32332 ? =A0 =A0 =A0 =A0Ss =A0 00:26 =A0 0:16 postgres:=
 repmgr repmgr 199.9.xxx.yyy(45239) idle =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0=0Ar=
oot =A0 =A0 25755 =A00.0 =A00.0 =A0 6440 =A0 840 pts/2 =A0 =A0S+ =A0 19:38 =
=A0 0:00 grep --color=3Dauto 21116=0A=0A=3D=3D=3D=3D=3D=3D=0A=0AWith the da=
tabase create/drop suspended we still see a steady accumulation of dead fil=
e descriptors, but at a slower rate.=0A< /dev/sda3 =A0 =A0 =A0 =A0 =A0 =A0 =
=A067G =A0 28G =A0 39G =A042% /=0A---=0A> /dev/sda3 =A0 =A0 =A0 =A0 =A0 =A0=
 =A067G =A0 29G =A0 38G =A044% /=0A=0AOther than abandoning repmgr I don't =
see a solution. I've posted this to the repmgr discussion group but have ha=
d zero responses (and, frankly, am not holding my breath).=0A=0AIf anyone h=
as any suggestions I'm all ears.=0A=0AThanks for the bandwidth!=0A=0AGreg W=
.