Re: server disk space

On Sunday 06 September 2009 10:28:30 you wrote:
> Thanks, I suspected that was the case. However, the plain vacuum on
> the largest table has been running for almost 24 hours now, despite
> the postgresql being idle (no rows being inserted or updated for 24
> hours). The vacuum full will probably take days... which is a problem.
> I can't really take the database offline over the whole weekend.... Do
> you have any suggestions?
> I was just looking at pg_class to see how big the tables were, so I
> know which are the largest tables. This largest one has a really large
> pg_toast_4643492 index...
>
> (I'm using this to find out which are the largest relations:
> SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC;)
>
> How do I force a reindex hitting just that pg_toast_<oid> relation?
>
> 2009/9/6 Kevin Kempter <kevink(at)consistentstate(dot)com>:
> > 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.

You could try a dump/restore of this table However Im not sure this would
actually be faster. I'd suggest you let it run as long as you can.

Likewise if the table is that big you should probably look at partitioning the
table. This will help you not only per vacuum/space management but performance
as well