PostgreSQL disk space reclaiming
От | Anthony Bull |
---|---|
Тема | PostgreSQL disk space reclaiming |
Дата | |
Msg-id | 4FC34125.2040800@mgroup.biz обсуждение исходный текст |
Список | pgsql-general |
Greetings, I am having problems reclaiming an excessive amount of disk space used by a database, running on Windows PostgreSQL v8.3 (unfortunately we are stuck with this version at the moment). The database had a 16GB table, that I deleted a lot of data from. After deleting from this table, I ran a VACUUM FULL on it and it didn't give the freed space to the operating system - whats more, running "select pg_database_size('db')" says that the database is still taking up 25GB. After this I did some reading, and decided to recreate the table - so I recreated it with a different name, ran a SELECT * to insert into it, and recreated the indexes on it. This new table takes up 3GB only. After this I dropped the old table, and renamed the new table to the same name as the old table. I read on various sites that as soon as you commit a "DROP TABLE" command, it returns the used disk space to the operating system. This did not work either - postgres still reports 25GB being used by the database. If I select the size of all the tables in the database postgres reports around 9GB or so, yet reports that the database itself is 25GB. After that, I ran a VACUUM FULL across the entire database, and it returned about 1GB to the operating system, which I think was from another large table that got cleaned out (but not recreated). I am now at a loss, as I want the 16GB from that old table back in the operating system for other means (disk space is severely limited on this particular machine) and have tried all suggestions I could find from googling to no avail. Due to new rules implemented in our data processing, our database will not grow anywhere near as big as it did previously - so postgres holding on to all this extra space is a complete waste also. Note that it is not really feasible for me to uninstall postgres, upgrade it and rebuild all my databases at this stage as our system has a high availability requirement. Is there anything else I can do to get postgres to give me back all that unused space? Thanks for any help people can give! Anthony Bull Software Architect m2Wealth International Ltd.
В списке pgsql-general по дате отправления: