Why does my DB size differ between Production and DR? (Postgres 8.4)

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

Responses

Browse pgsql-general by date

  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