From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Schema Size - PostgreSQL 9.2 |
Date: | 2016-03-18 02:10:58 |
Message-ID: | CANu8FiyFYCaJ99BuUw3_ebzjsAmMuTX8RLtfvy36WrHOHJddSg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Mar 17, 2016 at 4:45 PM, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
wrote:
> On Fri, Mar 18, 2016 at 09:38:30AM +1300, drum(dot)lucas(at)gmail(dot)com wrote:
>
> > Can you please provide me a Query that tells me how much space is a
> Schema
> > in my DB?
>
> There's been a discussion on that recently (like last month)
> which can be found in the archive.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
This should give you what you need:
SELECT n.nspname as schema,
pg_size_pretty(sum(pg_total_relation_size(quote_ident(n.nspname)||
'.' || quote_ident(c.relname)))) as size,
sum(pg_total_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(c.relname))) as size_bytes
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
GROUP BY 1
ORDER BY 3 DESC, 1, 2;
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2016-03-18 03:21:43 | Re: Error: insufficient data in the message |
Previous Message | David G. Johnston | 2016-03-18 00:44:38 | Re: Request - repeat value of \pset title during \watch interations |