Situation: Disk usage on production server root filesystem is at 68%
utilization (80 GB used), on DR is at 51% (56 GB used). We use
SlonyII-1.2.x to keep the DR up to date. I would like to account for
the 24 GB difference.
Symptoms:
1. Database size reported by psql c '\l+'
Production: 52 GB
DR: 30 GB
Difference: 22 GB
2. Top table by size (including index and TOAST), as reported by:
SELECT relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size" from
pg_catalog.pg_statio_user_tables ORDER BY
pg_total_relation_size(relid) DESC;
Production: 42 GB
DR: 24 GB
Difference: 18 GB
2b. That same table size (no index, no TOAST) as reported by: SELECT
relname as "Table", pg_size_pretty(pg_relation_size(relid)) As "Size"
from pg_catalog.pg_statio_user_tables ORDER BY pg_relation_size(relid)
DESC;
Production: 744 MB
DR: 528 MB
3. Output of "du -sh data" for my PostgreSQL installation:
Production: 60 GB
DR: 31 GB
Difference: 31 GB
How to account for the 18 GB difference in that single table size
between production and DR? I imagine it's in the TOAST but how do I
list TOAST table size? How to get production size down closer to that
of the DR?
Best,
-at