From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-admin@postgresql.org
Subject: Re: How to identify all storage in a schema
Date: Wed, 14 Jan 2015 17:11:20 +0100
Message-ID: <m964f9$bnt$1@ger.gmane.org> (raw)
In-Reply-To: <B75CD08C73BD3543B97E4EF3964B7D701FD5CDBD@CITESMBX1.ad.uillinois.edu>
References: <B75CD08C73BD3543B97E4EF3964B7D701FD5CDBD@CITESMBX1.ad.uillinois.edu>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Campbell, Lance schrieb am 14.01.2015 um 16:56:
> PostgreSQL 9.3
> Is there a query one can run that will give you a total storage for all objects (tables, indexes, etc) associated with a particular schema?
>
> Thanks for your assistance.
select table_schema,
sum(pg_total_relation_size(table_schema|| '.' ||table_name))
from information_schema.tables
where table_schema not in ('information_schema', 'pg_catalog')
group by table_schema;
Of for just a single schema:
select sum(pg_total_relation_size(table_schema|| '.' ||table_name))
from information_schema.tables
where table_schema = 'foobar';
There are other functions to retrieve the table or database sizes:
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT
Thomas
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: How to identify all storage in a schema
In-Reply-To: <m964f9$bnt$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox