Hi, Guys,

Is there a way to tell what the raw disk space used by a single
database is? I know that databases are fluid, but if there is way to
do even a snap shot view that is a "ball park" figure, I'd be happy.
My user keeps clammering for this figure.

Thanks,
Carol Walter

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Carol Walter напи�а:
> Hi, Guys,
>
> Is there a way to tell what the raw disk space used by a single database
> is? I know that databases are fluid, but if there is way to do even a
> snap shot view that is a "ball park" figure, I'd be happy. My user
> keeps clammering for this figure.
>


Depending on what you mean by "raw disk space" the "pg_database_size"
function (mentioned here -
http://www.postgresql.org/docs/curre...ons-admin.html) will
give you this information.



--
Milen A. Radev


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

walterc@indiana.edu (Carol Walter) writes:
> Is there a way to tell what the raw disk space used by a single
> database is? I know that databases are fluid, but if there is way to
> do even a snap shot view that is a "ball park" figure, I'd be happy.
> My user keeps clammering for this figure.

Sure, you can identify the database via "select oid, * from
pg_catalog.pg_database;"

Then you should be able to head to $PGDATA (where ever the database
data lives), and run "du", and search for the directory whose name is
the "oid" value for the database that you wanted to analyze.

If users are using tablespaces, then tables can live in
user-controlled places, which would make it rather more complex to do
this analysis, but if they have kept to the simpler approach of just
letting data fall where it will, this should do the trick...
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://linuxfinances.info/info/linux.html
"High-level languages are a pretty good indicator that all else is
seldom equal." - Tim Bradshaw, comp.lang.lisp

Thanks so much fro the info.

I've done as you suggested and I've got the correct directories. I
have a second question now. There are forty-four numeric entries in
the directory for the first databases that I looked at. The database
in question has only eight tables. There are eight tables that have
recent dates. Do I need the total of all the files or just the eight
that appear to be the tables that are being updated?

Thanks again,
Carol

On Aug 1, 2007, at 9:25 AM, Chris Browne wrote:

> walterc@indiana.edu (Carol Walter) writes:
>> Is there a way to tell what the raw disk space used by a single
>> database is? I know that databases are fluid, but if there is way to
>> do even a snap shot view that is a "ball park" figure, I'd be happy.
>> My user keeps clammering for this figure.
>
> Sure, you can identify the database via "select oid, * from
> pg_catalog.pg_database;"
>
> Then you should be able to head to $PGDATA (where ever the database
> data lives), and run "du", and search for the directory whose name is
> the "oid" value for the database that you wanted to analyze.
>
> If users are using tablespaces, then tables can live in
> user-controlled places, which would make it rather more complex to do
> this analysis, but if they have kept to the simpler approach of just
> letting data fall where it will, this should do the trick...
> --
> (reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
> http://linuxfinances.info/info/linux.html
> "High-level languages are a pretty good indicator that all else is
> seldom equal." - Tim Bradshaw, comp.lang.lisp
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Wed, Aug 01, 2007 at 12:34:07PM -0400, Carol Walter wrote:

> I've done as you suggested and I've got the correct directories. I
> have a second question now. There are forty-four numeric entries in
> the directory for the first databases that I looked at. The database
> in question has only eight tables. There are eight tables that have
> recent dates. Do I need the total of all the files or just the eight
> that appear to be the tables that are being updated?

Take the whole directory - there are indexes as well. And large tables
are split up AFAIK.

You may try the following shell script to determine the size of all
databases:

#!/bin/bash
#
# Shell script to determine disk usage of PostgreSQL databases.
# by Tino Schwarze/Community4you

PGDATADIR=/data/pgsql/74
PGPORT=5432
PSQLBIN=/opt/pgsql-7.4.3/bin/psql
PGOPTS="-U postgres"

echo "PostgreSQL 7.4.3 database sizes"
$PSQLBIN $PGOPTS -p $PGPORT -t -F " " -A template1 -c 'select oid,datname from pg_database' | \
while read oid dbname ; do
[ "$dbname" != "template1" ] || continue
[ "$dbname" != "template0" ] || continue
size="`du -sh $PGDATADIR/base/$oid | cut -f 1`"
while [ ${#dbname} -lt 16 ] ; do dbname="$dbname " ; done
printf "$dbname\t%6s\n" $size
done


HTH,

Tino.

--
www.quantenfeuerwerk.de
www.spiritualdesign-chemnitz.de
www.lebensraum11.de

Tino Schwarze * Parkstraße 17h * 09120 Chemnitz

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Oh, that's way cool. Thanks for the script.

Carol
On Aug 1, 2007, at 12:46 PM, Tino Schwarze wrote:

> On Wed, Aug 01, 2007 at 12:34:07PM -0400, Carol Walter wrote:
>
>> I've done as you suggested and I've got the correct directories. I
>> have a second question now. There are forty-four numeric entries in
>> the directory for the first databases that I looked at. The database
>> in question has only eight tables. There are eight tables that have
>> recent dates. Do I need the total of all the files or just the eight
>> that appear to be the tables that are being updated?
>
> Take the whole directory - there are indexes as well. And large tables
> are split up AFAIK.
>
> You may try the following shell script to determine the size of all
> databases:
>
> #!/bin/bash
> #
> # Shell script to determine disk usage of PostgreSQL databases.
> # by Tino Schwarze/Community4you
>
> PGDATADIR=/data/pgsql/74
> PGPORT=5432
> PSQLBIN=/opt/pgsql-7.4.3/bin/psql
> PGOPTS="-U postgres"
>
> echo "PostgreSQL 7.4.3 database sizes"
> $PSQLBIN $PGOPTS -p $PGPORT -t -F " " -A template1 -c 'select
> oid,datname from pg_database' | \
> while read oid dbname ; do
> [ "$dbname" != "template1" ] || continue
> [ "$dbname" != "template0" ] || continue
> size="`du -sh $PGDATADIR/base/$oid | cut -f 1`"
> while [ ${#dbname} -lt 16 ] ; do dbname="$dbname
> " ; done
> printf "$dbname\t%6s\n" $size
> done
>
>
> HTH,
>
> Tino.
>
> --
> www.quantenfeuerwerk.de
> www.spiritualdesign-chemnitz.de
> www.lebensraum11.de
>
> Tino Schwarze * Parkstraße 17h * 09120 Chemnitz
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings