Thread: Database size in specific metrics

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).


      I would really appreciate it if someone can assist with this.


Regards
Machiel

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_database_size('$i')/1024/1024 As fulldbsizeInMB;"; done


On 08/30/2010 02:25 PM, Machiel Richards wrote:

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).


      I would really appreciate it if someone can assist with this.


Regards
Machiel

-- 


With Regards
Ashish Karalkar

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;

On Wed, 01 Sep 2010 12:35:02 +0200, damien clochard <damien@dalibo.info>
wrote:

The results isn't exactly accurate, I tried it on my svr:

* your select: 12.0000
* du -sh /var/lib/..../<db_nb by oid2name>/: 13M

NOTE: du -sh gives the *theorical* (bytes) occupation on HD (don't take
      care of disk blocks not entirely filled, so the *real* occupation
      is usually >; might be ~13.5M)

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

--
Q:    Why don't lawyers go to the beach?
A:    The cats keep trying to bury them.