Skip site navigation (1) Skip section navigation (2)

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

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>
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
Date: 2012-09-28 15:59:56
Message-ID: 5065C97C.2010707@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-admin
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



In response to

Responses

pgsql-admin by date

Next:From: Dinesh BhandaryDate: 2012-09-28 17:09:39
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
Previous:From: Greg WilliamsonDate: 2012-09-28 05:28:26
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group