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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
