From: Steve Crawford <scrawford@pinpointresearch.com>
To: Greg Williamson <gwilliamson39@yahoo.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
Date: Thu, 27 Sep 2012 15:48:28 -0700
Message-ID: <5064D7BC.1000709@pinpointresearch.com> (raw)
In-Reply-To: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
References: <1348783559.78739.YahooMailNeo@web125906.mail.ne1.yahoo.com>
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.)?
What method of replication are you using?
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?
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.
Let us know what you find.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, gwilliamson39@yahoo.com
Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
In-Reply-To: <5064D7BC.1000709@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox