We use the PostgreSQL Version 7.2.2 bundled with SuSE Linux 8.2
Our database server contains 20 databases, many smaller and some larger
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
and then we fill them again using the COPY-command. The data that we insert
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
Our database currently consumes 20GB (!) of disk space!
The strange effect is: When we do a "pg_dumpall > db.dmp" and insert this
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
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
pgsql-admin by date
|Next:||From: scott.marlowe||Date: 2003-06-18 13:32:32|
|Subject: Re: Plz help: PostgreSQL takes too much disk-space|
|Previous:||From: Peter Eisentraut||Date: 2003-06-18 12:23:29|
|Subject: Re: Create Rule/trigger|