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@xxxxxxxxx> wrote: >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 > > -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
