Re: Disk is full, what's cool to get rid of?

Tom Lane wrote:
> Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
>
>> If the data isn't critical, you maybe could truncate a table to clear
>> enough space. Deleting anything under pg_xlog is more or less
>> guaranteed to mean your database is garbage.
>>
>
> If you're desperate you could shut down the postmaster, run
> pg_resetxlog, restart the postmaster. This would cut xlog contents
> to the minimum --- however, they'd probably soon grow back to whatever
> they are now, so it's not much of a long-term solution. It might give
> you some breathing room to look for other places to trim though.
>
> If the database hasn't been maintained well then you likely are
> suffering from table bloat and/or index bloat. A desperation measure
> for fixing that is drop all indexes, vacuum full, recreate all indexes.
> (There are other procedures you may find recommended, such as CLUSTER,
> but they have transient disk-space requirements that you're not gonna
> be able to afford when your back is to the wall.)
>
Thanks for your suggestions. Doing the tune2fs trick worked enough for
me to get a vacuum full to run -- which then pointed out the fact that
max_fsm_pages was still running off of the default 1000 (doing a SELECT
COUNT(*) FROM pg_class; showed over 100k relations) so, I made some much
needed tweaks to postresql.conf, restarted the db and then restarted the
vacuum full (it's still running now). As far as the rest of the
suggestion (CLUSTER, index drop/add, etc...), there really isn't much of
a point as this database is for development only and is only going to be
around for about another month when we build a whole new pristine
development db and environment from the ground up (I can't wait!), but
these are all good things to know.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)