pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oli Sennhauser <oli.sennhauser@bluewin.ch>
To: Robert <rjyoung@scs.carleton.ca>
Cc: pgsql-admin@postgresql.org
Subject: Re: Database size with postgres 7.2
Date: Fri, 09 Jan 2004 10:27:21 +0100
Message-ID: <3FFE73F9.80208@bluewin.ch> (raw)
In-Reply-To: <3FFDEE28.908@scs.carleton.ca>
References: <3FFD9982.6050409@scs.carleton.ca>
	<3FFDB33A.9080207@bluewin.ch>
	<3FFDEE28.908@scs.carleton.ca>

Hi Robert

> Thanks for the good start, it worked without a hitch in 7.2.  :)  So 
> that query lists the size (in kilobytes?) all of the relations and the 
> index's of the relations as well?

As you can see in the result header it gives the size in kb. Caution 1: 
The script is written a little dirty. I just multiply??? blocksize with 
8. Because standard blocksize is actually 8 kb. In future or probably 
depending of your OS it can give some wrong values if blocksize is not 
8kb anymore or only. I do not (yet) know how to find out actual block 
size dynamically.
Caution 2: As you can see in the comment you have to VACUUM the database 
before running the script. Otherwise the values are completely wrong!!!

Why for each user a separate db? Why do you not work with schemas? In my 
opinion it eases maintenance (and also uses less ressources)

> 1, to show the disk usage of each table of a given database

Hmmm, if this makes sense to only show the space of tables? Typically 
user asked me the total amount for a user/schema or an application, etc. 
and not for tables only.

> 2, show the total disk usage for each database (ie db in the 
> pg_database table.)

select sum(relpages)*8 AS "size_kb" from pg_class;

> The only problem is, this shows all of the pg_* tables in each 
> database - is there any way to not show these relations?

Caution: I did not clear what happens with "global objects". See:


  Chapter 43. System Catalogs

... A few catalogs are physically shared across all databases in a 
cluster; these are marked in the descriptions of the individual catalogs.

In my opinion pg_class and all dependant views etc are per db and not 
per cluster. An so I do NOT see a simple solution to show something like:

select dbname, sum(replpages)*8 from pg_databases, pg_class, where 
pg_database.oid = pg_class_db...

But I might be wrong.

> The second problem looks like it's going to be more difficult: I think 
> I'm going to have to open a connection to each DB seperatly to poll 
> for the sizes of the relations within each, again I'm going to run 
> into the same problem of needing to only count up the space taken up 
> by the relations in the current database, and not the extra pg_* ones.
> Is there any way to determine which records in pg_class are related to 
> a pg_database record?  Hmm...  Looks like I could use a ERD of the 
> postgres system tables...

If you find something to this point I would be verry interested in.

Regards Oli

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli.sennhauser@bluewin.ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import

Attachments:

  smime.p7s (application/x-pkcs7-signature, 3.4K) download

view thread (4+ messages)

Message-ID: <3FFE73F9.80208@bluewin.ch>
Permalink:  ../3FFE73F9.80208@bluewin.ch/
Also on:    postgresql.org/message-id/3FFE73F9.80208@bluewin.ch

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: oli.sennhauser@bluewin.ch, rjyoung@scs.carleton.ca
  Subject: Re: Database size with postgres 7.2
  In-Reply-To: <3FFE73F9.80208@bluewin.ch>
  References: <3FFD9982.6050409@scs.carleton.ca> <3FFDB33A.9080207@bluewin.ch> <3FFDEE28.908@scs.carleton.ca> <3FFE73F9.80208@bluewin.ch>

* 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