PostgreSQL

This document describes the PostgreSQL functionality of PandoraFMS discovery.

Introduction

The purpose of this plugin is to monitor PostgreSQL databases, through queries that will extract information about data that is key to knowing the performance and status of the databases, such as the number of connections, number of queries, status of restarts, ratio fragmentation and cache. These data will be reflected in PandoraFMS, in modules that will provide the statistical value, within an agent that will represent each database.

This plugin is developed to be used with Pandora FMS Discovery, so unlike other plugins it does not generate agents by XML, but rather everything discovered is returned in the JSON output of the plugin.

Prerrequisites

This plugin makes remote connections to the databases to be monitored, so it is necessary to ensure connectivity between the Pandora FMS server and said databases.

Parameters and configuration

Parameters

--conf Path to configuration file
--target_databases Path to the configuration file that contains the database targets
--target_agents Path to the configuration file that contains the agent targets
--custom_queries Path to the configuration file containing the custom queries

Configuration file (--conf)

agents_group_id = < ID of the group in which the agents will be created >
interval = < Agent monitoring interval in seconds >
user = <Connection user>
password = <Password>
threads = < Number of threads that will be used to create agents >
modules_prefix = <Modules Prefix>
execute_custom_queries = < Enable with 1 to enable the use of custom queries >
analyze_connections = < Enable with 1 to enable connection monitoring >
engine_uptime = <Enable with 1 to enable runtime monitoring>
query_stats = < Enable with 1 to enable query statistics monitoring >
fragmentation_ratio = < Enable with 1 to enable fragmentation ratio statistics monitoring >
cache_stats = < Enable with 1 to enable cache statistics monitoring >

Example

agents_group_id = 10
interval = 300
user = sa 
password = HHgD85V@
threads = 1
modules_prefix = 
execute_custom_queries = 1
analyze_connections = 1
engine_uptime = 1
query_stats = 1
fragmentation_ratio = 1
cache_stats = 1

List of target databases (--target_databases)

The content of the file will be a list of target databases, separating each database by commas or lines. The format for a database may be any of the following:

ip:port\databases

Example

172.17.0.3:5432\postgres

List of target agents (--target_agents)

The content of the file will be a list of agent name bases, separating each agent by commas or lines. These agent names will be used to dump the information from each target database into the corresponding indicated agent name, rather than letting the plugin generate the agent names automatically.
 
The position of each agent name in the listing must match the position of the target database in its own listing, that is, the name for the first target database will be the first name in this listing, taking into account that blank lines are ignored.

Example


agente1,,agente3
agente4
agente5,agente6,agente7,,agente9

Custom queries (--custom_queries)

A module must be introduced for each personalized query that is intended to be monitored. The modules must follow a structure, which is the following:

check_begin --> Module opening label
name --> Module name
description --> Description of the module.
operation --> Operation type
datatype --> Module type
min_warning --> Configuration of the minimum warning threshold
max_warning --> Configuration of the maximum warning threshold
str_warning --> Warning string configuration
warning_inverse --> Activate the inverse interval with 1 for warning threshold
min_critical --> Setting the minimum critical threshold
max_critical --> Setting the maximum critical threshold
str_critical --> Critical string configuration
critical_inverse --> Activate inverse interval with 1 for critical threshold
module_interval --> This interval is calculated as a multiplier of the agent interval.
target --> Custom query
target_databases --> Database agents in which the module will be created
check_end --> Module closing tag

Example

check_begin
name Select 1
description Number of invalid objects
operation value
datatype generic_data
min_warning 5
target SELECT 1 FROM dual
target_databases all
check_end

check_begin
name NumeroConexiones
description Number of connections
operation value
datatype generic_data
min_warning 5
target SELECT COUNT(*) AS NumeroConexiones FROM v$session;
target_databases pandora
check_end

check_begin
name NumeroRegistros.files_table
description Invalid objects (detail)
operation full
datatype generic_data
target SELECT COUNT(*) AS NumeroRegistros FROM files;
target_databases pandora
check_end

Manual execution

The plugin execution format is as follows:

./pandora_postgresql \
--conf < path to configuration file > \
--target_databases < path to the configuration file containing the target databases > \
[ --target_agents < path to agent configuration file > ] \
[ --custom_queries < path to the configuration file containing the custom queries > ]

Example:

./pandora_postgresql \
--conf /usr/share/pandora_server/util/plugin/postgresql.conf \
--target_databases /usr/share/pandora_server/util/plugin/targets.conf \
--target_agents /usr/share/pandora_server/util/plugin/target_agents.conf \
--custom_queries /usr/share/pandora_server/util/plugin/custom_queries.conf

 

Discovery

This plugin can be integrated with Pandora FMS Discovery.

To do so, you must load the ".disco" package that you can download from the Pandora FMS library:

https://pandorafms.com/library/ 

image.png

Once loaded, PostgreSQL environments can be monitored by creating Discovery tasks from the Management > Discovery > Applications section.

The following minimum data will be requested for each task:

image.png

You can also adjust the task configuration to customize the desired monitoring:

image.png

Tasks that are successfully completed will have an execution summary with the following information:

image.png

Tasks that are not completed successfully will have an execution summary recording the errors that occurred.

Agents and modules generated by the plugin

The plugin will create an agent for each target database. That agent will contain the following modules

If engine_uptime is activated:

restart_detection It will be 0 if an unexpected restart has been detected, and 1 if this is not the case. When a server restarts unexpectedly, there may be an interruption in access to the database and potentially lost transactions or data not saved correctly.

If query_stats is activated:

queries: select Number of SELECT queries. SELECT queries are used to retrieve data from the database. Monitoring SELECT queries allows you to assess query efficiency and index optimization. By identifying slow or inefficient SELECT queries, you can take steps to improve their performance, such as adding appropriate indexes, optimizing the queries, or adjusting server configuration.
queriers: update Number of UPDATE queries. UPDATE queries are used to modify existing data in the database. Monitoring UPDATE queries is important to assess the frequency and efficiency of data updates. You can identify UPDATE queries that affect a large number of rows or have a significant impact on server performance. This allows you to optimize queries, review table structure, or take steps to reduce the load generated by updates.

queries: delete

Number of DELETE queries. DELETE queries are used to delete data from the database. Monitoring DELETE queries is useful for evaluating the frequency and efficiency of data deletions. You can identify DELETE queries that affect a large number of rows or that have a significant impact on server performance. This allows you to optimize queries, review table structure, or take steps to reduce the load generated by deletes.
queries: insert Number of INSERT queries. INSERT queries are used to insert new data into the database. Monitoring INSERT queries allows you to assess the frequency and efficiency of data inserts. You can identify INSERT queries that are placing a high load on the server or could be causing performance issues. This allows you to optimize queries, review table structure, or consider lazy insert strategies to improve performance in high-concurrency environments.

If analyze_connections is activated:

session usage Number of current connections with respect to the total of maximum connections. Monitoring session usage in SQL Server is important to optimize performance, identify blocking issues, improve security and auditing, and efficiently plan server resources.

If cache_stats is enabled:

allocated buffer cache

Total memory space to store temporary data. The cache buffer is a critical part of memory management in PostgreSQL, as it stores data retrieved from tables and is used to reduce the need to constantly access disk storage.

backend user buffer cache Memory space per user for quick access. Each user connection can have its own buffer cache to temporarily store the data it accesses. This can help reduce the need to access the shared cache buffer.
checkpoints buffer cache Temporary memory to synchronize data with disk. The checkpoint buffer cache temporarily stores data related to the checkpoint process. A checkpoint is a point in time at which PostgreSQL ensures that modified data has been written to disk.
cleaned buffer cache Space for unmodified data, reducing disk reads. Maintaining a buffer cache for clean data can help reduce the need to read data from disk storage, thereby improving the performance of database queries and operations.

If fragmentation_ratio is activated:

fragmentation ratio The fragmentation ratio. The Fragmentation Ratio in PostgreSQL Database is a value that indicates the amount of space wasted due to fragmentation in a data structure. A high Fragmentation Ratio indicates greater fragmentation and wasted space, which can impact system performance.

The plugin will also create a module for each custom query defined in the configuration file.