Postgresql Monitoring User Guide

General information

Overview

Postgresql monitoring is a Gateway configuration file that enables monitoring of Postgresql database installation through the SQL Toolkit plug-in.

This integration template allows monitoring of queries, views, triggers in the Gateway set-up, and other server activities, such as:

  • Tracking of command being executed in the server.
  • Monitoring of information about table and index dataview.
  • Permits user-defined functions.

This guide discusses the steps to set up the Postgresql integration on a Gateway. Once the integration is set up, the samplers providing the dataviews become available to that Gateway.

To view the sample metrics and dataviews, see Postgresql Monitoring Technical Reference.

User requirements

This monitoring template is a Gateway configuration setup that can be included in the Gateway Setup Editor.

To use this template, your configuration must meet the following requirements:

  • A machine running the Netprobe must have access to the Postgresql published HTTP address and port.
  • SQL Data Manipulation Language (DML).
  • Basic connectivity setup between a SQL client and a SQL server.
  • An Active Console 2 that is connected to the Gateway.
  • Postgresql configuration files extracted to their Gateway set-up.

System requirements

The following requirements must be met prior to the installation and setup of the template:

  • Template package: geneos-integration-postgresql-<version>.zip.
  • Managed entities utilising the samplers defined in the include/PostgresqlMonitoring.xml.
  • Netprobe version 4.5.
  • Postgresql version 9.6.

Audience

This document is a reference guide to introduce templates and scripts built using the Geneos development toolkit plug-ins.

The template allows you to integrate specific applications and services to collect metrics without having to create a new plug-in.

  • Administrator - oversees the administration and installation of the server.
  • User - uses the Postgresql system to monitor the data and reports.
  • Application Team - provides the queries for the SQL Toolkit plug-in.

 

Install and set up

Ensure that you have read and can follow the system requirements prior to installation and setup of this integration template.

  1. Download the config file (geneos-integration-postgresql-<version>.zip) from the ITRS Downloads site.
  2. Open Active Console 2.
  3. Extract the file into the Gateway Setup directory.
  4. In the Navigation panel, click Includes to create a new file.
  5. Enter the location of the file to include in the Location field. In this example, use the include/PostgresqlMonitoring.xml:
  6. Expand the file location in the Include section.
  7. Select Click to load...
  8. Click Yes to load the new Postgresql include file.
  9. Click Managed entities in the Navigation panel.
  10. Add the Postgresql type to the Managed Entity section that you will use to monitor Postgresql.
  11. Click the Validate button to check your configuration.

 

The Validate button allows you to check if there are any errors or warnings in your configuration set-up.

 

Once the Gateway configuration appears in the Includes section, you can add the samplers and other variables.

Set up the samplers

These are the pre-configured samplers available to use in PostgresqlMonitoring.xml.

Configure the required fields by referring to the table below:

Samplers
Checkpoint
Database-Statistics
Processes
Replication
Active Locks
 

 

Set up the variables

The PostgresqlMonitoring.xml template provides the variables that are set in the Environments section:

Samplers Description
PSQL_SERVER_NAME IP/Hostname where the Postgresql installation resides.
PSQL_PORT Assigned port in the Postgresql service.
PSQL_USERNAME
  • pg_stat_user_tables
  • pg_stat_user_indexes
  • pg_statio_user_tables
  • pg_class
  • pg_namespace
  • pg_stat_bgwriter
  • pg_stat_database
  • pg_locks
  • pg_database
  • pg_stat_activity
Username with the rights to access the following tables.
PSQL_PASSWORD Password of the database user.
PSQL_DBNAME

Database names:

  • Server Specific Metrics uses the default database Postgresql.
  • Database Specific Metrics specifies the actual database to monitor.
   

After checking and saving the changes, the samplers you have set in the Gateway configuration display in Active Console 2.

 

Set up the rules

The PostgresqlMonitoring-SampleRules.xml template also provides a separate sample rules that you can use to configure the Gateway Setup Editor.

Your configuration rules must be set in the Includes section.

  1. Enter the location of the file to include in the Location field. In this example, use the include/PostgresqlMonitoring-SampleRules.xml:
  2. The priority controls the importance of a file when merging. Sections in a higher priority file will take precedence over sections in a lower priority file. This priority setting affects the priority of configuration in the main setup file.

  3. Expand the file location in the Includes section.
  4. Select Click to load...
  5. Click Yes to load the new Postgresql include rules file.
  6. Click Rules in the Navigation panel to create new rules.

If a rule has a critical and warning variable, you must configure it both to activate the rule:

Variables Critical Warning
Checkpoint - Checkpoint Requests : Checks the ratio of checkpoints requested vs. scheduled checkpoints PSQL_CHECKPOINT_SCHEDULED_CRITICAL:
Possible values 1.0 - 100.0
PSQL_CHECKPOINT_SCHEDULED_WARNING:
Possible values 1.0 - 100.0
Database-Statistics - Commit Ratio : Checks the ratio between transactions committed vs rolledback PSQL_COMMIT_RATIO_CRITICAL:
Possible values 1.0 - 100.0
PSQL_COMMIT_RATIO_WARNING:
Possible values 1.0 - 100.0
Database-Statistics - Connection utilisation : Rule that checks the connections used expressed in percentage compared to max_connections PSQL_CONNECTIONS_CRITICAL:
Possible values 1.0 - 100.0
PSQL_CONNECTIONS_WARNING:
Possible values 1.0 - 100.0
Database-Statistics - Deadlocks : Checks for deadlocks PSQL_DEADLOCK_RATIO_CRITICAL:
Integer value
PSQL_DEADLOCK_RATIO_WARNING:
Integer value
Processes - Long Running Queries : Checks the duration of query execution PSQL_QUERY_EXECUTION_THRESHOLD_IN_SECS_CRITICAL:
Integer value
PSQL_QUERY_EXECUTION_THRESHOLD_IN_SECS_WARNING:
Integer value
Replication - Delay : Checks for delays in replication PSQL_REPLICATION_DELAY_CRITICAL:
Integer value. Time in seconds
PSQL_REPLICATION_DELAY_WARNING:
Integer value. Time in seconds
Checkpoint - Written Buffers Checkpoint : Checks the ratio of dirty buffers written by checkpoint vs bgwriter plus backends PSQL_WRITTEN_BUFFERS_CRITICAL:
Possible values 1.0 - 100.0
PSQL_WRITTEN_BUFFERS_WARNING:
Possible values 1.0 - 100.0
Database-Statistics - Size Threshold : Checks the size of the databases in the currently monitored server PSQL_DATABASE_SIZE_CRITICAL:
Possible values - XXX kB, XXX MB, XXX GB and XXX TB
Not applicable

The sample configuration file was verified working with Postgresql 9.6 and Netprobe 4.5 versions.

Once the Gateway for rules configuration appears in the SampleRules Includes section, you can set the rules and alerts.