database Integration
postgresql (en)


ServicePilot database-postgresql


# PostgreSQL Database

Overview

This package aims at monitoring a PostgreSQL Database.

Description

This package automatically configures the ServicePilot Agent in order to monitor a PostgreSQL Database by using SQL requests.

The statistics gathered in this way allow system and process performance management. It also provides statistics on this database and instance rows.

Requirements

  • Network Flows - It is is necessary to ensure network connectivity between ServicePilot and the monitored device. In case of a network infrastructure using a firewall, all the ports below must be opened:

    • TCP/5432: Between ServicePilot Agent and PostgreSQL Server
    • ServicePilot Manager Web server access (by default TCP/80 when using HTTP or TCP/443 when using HTTPS although this port is configurable): Between ServicePilot Agent and ServicePilot Manager
  • Third Party Configuration

    Login Account on the PostgreSQL database. See the Notes section below.

  • ServicePilot Requirements

    • ServicePilot Manager minimum version: 8.5
    • ServicePilot Agent minimum version: 8.5 installed and configured

Installation

Before adding a resource to monitor, make certain that all pre-requisites are in place and if a ServicePilot Agent is required, that it is communicating correctly with the ServicePilot Manager.

Add resource using Views Configuration web interface

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration. The Configuration > Views web page will open.
  3. Click on the view in which to place the new resource in the Views hierarchy on the left of the interface. The View editor section will show the existing view contents.
  4. From the Packages list on the right of the interface, click and drag the database-postgresql package into the View editor and let go.
  5. The Package properties dialog box will open to allow resource configuration.
  6. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  7. Click Save to apply the new resource to ServicePilot configuration.

Key field notes

  1. In the Monitoring Policies tab, specify the policy or policies to apply to the package

  2. Parameters tab, complete the fields as follows:

    1. IP Address/FQDN: Specify the IP address, host name or FQDN for the PostgreSQL Database, as resolvable by the machine on which the ServicePilot Agent is running
    2. Database to monitor: Specify the name of the database to monitor
    3. Database Login: Specify the username of the PostgreSQL Login Account
    4. Database Password: Specify the password of the PostgreSQL Login Account

Notes

  1. Used Tables:

    • Pg_Database_Size
    • Pg_Stat_Database
    • Pg_Stat_user_tables
  1. PostgreSQL Login Account

This package requires a PostgreSQL Login Account to connect to the PostgreSQL Database to gather statistics for system and process performance management.

As a minimum, a PostgreSQL user might be created with:

  CREATE USER spmonitor;

  \password spmonitor

CREATE USER spmonitor; This creates a user spmonitor with LOGIN privileges. \password spmonitor This defines a password for the user spmonitor.

  1. Make sure that the user account to access the PostgreSQL database from ServicePilot respects the following settings (see the pg_hba.conf file)

    Connection type: hostnossl
    
    Authentication method: trust
    
    • Notes: after executing the above line you will be asked for the password to assign to spmonitor and you will need to confirm the password you previously entered.
database postgresql 0

database postgresql 1

database postgresql 2