Thread: PostgreSQL monitoring

PostgreSQL monitoring

From
Marcelo Martins
Date:
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


Re: PostgreSQL monitoring

From
"Jaume Sabater"
Date:
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"

Re: PostgreSQL monitoring

From
Cédric Villemain
Date:
-----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-----

Re: PostgreSQL monitoring

From
Johann Spies
Date:
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

Re: PostgreSQL monitoring

From
ashok raj
Date:
 RRD tool can used for drawing graphs by fetching the statics from DB servers and plotting graphs store it an image file and embedded it on the HTML page to look it nice .
 

2009/1/20 Johann Spies <jspies@sun.ac.za>
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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



--
__________________________________________|
Regards,
Ashokraj
9940419867

Re: PostgreSQL monitoring

From
Cédric Villemain
Date:
-----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-----

Re: PostgreSQL monitoring

From
Marcelo Martins
Date:
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"


Re: PostgreSQL monitoring

From
Vladimir Rusinov
Date:




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

--
Vladimir Rusinov
http://greenmice.info/

Re: PostgreSQL monitoring

From
Jaume Sabater
Date:
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"

Re: PostgreSQL monitoring

From
Cédric Villemain
Date:
-----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-----

Re: PostgreSQL monitoring

From
Vladimir Rusinov
Date:


On Wed, Jan 21, 2009 at 8:32 PM, Cédric Villemain <cedric.villemain@dalibo.com> wrote:
-----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.

Thank you, it is good starting point, but I'm going to create template and graphs for it, and it can take many time.

--
Vladimir Rusinov
http://greenmice.info/

Re: PostgreSQL monitoring

From
Vladimir Rusinov
Date:




On Wed, Jan 21, 2009 at 8:30 PM, Jaume Sabater <jsabater@gmail.com> wrote:
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). :)

Don't worry, I understand you. I'm just asking where I could find if your tools are completed and compare it to mine. Are you going to post link to this list?
It's also not on top of my TODO list, and I also think it should not be ready earlier then in 2-3 weeks. I'll post my templates and scripts to http://greenmice.info/ It's mostly on russian, but I'm translating most interesting posts to english, but it could take one more week. :( And I can also post link to this list if anyone interesed.

--
Vladimir Rusinov
http://greenmice.info/

Re: PostgreSQL monitoring

From
Rodrigo Hjort
Date:
Hello admins,

Based on these monitoring needs, I've posted an article called "PostgreSQL monitoring on ZABBIX" where I set up some parameters such as database size, cache hit ratio and success rate:

http://agajorte.blogspot.com/2009/12/postgresql-monitoring-on-zabbix.html

I also found out Vladimir's Zabbix Templates Collection, where several DBMS items are considered:

http://greenmice.info/en/node/136

For those interested in the subject: do you think there is any other valuable PostgreSQL-specific item to be monitored?

--
Best regards,

Rodrigo Hjort
SERPRO/CETEC/Curitiba
www.serpro.gov.br


2009/1/22 Vladimir Rusinov <vladimir@greenmice.info>

On Wed, Jan 21, 2009 at 8:30 PM, Jaume Sabater <jsabater@gmail.com> wrote:
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). :)

Don't worry, I understand you. I'm just asking where I could find if your tools are completed and compare it to mine. Are you going to post link to this list?
It's also not on top of my TODO list, and I also think it should not be ready earlier then in 2-3 weeks. I'll post my templates and scripts to http://greenmice.info/ It's mostly on russian, but I'm translating most interesting posts to english, but it could take one more week. :( And I can also post link to this list if anyone interesed.

--
Vladimir Rusinov
http://greenmice.info/