PostgreSQL

info

PostgreSQL New uses SmartAgent + Integration Agent to collect PostgreSQL metrics and DBM data for Query performance, execution-plan, active-session, schema-metadata, and service-correlation analysis.

1. Support Scope

Supported database versions:

PostgreSQL 9.6 / 13.22 / 17.10 / 18.0

Note: Other database versions are theoretically supported but must be verified by the user.

Database-side prerequisites

The database side must install the postgresql-contrib extension bundle, which mainly provides the pg_stat_statements performance-monitoring extension (most distributions already include the pg_stat_statements extension by default).

Supported system architectures:

Linux x86_64 (amd64), requires glibc ≥ 2.17. Verified versions: CentOS 7, CentOS 8, Ubuntu 21.10

Note: Other system versions are theoretically supported but must be verified by the user.

Supported agent versions:

SmartAgent 10.3.0+

2. Deployment

Install the SmartAgent probe and enable integration-agent.

3. PostgreSQL-side Configuration: User and Privileges

3.1 Create the bonree user

'bonree': the read-only user for the plugin probe to access the database; <UNIQUEPASSWORD>: the database access password you set.

Connect to the target database as a superuser (connecting to the postgres database by default):

psql -h <HOST> -p 5432 -U postgres -d postgres

CREATE USER bonree WITH PASSWORD '<UNIQUEPASSWORD>';

-- Recommended: limit the maximum number of connections to avoid a connection storm when the Agent restarts
ALTER ROLE bonree CONNECTION LIMIT 5;

Check that access works:

psql -h <HOST> -p 5432 -U bonree -d postgres -c "SELECT version();"

3.2 Basic privileges

3.2.1 PostgreSQL 15+ (15 / 16 / 17 / 18)

Starting with PG 15, roles no longer automatically inherit the privileges of member roles by default, so INHERIT must be enabled explicitly:

ALTER ROLE bonree INHERIT;

CREATE SCHEMA IF NOT EXISTS bonree;
GRANT USAGE ON SCHEMA bonree TO bonree;
GRANT USAGE ON SCHEMA public TO bonree;

GRANT pg_monitor TO bonree;
GRANT SELECT ON pg_stat_database TO bonree;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

3.2.2 PostgreSQL (10 / 11 / 12 / 13 / 14)

CREATE SCHEMA IF NOT EXISTS bonree;
GRANT USAGE ON SCHEMA bonree TO bonree;
GRANT USAGE ON SCHEMA public TO bonree;

GRANT pg_monitor TO bonree;
GRANT SELECT ON pg_stat_database TO bonree;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

3.2.3 PostgreSQL 9.6

PostgreSQL 9.6 has no pg_monitor role and cannot read the full session and SQL statistics, so SECURITY DEFINER is required (executing the internal SQL with the privileges of the function's creator):

CREATE SCHEMA IF NOT EXISTS bonree;
GRANT USAGE ON SCHEMA bonree TO bonree;
GRANT USAGE ON SCHEMA public TO bonree;

GRANT SELECT ON pg_stat_database TO bonree;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Read the full pg_stat_activity / pg_stat_statements
CREATE OR REPLACE FUNCTION bonree.pg_stat_activity()
RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION bonree.pg_stat_statements()
RETURNS SETOF pg_stat_statements AS
$$ SELECT * FROM pg_stat_statements; $$
LANGUAGE sql SECURITY DEFINER;

Privilege / Role	Purpose
`INHERIT` (PG 15+)	Allows the role to inherit privileges of member roles such as `pg_monitor`. PG 15 defaults to `NOINHERIT`; not enabling it renders the grants ineffective
`pg_monitor` (PG 10+)	Read `pg_stat_*`, lock views, active sessions, and other system statistics
`SELECT ON pg_stat_database`	Database-level cumulative metrics (commits/rollbacks, block reads/writes, etc.)
`CREATE EXTENSION pg_stat_statements`	Enables the statement-level performance statistics view; DBM statement metrics
`USAGE ON SCHEMA bonree`	Allows the user to enter the bonree schema

3.3 DBM Execution-Plan Collection: Create the `bonree.explain_statement` Function

PostgreSQL does not have a built-in EXPLAIN stored procedure like MySQL, so a SECURITY DEFINER function must be created in each monitored database.

CREATE OR REPLACE FUNCTION bonree.explain_statement(
   l_query TEXT,
   OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
   curs REFCURSOR;
   plan JSON;
BEGIN
   SET TRANSACTION READ ONLY;
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;

GRANT USAGE ON SCHEMA bonree TO bonree;
GRANT EXECUTE ON FUNCTION bonree.explain_statement(TEXT) TO bonree;

Note on SECURITY DEFINER: The function runs EXPLAIN with the identity of the definer (usually a superuser). The caller bonree only needs EXECUTE to obtain the execution plan, with no need to grant direct access to the business tables.

3.3.1 Optional: column-level statistics `bonree.column_statistics`

If you need to enable collect_column_statistics, additionally create the following in each monitored database:

CREATE OR REPLACE FUNCTION bonree.column_statistics()
RETURNS TABLE (
    schemaname name, tablename name, attname name,
    n_distinct real, avg_width integer, null_frac real,
    inherited boolean, correlation real, most_common_freqs real[]
) AS
$$ SELECT schemaname, tablename, attname, n_distinct, avg_width, null_frac,
          inherited, correlation, most_common_freqs
     FROM pg_catalog.pg_stats
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); $$
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp;

GRANT EXECUTE ON FUNCTION bonree.column_statistics() TO bonree;

Enable on the Agent side:

instances:
  - dbm: true
    ...
    collect_column_statistics:
      enabled: true

3.4 `postgresql.conf` Configuration (required for DBM collection)

Set the following parameters in the database configuration postgresql.conf. A database restart is required after changes for them to take effect.

# Required: preload the pg_stat_statements extension
shared_preload_libraries = 'pg_stat_statements'

# Required: avoid long SQL being truncated in pg_stat_activity (default 1024)
track_activity_query_size = 4096

# Optional: provide I/O timing for execution plans and pg_stat_statements
# track_io_timing = on

# Optional: track statements inside stored procedures/functions
# pg_stat_statements.track = all

# Optional: increase the upper limit of the pg_stat_statements normalized-SQL cache (default 5000; can be raised under high concurrency and many SQL variants)
# pg_stat_statements.max = 10000

# Optional: when set to off, only tracks SELECT / UPDATE / DELETE and does not track utility commands such as PREPARE and EXPLAIN
# pg_stat_statements.track_utility = off

3.5 Self-check script

Check database privileges: PostgreSQL 10+

export PGPASSWORD='<UNIQUEPASSWORD>'

psql -h <HOST> -p 5432 -U bonree -d postgres -A \
  -c "SELECT * FROM pg_stat_database LIMIT 1;" \
  && echo "pg_stat_database - OK" \
  || echo "pg_stat_database - FAIL"

psql -h <HOST> -p 5432 -U bonree -d postgres -A \
  -c "SELECT * FROM pg_stat_activity LIMIT 1;" \
  && echo "pg_stat_activity - OK" \
  || echo "pg_stat_activity - FAIL"

psql -h <HOST> -p 5432 -U bonree -d postgres -A \
  -c "SELECT * FROM pg_stat_statements LIMIT 1;" \
  && echo "pg_stat_statements - OK" \
  || echo "pg_stat_statements - FAIL"

psql -h <HOST> -p 5432 -U bonree -d postgres -A \
  -c "SELECT bonree.explain_statement('SELECT 1');" \
  && echo "explain_statement - OK" \
  || echo "explain_statement - FAIL"

For PostgreSQL 9.6, change the pg_stat_activity / pg_stat_statements checks to:

psql ... -c "SELECT * FROM bonree.pg_stat_activity() LIMIT 1;"
psql ... -c "SELECT * FROM bonree.pg_stat_statements() LIMIT 1;"

Expected output:

All four checks return OK.
SHOW shared_preload_libraries; contains pg_stat_statements.
SHOW track_activity_query_size; ≥ 4096.

4. Agent-side Configuration: `postgres.d/conf.yaml`

4.1 Minimal configuration

OS	Path
Linux	`${APM_HOME}/integration/conf/integration.d/postgres.d/conf.yaml`

instances:
  - host: 127.0.0.1         # IP or domain of the database host to connect to
    port: 5432              # Connection port; PostgreSQL default is 5432
    username: bonree        # Username the probe uses to access the database; must match the database-side configuration
    password: '<PASSWORD>'  # User password; quote it if it contains special characters
    dbm: false              # true: enable database performance monitoring; false: disable; default false
    # tags:
    #   - cluster:cluster_name        # Custom cluster tag to attach when reporting data
  # Multiple instances can be configured, e.g.:
  # - host: 10.1.1.1
  #   port: 5432
  #   username: bonree
  #   password: '<PASSWORD>'
  #   dbname: myapp           # Optional; default is postgres
  #   dbm: true
  #   tags:
  #     - cluster:prod-pg-cluster

4.2 Enabling DBM and Schema Collection

instances:
  - host: 127.0.0.1
    port: 5432
    username: bonree
    password: '<PASSWORD>'
    dbm: true                         # Enable DBM collection
    # tags:
    #   - cluster:cluster_name

    collect_schemas:
      enabled: true                   # Use with caution on large databases; collects database/table/column/index metadata. Default false.

4.3 Applying and Verifying the Configuration

The configuration takes effect hot. If a change does not take effect, restart SmartAgent (run the bash command: systemctl restart bonree-agent).

5. Service and Trace Correlation

When an application service (e.g. a Java service) accesses a monitored database, you can enable different propagation modes on the Bonree ONE platform, turning SQL-comment injection on/off to associate or disassociate services or traces.

Note: Correlation currently supports only Java services monitored by the Java probe; services monitored by other probes will be supported later.

5.1 Configuration steps

Go to the Bonree ONE platform home page -> Deployment Configuration -> Rule Configuration -> Data Collection -> Database -> Create (create an end-to-end correlation rule) -> Select service scope -> Select database type -> Select propagation mode (i.e. correlation mode; default is Off; options are Off, Full, and Service).

5.2 Propagation mode descriptions

Off mode: No comment is injected; database activity cannot be traced back to upstream services or traces.
Service mode: Only service-level information is injected into the SQL comment, providing basic correlation analysis; trace correlation is not supported.
Full mode: Complete service and Trace information is injected; database activity can be traced back to upstream services, traces, and related information.

5.3 Notes

Correlation prerequisite: Not all statements can be correlated. Correlation information can only be collected once the SQL comment has been successfully written into the database performance tables (e.g. long-running or blocking statements).
Performance impact: The SQL comment itself usually adds little overhead to network transmission, parsing, and CPU; however, Full mode may affect the execution-plan cache (e.g. cache misses, hard parses), so enable it with caution.
Prepared Statement injection: Applies to PostgreSQL only. When enabled, Trace information is carried via the connection-level ApplicationName before each PreparedStatement execution. This may cause extra session-attribute updates and network round-trips, increasing execution latency in high-frequency SQL scenarios; evaluate performance before enabling.
Comment position: For special compatibility in complex environments; by default the comment is injected before the SQL.

1. Support Scope​

Supported database versions:​

Supported system architectures:​

Supported agent versions:​

2. Deployment​

3. PostgreSQL-side Configuration: User and Privileges​

3.1 Create the bonree user​

3.2 Basic privileges​

3.2.1 PostgreSQL 15+ (15 / 16 / 17 / 18)​

3.2.2 PostgreSQL (10 / 11 / 12 / 13 / 14)​

3.2.3 PostgreSQL 9.6​

3.3 DBM Execution-Plan Collection: Create the bonree.explain_statement Function​

3.3.1 Optional: column-level statistics bonree.column_statistics​

3.4 postgresql.conf Configuration (required for DBM collection)​

3.5 Self-check script​

4. Agent-side Configuration: postgres.d/conf.yaml​

4.1 Minimal configuration​

4.2 Enabling DBM and Schema Collection​

4.3 Applying and Verifying the Configuration​

5. Service and Trace Correlation​

5.1 Configuration steps​

5.2 Propagation mode descriptions​

5.3 Notes​