pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Raw disk space used
6+ messages / 4 participants
[nested] [flat]

* Raw disk space used
@ 2007-08-01 12:49 Carol Walter <walterc@indiana.edu>
  2007-08-01 13:03 ` Re: Raw disk space used Milen A. Radev <milen@radev.net>
  2007-08-01 13:25 ` Re: Raw disk space used Chris Browne <cbbrowne@acm.org>
  0 siblings, 2 replies; 6+ messages in thread

From: Carol Walter @ 2007-08-01 12:49 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Raw disk space used
  2007-08-01 12:49 Raw disk space used Carol Walter <walterc@indiana.edu>
@ 2007-08-01 13:03 ` Milen A. Radev <milen@radev.net>
  1 sibling, 0 replies; 6+ messages in thread

From: Milen A. Radev @ 2007-08-01 13:03 UTC (permalink / raw)
  To: pgsql-admin

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/current/static/functions-admin.html) will
give you this information.



-- 
Milen A. Radev




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Raw disk space used
  2007-08-01 12:49 Raw disk space used Carol Walter <walterc@indiana.edu>
@ 2007-08-01 13:25 ` Chris Browne <cbbrowne@acm.org>
  2007-08-01 16:34   ` Re: Raw disk space used Carol Walter <walterc@indiana.edu>
  1 sibling, 1 reply; 6+ messages in thread

From: Chris Browne @ 2007-08-01 13:25 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Raw disk space used
  2007-08-01 12:49 Raw disk space used Carol Walter <walterc@indiana.edu>
  2007-08-01 13:25 ` Re: Raw disk space used Chris Browne <cbbrowne@acm.org>
@ 2007-08-01 16:34   ` Carol Walter <walterc@indiana.edu>
  2007-08-01 16:46     ` Re: Raw disk space used Tino Schwarze <postgresql@tisc.de>
  0 siblings, 1 reply; 6+ messages in thread

From: Carol Walter @ 2007-08-01 16:34 UTC (permalink / raw)
  To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Raw disk space used
  2007-08-01 12:49 Raw disk space used Carol Walter <walterc@indiana.edu>
  2007-08-01 13:25 ` Re: Raw disk space used Chris Browne <cbbrowne@acm.org>
  2007-08-01 16:34   ` Re: Raw disk space used Carol Walter <walterc@indiana.edu>
@ 2007-08-01 16:46     ` Tino Schwarze <postgresql@tisc.de>
  2007-08-01 17:51       ` Re: Raw disk space used Carol Walter <walterc@indiana.edu>
  0 siblings, 1 reply; 6+ messages in thread

From: Tino Schwarze @ 2007-08-01 16:46 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Raw disk space used
  2007-08-01 12:49 Raw disk space used Carol Walter <walterc@indiana.edu>
  2007-08-01 13:25 ` Re: Raw disk space used Chris Browne <cbbrowne@acm.org>
  2007-08-01 16:34   ` Re: Raw disk space used Carol Walter <walterc@indiana.edu>
  2007-08-01 16:46     ` Re: Raw disk space used Tino Schwarze <postgresql@tisc.de>
@ 2007-08-01 17:51       ` Carol Walter <walterc@indiana.edu>
  0 siblings, 0 replies; 6+ messages in thread

From: Carol Walter @ 2007-08-01 17:51 UTC (permalink / raw)
  To: Tino Schwarze <postgresql@tisc.de>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2007-08-01 17:51 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-01 12:49 Raw disk space used Carol Walter <walterc@indiana.edu>
2007-08-01 13:03 ` Milen A. Radev <milen@radev.net>
2007-08-01 13:25 ` Chris Browne <cbbrowne@acm.org>
2007-08-01 16:34   ` Carol Walter <walterc@indiana.edu>
2007-08-01 16:46     ` Tino Schwarze <postgresql@tisc.de>
2007-08-01 17:51       ` Carol Walter <walterc@indiana.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox