Re: Excessive space allocations in Postgresql 9.1.6 system files causing the file system to run out of space.

"fburgess(at)radiantblue(dot)com" <fburgess(at)radiantblue(dot)com> wrote:

> We have a Postgres database that was recently upgraded from 8.4.3
> to 9.1.6.  We have noticed unusual growth in the data files and
> generated a script to perform the following actions.

> 1. Query pg_class for all records
> 2. Generate a file listing of all postgres data files
> 3. Compare the two lists and eliminate all files that are
>    contained within pg_class
>
> There are 17359 data files.  After running the script, there are
> 5802 data files remaining that are not listed in pg_class.  Due
> to the size of the (5802) data files (~4TB), I am not comfortable
> about deleting them from the file system.  Does postgres 9.1.6
> catalog every data file in pg_class?   Or does it leave some data
> files off of this table?  If so, how can I determine if I have
> stale, unnecessary data files on my file system?

Yeah, it's good to be cautious -- deleting a needed file can render
your database cluster unusable.  Be sure you have a good backup you
can go back to if you delete the wrong thing.

What directories are you looking in?

For a database or tablespace directory, are you excluding all files
which start with a filename you derived from pg_class and has a dot
or underscore followed by more characters?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company