pgsentinel

Overview

Package/source release and PIGSTY RPM are 1.4.2; shipped pgsentinel.control and PGDG DEB remain 1.4.1.

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 and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add 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.4.2 README
• pgsentinel 1.4.2 release
• pgsentinel 1.4.1 to 1.4.2 changes
• 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 Operational Notes

• Release 1.4.2 fixes query-statistics history on PostgreSQL 17, whose pg_stat_statements view renamed block I/O timing columns to shared_blk_read_time and shared_blk_write_time.
• The same release adds PostgreSQL 19 build compatibility without changing the SQL views or GUC surface.
• Ring-buffer history is memory-resident and finite; increase the entry limits only after accounting for shared-memory use, and export samples externally when longer retention is required.
• Query text can contain literal parameter values. Restrict access to the history views when statements may include sensitive data.