Re: Database size with postgres 7.2

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(dot)sennhauser(at)bluewin(dot)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