Re: no space left on device

On Fri, 9 Jan 2004, Aurangzeb M. Agha wrote:

> :That's normal. 32 meg isn't really that big. How big of a partition do
> :you have this database on? Your best bet is to put it on a bigger
> :partition. the pg_xlog directory is gonna be at least 16 megs for most
> :installations.
> :
> :Do you have any transactions sitting at idle keeping postgresql from
> :recycling the xlogs?
> :
> :Normally when you run out of space it's a lack of vacuuming, but here it
> :just sounds like either the partition is too small or the postgres user is
> :living under a quota on that partition.
>
> Scott -- I'm at 93% disk usage:
>
> [postgres - DB]$ df -m .
> Filesystem 1M-blocks Used Available Use% Mounted on
> - 63328 55308 4803 93% /

Do you have root access to it? if so, set the reserved space for root to
be 0%, and then try vacuuming. Vacuuming requires some free space, and
since you're pretty much out, it isn't gonna be able to complete.

> I don't know about transactions sitting idle--like I mentioned this DB is
> read-only, and there's no writes taking place. Would I still need to
> worry about transactions? How can I check to see if there are any?

If you stop and restart it all transactions that are holding will be
disconnected, so that would clear that up.

But it looks to me like you just have it on too small of a partition. On
a modern multi-gigabyte hard drive, Postgresql's usage of tens of megs for
transactions logs is no big deal, but on a smaller partition like yours it
can cause problems.

> Re vacuuming, I haven't run vacuum for the same reason as above. This is
> only a read-only DB, and I didn't think a vacuum was necessary if there's
> no writes happening to the DB.

Well, if the database has been emptied and refilled it would use the
space, so it might be something like that. Or that it was right on the
edge of being out of space and some single alter user kinda thing drove
it over the edge. Hard to say. It looks like your individual databases
are pretty small, so I doubt there's lots of lost space in them.

Can you get a larger partition to move the data directory to on that box?
I'd recommend having about twice the max size of your database as a
minimum, which would be 120 to 150 megs for you.