Steve (and others who replied): ----- Original Message ----- > From: Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> > To: Greg Williamson <gwilliamson39@xxxxxxxxx> > Cc: "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx> > Sent: Thursday, September 27, 2012 3:48 PM > Subject: Re: 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 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
