How to identify all storage in a schema

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

How to identify all storage in a schema
3+ messages / 2 participants
[nested] [flat]

* How to identify all storage in a schema
@ 2015-01-14 15:56  Campbell, Lance <lance@illinois.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Campbell, Lance @ 2015-01-14 15:56 UTC (permalink / raw)
  To: pgsql-admin

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.

Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382 



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to identify all storage in a schema
@ 2015-01-14 16:11  Thomas Kellerer <spam_eater@gmx.net>
  parent: Campbell, Lance <lance@illinois.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Thomas Kellerer @ 2015-01-14 16:11 UTC (permalink / raw)
  To: pgsql-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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to identify all storage in a schema
@ 2015-01-14 16:38  Campbell, Lance <lance@illinois.edu>
  parent: Thomas Kellerer <spam_eater@gmx.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Campbell, Lance @ 2015-01-14 16:38 UTC (permalink / raw)
  To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-admin

Fantastic!  Thanks so much for the query.


Thanks,

Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382 



-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Thomas Kellerer
Sent: Wednesday, January 14, 2015 10:11 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to identify all storage in a schema

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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2015-01-14 16:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-01-14 15:56 How to identify all storage in a schema Campbell, Lance <lance@illinois.edu>
2015-01-14 16:11 ` Thomas Kellerer <spam_eater@gmx.net>
2015-01-14 16:38   ` Campbell, Lance <lance@illinois.edu>

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