Re: PostgreSQL and FreeBSD SoftUpdates

>>> Right off the top of my head, it almost sounds like a file is being 
>>> held
>>> open after its been deleted ... we went through that with the new 
>>> aspseek
>>> a little while back, where 170gig just disappeared overnight, but du
>>> showed hardly any disk space being used ...
>>>
>>> Does restarting the database server (not rebooting, just restarting 
>>> the
>>> postmaster) free up the disk space?
>>
>> No - have to reboot.  That's probably because of softupdates though.
>
> 'k, *shouldn't* require a reboot ... but, what I'd try is to do what
> you've thought .. disable softupdates and see if you can recreate ... 
> if
> killing off the process auto-reclaims the space fast, then it sounds 
> like
> a stale file being held open (log file being rotated improperly?) ...

Install the latest version of lsof(8) and see if there are any stale 
files being held open.  I've got databases on FreeBSD 4.X and 5.X with 
softupdates on both and haven't had a problem.  I'm wondering if your 
database is doing something "exotic" that hasn't been tickled.  The 
first thing that comes to mind is, are you using deferred constraints?  
Second, if it is a soft updates issue, then a reboot isn't necessary 
(as Marc says)... you should be able to stop the database and type df 
-k && sync && sleep 30 && df -k see space being freed up.  -sc

-- 
Sean Chittenden