Thread: determining database size

I would just like to know how to determine the size of a database. The
directories inside the data directory are numerical, so I can't associate
them with a particular database or table.

I would basically just like to create reports so I can determine how fast the
disk usage is increasing.

Any suggestions?

Thanks,
    Jeff Davis

Jeff Davis wrote:
> I would just like to know how to determine the size of a database. The
> directories inside the data directory are numerical, so I can't associate
> them with a particular database or table.
>
> I would basically just like to create reports so I can determine how fast the
> disk usage is increasing.
>
> Any suggestions?
>

Try:
test=# select oid, datname from pg_database;
   oid  |  datname
-------+-----------
  16613 | test
      1 | template1
  16560 | template0
  16671 | test2


[postgres@jec-linux dblink]$ cd $PGDATA
[postgres@jec-linux data]$ cd base
/opt/data/pgsql/data/base
[postgres@jec-linux base]$ ls
total 24
drwx------    6 postgres postgres     4096 Apr 16 10:44 .
drwx------    6 postgres postgres     4096 Apr 17 10:53 ..
drwx------    2 postgres postgres     4096 Apr 16 10:43 1
drwx------    2 postgres postgres     4096 Apr 16 10:42 16560
drwx------    2 postgres postgres     4096 Apr 16 10:44 16613
drwx------    2 postgres postgres     4096 Apr 16 10:44 16671


And you can see which directory is which database. There is also
something in ~/pgsql/contrib/oid2name that you might want to look at
(never used it myself though).

HTH,

Joe

On Wed, 17 Apr 2002, Jeff Davis wrote:

> I would just like to know how to determine the size of a database. The
> directories inside the data directory are numerical, so I can't associate
> them with a particular database or table.
>
> I would basically just like to create reports so I can determine how fast the
> disk usage is increasing.
>
> Any suggestions?

If you look at the relpages column in pg_class for the tables, etc. in your
database(s) I think you should be able to add up the storage used.

On a related note, sort of, I see in pg_database a column for datpath. Does
this indicate that we can move entire databases to other locations by changing
this field?


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

Take a look at dbsize in the contrib directory of the source
distribution - it's a function you add to your DB to get the size of
databases. I don't know how the RPMs handle the contrib stuff, but it
might be available without compiling it yourself.

Nicer than the other ways...

Jeff Davis writes:
 > I would just like to know how to determine the size of a database. The
 > directories inside the data directory are numerical, so I can't associate
 > them with a particular database or table.
 >
 > I would basically just like to create reports so I can determine how fast the
 > disk usage is increasing.