PostgreSQL

Plugin to monitor PostgreSQL using custom queries.

Introduction

Ver. 07-03-2022

With this plug-in we will be able to monitor postgreSQL through customized queries.

Type: Server plug-in

Compatibility matrix

 

Systems where tested

CentOS 7, Fedora, rocky linux
Systems where it should work

Cualquier sistema linux 

Pre requisites

Parameters

Parámetro Descripción
-d,--database Database name (required)
-u,--user Name of the postgreSQL user(required)
-p,-password postgreSQL password (required)
-i,--ip server ip (required)
--port server port. Default 5432 (optional)
--tentacle_port Tentacle port (default 41121)(optional)
--tentacle_address Ip of the tentacle server to send the data (optional)
--agent_name To name the agent that will contain the custom queries modules, by default: "PostgreSQL" (optional).
--prefix_module To prefix the modules in case you want to differentiate them from other executions (optional).
--conf Path of the conf file with the queries (required)
-g,--group Pandora FMS target group (optional)
--data_dir Pandora FMS data directory. By default it is /var/spool/pandora/data_in/ (optional)
--transfer_mode XML transfer method, the two possible options are: tentacle or local.

Pandora_postgresql.conf

In this file the queries that will create modules in PandoraFMS will be introduced.

The format to add the queries is the following:

name_module:query:type:description

* The description is not mandatory, if not entered it will remain empty.

Example:

number_connections:SELECT count(*) FROM pg_stat_activity;:generic_data:Number of Connections or running backend
connections_waiting_in_locks:SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'Lock';:generic_data:Number of connections/backends waiting on locks
connections_idle_in_transaction:SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'idle in transaction';:generic_data:Number of backends in a transaction, but are currently not doing anything and could be waiting for an input from the end user.
number_users:SELECT COUNT(usename) FROM pg_stat_activity;:generic_data:Number users conected in postgreSQL
number_roles:SELECT count(*) FROM pg_roles;:generic_data:A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.
psql_version:SELECT version();:generic_data_string:PostgrSQL version
num_blocks:SELECT COUNT(*) FROM pg_locks;:generic_data:A high number of deadlocks indicates that there is a higher probability that two or more processes will attempt to access the same resource.
number_commits:SELECT sum(xact_commit) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = _database_;:generic_data:Number of commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs.
number_rollbacks:SELECT sum(xact_rollback) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = _database_;:generic_data:This metric represents the number of times, per transaction during the sample period, that users manually issue the ROLLBACK statement or an error occurred during a user's transactions.
tuples_returned:SELECT sum(tup_returned) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = _database_;:generic_data:Number of rows returned by queries in this database
tuples_searched:SELECT sum(tup_fetched) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname )WHERE db.datname = _database_;:generic_data:Number of tuples searched
tuples_inserted:SELECT sum(tup_inserted) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = _database_;:generic_data:Number of rows inserted into table
tuples_updated:SELECT sum(tup_updated) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = _database_;:generic_data:Number of tuples updated
tuples_removed:SELECT sum(tup_deleted) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = _database_;:generic_data:Number of tuples removed
state:SELECT state FROM pg_stat_activity WHERE datname = _database_;:generic_data_string:Database status
number_databases_list:select * from pg_database;:generic_data_string

Manual execution

 

./pandora_postgresql -d <database> -u <user> -p <password> -i <ip> --port <port> --conf <path conf> --transfer_mode tentacle

 

image-1692351671740.png

 

La ruta por defecto de "data_dir" es /var/spool/pandora/data_in , si esta no existe en la máquina que se esta ejecutando el plugin dará error, de ser este el caso, se debe cambiar la ruta con el parámetro "--data_dir" para elegir otra ruta donde se crearán los XML que posteriormente se envíaran.

 

 

 

Configuration in PandoraFMS

As a server plugin

Installation from console

To register the plugin, from the console, go to the "register plugin" section.

register_plugin.png

Click on select file.

register_plugin2.png

Select the pspz2 file that contains the plugin

register1_postgresql.png

A message will be displayed indicating that you have successfully registered.

register2_postgresql.png

Once the plugin is registered, we will see it in the plugins section.

serversingles.png

Clicking on the plugin title will take you to the plugin menu.

register3_postgresql.png

In macros will appear all those that have the plugin, remember that not all are obligatory, only those that are specified.

register4_postgresql.png

Below you can assign the required value to each variable.

register5_postgresql.png

 

Manual installation

Go to servers > plugins:

image-1629974405286.png

Click on add:

image-1629974430627.png

We put the name and description of your choice:

image-1646737918843.png

 

We enter as command the execution with the path of the plugin:

/path_pandora_postgresql

image-1646738193972.png

And in plugin parameters we will enter these followed by the macro "_field_", the mandatory ones for the plugin to work are -d,-u,-p,-i,--port, --transfer_mode and --conf.

We will define these with the macros below.

 

Database

image-1646741029424.png

user

image-1646741076563.png

password

image-1646741107573.png

ip

image-1646741133355.png

puerto

image-1646741160772.png

conf

image-1646741187282.png

transfer_mode

image-1692351184433.png

 

Once this is done, click on "create".

You can check that you have entered the values correctly by executing the terminal preview command


If it does not give errors it is that it has been introduced well.

Once this is done, the only thing left to do is to call it, so we will go to some agent's view and create a module of complements:

image-1646741530197.png

We will give it a name and in the section "plugin" we will put the one we have just configured.

image-1646741600061.png

 

Once this is done, click on create.

If the module is shown with 1, it means that it is running correctly.

image-1646741405902.png

 

 

Modules generated by the plugin

The plugin will create an agent with the name that we have set with the parameter "--agent_name" if this parameter is not used it will stay with "PostgreSQL" by default:

image-1646739709089.png

In this agent, a module will be created for each customized query specified in pandora_postgresql.conf, with the name that we have assigned to each one of them.

For example the following conf:

number_connections:SELECT count(*) FROM pg_stat_activity;:generic_data:Number of Connections or running backend
connections_waiting_in_locks:SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'Lock';:generic_data:Number of connections/backends waiting on locks
connections_idle_in_transaction:SELECT count(*) FROM pg_stat_activity WHERE wait_event = 'idle in transaction';:generic_data
number_users:SELECT COUNT(usename) FROM pg_stat_activity;:generic_data
number_roles:SELECT count(*) FROM pg_roles;:generic_data
psql_version:SELECT version();:generic_data_string
num_blocks:SELECT COUNT(*) FROM pg_locks;:generic_data
psql_diskspace:SELECT ROUND(SUM(pg_database_size(oid))/1024/1024) FROM pg_database;:generic_data
number_commits:SELECT sum(xact_commit) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = 'postgres';:generic_data
number_rollbacks:SELECT sum(xact_rollback) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = 'postgres';:generic_data
tuples_returned:SELECT sum(tup_returned) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = 'postgres';:generic_data
tuples_searched:SELECT sum(tup_fetched) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname )WHERE db.datname = 'postgres';:generic_data
tuples_inserted:SELECT sum(tup_inserted) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = 'postgres';:generic_data
tuples_updated:SELECT sum(tup_updated) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = 'postgres';:generic_data
tuples_removed:SELECT sum(tup_deleted) FROM (pg_database AS db JOIN pg_stat_database sdb ON db.datname=sdb.datname) WHERE db.datname = 'postgres';:generic_data
state:SELECT state FROM pg_stat_activity WHERE datname = 'postgres';:generic_data_string

It will create the following modules:

image-1646740864822.png