Tom --
----- Original Message -----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Greg Williamson <gwilliamson39@yahoo.com>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.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@yahoo.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
identifiableprocess that is still running, but some do and they have pointed me away from the hourly drop / rebuild, at
leastfor now. Looks like the stats database may be the issue.
Greg W.
