BindPlane Documentation
BindPlane Documentation

Setup Requirements

Suggest Edits

Data Collection Setup

Metrics are collected via JDBC from PostgreSQL database instances.

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

Enable Query Tracking:

Query Tracking: To track queries, these settings need to be added to the shared_preload_libraries settings in postgresql.conf:

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;

📘

Define Connected Database Instance

By default, BindPlane will connect to ‘pg_ database’ and retrieve available databases through it. If another database is specified when adding a source, it will connect to that database only.

Network Requirements

Port: 5432 (TCP) Default

Least Privileged User

🚧

Query Level Monitoring Permissions

For security, an administrative-level monitoring user (i.e., “super user”) is required to view the SQL text or queryid of queries executed by other users.

An “insufficient privileges” error will be returned in the Query text field if a read-only user is used. Tablespace data also requires an administrative-level monitoring user.

However, using our defined LPU script will allow you to bypass assigning the "super user" attribute.

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 bluemedora WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO bluemedora;
GRANT SELECT ON pg_stat_bgwriter TO bluemedora;
GRANT SELECT ON pg_stat_database TO bluemedora;
GRANT SELECT ON pg_stat_user_indexes TO bluemedora;
GRANT SELECT ON pg_stat_user_tables TO bluemedora;
GRANT SELECT ON pg_statio_all_sequences TO bluemedora;
GRANT SELECT ON pg_statio_user_indexes TO bluemedora;
GRANT SELECT ON pg_statio_user_tables TO bluemedora;
GRANT SELECT ON pg_tables TO bluemedora;
GRANT SELECT ON pg_tablespace TO bluemedora;
GRANT SELECT ON pg_user TO bluemedora;
GRANT SELECT ON pg_stat_replication TO bluemedora;
GRANT SELECT ON pg_stat_database_conflicts TO bluemedora;
GRANT SELECT ON pg_trigger TO bluemedora;
GRANT SELECT ON pg_stat_activity TO bluemedora;
GRANT SELECT ON pg_stat_statements TO bluemedora;
GRANT USAGE ON SCHEMA bluemedora TO bluemedora;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO bluemedora;

Supported Versions

PostgreSQL: 9.1+

Connection Parameters

NameRequired?Description
HostRequiredThe hostname or IP address of the PostgreSQL instance to connect to.
PortThe port for communication to the PostgreSQL instance.
UsernameRequired
Password
SSL ConfigurationThe SSL mode to use when connecting to the target. Can be configured to not use SSL (No SSL), use SSL but do not verify the target's certificate (No Verify), and use SSL and verify the target's certificate (Verify).
DatabaseComma-separated list of database(s) to monitor. Leave blank to monitor all databases.
Query CountThe number of queries to collect. Enter 0 to disable query monitoring.
Order Queries ByEnter how you want your queries ordered. Options include: Calls, Average Time, Total Time, and All.
Show Query TextWhether to show query text. If disabled, just the query ID will be shown.
Function CountEnter the number of queries to collect. Enter 0 to disable function monitoring.
Order Functions ByEnter how you want your functions ordered. Options include: Calls, Average Time, Total Time, and All.

Updated almost 5 years ago