server disk space

From: Brian Modra <epailty(at)googlemail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: server disk space
Date: 2009-09-06 06:05:04
Message-ID: 5a9699850909052305g6a41c234tb3c613c8562e7532@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Kempter 2009-09-06 16:04:12 Re: server disk space
Previous Message Tom Lane 2009-09-04 18:31:32 Re: postgres processes not reflected in pg_stat_activity