Database monitoring (Postgres)

Sentrinel watches Postgres the way pganalyze and Datadog do: a small collector runs next to the database, polls the pg_stat_* views, and pushes what it finds to the ingest API.

Sentrinel never connects to your database. The collector holds the credentials and lives on your side of the network, so a Sentrinel compromise is not a database compromise, and no connection string is ever stored here.

Setup

1. A read-only role

CREATE ROLE sentrinel_monitor LOGIN PASSWORD 'a-strong-password';
GRANT pg_monitor TO sentrinel_monitor;

pg_monitor covers every statement the collector runs. It exists on RDS, Aurora, Cloud SQL and Azure, where superuser is not on offer.

Two optional grants, each unlocking one feature. Neither is required, and the collector says which one is missing rather than showing an empty page:

-- Sequence exhaustion checks. pg_monitor cannot read sequence values, so
-- without this they are reported as "could not be read", never as healthy.
-- SELECT on a sequence permits reading its value and nothing else — nextval
-- needs USAGE or UPDATE, so this cannot advance anything.
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO sentrinel_monitor;

-- Error collection from the server log. Think before granting these: they let
-- the role read files as the Postgres OS user.
GRANT pg_read_server_files TO sentrinel_monitor;
GRANT EXECUTE ON FUNCTION pg_read_file(text, bigint, bigint) TO sentrinel_monitor;
GRANT EXECUTE ON FUNCTION pg_stat_file(text) TO sentrinel_monitor;
GRANT EXECUTE ON FUNCTION pg_current_logfile(text) TO sentrinel_monitor;

Log collection also needs Postgres writing a parseable log:

logging_collector = on
log_destination = 'csvlog'    # or 'jsonlog' on PG15+

Managed Postgres does not offer pg_read_file at all. Everything else works there; only the Errors tab is unavailable.

2. pg_stat_statements — optional, but it is the good part

# postgresql.conf — requires a restart
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

Without it you still get activity sampling, wait events, blocking chains, connection and cache metrics, and table statistics. You lose per-query aggregates. The collector says so at startup rather than failing — needing a restart scheduled is not a reason to collect nothing in the meantime.

3. Install the collector

curl -fsSL https://sentrinel.dev/install-collector.sh | sudo bash

That installs the sentrinel-collector command, a systemd service running as its own unprivileged user, and starts it on boot. Bun is installed too if the machine does not already have it.

To configure as you install — the service starts immediately:

Issue a Database collector key for this (API Keys → Generate → Database collector). It lives on the database host and can send Postgres statistics and nothing else — a leaked one cannot post application telemetry or read your data.

curl -fsSL https://sentrinel.dev/install-collector.sh | sudo -E env \
  DATABASE_URL=postgres://sentrinel_monitor:pw@localhost/orders \
  SENTRINEL_URL=https://api.sentrinel.dev \
  SENTRINEL_KEY=snt_db_... \
  SENTRINEL_INSTANCE=orders-primary \
  bash

Or install first and configure after:

sentrinel-collector config set \
  DATABASE_URL=postgres://sentrinel_monitor:pw@localhost/orders \
  SENTRINEL_KEY=snt_db_... \
  SENTRINEL_INSTANCE=orders-primary

sentrinel-collector check     # does the database let us in?
sentrinel-collector start

The database registers itself on first contact and appears under Databases within seconds. SENTRINEL_KEY is an ordinary ingest key — it establishes which organization owns the data, nothing more.

Re-running the installer upgrades in place and leaves your configuration alone.

TLS

The collector connects the way psql does — it tries TLS and falls back to plaintext, so a server that requires encryption and one that does not both work without you configuring anything.

Set sslmode yourself when you want something stricter than the default, and it is honoured as given:

sentrinel-collector config set \
  DATABASE_URL='postgres://sentrinel_monitor:[email protected]/orders?sslmode=verify-full'

verify-full checks the server's certificate against a CA and is worth setting for a database the collector reaches over a network you do not control. The default encrypts but does not verify, which is the same guarantee psql gives.

The command

sentrinel-collector status              is it running, and what is it watching
sentrinel-collector start|stop|restart
sentrinel-collector logs -f             follow the service output
sentrinel-collector check               connect and report what it can see
sentrinel-collector config              show settings, secrets redacted
sentrinel-collector config set K=V      change a setting and restart
sentrinel-collector run                 run in the foreground, for debugging
sentrinel-collector ls                  every instance on this machine
sentrinel-collector update              fetch the latest collector, keep settings
sentrinel-collector uninstall           remove this instance, keep the config

  -i, --instance NAME                   which collector to act on

More than one Postgres on a machine

A server running a 17 on :5432 and an 18 on :5433 has two clusters, and each needs its own collector. That is not packaging pedantry: pg_stat_statements counters are per-cluster, and the collector ships deltas between snapshots — mix two clusters into one process and every number is nonsense.

The service is a systemd template, so instances are independent:

sentrinel-collector -i pg17 config set \
  DATABASE_URL=postgres://sentrinel_monitor:pw@localhost:5432/app \
  SENTRINEL_KEY=snt_db_... \
  SENTRINEL_INSTANCE=orders-pg17
sentrinel-collector -i pg17 start

sentrinel-collector -i pg18 config set \
  DATABASE_URL=postgres://sentrinel_monitor:pw@localhost:5433/app \
  SENTRINEL_KEY=snt_db_... \
  SENTRINEL_INSTANCE=orders-pg18
sentrinel-collector -i pg18 start

sentrinel-collector ls

  pg17             running   postgres://mon:****@localhost:5432/app
  pg18             running   postgres://mon:****@localhost:5433/app

config set creates the instance if it does not exist yet, so there is no separate "add" step. Each instance keeps its own /etc/sentrinel/collector-<name>.env and its own unit, sentrinel-collector@<name>.

Without -i every command acts on the instance named default, which is what a single-database machine gets and never has to think about. Upgrading from an older install migrates collector.env to collector-default.env and retires the old unit.

uninstall removes one instance. The binary, the unit template and the CLI stay until the last instance is gone — removing them earlier would silently stop the collectors you kept.

Replicas count as separate instances too. The collector reports role: replica, and replication lag only means something measured from the primary.

One cluster, many databases

Within a single cluster the picture is different, because most of the pg_stat_* views are cluster-wide. One collector covers every database on that server for queries, wait events, blocking, connections, cache hit ratio and replication lag — the rows carry datname, so you can see which database a query came from.

The exception is table statistics. pg_stat_user_tables only ever shows the database you are connected to, so the Tables tab and its bloat / missing-index advisories cover the database named in DATABASE_URL and no other. To watch tables in a second database on the same server, give it its own instance.

check is the one to reach for when a chart is empty. Without pg_monitor most of pg_stat_activity still returns rows — just with the query text and wait event blanked out for every backend but your own, which looks like an idle database rather than a permissions problem:

  Postgres      16.14  (primary)
  Role          holds pg_monitor
  Activity      14 backend(s) with visible query text
  Statements    pg_stat_statements installed

Configuration lives in /etc/sentrinel/collector.env, owned 0640 root:sentrinel — it holds a database password and an ingest key, and nothing else on the box needs to read it.

uninstall removes the service and the CLI but keeps that file: deleting someone's credentials is not an uninstaller's decision.

What is collected, and how often

Activity is sampled every second on purpose. The lock you care about is held for 200ms; at a ten-second cadence it is invisible.

pg_stat_statements counters are cumulative since the last reset, so the collector ships the delta between consecutive snapshots. The first snapshot only primes the baseline — otherwise months of traffic would land as one spike. A negative delta means a reset happened, and that row is dropped rather than clamped.

Query text: full by default, maskable

By default you see the statement Postgres actually ran, values included:

SELECT * FROM users WHERE email = '[email protected]'

That is the point. A query you can paste into psql gives you the same plan; a masked one does not, and "which value was slow" is often the whole answer.

To strip literals before anything leaves the database host:

sentrinel-collector config set SENTRINEL_MASK_QUERIES=true

SELECT * FROM users WHERE email = ?

String literals, numbers, dollar-quoted bodies and IN-list contents are all replaced. The same applies to error text, where the DETAIL line of a constraint violation quotes the offending value verbatim — Key (email)=(?) already exists., keeping the column name and dropping the value.

Masking has to happen in the collector, not the dashboard. A display-time toggle would mean the raw values had already been sent and stored, which is the one thing masking exists to prevent. So it is a setting on the machine next to your database, and the dashboard shows which mode each database is in — without that label a ? is ambiguous between a masked literal and a bind parameter.

Turning it on affects new data only. Text already collected stays as it was stored; the reverse is also true, so switching it off does not un-mask history.

Grouping does not change

Query identity is always computed from the literal-free form, in both modes. Fingerprinting raw text would make WHERE id = 1 and WHERE id = 2 two different queries, so a statement called a million times would scatter into a million rows of one call each — and the Queries page, which exists to rank by share of database time, would rank nothing.

Errors group the same way: a thousand duplicate-key failures across a thousand different emails are one problem, and they stay one row whether or not the emails are shown.

When to turn it on

Consider it where query text carries data you are not permitted to store outside its original system — regulated personal data, payment details, health records — or where the people who can read the dashboard are not the people allowed to read the database. pg_stat_activity is the sharpest case: it carries the fully substituted text of whatever is running right now, so a client that interpolates rather than binds puts real values in front of anyone with dashboard access.

Reading the pages

Queries ranks by share of total database time, not by mean duration. A 4ms query called a million times outranks a 900ms query called twice, and only the "% of total" column makes that visible. The collector's own polling queries appear here too — that is deliberate; you should be able to see what the observer costs.

Activity shows wait events over time, the longest-running backends, and blocking chains. A row with Lock / transactionid and a blocking PID is one transaction waiting on another.

Metrics covers connections against max_connections, cache hit ratio, rollback rate and deadlocks.

Tables lists sizes and bloat, and derives advisories from them:

Small tables are excluded from missing_index — a sequential scan of 200 rows is the right plan.

Clicking a table

Indexes is the reason the drill-down exists. Postgres will tell you an index's size, and it will tell you its scan count, but never side by side — and the pair is the whole judgement. Size is a permanent tax on every insert, update and delete on the table; idx_scan is what it earns back.

Each index shows its definition, access method, whether it is unique, primary or invalid, its columns in order, its share of the table's index space, and:

The index advisors:

Primary keys and unique indexes are never reported as unused: they enforce a constraint, which is worth their size whether or not anything reads them. And redundant_index stays quiet when the wider index is itself unused — "drop this one, that one covers it" is not advice when nobody uses either.

Columns lists types (with lengths — character varying(255), not varchar), nullability and defaults, and marks which indexes lead on each column versus merely include it. Only a leading column can serve a lookup on that column alone.

Activity shows the read mix (index versus sequential scans, and rows read per sequential scan), the write mix, HOT update share, and size over the last seven days.

Health — the four ways a Postgres database stops

Every one of these is knowable days or weeks ahead, and none of them shows up on a dashboard of averages. That is the entire reason the Health tab exists.

Transaction ID wraparound. Transaction IDs are 32-bit and wrap. Autovacuum freezes old rows to prevent it; when something stops it doing that — a long transaction, an idle-in-transaction client, an abandoned replication slot, a prepared transaction nobody committed — the age climbs, and at 2 billion Postgres refuses all writes to protect the data. Recovery is a single-user VACUUM measured in hours. Warned at half the limit and again at 90%. MultiXact age is tracked separately, because it wraps on its own counter and is driven by row-share locking and foreign keys — it can be critical while transaction age looks fine.

Sequence exhaustion. An integer primary key backed by a serial stops at 2,147,483,647, and every insert fails at once. Widening the column to bigint rewrites the table under an exclusive lock, so it is a scheduled migration if you see it coming and an outage if you do not — which is why the warning starts at 50% rather than 90%.

Abandoned replication slots. A slot with no consumer retains every WAL segment it has not confirmed, without limit, until the volume is full. The same slot pins the transaction horizon, so it drives wraparound at the same time. One forgotten slot, two outages.

Checkpoint pressure. A requested checkpoint means WAL hit max_wal_size before the scheduled one came round, so checkpoints fire on write volume rather than the clock. Users see periodic write stalls that no individual query explains.

The tab also shows vacuums in flight with their phase and progress — without which a long vacuum and a stuck one look identical — and the tables holding the cluster's freeze horizon back, which is where to look once the wraparound alarm rings.

Errors

pg_stat_database counts deadlocks; it does not say which two transactions, on which table, running what. That only exists in the server log, so the collector reads it where permitted and groups what it finds by SQLSTATE and message.

Errors are shown as Postgres wrote them, including the value a constraint violation quotes back at you — which is usually the fastest route to the bug.

Key (email)=([email protected]) already exists.

With SENTRINEL_MASK_QUERIES=true the value is removed on your database host before anything is sent:

Key (email)=(?) already exists.

Column names survive either way, because they are schema and they are the useful part. The same handling applies to Failing row contains (…) and to any value quoted after a colon. A relation or constraint name in double quotes is always kept — relation "users" does not exist is useless without it.

Errors are capped at both ends: bounded bytes per read, bounded rows per send. A database in a crash loop writes megabytes a second, and an agent that forwards all of it faithfully turns one incident into two. What gets dropped is counted and reported rather than silently discarded.

Config & Security

A configuration and security audit, refreshed every fifteen minutes. Findings are computed on the server, not in the collector, so a rule can be corrected with a deploy rather than a fleet upgrade of agents on customer database hosts.

Three rules govern what appears there:

Nothing is claimed clean that was not checked. Reading whether a role has a password needs pg_authid, which is superuser-only — the correct result for a properly scoped monitoring role. The page says "could not be checked" rather than "no passwordless roles", because the second would be fiction.

Every finding names a consequence and a fix. "ssl is off" is an observation. "Every query and every password crosses the network in plaintext — set ssl = on and require it with hostssl" is something to act on.

Enabled is not the same as used. ssl = on means the server offers TLS. Whether any client takes it is a separate question, and a server that looks compliant on a settings page while every connection is plaintext is the case worth catching.

Nothing about credentials is ever collected: no password hashes, no pg_hba lines, no connection strings. The role check reads whether a password exists, never what it is.

Disk space and backups

Two more ways a database stops, both of which it cannot tell you about itself.

Free space

Postgres tracks its own size, the WAL a replication slot is holding, and how fast each table is growing — every input to running out of disk — while having no idea how much room is left. "This slot is retaining 40 GB" means something very different on a 2 TB volume than on a 50 GB one.

So it is measured from the filesystem, using the data_directory the server reports. That works because the collector runs on the database host, which is what the installer sets up. Where it does not — a collector pointed at a remote database — free space is reported as unavailable with the reason, rather than measuring the collector's own volume and calling it the database's. A number from the wrong filesystem reads as perfectly fine while the real disk fills.

The projection is the useful half:

Disk free    3%          28 GB of 926 GB
Growth       1.2 GB/day  23 days of room

"82% full" is a number people learn to ignore. "82% full, and at this rate that is nine days" is a date. Growth is measured from the observed change in database size over the last week, and the projection is omitted entirely when the database is not measurably growing — extrapolating from noise would produce a confident number built on nothing. A short runway is its own finding, so a disk at 40% free and filling fast is caught before a free-space threshold would notice.

WAL archiving

archive_mode = on appears in the config audit and says only that archiving is configured. Whether it works is a different question, and the one that matters: a failing archive_command leaves the database running normally, serving traffic, retrying the same segment forever, while the backup chain has been broken since Tuesday. Nobody finds out until they need to restore.

It is also a disk problem. WAL that has not been archived cannot be recycled, so a broken archive fills the volume on its own schedule.

The check is "did the most recent attempt fail", not "has anything ever failed". The lifetime failure counter never decreases, so a rule built on it could never clear — and an alert that cannot clear is one people mute.

Alerts

Detecting a wraparound outage two weeks ahead is only half a feature if finding out requires someone to have the Health tab open. Every database gets four rules the moment it is created — from the dashboard or by a collector registering itself — with no configuration:

Defaults exist because a rule you have to know to configure prevents very few outages. They arrive with no notification channel, so they show on the Health tab immediately but only reach a person once you attach one — notifications should start because someone chose that, not because a database was added.

Deleting a default is permanent. The rules are created once, by existence rather than by name, so a rule you removed does not come back on the collector's next restart.

Every metric you can alert on

wraparound_percent, mxid_wraparound_percent, sequence_percent, inactive_slots, slot_retained_bytes, connection_saturation, replication_lag_bytes, cache_hit_ratio, deadlocks, checkpoints_requested, db_size_bytes, bloat_percent, error_count, disk_free_percent, archive_failing.

Each carries a one-line statement of what breaching it costs, shown in the rule editor — a threshold without a consequence is a number nobody can calibrate.

A silent collector is not a healthy database

This is the trap worth naming. A database whose collector has stopped reports zero for every metric, and zero on a > rule reads as perfectly healthy. So when a collector has not reported for two minutes the rule is not evaluated at all: it goes to not reporting rather than resolving to ok.

Silence is not evidence of health, and an alert that turns green because its input disappeared is worse than one that never existed.

Live

The Activity tab streams pg_stat_activity at the collector's own one-second cadence over Server-Sent Events. It is pushed, not polled — the collector already performs that read to store it, and the same sample is fanned out to whatever dashboards are open, so ten people watching costs the database nothing extra.

One second is the point. The lock you care about is held for 200ms and is invisible at any slower refresh.

Retention

Activity is the highest-volume table by far — one row per active backend per second — which is why it expires first.