Skip to main content

PostgreSQL DB

Pack Assets​

Templates​

The Centreon Pack PostgreSQL brings a host template:

  • App-DB-Postgres-custom

It brings the following service templates:

Service AliasService TemplateService DescriptionDefaultDiscovery
BloatApp-DB-Postgres-BloatCheck tables and btrees bloat
Cache-HitratioApp-DB-Postgres-Cache-HitratioCheck the buffer cache hitratioX
CollectionApp-DB-Postgres-CollectionCollect and compute SQL datas
ConnectionApp-DB-Postgres-ConnectionCheck database connectionX
Connection-NumberApp-DB-Postgres-Connection-NumberCheck the current number of connections on databasesX
Database-SizeApp-DB-Postgres-Database-SizeCheck databases sizeX
LocksApp-DB-Postgres-LocksCheck databases locksX
Query-TimeApp-DB-Postgres-Query-TimeCheck the time of running queries on databasesX
Sql-StatementApp-DB-Postgres-Sql-StatementCheck allowing to execute a custom SQL request with a digital answer
StatisticsApp-DB-Postgres-StatisticsCheck databases queries statistics
Tablespace-SizeApp-DB-Postgres-Tablespace-SizeCheck tablespaces usage
Time-SyncApp-DB-Postgres-Time-SyncCheck time offset between the poller and the server
VacuumApp-DB-Postgres-VacuumCheck the last execution vacuum time

Discovery rules​

Rule nameDescription
App-DB-Postgres-Databases-SizeDiscover databases and monitor space usage

More information about discovering services automatically is available on the dedicated page and in the following chapter.

Collected metrics & status​

Metric NameUnit
db_name~table_name#table.space.usage.bytesB
db_name~table_name#table.space.free.bytesB
db_name~table_name#table.dead_tuple.bytesB
db_name~index_name#index.space.usage.bytesB
db_name~index_name#index.leaf_density.percentage%

Prerequisites​

To monitor your PostgreSQL server, create a dedicated read-only user:

CREATE USER centreonro WITH PASSWORD 'test';
GRANT CONNECT ON DATABASE postgres TO centreonro;
GRANT USAGE ON SCHEMA public TO centreonro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO centreonro;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO centreonro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO centreonro;

To use service Tablespace-Size, you need to use a superuser account.

To use service Bloat, you need to install the extension pgstattuple (https://docs.postgresql.fr/13/pgstattuple.html) and add following privileges:

GRANT EXECUTE ON FUNCTION pgstattuple(regclass) TO centreonro;
GRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO centreonro;

Setup​

  1. Install the Centreon plugin package on every Centreon poller expected to monitor PostgreSQL resources:
yum install centreon-plugin-Applications-Databases-Postgresql
  1. On the Centreon web interface, install the PostgreSQL Centreon Pack on the Configuration > Plugin Packs page.

Configuration​

Host​

  • Log into Centreon and add a new host through Configuration > Hosts.
  • Fill the Name, Alias and IP Address/DNS fields according to your PostgreSQL server's settings.
  • Apply the App-DB-Postgres-custom template to the host.
  • Once the template is applied, fill in the corresponding macros. Some macros are mandatory.
MandatoryMacroDescription
XPOSTGRESUSERNAME
XPOSTGRESPASSWORD
POSTGRESPORTPort used (Default: 5432)
POSTGRESDATABASEDatabase connection (Default: postgres)
POSTGRESEXTRAOPTIONSAny extra option you may want to add to every command line (eg. a --verbose flag)

How to check in the CLI that the configuration is OK and what are the main options for?​

Once the plugin is installed, log into your Centreon Poller's CLI using the centreon-engine user account (su - centreon-engine) and test the plugin by running the following command:

/usr/lib/centreon/plugins/centreon_postgresql.pl \
--plugin=database::postgres::plugin \
--mode=connection-time \
--hostname=10.0.0.1 \
--database=postgres \
--port=5432 \
--username='centreonro' \
--password='test'

The expected command output is shown below:

OK: Connection established in 0.533s. | 'connection.time.milliseconds'=533ms;;;0;

All available options for a given mode can be displayed by adding the --help parameter to the command:

/usr/lib/centreon/plugins/centreon_postgresql.pl \
--plugin=database::postgres::plugin \
--mode=connection-time \
--help

All available options for a given mode can be displayed by adding the --list-mode parameter to the command:

/usr/lib/centreon/plugins/centreon_postgresql.pl \
--plugin=database::postgres::plugin \
--list-mode

Troubleshooting​

Please find the troubleshooting documentation for Centreon Plugins typical issues.