Historical Logging Mode for pg_activity (Configurable Interval & Retention) #448
Description
We currently use Grafana to monitor PostgreSQL query performance, but during periods of high CPU load the database server becomes unable to push metrics to our remote monitoring stack. While OS-level tools like atop continue writing historical data to local disk, we lose visibility into what is happening inside PostgreSQL at the exact time of the bottleneck.
It would be extremely valuable if pg_activity could optionally run in a historical logging mode, periodically collecting its usual runtime metrics (active queries, wait events, I/O stats, blocking chains, etc.) and writing them to a local file. Ideally, this feature would allow configuring retention duration (e.g., N days) and collection interval (e.g., every X seconds). This would make pg_activity similar to atop’s historical mode, enabling us to diagnose database-side contention even when external monitoring systems temporarily fail to ingest metrics. Such a feature would significantly improve post-incident analysis, especially for environments where the DB server cannot reliably export telemetry under heavy load.