Building a PostgreSQL Monitoring Stack

At Topocart, we managed multiple PostgreSQL databases for geoprocessing projects. The problem? We had zero visibility into performance until something broke.

The Goal

Create a monitoring stack that:

1. Tracks database health in real-time
2. Alerts us before incidents happen
3. Provides actionable insights through dashboards

The Stack

• Prometheus: Time-series database for metrics
• Grafana: Visualization and dashboarding
• PostgreSQL Exporter: Exposes database metrics
• Alertmanager: Routes alerts to Discord/Telegram

Architecture Overview

[PostgreSQL] → [PG Exporter] → [Prometheus] → [Grafana]
                                   ↓
                             [Alertmanager] → [Discord/Telegram]

Implementation Steps

1. Deploy PostgreSQL Exporter

Using Docker Compose:

services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://user:password@postgres:5432/dbname"
    ports:
      - "9187:9187"

2. Configure Prometheus

Add scrape config:

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['postgres-exporter:9187']

3. Build Grafana Dashboards

Key metrics to track:

• Connections: Active vs. max connections
• Query Performance: Slow queries, cache hit ratio
• Locks: Deadlocks and lock wait times
• Disk Usage: Table/index size growth

4. Set Up Alerts

Example alert for high connection usage:

- alert: HighPostgresConnections
  expr: pg_stat_database_numbackends > 80
  for: 5m
  annotations:
    summary: "PostgreSQL connection count is high"

Results

After deployment:

• Detected a connection leak before it caused downtime
• Identified slow queries dragging down performance
• Reduced incident response time by 60%

Lessons Learned

1. Start simple: Don't try to monitor everything at once
2. Alert on trends: Catch problems before they escalate
3. Document your queries: Future you will be grateful

---

Want the full setup? Check out my Monitoring Stack repo with Docker configs and dashboard JSON exports.