Thread: PostgreSQL monitoring

Do you guys have any suggestions on monitoring tools for postgresql
that would provide graphing of data & alerts ?
I have looked and tried out hyperic, munin, Nimbus but was hoping to
something better.

Thanks,

Marcelo
PostgreSQL DBA
Linux/Solaris System Administrator

On Mon, Jan 19, 2009 at 4:38 PM, Marcelo Martins <pglists@zeroaccess.org> wrote:

> Do you guys have any suggestions on monitoring tools for postgresql that
> would provide graphing of data & alerts ?
> I have looked and tried out hyperic, munin, Nimbus but was hoping to
> something better.

To monitor I use the following:

- pg_osmem: http://www.kennygorman.com/wordpress/?p=250
- pg_buffercache (contrib package in Debian)
- pg_top: http://ptop.projects.postgresql.org/
- ps
- pgd: http://www.kennygorman.com/wordpress/?p=260
- iotop: http://guichaz.free.fr/iotop/

And I am in the process of writing a bunch of scripts that the Zabbix
agent will execute to feed the server with information, so that alerts
and graphs can be produced.

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Marcelo Martins a écrit :
>
> Do you guys have any suggestions on monitoring tools for postgresql that
> would provide graphing of data & alerts ?
> I have looked and tried out hyperic, munin, Nimbus but was hoping to
> something better.

Note that you have some plugins :

 * http://bucardo.org/check_postgres/ (plugin for nagios and others)
 * http://muninpgplugins.projects.postgresql.org/ (for munin)


I prefer nagios+munin or nagios+other-graph_tool.

>
> Thanks,
>
> Marcelo
> PostgreSQL DBA
> Linux/Solaris System Administrator
>
>


- --
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkl0pdoACgkQo/dppWjpEvykagCdF0BlMSfyt92XM6NUj6Ez/Nl7
E4cAn2a0HVBgs33cKCoGW+uUSAuvtcg8
=MIMf
-----END PGP SIGNATURE-----

On Mon, Jan 19, 2009 at 05:10:07PM +0100, Cédric Villemain wrote:
> Note that you have some plugins :
>
>  * http://bucardo.org/check_postgres/ (plugin for nagios and others)
>  * http://muninpgplugins.projects.postgresql.org/ (for munin)
>

Thanks for these links.  I am trying out the muninpgplugins but I get
"Global symbol "$statscope" requires explicit package name at /etc/munin/plugins//pg_exilog_stat_database line 179".
Execution of /etc/munin/plugins/pg_exilog_stat_database aborted due to compilation errors.


I have the following in /etc/munin/plugin-conf.d/munin-node

[pg_exilog*]
user postgres
env.dbhost localhost
env.dbname exilog
env.dbpass xxxxxxxxxx
env.dbuser exilog

Any idea why I get the error message?

Regards
Johann

--
Johann Spies          Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch

     "Neither is there salvation in any other; for there is
      none other name under heaven given among men, whereby
      we must be saved."               Acts 4:12

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Johann Spies a écrit :
> On Mon, Jan 19, 2009 at 05:10:07PM +0100, Cédric Villemain wrote:
>> Note that you have some plugins :
>>
>>  * http://bucardo.org/check_postgres/ (plugin for nagios and others)
>>  * http://muninpgplugins.projects.postgresql.org/ (for munin)
>>
>
> Thanks for these links.  I am trying out the muninpgplugins but I get
> "Global symbol "$statscope" requires explicit package name at /etc/munin/plugins//pg_exilog_stat_database line 179".
> Execution of /etc/munin/plugins/pg_exilog_stat_database aborted due to compilation errors.
>
>
> I have the following in /etc/munin/plugin-conf.d/munin-node
>
> [pg_exilog*]
> user postgres
> env.dbhost localhost
> env.dbname exilog
> env.dbpass xxxxxxxxxx
> env.dbuser exilog
>
> Any idea why I get the error message?

Buggy script :-(

I will update the cvs and push a new version very soon.

>
> Regards
> Johann
>


- --
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkl1u0UACgkQo/dppWjpEvxizACfbfsB6kQ1HDK4LCnYG7wxBJgq
8HoAoKyCjKTjwB1trA32dtP+R2yhDy63
=iQa/
-----END PGP SIGNATURE-----

Cool, I have actually been trying out Zabbiz 1.6.x now since I like it
better than munin.

Thanks,

Marcelo
PostgreSQL DBA
Linux/Solaris System Administrator

On Jan 19, 2009, at 9:56 AM, Jaume Sabater wrote:

> On Mon, Jan 19, 2009 at 4:38 PM, Marcelo Martins <pglists@zeroaccess.org
> > wrote:
>
>> Do you guys have any suggestions on monitoring tools for postgresql
>> that
>> would provide graphing of data & alerts ?
>> I have looked and tried out hyperic, munin, Nimbus but was hoping to
>> something better.
>
> To monitor I use the following:
>
> - pg_osmem: http://www.kennygorman.com/wordpress/?p=250
> - pg_buffercache (contrib package in Debian)
> - pg_top: http://ptop.projects.postgresql.org/
> - ps
> - pgd: http://www.kennygorman.com/wordpress/?p=260
> - iotop: http://guichaz.free.fr/iotop/
>
> And I am in the process of writing a bunch of scripts that the Zabbix
> agent will execute to feed the server with information, so that alerts
> and graphs can be produced.
>
> --
> Jaume Sabater
> http://linuxsilo.net/
>
> "Ubi sapientas ibi libertas"

On Wed, Jan 21, 2009 at 5:51 PM, Vladimir Rusinov
<vladimir@greenmice.info> wrote:

>> And I am in the process of writing a bunch of scripts that the Zabbix
>> agent will execute to feed the server with information, so that alerts
>> and graphs can be produced.
>
> Oh, I have exactly the same in my plans. Where it would possible to download
> them (if would)?

Of course you would but, honestly, it is not on top of my TODO list. I
will have to do them, eventually, but I don't see it happening in the
next 4 weeks. Feel free to get started with something and I'll
contribute with what I have as soon as I can (quite busy at work at
the moment, sorry). :)

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Vladimir Rusinov a écrit :
> On Mon, Jan 19, 2009 at 6:56 PM, Jaume Sabater <jsabater@gmail.com> wrote:
>
>> And I am in the process of writing a bunch of scripts that the Zabbix
>> agent will execute to feed the server with information, so that alerts
>> and graphs can be produced.
>
>
> Oh, I have exactly the same in my plans. Where it would possible to download
> them (if would)?
>

I did something in that way, check_postgres.pl is a good script to start from
(add a zabbix output perhaps).  I stop working on that because zabbix is too
painfull to use (*for me*).

Here some draft note about zabbix+check_postgres...Hope it can help.

Agent side:

zabbix_agentd.conf :

  UserParameter=pgs[*],sudo -u postgres
/usr/local/share/zabbix/check_postgres.pl --output=simple --db=$2 --action=$1 $3
| head -n 1
UserParameter=pgm[*],sudo -u postgres /usr/local/share/zabbix/check_postgres.pl
- --output=mrtg   --db=$2 --action=$1 $3 | head -n 1

sudoers :

Runas_Alias DB = postgres
User_Alias  SUPERVISOR = zabbix
Cmnd_Alias  CHECK_PG = /usr/local/share/zabbix/check_postgres.pl
SUPERVISOR  ALL=(DB) NOPASSWD: CHECK_PG


init checksum :

sudo su -s /bin/bash - zabbix
sudo -u postgres /usr/local/share/zabbix/check_postgres.pl  --db=DBNAME
- --action=settings_checksum --critical=0

Server side:

pgs :
 check_postgres.pl output simple
 pgs[ACTION,DBNAME]
pgm :
 check_postgres.pl output mrtg
 pgs[ACTION,DBNAME,EXTRA]


- --
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkl3XDgACgkQo/dppWjpEvz1LQCcDpd88zLVFSiFpqmA6Fr6y7rF
LqMAn2z2SdI4BoceJf9elq72c1OytiHE
=J5LP
-----END PGP SIGNATURE-----