Re: Calculating database sizes using a script

HI All

Regarding this previous question I had on determining the database sizes.

Using the command below I was able to determine the database sizes of all databases in the list of databases.

These were also displayed in human redable sizes.

I am however now looking at putting this into a script which will be handed over to another person for several reasons.

One of the things I would like to add in the script is to calculate the total database size (i.e. the size of all the databases combined) and include this in the script as part of the output.

I was thinking of using the linux bc (command line calculator) to calculate the total size, however the output I recieve by the command below is not all in the same units (i.e. kb,mb,gb ) as the sizes vary.

Any ideas on how the total size can be calculated and displayed in a human readable size.

I would appreciate the assistance.

Regards
Machiel

*
*
*

On Tue, Mar 23, 2010 at 5:08 AM, Machiel Richards <machielr(at)rdc(dot)co(dot)za> wrote:

> Hi all

>

>

>

> I have a client that is running a total of 190 Postgresql databases.

>

>

>

> I am busy with an audit on the database sizes,

> performance tuning,etc...

>

>

>

> However to check the database sizes is quite tedious

> as some of the servers are running up to 24 databases on one instance.

>

>

>

> Due to security restrictions I am not yet able to use

> pgadmin / pgphpadmin and need to manually calculate this using ?SELECT

> pg_size_pretty(pg_database_size(''));?

>

>

>

>

>

> Is there a way of running this in a script for each

> database in the instance without having to manually type in each database name?

>

>

>

> For instance. Let the script extract the database

> names and then check the sizes printing it to an output file / screen?

A one-liner shell script is pretty straightforward. You could use your scripting language of choice, besides the shell, if you like:

for i in `psql -t -c "select datname from pg_catalog.pg_database d"`; do echo $i; psql -t -c "select pg_size_pretty(pg_database_size('$i'))"; done

Hope that helps.

Sean

--

Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org) To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-novice