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

Steve (and others who replied):


----- Original Message -----
> From: Steve Crawford <scrawford@pinpointresearch.com>
> To: Greg Williamson <gwilliamson39@yahoo.com>
> Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
> Sent: Thursday, September 27, 2012 3:48 PM
> Subject: Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1
>
> On 09/27/2012 03:05 PM, Greg Williamson wrote:
>>  Dear list,
>>
>>  I have a postgres database, 9.1.3, which shows a fairly constant amount of
> space used by postgres, but total disk space kees shrinking.
>>  If I restart postgres the space on my file system returns.
>>
>>  This cluster is replicated to another; that custer does not show this
> problem.
>>
>>  There are two main databases on this cluster, both using logging. One is
> used to accumulate some stats on our systen.
>>
>>  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.
>>
>>  I posted a message about ths a few days and got no responses. I am hoping
> for better luck this time as this is a serious issue,not relayed t vacuuming or
> routine admin tasks which may or may not be happening.
>>
>>
> No answer but some questions.
>
> How are you determining space used by PostgreSQL? From the OS (i.e. du on
> PostgreSQL's directories or similar) or from PostgreSQL itself (pg_ system
> tables, \dt+, etc.)?
> 

df -h and the postgres internal functions that show size of relations.

> What method of replication are you using?


streaming replication w/ synchronous commit

>
> Do you have any indication if the issue is only related to which database is
> causing the issue? Can you stop activity on one of them and see if the problem
> persists?


If I suspend the build / drop database process the shrinking of available disk space slows dramatically.

>
> 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)
<...>

while ls shows:
root@db11:~# ls /var/lib/postgresql/9.1/main/base/ | more
1
11938
11946
1418400
16387
16392
16402
16424
16449
2047839
pgsql_tmp

We've a planned restart scheduled soon which will let me find any scripts that might be keeping things open, and I'll
reviewmy script that creates / populates / drops the database hourly. 
 

This does not seem to be related to temp tables. Something in the script, perhaps ...

Further suggestions and  / or questions welcome.

Thanks !

Greg W.