PostgreSQL Plugin

The PostgreSQL plugin registers PostgreSQL servers as Konduo resources and connects connection, replication, vacuum, lock, transaction, and query activity signals to the operator UI.

Highlights

• Collects server state and operational snapshots with read-only SQL queries.
• Supports both Prometheus mapping packs and Konduo-managed PostgreSQL metric collection.
• Focuses dashboards on availability evidence, connection pressure, transaction age, lock activity, replication lag, and cache/write behavior.
• Separates diagnostics for connectivity, permissions, metric coverage, replication, and bloat/vacuum risk.

Before Registration

• Prepare PostgreSQL host, port, database, username, password, and TLS settings.
• Use a least-privilege account that can run the required read-only diagnostic queries.
• Tune timeouts for managed collection so diagnostic queries do not add unexpected load.
• When Prometheus is the managed collection write target, run Prometheus with --web.enable-remote-write-receiver.

Operations Tips

• Review connection pressure together with max connection settings.
• Interpret replication lag with primary/replica role, WAL receiver state, and apply delay.
• For long transactions, lock waits, and delayed vacuum, read diagnostic snapshots together with metric trends.
• Treat an idle-in-transaction alert as a pg_stat_activity backend session problem. Managed collection uses PID and transaction start time to verify continuity, so a replacement transaction starts a new alert hold period. Inspect PID, user, database, client, last query, and transaction age together.

Operator Views

• The activity view shows session state, long-running queries, transaction age, and wait events in units an operator can act on.
• The idle transaction sessions view lists only idle in transaction sessions with PID, user, database, application, client, transaction start, state-change time, and last query.
• The locks view exposes blocking and blocked relationships plus lock mode to support cancel or terminate decisions.
• The replication view combines primary/standby role, replay/apply lag, receiver state, and archiver evidence.
• The storage/vacuum view connects database size, dead tuples, autovacuum, and vacuum/analyze needs to diagnostic evidence.
• Failover-planning evidence helps review current role and replication state; automatic failover orchestration is outside CE scope.

Idle In Transaction Response

• PostgreSQL idle in transaction means a backend session is idle while a transaction remains open. Even without an active query, it can hold locks, delay vacuum visibility, and consume connection pool capacity.
• Konduo-owned PostgreSQL connections set idle_in_transaction_session_timeout by default so a Konduo-side bug is less likely to leave an indefinite idle transaction in Konduo's own database.
• In managed collection mode, the Long Transaction alert tracks client backends in the current database by PID and transaction start time. PostgreSQL background work and idle in transaction sessions are excluded, and a replacement transaction cannot inherit the previous transaction's hold period.
• When a target PostgreSQL resource raises the alert, open the idle transaction sessions view first and identify the PID and ownership context.
• If a query is active, consider query cancellation first. For a pure idle in transaction session, there is no running statement to cancel, so application-owner coordination or backend termination after impact review is usually the practical response.
• Before termination, preserve the user, database, application name, client address, last query, and transaction age as evidence, and keep audit and confirmation boundaries intact.

Diagnostics And Alerts

• Diagnostics separate connectivity and permission checks, connection pressure, lock contention, long transactions, vacuum/bloat, replication, and WAL/checkpoint risk.
• Alert rules cover scrape health, connection utilization, long transactions, lock contention, cache/temp pressure, checkpoint/WAL pressure, replication lag, and archiver health.
• Managed collection converts read-only SQL snapshots into postgres_exporter-style samples and isolates them with the resource identity label.
• When historical evidence is missing, inspect the metric source or mapping pack instead of assuming PostgreSQL is healthy.

Management Boundary

• Arbitrary SQL console access is intentionally not provided.
• Query cancel, backend terminate, analyze, and vacuum analyze are management actions that require confirmation, reason, and safe-mode override when applicable.
• Terminating an idle-in-transaction session can abort user work, so treat it as an impact-reviewed operation rather than a simple alert cleanup.
• Schema/table changes, user and role management, backup/restore, and replication topology changes are outside the CE operating boundary.
• Even read-only accounts can lack catalog-view permissions, so distinguish partial diagnostic evidence from actual PostgreSQL health.

Example Workflows

• For connection saturation, inspect active/idle sessions, max connections, pooler settings, and long transactions together.
• For Idle In Transaction Sessions alerts, open the dedicated view to identify PID and last query, then review pooler or application transaction boundaries.
• For lock waits, review the blocking session, query age, wait event, and workload impact before choosing cancel or terminate.
• For delayed vacuum, compare dead tuples, autovacuum activity, transaction age, and table-size growth.
• For replica lag, combine WAL generation, receiver state, replay lag, and network or storage latency evidence.

---

PostgreSQL Enterprise Overlay

The PostgreSQL Enterprise overlay adds MCP descriptors, advanced diagnostics, log evidence mapping, metric mapping, and anomaly rules to PostgreSQL resources. It summarizes session, lock, connection, replication, storage, and vacuum state as operator evidence.

Highlights

• Provides database info, monitoring overview, connectivity checks, diagnostics, topology, and metric mapping through MCP.
• Includes session, lock, connection, configuration, replication, and storage/vacuum evidence in diagnostics.
• Connects PostgreSQL diagnostics to an Enterprise log source through log evidence mapping.
• Evaluates PostgreSQL logical metrics through declarative metric anomaly rules.
• Connects the Community idle-in-transaction session view and PID-based action path with Enterprise log evidence, MCP lookup, and anomaly analysis.

Before Registration

• Community PostgreSQL connectivity and read permissions must be healthy.
• Prepare Prometheus postgres exporter mapping and an OpenSearch log source when log evidence is needed.
• Confirm topology read permissions for replication and failover planning context.

Operations Tips

• Interpret lock waits, connection saturation, and replication lag together with application deployments and vacuum state.
• Treat Idle In Transaction Sessions alerts as pg_stat_activity backend session evidence, not as a separate transaction inventory. Managed collection uses PID and transaction start time to verify continuity, so a replacement transaction starts a new alert hold period.
• In managed collection mode, the Long Transaction alert tracks client backends in the current database by PID and transaction start time. PostgreSQL background work and idle in transaction sessions are excluded, and a replacement transaction cannot inherit the previous transaction's hold period.
• A pure idle in transaction session may not have an active query to cancel; review PID, user, database, client, last query, transaction age, and application logs before deciding whether termination is appropriate.
• Failover descriptors provide planning and inventory context; actual state changes need separate authorization and audit boundaries.

Operational Scenarios

• For API latency or transaction backlog, inspect sessions, locks, connection pools, vacuum state, and replication lag in one flow.
• When idle-in-transaction alerts fire, open the dedicated session list to identify PID and last query, then use same-window OpenSearch log evidence to trace application transaction boundaries.
• During incident analysis, connect PostgreSQL diagnostics to OpenSearch log evidence and compare application and database errors from the same time window.
• Use failover planning context for topology and inventory review; perform actual role changes through approved operational procedures.

Boundaries

• The EE overlay provides advanced diagnostics, log evidence mapping, and anomaly metadata. It does not automatically run destructive maintenance.
• Without PostgreSQL metric mapping, storage, vacuum, and replication anomalies should remain unavailable or partial evidence.
• Terminating idle-in-transaction sessions can abort user work, so Enterprise correlation analysis must not execute it automatically.