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

On 09/27/2012 07:01 PM, Greg Williamson wrote:
> Steve (and others who replied):
> ...
> The other is a slimmed-down version of our production database, which gets
>> recreated hourly by a shell script which pulls data from remote servers, does a
>> pg_dump of the resulting 3 gig database, and then drops it.
>> ...

Could you explain this process in more detail? Are you creating a new
database, reading in data, dumping then dropping the whole database or
just manipulating tables within an existing database?

>> Have you checked to see if there are any processes that have open handles to
>> deleted files (lsof -X | grep deleted). Deleted files won't show up in du
>> but won't release their disk space until the process exits. Perhaps a script
>> or scripts, even one of your hourly ones, that terminate when the server
>> restarts? You could save the output of lsof and ps immediately before and after
>> a restart and compare them.
>>
> 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)
> <...>...
I'll leave it to you and Tom to puzzle over the the postgres-related
open files. Meanwhile, I'm a bit curious about the other 800+ and
whether they are associated with scripts or processes that are connected
to PostgreSQL.

First, what is the output of "select * from pg_stat_activity;"? Are
there connections you don't expect to see? If you force any of them
closed (after checking with anyone who may be impacted), do you see any
file handles released or disk-space freed?

Second, do any of the processes associated with the other
open-but-deleted files relate to programs or scripts that connect to
PostgreSQL? Next time you do a restart, do any of the processes exit or
do any of the deleted files get closed?

I'm wondering if you have processes that connect to PostgreSQL which
terminate and release their file-handles when PG is restarted.

Cheers,
Steve