PostgreSQL
See what is running, waiting and lagging inside PostgreSQL, with a read-only monitoring login.
What it answers
Questions an on-call engineer asks, which an AI client can answer with this plugin.
- What is running on the database right now, and what is it waiting on?
- Which session is blocking the others, and how long has it held its locks?
- How far behind is the replica, and is a replication slot filling the disk?
- Which transactions have been open for hours and are holding back vacuum?
- Which tables need vacuuming, and is autovacuum switched off anywhere?
- How close is each database to transaction ID wraparound?
- Which database grew, and which settings were changed from the defaults?
- Which statements cost the server the most time?
What it returns
Each of the plugin's read-only tools, described in words.
- Sessions on the PostgreSQL server that are doing something, with their state, wait event, ages and normalised statement
- PostgreSQL sessions waiting on a lock, each with the session blocking it and how long it has waited
- Locks held and awaited on the PostgreSQL server, grouped by lock type, mode, relation and whether they are granted
- PostgreSQL replication from both ends - standbys connected to this primary with their lag, inactive slots, and this standby's receiver
- PostgreSQL transactions open longer than a threshold, oldest first, with how far back their snapshot holds vacuum
- Tables in the connected PostgreSQL database with the most dead rows, and when vacuum and analyze last ran on them
- Transaction ID and multixact age of every PostgreSQL database, and how close each is to the wraparound limit
- Size of every PostgreSQL database on disk, with its sessions, commits, rollbacks, cache hit ratio, temp files and deadlocks
- PostgreSQL settings that differ from the built-in default, where each was set, and which wait for a restart
- The statements that cost PostgreSQL the most time, from the pg_stat_statements extension, with calls, timings and rows
At a glance
- Plugin
- postgres 1.0.0
- Publisher
- levantar
- Platforms
- Linux, Windows, macOS
Tested on
- PostgreSQL 13.22, 15.14, 16.10 and 17.6 on Linux (Ubuntu, Docker), CI runner
- PostgreSQL 17 on macOS (Homebrew), CI runner
- PostgreSQL 17 on Windows Server, CI runner
How it logs in
Without an explicit choice, install tries the methods that need no secret first, in this order.
- The agent's OS account on the same host, with no secret
- A user name and password
- A client certificate
The least-privilege login
The grant script in the pack. secronyx plugin setup postgres --apply runs it with a random password for the new login, or you can run it by hand.
See it run
Recording coming. Install, setup and status runs for this plugin are recorded on the shared test lab, and appear here once they have run.
Read-only, in layers
Every query is fixed text inside the signed pack: one SELECT or WITH statement whose functions are all on the agent's allowlist for that database. It runs in a read-only transaction with server-side statement and lock timeouts, and is always rolled back. Callers can only fill in typed, bounded arguments, which are bound as parameters.
These controls narrow what the plugin can do; none of them is a guarantee on its own. An administrator can still accept a more powerful login on purpose (audited and shown in status), root or Administrator on the host can replace the agent, and the plugin can read whatever its login can see.