Database Automation & Observability: Prometheus, Grafana, pg_stat

In modern production environments, databases are the heart of your application. A single slow query or connection spike can cascade into a full outage. Yet many developers rely on ad-hoc queries to check database health. This tutorial introduces a robust observability stack: Prometheus for metric collection, Grafana for visualization, and PostgreSQL's pg_stat views as the data source. You'll learn to automate monitoring, detect anomalies, and debug production issues with confidence. We'll cover setting up postgres_exporter, building dashboards for key metrics like query performance, replication lag, and connection pools, and writing alerts that prevent incidents. By the end, you'll have a production-ready monitoring system that turns raw pg_stat data into actionable insights.

1. Understanding pg_stat Views

PostgreSQL provides a rich set of system views under the pg_stat schema that expose internal performance metrics. These views are the foundation for any monitoring solution. Key views include: - pg_stat_activity: Shows current server processes, including queries, state, and wait events. - pg_stat_database: Per-database statistics like transactions, tuples fetched, and cache hit ratio. - pg_stat_statements: Requires extension; tracks execution statistics of SQL statements (calls, total time, rows). - pg_stat_replication: Replication status for streaming replicas. - pg_stat_bgwriter: Background writer performance (checkpoints, buffers).

To enable pg_stat_statements, run: CREATE EXTENSION IF NOT EXISTS pg_stat_statements; and add 'pg_stat_statements' to shared_preload_libraries in postgresql.conf, then restart. These views are updated in real-time and provide granular data for observability.

-- Check top 5 queries by total execution time
SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

-- Current active queries
SELECT pid, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

2. Setting Up Prometheus for PostgreSQL

Prometheus is a time-series database that scrapes metrics from exporters. For PostgreSQL, we use the postgres_exporter (maintained by the Prometheus community). Installation steps: 1. Download the postgres_exporter binary from GitHub releases. 2. Create a monitoring user in PostgreSQL: CREATE USER pg_monitor WITH PASSWORD 'secret' INHERIT; GRANT pg_monitor TO pg_monitor; (or use a dedicated role with SELECT on pg_stat_* views). 3. Set environment variables: DATA_SOURCE_NAME="postgresql://pg_monitor:secret@localhost:5432/postgres?sslmode=disable" 4. Run the exporter: ./postgres_exporter --web.listen-address=:9187

The exporter exposes metrics at /metrics. Configure Prometheus to scrape this endpoint. Example prometheus.yml: scrape_configs: - job_name: 'postgresql' static_configs: - targets: ['localhost:9187']

Prometheus will collect metrics like pg_stat_database_tup_fetched, pg_stat_activity_count, and pg_stat_statements_total_time.

global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metrics_path: /metrics

3. Building Grafana Dashboards

Grafana connects to Prometheus as a data source and visualizes metrics. Install Grafana, add Prometheus data source (URL: http://localhost:9090). Then create dashboards. Essential panels: - Database Connections: Use query pg_stat_activity_count{datname="yourdb"} to show total connections over time. - Query Performance: pg_stat_statements_total_time{query!=""} top 5 queries. - Cache Hit Ratio: (pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read)) * 100. - Replication Lag: pg_stat_replication_lag.

You can import pre-built dashboards from Grafana.com (e.g., ID 9628 for PostgreSQL). Customize alerts: e.g., alert when connections > 80% of max_connections. Use Grafana's alerting rules or Prometheus Alertmanager.

-- Example PromQL queries for Grafana
-- Connections by state
sum by (state) (pg_stat_activity_count{datname="mydb"})

-- Top 5 queries by total time
topk(5, pg_stat_statements_total_time{datname="mydb"})

-- Cache hit ratio
(avg by (datname) (pg_stat_database_blks_hit{datname="mydb"}) / (avg by (datname) (pg_stat_database_blks_hit{datname="mydb"}) + avg by (datname) (pg_stat_database_blks_read{datname="mydb"}))) * 100

4. Automating Alerts with Prometheus Alertmanager

Alertmanager handles alerts from Prometheus. Define alert rules in Prometheus configuration. Example rule for high connections:

groups: - name: postgresql_alerts rules: - alert: HighConnections expr: pg_stat_activity_count{datname="mydb"} > 80 for: 5m labels: severity: warning annotations: summary: "High database connections on {{ $labels.instance }}" description: "Connections are at {{ $value }}"

Configure Alertmanager to send notifications via email, Slack, PagerDuty, etc.

groups:
  - name: postgresql
    rules:
      - alert: HighConnections
        expr: pg_stat_activity_count{datname="mydb"} > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High connections on {{ $labels.instance }}"
      - alert: ReplicationLag
        expr: pg_stat_replication_lag > 10
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Replication lag on {{ $labels.instance }}"

5. Correlating Metrics with Application Performance

Observability is not just about database metrics; it's about connecting them to application behavior. Use Grafana's annotations to mark deployments or incidents. For example, when application response time spikes, check if database connections or slow queries also spiked. You can also instrument your application to emit custom metrics (e.g., request duration) and correlate with pg_stat_activity.

Example: If your app shows increased latency, query pg_stat_activity for queries running longer than 1 second. Use Prometheus to track query duration percentiles via pg_stat_statements. Create a dashboard that overlays application latency with database query time.

-- Find queries running longer than 1 second
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '1 second'
ORDER BY duration DESC;

6. Advanced: Automating Remediation with pg_stat

Beyond monitoring, you can automate responses to common issues. For example, a cron job can kill idle transactions that block other queries. Use pg_stat_activity to detect and terminate them. Example script:

#!/bin/bash psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND state_change < now() - interval '30 minutes';"

Similarly, you can auto-scale connection pools or restart replicas if lag exceeds threshold. Integrate with orchestration tools like Kubernetes or Ansible. However, be cautious: automated actions can cause unintended side effects. Always log actions and have a manual override.

#!/bin/bash
# Kill idle transactions older than 30 minutes
psql -U pg_monitor -d postgres -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '30 minutes';
"
echo "Killed idle transactions at $(date)" >> /var/log/db_auto_remediation.log