Thread: Sizes for all databases

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
********************************************************

This looks pretty neat. Perhaps you can add it to the PostgreSQL
Cookbook as well?

http://www.brasileiro.net:8080/postgres/cookbook/

Robert Treat

On Mon, 2003-03-31 at 10:32, Daniel Rubio wrote:
> 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
> ********************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly