From: | Aleksey Tsalolikhin <atsaloli(dot)tech(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Why does my DB size differ between Production and DR? (Postgres 8.4) |
Date: | 2011-02-01 00:54:38 |
Message-ID: | AANLkTikTdf4BntkH-tKM1uFo0FTjNNSPD3CsR3mMR=k5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-02-01 01:05:25 | Re: Issues with generate_series using integer boundaries |
Previous Message | Thom Brown | 2011-02-01 00:48:13 | Re: Issues with generate_series using integer boundaries |