Sizes for all databases - Mailing list pgsql-admin

From Daniel Rubio
Subject Sizes for all databases
Date
Msg-id 3E885F80.80703@tinet.org
Whole thread Raw
Responses Re: Sizes for all databases
List 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@oasi.org
********************************************************


pgsql-admin by date:

Previous
From: Kevin Brown
Date:
Subject: Re: [pgsql-advocacy] uppercase = lowercase
Next
From: Beatrice Yueksel
Date:
Subject: question regarding REFERENCES and INHERITS