Hi all:
I'm running Pgsql 7.4.6 on Red Hat Enterprise Linux 3.0 (UR4 and UR6).
My question/problem is around how much disk space PostgreSQL uses when
tables are grown (by a restore) and then shrunk down again (delete + vacuum
).
When I initialize my database and load my schema df tells me that 62MB of
space is used in my DB data partition (with the database running).
I then restore a backup to the new server with pg_restore, I get a final
data size of 49500 MB. This is all fine/expected.
I then want to reduce the space taken by the data down to say 10GB of data,
so I wrote a simple minded script that does the following (on a test
system):
While df returns more than 10GB of data in DB data partition
DELETE 33% of rows from all non-static tables in the schema
/usr/local/pgsql/bin/vacuumdb
End while.
I left this running over the weekend, and was surprised to find the script
still running in it's 25th iteration this morning, with basically no rows
left in any of the non-static tables.
The interesting thing was that the data partition still had almost 26GB of
space used, no matter how much data was actually in the database.
I'm probably missing something rather fundimental here. Anything obvious
jump out at anyone? Restarting the DB doesn't change anything.
Du output shows:
[root@customer3 root]# du -h /var/pgsql/entu
4.5M /var/pgsql/entu/base/1
4.4M /var/pgsql/entu/base/17141
4.0K /
26G /var/pgsql/entu/base/17142
26G /var/pgsql/entu/base
140K /var/pgsql/entu/global
129M /var/pgsql/entu/pg_xlog
20K /var/pgsql/entu/pg_clog
26G /var/pgsql/entu
Where /var/pgsql/entu is the root of our database data directory.
Thanks
Tom.