pgsentinel

Overview

Requires preload alongside pg_stat_statements.

Version

Build

You can build the RPM / DEB packages for pgsentinel using pig build:

pig build pkg pgsentinel         # build RPM / DEB packages

Install

You can install pgsentinel directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pgsentinel;          # Install for current active PG version

pig ext install -y pgsentinel -v 18  # PG 18
pig ext install -y pgsentinel -v 17  # PG 17
pig ext install -y pgsentinel -v 16  # PG 16
pig ext install -y pgsentinel -v 15  # PG 15
pig ext install -y pgsentinel -v 14  # PG 14

dnf install -y pgsentinel_18       # PG 18
dnf install -y pgsentinel_17       # PG 17
dnf install -y pgsentinel_16       # PG 16
dnf install -y pgsentinel_15       # PG 15
dnf install -y pgsentinel_14       # PG 14

apt install -y postgresql-18-pgsentinel   # PG 18
apt install -y postgresql-17-pgsentinel   # PG 17
apt install -y postgresql-16-pgsentinel   # PG 16
apt install -y postgresql-15-pgsentinel   # PG 15
apt install -y postgresql-14-pgsentinel   # PG 14

Preload:

shared_preload_libraries = 'pgsentinel';

Create Extension:

CREATE EXTENSION pgsentinel;

Usage

Sources:

• pgsentinel 1.5.1 README
• pgsentinel 1.5.1 release
• 1.5.1 upgrade SQL
• pgsentinel control file

pgsentinel records active session history by sampling pg_stat_activity at regular intervals and linking activity with pg_stat_statements query statistics. It keeps recent samples in shared-memory ring buffers managed by a background worker.

shared_preload_libraries = 'pg_stat_statements,pgsentinel'
pg_stat_statements.track = all
pgsentinel.db_name = 'postgres'

Restart PostgreSQL, then enable both extensions in the database used by the worker:

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pgsentinel;

Active Session History

SELECT ash_time, datname, usename, pid, state,
       wait_event_type, wait_event, query, queryid
FROM pg_active_session_history
ORDER BY ash_time DESC;

Key columns beyond pg_stat_activity:

Query Statistics History

When enabled, pgsentinel also samples pg_stat_statements concurrently:

SELECT ash_time, queryid, calls, total_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements_history
ORDER BY ash_time DESC;

Example: Wait Analysis

-- Top wait events in the last hour
SELECT wait_event_type, wait_event, count(*)
FROM pg_active_session_history
WHERE ash_time > now() - interval '1 hour'
  AND wait_event IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

-- Blocking analysis
SELECT blockerpid, blocker_state, count(*)
FROM pg_active_session_history
WHERE blockers > 0
GROUP BY 1, 2
ORDER BY 3 DESC;

Configuration

Version and Privilege Changes

Version 1.5.0 separates queryid from nested_queryid: on PostgreSQL 16 and later the former comes from the activity query identifier, while the latter identifies the inner parsed statement; they match on older versions. The upgrade recreates the history view, so inspect dependent views and grants first.

Version 1.5.1 revokes PUBLIC execution of get_parsedinfo(int) because it can expose other backends’ query text and literals. Apply the SQL upgrade after installing the new files, and grant access only to roles that need it:

ALTER EXTENSION pgsentinel UPDATE TO '1.5.1';

History remains in finite shared-memory ring buffers; export it for long-term retention. Sampled query text can contain sensitive values, so review access to history views as well. Upstream CI covers PostgreSQL 10–19.