Configuring monitoring of PostgreSQL database

As an administrator, you want to keep your environment up and running. For this purpose, you use BMC Helix Operations Management  or TrueSight Operations Management. You start by collecting information (in the form of metrics) about your environment and then use this information to proactively resolve issues. You create a monitor policy after installing the PATROL Agent and knowledge modules (KMs). After the policy is enabled, data collection starts. For more information about monitor policies, see Defining monitor policies.


Related topics

Monitor-types-and-attributes

Before you begin

Configure a monitoring environment to collect the metrics of your monitoring environment.  

  • Make sure that the monitoring users have appropriate permissions. For more information, see  Monitoring-users-requirements.
  • To monitor table spaces locally, make sure the PATROL Agent user has read access to the file system. If you monitor table spaces remotely, the user you entered in the Remote OS Connection details   field has read access to the file system.
  • Make sure that the pg_buffercache extension is enabled in the database to be able to monitor the buffer metrics.
  • Make sure that the pg_stat_statements extension is enabled in the database to monitor the wait time metrics.

Important

While configuring monitoring for Schema, Tables, and Index, make sure that you specify the appropriate filtering option because it creates many metrics.

To configure monitoring of PostgreSQL database

  1. Perform one of the following actions:
    • In BMC Helix Operations Management, click Configuration, select Monitor Policies, and click Create.
    • In TrueSight Operations Management, click Configuration, select Infrastructure Policies, and click Create Policy.
  2. On the Monitoring tab, click Add Monitoring Configuration.

  3. To configure the KM, set the following preferences in the Add Monitoring Configuration dialog box:

    Monitoring Solution

    Monitor Profile

    Monitor Type

    PostgreSQL Database

    PostgreSQL Monitoring

    PostgreSQL

  4. In the Environment Configurations section, click Add and enter the following details: 

Field

Description

Environment name

Enter a unique name for the environment under which PostgreSQL instances are discovered.
Use only alphanumeric characters in the name (A-Z, a-z, 0-9).
Do not use any of the following characters: ![@#$%?{}^\\\/|+=\&*();']

PostgreSQL Connection details

PostgreSQL host

Enter the host name or the IP address of the PostgreSQL host.
You can use the $HOSTNAME variable for the PostgreSQL host if it is running on the PATROL Agent host.

PostgreSQL Port

Enter the port number to connect to the PostgreSQL database.

PostgreSQL database name

Enter the name of the PostgreSQL database to monitor.
Important: Leave the field blank if the database and user name are the same.

User Name

Enter the user name to connect to the PostgreSQL database.
Important: The user must have a superuser role to monitor table spaces in the PostgreSQL database.

Password
Confirm Password

Enter the password to connect to the PostgreSQL database.
Confirm the password.

SSL Connection Details

SSL connection mode

Select whether to use SSL to connect to the database and, if yes, select the required mode. The following options are provided:

  • Require
  • None
  • Verify-full
  • Verify-ca

The default option is None.

Client cert file

Enter the full path of the client certificate file.
For example, /home/user/PostgreSQL/postgres-client.crt.

Client key file

Enter the full path of the client key file. The key file must be formatted as pk8, and the user permission must be chmod 600.
For example, /home/user/PostgreSQL/postgres-client.key.pk8.

Root cert file

Enter the full path of the root certificate file. For example, /home/user/PostgreSQL/root.crt.

Remote OS Connection details for Tablespace monitoring

Remote OS type

If the database is on a remote host, select the operating system of the host. The following options are provided:

  • UNIX
  • Windows
  • None

Select None if the database is on a local host and proceed to the Filtering section.

Remote OS Credentials

Enter the following Remote OS Credentials for table space monitoring:

  • User Name: Enter the user name.
  • Password: Enter the password.
  • Confirm Password: Confirm the password.

Remote SSH port (UNIX only)

Enter the port number if the database is installed on a UNIX operating system. The default port number is 22.

Important: If the SSH connection fails, update the crypto policies to DEFAULT: SHA1.

Filtering

Database filtering options

Important: If you monitor all databases by entering the .* regular expression, you will collect a large number of unwanted metrics and adversely affect your storage. Many metrics will be collected if you monitor all databases by entering the .* regular expression. This will
lead to the collection of a large number of unwanted metrics and also affect your storage. Th. Therefore, to use storage spaces judiciously, we recommend that you identify important databases for monitoring and enter regular expressions that identify only those databases.

Filtering mode

Select this option to include or exclude a database.

Filtering criteria

Enter the database names or regular expressions to identify the databases to include or exclude from monitoring. Use a comma to separate multiple entries.
For example, database1, database2, truesight.*

Tablespace filtering options

Important: If you monitor all table spaces by entering the .* regular expression, a large number of metrics are collected. It will lead to a collection of large number of unwanted metrics and affect your storage also. Therefore, to use the storage spaces judiciously, we recommend that you identify important table spaces for monitoring and enter the regular expressions that identify only those table spaces.

Filtering mode

Select this option to include or exclude a table space.

Filtering criteria

Enter the table space names or regular expressions to identify the table spaces to include or exclude from monitoring. Use a comma to separate multiple entries.
For example, table space1, table space2, truesight.*

Schema filtering options

Important: If you monitor all schemas by entering the .* regular expression, many metrics will be collected. This will lead to the collection of a large number of unwanted metrics and also affect your storage. Therefore, to use the storage spaces judiciously, we recommend that you identify important schemas for monitoring and enter regular expressions that identify only those schemas.

Filtering mode

Select this option to include or exclude a schema.

Filtering criteria

Enter the schema names or regular expressions to identify the schemas to include or exclude from monitoring. Use a comma to separate multiple entries.
For example, schema, schema2, truesight.*

Table filtering options

Filtering option

Select the filtering option from the list of available filtering options.

Filter by names

Filtering mode

Select this option to include or exclude a table.

Filtering criteria

Enter the table names or regular expressions to identify the tables to include or exclude from monitoring. Use a comma to separate multiple entries.
For example, table1, table2, truesight.*

Filter by top n tables

Top number of tables

Enter the number of tables to be filtered. This field is mandatory.
The valid input range is 1-100.

Maximum table instances limit

Enter the maximum number of table instances to filter. This field is mandatory.
The valid input range is 1-2500.

Number of days to keep table instances

Enter the number of days to store the table instances. This field is mandatory.
The valid input range is 1-60.

Selection Criteria

Select the selection criteria to obtain the top n tables.

  • Total Size
  • Sequential Rows Read
  • Sequential Scans
  • Index Scans
  • Live Rows Fetched by Index Scans
  • Dead Rows (Bloat) Percent
  • Buffer Size Used

Index filtering options

Filtering mode

Select this option to include or exclude an index.

Filtering criteria

Enter the index names or regular expressions to identify the indexes to include or exclude from monitoring. Use a comma to separate multiple entries.
For example, index1, index2, truesight.*

Filter for top n queries by total wait time

For more information, see Configuring Configuration Variables.

Total wait time monitoring

Select this option to enable filtering of top n queries by total wait time.

Top Number of queries

Enter the number of queries that you want to filter for monitoring. This field is mandatory.
The valid input range is 1-100.

Maximum query instances limit

Enter the maximum number of query instances to filter. This field is mandatory.
The valid input range is 1-2500.

Number of days to keep query instances

Enter the number of days to store the query instance. This field is mandatory.
The valid input range is 1-60.

Custom SQL queries

Important: Custom SQL query options are available if you click Add. For more information about using this option, see Using-custom-SQL-queries.

Execute query on

From the list of instances, select the instance on which you want to execute custom SQL queries:

  • All: Select this option to execute a query on all instances.
  • Primary: Select this option to execute a query on the primary instance.
  • Standby: Select this option to execute a query on the standby instance.

SQL query name

Enter the name for the SQL query. Use the following characters in the environment name:
A-Z a-z 0-9 -_
Do not use the following characters in the environment name:
 ![@#$%?{}^\\\/|+=\&*();']

PostgreSQL database name

Enter the name of the PostgreSQL database on which this query is executed.

SQL query

Enter the SQL query command.

Collection time (min)

Enter the SQL query collection time. The default collection time is 1 minute.

Environment settings

Logging

Select this option to enable detail level logging for all PostgreSQL databases configured on this environment to get more details about policy configuration and data collection. The log files are created under the PATROL_HOME directory:
Java: PATROL_HOME/Patrol3/pgrkm/logs/java
PSL: PATROL_HOME/Patrol3/log/pgr-*.kmlog

Device Mapping Settings

Device mapping



User defined name

Use the selected option as a device name on the Devices page. Select one of the following options to choose as the device name:

FQDN: The fully qualified hosting name is used as the device name.
User Defined: Enter a custom name for the device in the User defined name field.
PATROL Agent host name: The device is created with the PATROL Agent name used to collect data.

If you selected the User-Defined option, enter the user defined device name for the device mapping.
The user-defined name cannot contain any special characters.

Long running duration settings

Query (minutes) 

Enter the time a query must run before it is considered a long running query. Long running queries are reported by the Long Running SQL attribute.
The valid input range is 1-65536.

Long running duration settings

Specify the condition for long running queries.

For example, if you want to exclude replication or auto vacuum related queries, enter the following condition:
query not like '%replication%' or query not like 'auto%'

Transaction (minutes)

Enter the time a transaction must run before it is considered as a long running transaction. Long transactions are reported by the Long Running Idle Transactions attribute.
The valid input range is 1-65536. 

Java Collector Settings

Java path

Enter the path to the JRE directory (or the $JAVA_HOME environment variable) on the PATROL Agent host.

If you use the $JAVA_HOME variable, the KM uses the Java location of the user running the PATROL Agent.

If you enter a path for the JRE directory, you must enter the full path, such as C:\Program files\Java\jre1.8.0_151. Do not use the short path format.

If this field is blank, the KM looks for the Java installed in the PATROL Agent home directory as:

Linux: $PATROL_HOME/../openjdk; $PATROL_HOME/../jre64
Windows: %PATROL_HOME%/../openjdk; %PATROL_HOME%/../jre64

JVM arguments

Enter the optional Java Virtual Machine arguments for the Java collector.

Collection Settings

Availability collection time (min)

Enter the time interval used to check for the availability of the environment for collecting data. The default collection time is 1 minute. 
The valid input range is 1-60.

Data collection time (min)

Enter the data collection time in minutes. The default collection time is 5 minutes.
The valid input range is 1-60.
Important: If the data collection time is set to less than 5 minutes, there might be KM performance issues.

Table data collection time (min)

Enter the data collection time in minutes for the table. The default table data collection time is 60 minutes.
The valid input range is 10-1440. 

Wait time SQL data collection time (min)

Enter the total time interval in minutes to collect or report the value for the wait time SQL attributes. This field applies to SQL instance attributes only. The default wait time for SQL data collection is 5 minutes. 
The valid input range is 5-60.

For more information, see Configuring Configuration Variables.

Global Monitoring Setting

Select this option to enable debug logging for global configuration.

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*