Re: Database size with postgres 7.2

Oli Sennhauser wrote:

> Hi Robert
>
>> I'm hoping to find a easy way to find the disk space used by each
>> database on a given postgres v7.2 server - I've been looking through
>> the docs and have seen some references to oid2name, but that doesn't
>> really help my situation.
>
>
> This script gives you the used size per object. Unfortunately only for
> the actual db. If it also works on 7.2: I do not know (old stuff)
>
> --
> -- Amount of space per object used after vacuum
> --
> \echo
> \echo 'Caution: This skript does only print usefull information'
> \echo ' if you run VACUUM before!'
> \echo
> --VACUUM;
> SELECT c1.relname AS "tablename", c2.relname AS "indexname",
> c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename"
> FROM pg_class c1, pg_class c2, pg_index i
> WHERE c1.oid = i.indrelid
> AND i.indexrelid = c2.oid
> UNION
> SELECT relname, NULL, relpages * 8, relfilenode
> FROM pg_class
> WHERE relkind = 'r'
> ORDER BY tablename, indexname DESC, size_kb;
>
> Does it help?
>
> Regards Oli
>

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?

I'm trying to write a few queries for a postgres server with a bunch of
users, where each user is in charge of one database: I have two goals:
1, to show the disk usage of each table of a given database, and 2, show
the total disk usage for each database (ie db in the pg_database
table.) I believe I can basically accomplish 1 with the query below
(although I'm not sure if it accounts for records with the relkind = 'S'):

SELECT tablename, SUM( size_kb )
FROM
( SELECT c1.relname AS "tablename",
c2.relpages * 8 AS "size_kb"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
AND i.indexrelid = c2.oid
UNION
SELECT relname, relpages * 8
FROM pg_class
WHERE relkind = 'r' ) AS relations
GROUP BY tablename;

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

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...

Thanks for the help so far,
Robert Young