Aller au contenu principal

PostgreSQL DB

Prerequisites

Create a user dedicated on the server.

CREATE USER monitoring WITH PASSWORD 'centreon';

Grant SELECT Privileges on in order to use the SQL mode

GRANT SELECT ON mytable TO monitoring;

Centreon Plugin

Install this plugin on each needed poller:

yum install centreon-plugin-Applications-Databases-Postgresql

In order to use this template, the following RPM are needed on your poller:

  • postgresql
  • postgresql-libs
  • perl-DBD-Pg

Centreon Configuration

Create a new server postgresql

Go to "Configuration > Hosts" and click "Add". Then, fill the form as shown by the following table :

FieldValue
Host nameName of the host
AliasHost description
IPHost IP Address
Monitored fromMonitoring Poller to use
Host Multiple TemplatesApp-DB-Postgre-custom

Click "Save" button.

Those services were automatically created for this host:

ServiceDescription
BloatCheck bloat (unused amount of dead space) in table and indexes of the Postgres server
Cache-HitratioCheck the "buffer cache hitratio" of the Postgres server
ConnectionMonitor to the connection time to the db
Connection-NumberMonitor the number of connections
LocksCheck locks of the Postgres server
PingMonitor host response time
Query-timeCheck how long a specific query takes to run
Time-SyncCheck time between poller and the Postgres server

Host Macro Configuration

The following macros must be configured on host:

MacroDescriptionDefault valueExample
POSTGRESPORTPort used to connect to the DB server54325432
POSTGRESUSERNAMEthe postgres db userUSERNAMEroot
POSTGRESPASSWORDthe postgres db user's passwordPASSWORDp@ssw0rd

Service Macro configuration

The following macros must be configured on services:

ServiceMacroDescriptionDefault ValueExample
BloatWARNINGWarning Threshold1GB1GB
BloatCRITICALCritical Threshold2GB2GB
BloatACTIONThe test determined by the pluginbloat
BloatEXTRAEXCLUDEDatabase to exclude--exclude=postgres,template0,template1
Cache-HitratioWARNINGWarning Threshold90%90%
Cache-HitratioCRITICALCritical Threshold80%80%
Cache-HitratioACTIONThe test determined by the pluginhitratio
Cache-HitratioEXTRAEXCLUDEDatabase to exclude--exclude=postgres,template0,template1
ConnectionACTIONThe test determined by the pluginconnection
ConnectionWARNINGWarning Threshold00
ConnectionCRITICALCritical Threshold00
Connetions-NumberACTIONThe test determined by the pluginbackends
connections-NumberWARNINGWarning Threshold90%90%
Connection-NumberCRITICALCritical Threshold95%95%
Connection-NumberEXTRAEXCLUDEDatabase to exclude--exclude=postgres,template0,template1
LocksWARNINGWarning Threshold200200
LocksCRITICALCritical Thresholdtotal=250:waiting=5:exclusive=20total=250:waiting=5:exclusive=2
LocksACTIONThe test determined by the pluginlocks
Query-TimeWARNINGWarning Threshold30 secondes30 secondes
Query-TimeCRITICALCritical Threshold1 minutes1 minutes
Query-TimeACTIONThe test determined by the pluginquery_time
Query-TimeEXTRAEXCLUDEDatabase to exclude--exclude=postgres,template0,template1
Query-TimeEXTRAEXCLUDEUSERUser to exclude--excludeuser=postgres
Time-SyncWARNINGWarning Threshold22
Time-SyncCRTICALCritical Threshold55
Time-SyncACTIONThe test determined by the plugintimesync
Time-SyncEXTRAEXCLUDEDatabase to exclude--exclude=postgres,template0,template1