[check_postgres] Getting per-database size values

[Emmanuel Lesouef]

Hi Cedric, and thanks for answering !

Le Mon, 21 Nov 2011 16:38:54 +0100,
Cédric Villemain <cedric.villemain.debian at gmail.com> a écrit :

> 2011/11/21 Emmanuel Lesouef <e.lesouef at crbn.fr>:
> > Hi,
> >
> > I'm currently using check_postgres to get some numerical values
> > (using --output=simple) in order to get them in our Zabbix
> > monitoring system.
> >
> > I would like to get the database size on a per-database basis.
> >
> > Is it possible ?
> 
> yes
> 

Cool.

> >
> > I thought I could use it like this :
> >
> > postgres at pgsql0:~$ /usr/local/bin/check_postgres.pl --output=simple
> > --action=database_size -db db1
> > 1522500408
> > postgres at pgsql0:~$ /usr/local/bin/check_postgres.pl --output=simple
> > --action=database_size -db postgis
> > 1522500408
> >
> > But obviously, it returns the size of the whole cluster.
> >
> > Did I do something wrong ?
> 
> yes and no :)
> There is difference between the connection elements
> (port,db,user,host,service) and the include/exclude to gather
> information.
> 
> In your case, you are connecting to -db db1 or postgis, good. but you
> need to add --include=gis to get the gis db size for example.
> 
> see
> http://bucardo.org/check_postgres/check_postgres.pl.html#basic_filtering
> 
> 

Ok. Understood. It seems to work well. It is strange to me that the
result differs between the use of check_postgres and pg_database_size :

postgres at pgsql0:~$ /usr/local/bin/check_postgres.pl --output=simple,MB
--action=database_size --db=postgis --include=postgis
0
postgres at pgsql0:~$ /usr/local/bin/check_postgres.pl --output=simple,MB
--action=database_size --db=postgis --include=db1
1453
postgres at pgsql0:~$ psql
psql (9.1.1)
Saisissez « help » pour l'aide.

postgres=# SELECT pg_size_pretty(pg_database_size('postgis'));
 pg_size_pretty 
----------------
 37 MB
(1 ligne)

postgres=# SELECT pg_size_pretty(pg_database_size('db1'));
 pg_size_pretty 
----------------
 1454 MB
(1 ligne)

Is it normal ?

-- 
Emmanuel Lesouef
CRBN/DSI/SITI
t: 0231069671
e: e.lesouef at crbn.fr