On Sunday 06 September 2009 00:05:04 Brian Modra wrote:
> Hi,
> I'm maintaining a fairly large online database, and am trying to free
> up disk space. Its got to 98% full.
> I am certain that the postgresql data files are responsible for more
> than 97% of this partition's usage.
> The WAL logs for example are stored elsewhere.
>
> The largest tables in this database are only inserted, not updated.
> There are about 6 inserts per second. Its all time-stamped, and I am
> deleting old rows.
> There are 5 such tables, each 3 times as large as the previous.
>
> On the 2 smallest tables, I have already done a create table ... (like
> ...), a re-insert of everything after a certain date, a vaccuum
> analyse, and recreated the indexes. But they are relatively small, so
> no real gains.
>
> On the larger tables though, I have deleted old rows, and am now
> running a (plain) vacuum.
> The 3rd largest table's vacuum has completed. No space gain at all.
>
> The other two (largest) table's vacuums are still in progress (still
> running since last evening). I have shut down part of the service so
> that its no longer inserting data to the tables, but rather caching it
> for later insertion.
>
> I suspect I need to run vacuum full, and drop indexes. Then re-create
> the indexes...
>
> But is there something I'm missing, e.g. that although the database
> disk is 98% full, postgresql sees the database as having large blocks
> of free space that it can write into? A vacuum full is going to take
> an age, and I'm not sure if I can afford to have the database offline
> for that period...
>
> I will appreciate your help.
> Thanks
> Brian
Brian;
you may simply have too much data, try the check-postgres script(s) you can
get it here (http://bucardo.org/check_postgres/) , specifically look at the
bloat or dead space in your biggest tables. You may need to run a 'VACUUM
FULL' on those tables to reclaim disk space, a normal vacuum will not reclaim
any disk space, just make the space in the table available for re-use by the
database.