The hardest part of incident analysis is often reconstructing what the database and host looked like at the same moment. pg-diag collects version-aware PostgreSQL 10-18 statistics and optional Linux evidence with read-only SQL, then writes portable JSON and self-contained HTML.

The payoff

Instead of pasting screenshots and unrelated command output into a ticket, you get one timestamped artifact with collection status, source provenance, charts, and machine-readable data that can be validated or compared later.

Real tasks it helps with

Production health audit

Review settings, locks, activity, replication, extensions, object ownership, and host resources without applying SQL changes.

Incident capture

Collect repeated database and OS samples during a bounded window to preserve rates, deltas, tables, and charts.

Before-and-after evidence

Capture a baseline before an upgrade, configuration rollout, or migration and retain the JSON for deterministic comparison.

Configuration facts

Extract a bounded hardware and effective-settings artifact for an existing-server pg-play configuration review.

Choose the access path

Three collection topologies
remote-db-only                 local
collector --> PostgreSQL      pg-diag + PostgreSQL + Linux host
DB evidence only              DB + local host evidence

remote
collector -- verified SSH --> Linux target
    |             |
    +-- DB tunnel +---------> PostgreSQL endpoint

one-shot  = point-in-time facts
snapshots = repeated samples + calculated rates and charts

Collection modes

Mode Database access Host evidence
remote-db-only Direct PostgreSQL connection None; host-dependent items are omitted
local Local socket or TCP from the collector The machine running pg-diag
remote PostgreSQL protocol through an SSH local forward The verified SSH target

Report commands

Command Best for Result
one-shot Audits, inventory, configuration review Point-in-time facts; no interval metrics
snapshots Performance and incident windows Samples, rates, deltas, tables, and charts
render Rebuild HTML from a saved JSON artifact Self-contained offline report
validate-artifact/summarize Automation and evidence handoff Validation result or compact deterministic summary

Collect the first report

python -m pip install pg-diag

pg-diag one-shot \
  --dsn "postgresql://diag@127.0.0.1:5432/appdb" \
  --collection-mode remote-db-only \
  --out reports/appdb-audit

For least-privilege collection, use a dedicated login role with pg_monitor and a protected libpq passfile. The output directory contains report.json, report.html, and report.log.

Know the boundary

Read-only SQL does not make diagnostic data non-sensitive. Reports may contain query text, object names, settings, host inventory, and operational metadata. Review them before sharing. Missing permissions or optional host tools are recorded as unavailable or partial evidence; they should not be mistaken for a clean finding.

GitHub repository ↗ PyPI package ↗