Thread: Detemine database size on Postgres 8.0

Hi all,

We have a database server which is on version 8.0.12.  I was wondering if anyone knows of a way to tell the size of each database?

I've tried:
select pg_size_pretty(pg_database_size('test'));

which doesn't work because pg_database_size is unrecognised.  And I've tried:

select   *
from   (
   select   pc.RelName as "ObjectName"
      ,pt.spclocation   || '/' || pc.relfilenode as "PhysLoc"
      ,pc.relfilenode as "DirNum"
      ,pc.relkind as "ObjectType"
      ,(pc.relpages * 8)/1024 as "MBUsed"
      ,pc.reltuples   as "NumRows"
   from   pg_roles pr
   join   pg_class pc
   on   pr.oid = pc.relowner
   join   pg_tablespace pt
   on   pr.oid = pt.spcowner
   and   pc.reltablespace = pt.oid
   where   pr.rolname = 'EMS_SA'
   and   pc.relkind in ('r','i')
   order   by pc.relname
   ) a
where   1 = 1
--and   "DirNum" = 66252
--and   "ObjectName" like '%Encrypted_Data%'
and   "ObjectType" = 'r'
order   by "MBUsed" desc
   ,"NumRows" desc

which doesn't work because pg_roles doesn't exist.

Thanks

Thom

On Mon, Mar 2, 2009 at 8:25 AM, Thom Brown <thombrown@gmail.com> wrote:
> Hi all,
>
> We have a database server which is on version 8.0.12.  I was wondering if
> anyone knows of a way to tell the size of each database?

Back in the olden days when I was on 8.0 I would just use du -sh to find out.

sudo su - postgres
cd $PGDATA/base
oid2name
du -sh *

and match them up.  yeah, I had a script that made it prettier but
that worked fine.

If you're on windows I don't know what to tell you about doing these
things from the shell.

On Mon, 2 Mar 2009, Scott Marlowe wrote:

> Back in the olden days when I was on 8.0 I would just use du -sh to find
> out. and match them up.  yeah, I had a script that made it prettier but
> that worked fine.

Not sure what version I started using this trick on:

SELECT relfilenode, relpages * 8 AS kb FROM pg_class;

But that might have been 8.0.

There is an example of a script like you describe on starting on P88 of
Bruce's presentation at

http://momjian.us/main/writings/pgsql/administration.pdf

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD