Plz help: PostgreSQL takes too much disk-space
От | Gaffga, Stefan |
---|---|
Тема | Plz help: PostgreSQL takes too much disk-space |
Дата | |
Msg-id | A16CCF0F0DA1D5119FFF000255A0CDB7011231C2@NT-EXCHANGE обсуждение исходный текст |
Ответы |
Re: Plz help: PostgreSQL takes too much disk-space
|
Список | pgsql-admin |
Hello! We use the PostgreSQL Version 7.2.2 bundled with SuSE Linux 8.2 Professional. Our database server contains 20 databases, many smaller and some larger ones. 10 of these databases contain most of the data. These data need to be updated every day. We delete all rows of the each table using "delete from tab" and then we fill them again using the COPY-command. The data that we insert using the COPY command is converted from a text file we receive every evening. The complete operation takes about 1 hour. After we updated all of our tables, we do a "vacuumdb --all --full". Now the problem: We encountered that the database is growing far more rapidly than the data! Our database currently consumes 20GB (!) of disk space! The strange effect is: When we do a "pg_dumpall > db.dmp" and insert this dump into a clean DB-server environment, the consumed disk space is 2.1 GB!! So you see, that we do not have that much data to really fill anything around 20GB... When I do a "select sum(relpages) from pg_class where relname not like 'pg_%';" to get the used pages from all non-system tables the result is: 849034. As we did not change the block size, we get 849034 * 8k = 6792272 k = 6,5 GB Then for the system tables: "select sum(relpages) from pg_class where relname like 'pg_%';" The result is: 267 => 267 * 8k = 2 MB As you see, PostgreSQL thinks it is using 6,5 GB, but "du -sh" says 20GB ... Any help / hints / links / ideas are VERY appreciated! Thank you all in advance Stefan
В списке pgsql-admin по дате отправления: