Global Configuration for monitoring PostgreSQL database

Monitor the performance and metrics of various database servers with a global filtering feature. You can apply a selected filter option if you monitor multiple database servers. For details about what attributes are collected in monitoring, see Monitor-types-and-attributes.

Related topics

Monitor-types-and-attributes

Before you begin

Make sure that you have configured the environment by following the guidelines outlined in Configuring-the-PostgreSQL-KM-in-TrueSight-or-Central-Monitoring-Administration for the global configuration of the PostgreSQL database.

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. In 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 Global

  4. In the Global Configurations window, enter the following details: 

Field

Description

Filtering

Database filtering options

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

Override local configuration

Select this checkbox to apply the selected filtering options across all environments.

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 a 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.

Override local configuration

Select this checkbox to apply the selected filtering options across all environments.

Filtering mode

Select if you want 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, a large number of metrics are collected. It will lead to a collection of a large number of unwanted metrics and affect your storage also. Therefore, to use the storage spaces judiciously, we recommend that you identify important schemas for monitoring and enter the regular expressions that identify only those schemas.

Override local configuration

Select this checkbox to apply the selected filtering options across all environments.

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

Override local configuration

Select this checkbox to apply the selected filtering options across all environments.

Filtering option

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

Filter by names

Filtering mode

Select if you want 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

Override local configuration

Select this checkbox to apply the selected filtering options across all environments.

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

Important: 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.

Enable global Custom SQL queries

Select this check box to apply the added custom SQL queries across all environments. Also, make sure to choose one of the following configuration options from the dropdown menu based on your specific requirements:

  • Select Merge with local configuration to merge the existing custom SQL queries with local configuration settings.
  • Select Override local configuration to override the local configuration settings.

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 PostgreSQL database name. You can use regular expressions or comma-separated lists for multiple databases on which you want to run SQL queries. If the specified database is not found on the PostgreSQL server, the SQL query will not be executed.

Filtering examples:
Example 1
To filter a database with an exact name like Production, enter \bProduction\b
Example 2
To filter the database starting with Pro, enter ^Pro.*
Example 3
To filter all databases that contain the word duct, enter *duct.*
Example 4
To filter all databases that end with ion, enter .*ion$
Example 5
To filter multiple databases, starting with Pro and ending with ion, enter ^Pro.*|.*ion$

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

Override local configuration

Select this checkbox to apply this setting across all environments.

Device Mapping Settings

Device mapping



User defined name

Device mapping enables you to 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.

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 as 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 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 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 is applicable for SQL instance attributes only. The default wait time for SQL data collection is 5 minutes. 
The valid input range is 5-60.

Important: For more information, see Configuring-configuration-variables.

 

 

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