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 [EMAIL PROTECTED] 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
smime.p7s
Description: S/MIME Cryptographic Signature
