AppDynamics PostgreSQL Database - Monitoring Extension

Use Case

PostgreSQL is an open source object-relational database system.

The PostgreSQL monitoring extension captures metrics from a PostgreSQL database and displays them in the AppDynamics Metric Browser.

Prerequisites

Before the extension is installed, the prerequisites mentioned here need to be met. Please do not proceed with the extension installation if the specified prerequisites are not met.

Installation

1. Run 'mvn clean install' from "PostgreSQLMonitorRepo"
2. Unzip the contents of "PostgreSQLMonitor-VERSION.zip" from target directory as "PostgreSQLMonitor" and copy the "PostgreSQLMonitor" directory to <MachineAgentHome>/monitors/
3. Configure the extension by referring to the Configuration section.
4. Configure the path to the config.yml file by editing the task-arguments in the monitor.xml file.

       <task-arguments>
           <argument name="config-file" is-required="true" default-value="monitors/PostgreSQLMonitor/config.yml" />
       </task-arguments>
   

5. Restart the machine agent.

Please place the extension in the "monitors" directory of your Machine Agent installation directory. Do not place the extension in the "extensions" directory of your Machine Agent installation directory.

Configuration

Note : Please make sure not to use tab (\t) while editing yaml files. You can validate the yaml file using a yaml validator

Configure the extension by editing the config.yml file in <MachineAgentHome>/monitors/PostgreSQLMonitor/. The metricPrefix of the extension has to be configured as specified here. Please make sure that the right metricPrefix is chosen based on your machine agent deployment, otherwise this could lead to metrics not being visible in the controller.

Configuring the servers and database

1. Configure the PostgreSQL clusters/servers properties by specifying the displayName(required), host(required), port(required), user(required), password (only if authentication enabled), encryptedPassword(only if password encryption required) under servers. Also specify the databases that have to be monitored. You can specify multiple servers in the same config.yml file.

   servers:
     - displayName: "Local cluster"
       host: "127.0.0.1"
       useIpv6: "false"
       port: "5432"
       user: ""
       password: ""
       encryptedPassword: ""
   #    optionalConnectionProperties:
   #      connectTimeout: 100
   #      tcpKeepAlive: true
       databases:
         ...
     - displayName: "Local cluster"
       host: "::1"
       useIpv6: "true"
       port: "5432"
       user: ""
       password: ""
       encryptedPassword: ""
       databases:
         ...
   

   When using ipv6 address set useIpv6: "false". Additional connection properties can be set using optionalConnectionProperties, you can refer here for all connection parameters.
2. Configure the databases under each server, atleast on database is required under one server to configure some queries and fetch metrics. Configure the database by providing dbName and configuring queries as explained in the next section. You can configure multiple databases.

   databases:
     - dbName: "test"
       queries:
         ...
   

Configuring queries

Only queries that start with SELECT are allowed.
The extension supports getting values from multiple columns at once but it can only pull the metrics from the latest value from the row returned.

The name of the metric displayed on the Metric Browser will be the "name" value that is specified in columns.

queries : You can add multiple queries under this field, each query configured will consist of the following

1. name : The name you would like to give to the metrics produced by this query.
2. serverLvlQuery : Set this to true only if the query returns stats for the databases under the current server
3. queryStmt : This will be your SQL Query that will be used to query the database.
4. columns : Under this field you will have to list all the columns that you are trying to get values from.
   • name : The name of the column you would like to see on the metric browser.
   • type : This value will define if the value returned from the column will be used for the metric path or if it is going to be the value of the metric.
     • metricPath : If you select this, this value will be added to the metric path for the metric.
     • metricValue : If you select this, then the value returned will become your metric value that will correspond to the name you specified above.

Example, Consider the below query for server Local Cluster

databases:
  - dbName: "test"
    queries: 
    # Add where clauses to query to filter databases
      - name: "Database Stats"
        serverLvlQuery: "true"
        queryStmt: "SELECT datname, numbackends
                    FROM pg_stat_database"
        # the columns are the metrics to be extracted
        columns:
          - name: "datname"
            type: "metricPath"
          - name: "numbackends"
            type: "metricValue"
            properties:
              alias: "Number of connections"
              aggregationType: "OBSERVATION"
              timeRollUpType: "AVERAGE"
              clusterRollUpType: "INDIVIDUAL"

The above query will return 1 metric, with metric path -
Custom Metrics|Local Cluster|Database Stats|<datname>|Number of connections. Since the above query has serverLvlQuery: true dbName won't be a part of the metric path.

Consider the below query for server Local Cluster

databases:
  - dbName: "test"
    queries:
      - name: "Table Stats"
        serverLvlQuery: "false"
        # add where clause to the query to filter tables
        queryStmt: "SELECT relname, seq_scan, seq_tup_read
                    FROM pg_stat_user_tables where relname = 'myTable'"
        columns:
          - name: "relname"
            type: "metricPath"
          - name: "seq_scan"
            type: "metricValue"
            properties:
              alias: "Sequential Scans"
              delta: "true"
              aggregationType: "OBSERVATION"
              timeRollUpType: "AVERAGE"
              clusterRollUpType: "INDIVIDUAL"
          - name: "seq_tup_read"
            type: "metricValue"
            properties:
              alias: "Tuples fetched by Sequential Scans"
              delta: "true"
              aggregationType: "OBSERVATION"
              timeRollUpType: "AVERAGE"
              clusterRollUpType: "INDIVIDUAL"

Assume that this query returns -

The above query will return 2 metrics-

Custom Metrics|Local Cluster|test|Table Stats|relname|Sequential Scans = 10
Custom Metrics|Local Cluster|test|Table Stats|relname|Tuples fetched by Sequential Scans = 200

numberOfThreads

Use the following formula for calculating numberOfThreads

numberOfThreads = for each server (1 + number_of(databases)). For example if you have 1 server and 2 databases then numberOfThreads = 1 + 2 = 3

metricPathReplacements

Please visit this page to get detailed instructions on configuring Metric Path Character sequence replacements in Extensions.

Credentials Encryption

Please visit this page to get detailed instructions on password encryption. The steps in this document will guide you through the whole process.

Extensions Workbench

Workbench is an inbuilt feature provided with each extension in order to assist you to fine tune the extension setup before you actually deploy it on the controller. Please review the following document for how to use the Extensions WorkBench

Troubleshooting

Please follow the steps listed in the troubleshooting document in order to troubleshoot your issue. These are a set of common issues that customers might have faced during the installation of the extension.

Contributing

Always feel free to fork and contribute any changes directly via GitHub.

Version

Note: While extensions are maintained and supported by customers under the open-source licensing model, they interact with agents and Controllers that are subject to AppDynamics’ maintenance and support policy. Some extensions have been tested with AppDynamics 4.5.13+ artifacts, but you are strongly recommended against using versions that are no longer supported.