Thread: Calculating database sizes using a script
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?
Machiel
On Tue, Mar 23, 2010 at 5:08 AM, Machiel Richards <machielr@rdc.co.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
Machiel Richards pisze:
Due to security
restrictionsI
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?
select
pg_size_pretty(pg_database_size(datname)), datname from
pg_catalog.pg_database ;
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@rdc.co.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@postgresql.org) To make changes to your subscription: