Обсуждение: Raw disk space used
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
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
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
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
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