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

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




view thread (4+ messages)  latest in thread

Message-ID: <3FFDEE28.908@scs.carleton.ca>
Permalink:  ../3FFDEE28.908@scs.carleton.ca/
Also on:    postgresql.org/message-id/3FFDEE28.908@scs.carleton.ca

 · 

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: rjyoung@scs.carleton.ca, oli.sennhauser@bluewin.ch
  Subject: Re: Database size with postgres 7.2
  In-Reply-To: <3FFDEE28.908@scs.carleton.ca>
  References: <3FFD9982.6050409@scs.carleton.ca> <3FFDB33A.9080207@bluewin.ch> <3FFDEE28.908@scs.carleton.ca>

* 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