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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
