Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1

Tom --

----- Original Message -----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Greg Williamson <gwilliamson39(at)yahoo(dot)com>
> Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
> Sent: Thursday, September 27, 2012 7:14 PM
> Subject: Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1
>
>G reg Williamson <gwilliamson39(at)yahoo(dot)com> writes:
>>> Have you checked to see if there are any processes that have open
> handles to
>>> deleted files (lsof -X | grep deleted).
>
>> lsof -X | grep deleted | wc -l
>
>> shows: 835 such files.
>
>> A couple:
>> postgres  2540 postgres  50u      REG                8,3    409600     
> 93429 /var/lib/postgresql/9.1/main/base/2789
>> 200/11816 (deleted)
>> postgres  2540 postgres  51u      REG                8,3  18112512 
> 49694570 /var/lib/postgresql/9.1/main/base/2789
>> 200/2791679 (deleted)
>> <...>
>
> So, which processes are holding these open, and what are they doing
> exactly?  Let's see output from ps and pg_stat_activity, maybe even
> attach to them with gdb and get stack traces.
>
>> We've a planned restart scheduled soon which will let me find any
>> scripts that might be keeping things open,
>
> A restart will destroy all the evidence, so let's not be in a hurry
> to do that before we've identified what's happening.
>
>             regards, tom lane
>

Thanks for the suggestions -- I'll post back when I have more info. Many of these do not seem to have a link to any identifiable process that is still running, but some 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.

Greg W.