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
