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

I'd also keep track of tmp directory while building. I hope it is not
creating a huge external sort file. By building you meant to say
pg_restore, I assume.
How big is your maintenance_work_mem? Index creation pools memory from
this buffer. 

n Sep 27, 2012 07:01 PM, Greg Williamson <gwilliamson39(at)yahoo(dot)com>
wrote:

>Steve (and others who replied):
>
>
>----- Original Message -----
>>From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
>>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 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(at)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 review my 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.
>
>
>
>--
>Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-admin
>
>