Set up PostgreSQL for Monitoring

User Permissions

Create a non-superuser and grant monitoring permissions. To achieve this, 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.
Warning: You must be a superuser to execute this section.
  1. Call the get_sa() function:
    CODE 
    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;
  2. Call the get_querystats( ) function:
    CODE 
    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;
The monitoring user must also be able to connect remotely to the PostgreSQL instance from Splunk AppDynamics for the database machine.

Enable the pg_stat_statements Section

Warning: You must be a superuser to execute this section.
  1. Run the following command to create the pg_stat_statements extension:
    CODE 
    create extension pg_stat_statements
  2. Restart the database if you are creating the pg_stat_statements extension for the first time.

Validate the Setup

Warning: Ensure that the newly created appduser (monitoring user) executes this section.
Run the following queries:
CODE 
SELECT * FROM pg_stat_activity_allusers
CODE 
SELECT * FROM pg_stat_statements_allusers
If the queries run successfully and you get an output, the setup is successful.