Message-ID: <5065D9D3.2070502@iii.com>
Date: Fri, 28 Sep 2012 10:09:39 -0700
From: Dinesh Bhandary <dbhandary@iii.com>
Reply-To: dbhandary@iii.com
Organization: Innovative Interfaces
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
To: Steve Crawford <scrawford@pinpointresearch.com>
CC: Greg Williamson <gwilliamson39@yahoo.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Database size stays constant but disk space keeps shrinking
	-- postgres 9.1
References: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
	<5064D7BC.1000709@pinpointresearch.com>
	<1348797684.2846.YahooMailNeo@web125904.mail.ne1.yahoo.com>
	<5065C97C.2010707@pinpointresearch.com>
In-Reply-To: <5065C97C.2010707@pinpointresearch.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

We had a situation where external sort was creating a humungous temp 
file and the space was reclaimed when the process was completed.

Thanks.
Dinesh

On 9/28/2012 8:59 AM, Steve Crawford wrote:
> 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
>
>
>