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 --full).
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 --analyze --all --full
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 /var/pgsql/entu/base/17142/pgsql_tmp
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.