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

Greg Williamson <gwilliamson39(at)yahoo(dot)com> writes:
>>> postgres 2540 postgres 50u REG 8,3 409600 93429 /var/lib/postgresql/9.1/main/base/2789200/11816 (deleted)
>>> postgres 2540 postgres 51u REG 8,3 18112512 49694570 /var/lib/postgresql/9.1/main/base/2789200/2791679 (deleted)

> Thanks for the suggestions -- I'll post back when I have more info. Many of these do not seem to have a link to any identifiable process that is still running, but some do and they have pointed me away from the hourly drop / rebuild, at least for now. Looks like the stats database may be the issue.

BTW, looking at that again --- the filenames appear to be ordinary
tables in database 2789200, but there is something mighty odd about the
first one: 11816 is an OID that should only be handed out during initdb.
And in 9.1 what it would be handed out to is pg_shdescription. Now it's
not impossible that pg_shdescription's original table file would get
deleted: a VACUUM FULL or CLUSTER on that catalog would do it. But
AFAICS there is no situation in which that relfilenode number would
appear in a regular database --- it should be under the global/
subdirectory of $PGDATA. So unless you miscopied that filename, there
is something odd going on here above and beyond the problem of open
file descriptors not getting closed. Do you have any nonstandard
maintenance practices in this installation, such as doing database-wide
VACUUM FULL every so often?

regards, tom lane