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

Greg Williamson <gwilliamson39(at)yahoo(dot)com> 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