The following topic outlines the required views, tables, and functions needed to properly monitor each PostgreSQL database resource.
- PostgreSQL Required Views, Tables, and Functions
- Creating a Least-Privileged User (LPU)
PostgreSQL Required Views, Tables, and Functions
The table below lists PostgreSQL database resource kinds and their required views, tables, or functions needed to monitor each resource correctly. Prior to configuring an adapter instance for the Management Pack, ensure the following PostgreSQL function/query functionality is added for your PostgreSQL resources.
Resource Type | View/Table/Function Name | Required? | Version |
---|---|---|---|
Instance | pg_stat_bgwriter | Yes | 9.0+ |
Database | pg_stat_database | Yes | 9.0+ |
Database | pg_database | Yes | 9.0+ |
Database | pg_stat_database_conflicts | Yes | 9.1+ |
Database | pg_tablespace | Yes | 9.0+ |
Tablespace* | pg_user* | Yes | 9.0+ |
Table | pg_stat_user_tables | No | 9.0+ |
Table | pg_statio_user_tables | No | 9.0+ |
Table | pg_tables | No | 9.0+ |
Trigger | pg_trigger | No | 9.1+ |
Index | pg_stat_user_indexes | No | 9.0+ |
Index | pg_statio_user_indexes | No | 9.0+ |
Function | pg_stat_user_functions | No | 9.0+ |
Sequence | pg_statio_all_sequences | Yes | 9.0+ |
Session | pg_stat_activity | Yes | 9.2+ |
Query* | pg_stat_statements* | Yes | 9.0+ |
Function Tracking: To track functions, ‘pg_stat_user_functions’ requires the log_statement_stats field to be modified (in postgresql.conf) to allow for any function tracking to happen.
Reference: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-FUNCTIONS
Query Tracking: To track queries, these settings need to be added to the shared_preload_libraries settings:
shared_preload_libraries = ‘pg_stat_statements’
pg_stat_statements.max = 10000
pg_stat_statements.track = all
You also need to create the extension in the database. The extension is database bound and must be created for each database, even though it pulls data from each:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements
You can check if it is installed on the database by calling:
SELECT *
FROM pg_available_extensions
WHERE name = ‘pg_stat_statements’ AND installed_version IS NOT NULL
Creating a Least-Privileged User (LPU)
To help automate the creation of a least-privileged user (LPU) for your PostgreSQL instance, run the following script on the database you plan to monitor. If you're monitoring multiple databases, run the script on the postgres database.
CREATE SCHEMA bluemedora; CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS $$ SELECT * FROM public.pg_stat_statements; $$ LANGUAGE sql VOLATILE SECURITY DEFINER; CREATE OR REPLACE FUNCTION public.explain_this ( l_query text, out explain json ) RETURNS SETOF json AS $$ BEGIN RETURN QUERY EXECUTE 'explain (format json) ' || l_query; END; $$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY DEFINER COST 100 ROWS 1000; CREATE USER lpu WITH PASSWORD 'tmppassword'; GRANT SELECT ON pg_database TO lpu; GRANT SELECT ON pg_stat_bgwriter TO lpu; GRANT SELECT ON pg_stat_database TO lpu; GRANT SELECT ON pg_stat_user_indexes TO lpu; GRANT SELECT ON pg_stat_user_tables TO lpu; GRANT SELECT ON pg_statio_all_sequences TO lpu; GRANT SELECT ON pg_statio_user_indexes TO lpu; GRANT SELECT ON pg_statio_user_tables TO lpu; GRANT SELECT ON pg_tables TO lpu; GRANT SELECT ON pg_tablespace TO lpu; GRANT SELECT ON pg_user TO lpu; GRANT SELECT ON pg_stat_replication TO lpu; GRANT SELECT ON pg_stat_database_conflicts TO lpu; GRANT SELECT ON pg_trigger TO lpu; GRANT SELECT ON pg_stat_activity TO lpu; GRANT SELECT ON pg_stat_statements TO lpu; GRANT USAGE ON SCHEMA bluemedora TO lpu; GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO lpu;
Once complete, you can ensure the user has the EXPLAIN privilege by running:
SELECT exists( SELECT 1 FROM information_schema.role_routine_grants WHERE routine_name='explain_this' AND privilege_type='EXECUTE' AND grantee='explainer');
This query should return a single row with a single column of "exists" with a value of "true".