Re: Database size in specific metrics

Le 30/08/2010 10:55, Machiel Richards a écrit :
> Good day all
>
> I am really hoping that someone can help me out here.
>
>
> I have a server (as example) with 24 postgresql databases running
> on it. Each database differs in size from a couple of kb to a couple of Gb.
>
> When using these different sizes to try and create charts which
> will show the database growth, I am having some issues.
>
>
> The reason for this is that all the values are stored / noted in
> the spreadsheets with the specific metric such as kb, mb,etc... next to
> it in order to differentiate between them.
>
>
> In order to be able to create the chart, the values however
> must only contain the numeric values with no metric values.
>
>
> I am running the following commands to retrieve the database sizes:
>
> *for i in `psql -t -c "select datname from
> pg_catalog.pg_database d" | grep -v template0 | grep -v template1`; do
> echo $i;psql -d $i -t -c "SELECT pg_size_pretty(pg_database_size('$i'))
> As fulldbsize;"; done*
>
> My question:
>
> Does anybody know of a method to retrieve the sizes, however
> , by specifying in which metric it should be displayed? (i.e. 1Gb will
> be returned as 1024Mb).
>

SELECT datname,
pg_database_size(datname)::float/1024/1024
FROM pg_database;