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

Tom --

----- Original Message -----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Greg Williamson <gwilliamson39(at)yahoo(dot)com>
> Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
> Sent: Thursday, September 27, 2012 7:55 PM
> Subject: Re: [ADMIN] Database size stays constant but disk space keeps shrinking -- postgres 9.1
>
>G reg 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.

Nope -- a sim-ple copy that seems to have gotten all of the data output.
>                                                          Do you have any nonstandard
> maintenance practices in this installation, such as doing database-wide
> VACUUM FULL every so often?

None that I know of -- logs don't show any. and there are none on cronjobs. I'm asking the developers behind this app -- they may be doing something strange.  I'll post back as they answer.

Thanks hugely,

Greg W.