PostgreSQL

PostgresSQL monitoring solution is based on a remote monitoring approach implemented as a Dynatrace ActiveGate extension. The extension queries Postgres databases for key performance and health. Dynatrace's DAVIS AI then analyzes these metrics to provide anomaly and problem analysis.

• Monitor the health state and performance of their PostgreSQL setup.
• Detect anomalies and alert on them.
• Take pre-emptive measures to avoid service degradations.

Activation

Steps to activate remote monitoring (version 1.255+):

• Activate Extension in the Hub by going to:
• Manage → Dynatrace Hub → PostgreSQL (remote monitoring) → Add to environment
• Add new monitoring configuration

Permissions

Create a database user using the following template:

CREATE USER dynatrace WITH PASSWORD '<PASSWORD>' INHERIT;  
GRANT pg_monitor TO dynatrace;

Requirements

There must be connectivity between the ActiveGate, where the extension is deployed, and the Postgres database.

For top query monitoring to work, the pg_stat_statements table needs to be available. This has to be done at the database level as per the official docs. Ensure also that you have log monitoring enabled.

Top query monitoring

If both Dynatrace log monitoring is enabled and the pg_stat_statements table is available, Dynatrace will ingest the top 100 queries (sorted by total execution time) every 5 minutes and store them as logs. These logs are available either from the database instance screen or on the Databases App, under "Top queries by total execution time". To filter by these queries on a dashboard or notebook, one can filter by dt.extension.name = com.dynatrace.extension.postgres and event.group = top_queries. See below a DQL query example:

fetch logs
| filter dt.extension.name=="com.dynatrace.extension.postgres" and event.group=="top_queries"
| sort total_exec_time desc

Note also that the planning time information for these queries requires pg_stat_statements.track_planning to be enabled.

Regardless of whether pg_stat_statements is available or not, Dynatrace will still collected queries from pg_stat_activity as part of the Queries feature set, which are similarly ingested as logs with event.group = longest_queries.

Execution plan details

For SaaS users who have access to the Databases app and who have top query monitoring enabled (see previous section), fetching execution plans for these queries is now possible. This can be done from the Databases app, under Statement performance, by clicking Request on the execution plan for a specific query.

For that query, the extension will then attemp to execute the following:

SELECT * from dynatrace.dynatrace_execution_plan({query})

and then ingest into Dynatrace the first row of the column named explain. These execution plans are ingested as logs with event.group = execution_plans.

For security reasons which prevent the extension from directly executing anything other than a SELECT command, the dynatrace_execution_plan function has to be defined beforehand at the database level as follows:

CREATE OR REPLACE FUNCTION dynatrace.dynatrace_execution_plan(
   query text,
   OUT explain JSON
) RETURNS SETOF JSON
   LANGUAGE plpgsql
   VOLATILE
   RETURNS NULL ON NULL INPUT
   SECURITY DEFINER
   ROWS 1
   SET plan_cache_mode = force_generic_plan
AS
$$DECLARE
   arg_count integer;
   open_paren text;
   close_paren text;
   explain_cmd text;
   json_result json;
BEGIN

	/* reject statements containing a semicolon in the middle */
   IF pg_catalog.strpos(
         pg_catalog.rtrim(dynatrace_execution_plan.query, ';'),
         ';'
      ) OPERATOR(pg_catalog.>) 0 THEN
      RAISE EXCEPTION 'query string must not contain a semicolon';
   END IF;

   /* get the parameter count */
   SELECT count(*) INTO arg_count
   FROM pg_catalog.regexp_matches( /* extract the "$n" */
           pg_catalog.regexp_replace( /* remove single quoted strings */
              dynatrace_execution_plan.query,
              '''[^'']*''',
              '',
              'g'
           ),
           '\$\d{1,}',
           'g'
        );

   IF arg_count OPERATOR(pg_catalog.=) 0 THEN
      open_paren := '';
      close_paren := '';
   ELSE
      open_paren := '(';
      close_paren := ')';
   END IF;

   /* construct a prepared statement */
   EXECUTE
      pg_catalog.concat(
         'PREPARE _stmt_',
         open_paren,
         pg_catalog.rtrim(
            pg_catalog.repeat('unknown,', arg_count),
            ','
         ),
         close_paren,
         ' AS ',
         dynatrace_execution_plan.query
      );

   /* construct an EXPLAIN statement */
   explain_cmd :=
      pg_catalog.concat(
         'EXPLAIN (FORMAT JSON, ANALYZE FALSE) EXECUTE _stmt_',
         open_paren,
         pg_catalog.rtrim(
            pg_catalog.repeat('NULL,', arg_count),
            ','
         ),
         close_paren
      );

   /* get and return the plan */
    EXECUTE explain_cmd INTO json_result;
    RETURN QUERY SELECT json_result;

   /* delete the prepared statement */
   DEALLOCATE _stmt_;
END;$$;

Notice that the function above is defined with SECURITY DEFINER, meaning it executes with the privileges of the user who defined it, not the one executing it. This is due to the fact that the permissions required to EXPLAIN a query are the same as the one required to run that query, meaning that the user with which this function is defined needs to have a sufficient priveliges to run PREPARE and EXPLAIN on the queries it will be explaining. As such, for full functionality ensure that the function is defined with the appropriate user.

As with any function of this type, we recommend thorough testing before rolling out across big environments.

Troubleshooting execution plan monitoring

If after a couple of minutes the execution plans are not showing up when you press to fetch the plan, one of two things might be happening: either the EXPLAIN query is failing or the ingestion into Dynatrace is causing trouble.

First, to check whether the logs are being brought into Dynatrace at all or if the problem is related to the Databases app open a Notebook and query:

fetch logs
| filter event.group=="execution_plans" and dt.extension.name=="com.dynatrace.extension.postgres" and sql_id=="<Statement ID>"
| sort timestamp desc

If there are logs showing up for the desired time frame this means that execution plan fetching is happening as normal but the Databases app is not picking them up. In this case, please contact Dynatrace support.

If the logs have content Couldn't obtain an execution plan. No execution plan was found., the next step would be to check the ActiveGate datasource logs from a support archive at <support_archive>/remoteplugin/log/extensions/datasources/com.dynatrace.extension.postgres/dynatracesourcesql.jar.log for more details around the error. In tandem with this, it can be helpful to log into the database with the same user which is configured in the Dynatrace extension monitoring configuration and attempt to run

SELECT * from dynatrace.dynatrace_execution_plan({query})

for the problematic query.

If the error relates to permissions, please ensure that the function was defined with the right user (see above). Note also that some queries cannot be explained (for example SET or EXPLAIN queries) so failure for those is expected.

If you believe that the EXPLAIN should have succeeded, please contact Dynatrace support.

The extension is designed to work with PostgreSQL version 11 and later.