Set Up PostgreSQL for Monitoring

The following details are required to add required user permission to monitor the PostgreSQL database:

User Permissions

You must be a superuser to execute this section.

Create a non-superuser and grant monitoring permissions. Perform the following steps to create a SECURITY DEFINER function. This allows non-superusers to view the contents of pg_stat_activity and pg_stat_statements.

1. Call the get_sa() function:

   CREATE FUNCTION get_sa() 
   RETURNS SETOF pg_stat_activity LANGUAGE sql AS
   $$ SELECT * FROM pg_catalog.pg_stat_activity; $$
   VOLATILE
   SECURITY DEFINER;
    
   CREATE VIEW pg_stat_activity_allusers AS SELECT * FROM get_sa(); 
   GRANT SELECT ON pg_stat_activity_allusers TO public;

   CODE

2. Call the get_querystats() function:

   CREATE FUNCTION get_querystats() 
   RETURNS SETOF pg_stat_statements LANGUAGE sql 	AS
   $$ SELECT * FROM pg_stat_statements; $$
   VOLATILE
   SECURITY DEFINER;
   CREATE VIEW pg_stat_statements_allusers AS SELECT * FROM get_querystats();
   GRANT SELECT ON pg_stat_statements_allusers TO public;

   CODE

The monitoring user must also be able to connect remotely to the PostgreSQL instance from Cisco AppDynamics for the database machine.

Enable the pg_stat_statements Section

You must be a superuser to execute this section.

Run the following command to create the pg_stat_statements extension:

create extension pg_stat_statements

Validate the Setup

Ensure that the newly created appduser (monitoring user) executes this section.

Run the following queries:

1. SELECT * FROM pg_stat_activity_allusers
2. SELECT * FROM pg_stat_statements_allusers

If the queries run successfully and you get an output, the setup is successful.