I posted over the weekend about this issue under the subject of "VACUUM" but the replies I got did not help. We have several large tables which seem to be taking 4 times as much disk space as they should?
Let me give a little background:
Every night, we do a pg_dump of our production DB and a pg_restore of this dumb into a standby DB. What I notice is the size of the standby DB is MORE THAN HALF the size of production.
For example, here is the oid2name dump of a table called users on PRODUCTION:
$ oid2name -d EBPRD1 -t users
Oid of table users from database "EBPRD1":
_______________________________
17260 = users
$ ls -l 17260*
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.1
-rw------- 1 postgres postgres 1073741824 Dec 18 17:48 17260.2
-rw------- 1 postgres postgres 165445632 Dec 18 17:51 17260.3
but the same table on the standby DB yields this:
$ oid2name -d EBPRDS1 -t users
Oid of table users from database "EBPRDS1":
_______________________________
3828262123 = users
$ ls -l 3828262123
-rw------- 1 postgres postgres 1073741824 Dec 18 16:55 3828262123
I tried a full VACUUM of the users table on production, but I didn't get any disk space back. I also looked thru all oids and there are no pg_toast files of significance. I am on postgres 7.2 How do I reclaim this disk space?
Any help would be GREATLY Appreciated!
thanks in advance