Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THkNy-0005wQ-CS for pgsql-admin@postgresql.org; Fri, 28 Sep 2012 23:54:22 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THkNx-0005a3-Au for pgsql-admin@postgresql.org; Fri, 28 Sep 2012 23:54:21 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q8SNn9gU018018; Fri, 28 Sep 2012 19:49:09 -0400 (EDT) From: Tom Lane To: Greg Williamson cc: Steve Crawford , "pgsql-admin@postgresql.org" Subject: Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1 In-reply-to: <1348872472.9854.YahooMailNeo@web125904.mail.ne1.yahoo.com> 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> <1348872472.9854.YahooMailNeo@web125904.mail.ne1.yahoo.com> Comments: In-reply-to Greg Williamson message dated "Fri, 28 Sep 2012 15:47:52 -0700" Date: Fri, 28 Sep 2012 19:49:09 -0400 Message-ID: <18017.1348876149@sss.pgh.pa.us> X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201209/175 X-Sequence-Number: 40116 Greg Williamson writes: >> 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. > These all seem to be from two places -- repmgr (transient ) and this stats application. Do you have any characterization yet of which deleted files are being held open by which processes? In particular I'm wondering if the held-open deleted files are in a recently-dropped database, and whether they are being held open by regular backends or one of the background processes such as bgwriter, and if the former what are those backends doing exactly. It's entirely expected that recently-deleted files might be held open for a little while, but there are mechanisms that are supposed to prevent them from being held open indefinitely. I'm guessing that your usage pattern might be tripping over some gap in those mechanisms, but we don't have enough info yet to speculate about what. BTW, I now think that my question about the unexpected OID value shown for one deleted file may have been a red herring --- it seems not implausible that lsof was just lying to you. It has to do some guesswork to reconstruct file paths for deleted files, and I don't think it's always right about that. regards, tom lane