Skip site navigation (1) Skip section navigation (2)

Sizes for all databases

From: Daniel Rubio <drubior(at)tinet(dot)org>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Sizes for all databases
Date: 2003-03-31 15:32:16
Message-ID: 3E885F80.80703@tinet.org (view raw or flat)
Thread:
Lists: pgsql-admin
Hi!

Not long time ago, I asked the list how to obtain the sizes of all the 
databases on my postgres, to have a control on how growths the client's 
applications.

Now I've found a solution and I thinked that it could be useful for 
people working on ISP's offering postgres services or simply as an other 
control utility then I'll put here for those who are interesed.

1-The function uses the database_size function that you can find in the 
contrib directory (dbsize)

2-Create a table in one of your databases (with plpgsql active and the 
addient permissions) with this structure

CREATE TABLE "mides" (
    "nom" name NOT NULL,
    "mida" int4 NOT NULL
);

3-This is the function you must add

CREATE FUNCTION mides_bds() RETURNS setof mides AS '
  DECLARE
   actual mides%ROWTYPE;
   treball RECORD;
   mida int4;
  BEGIN
  FOR treball IN SELECT datname FROM pg_database LOOP
  SELECT INTO mida database_size(treball.datname);
  mida:=mida/1024;
  SELECT INTO actual a.datname,mida FROM pg_database a WHERE 
a.datname=treball.datname;
  RETURN NEXT actual;
  END LOOP;
  RETURN actual;
  END;
  'LANGUAGE 'plpgsql';

4-Now, if you execute SELECT * FROM mides_bds() you'll obtain the sizes 
in Kb for all your databases

I hope it's useful for someone else, and once more, sorry for my english
-- 
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio(at)oasi(dot)org
********************************************************


Responses

pgsql-admin by date

Next:From: DHS WebmasterDate: 2003-03-31 15:54:11
Subject: best OS suggestions / ease my doubts
Previous:From: Andrew BiagioniDate: 2003-03-31 14:56:31
Subject: Re: pgAdmin II questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group