Re: Calculating database sizes using a script

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