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

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

-------------------------------------------------------

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.sennhauser@bluewin.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

Attachments:

  smime.p7s (application/x-pkcs7-signature, 3.4K) download

view thread (4+ messages)  latest in thread

Message-ID: <3FFDB33A.9080207@bluewin.ch>
Permalink:  ../3FFDB33A.9080207@bluewin.ch/
Also on:    postgresql.org/message-id/3FFDB33A.9080207@bluewin.ch

 · 

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

* 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