Skip to content

Database Observability and Monitoring

Comprehensive monitoring setup for Blacklake's database infrastructure with Prometheus metrics, Grafana dashboards, and alerting.

Architecture

graph TB
    subgraph "Database Layer"
        PG0[Patroni PG0]
        PG1[Patroni PG1]
        PGB[PgBouncer]
    end

    subgraph "Metrics Collection"
        PGE[PostgreSQL Exporter]
        PBME[PgBouncer Exporter]
        HAE[HAProxy Exporter]
    end

    subgraph "Monitoring Stack"
        PROM[Prometheus]
        GRAF[Grafana]
        ALERT[AlertManager]
    end

    subgraph "Storage"
        TS[Time Series DB]
        LOGS[Log Storage]
    end

    PG0 --> PGE
    PG1 --> PGE
    PGB --> PBME
    PGE --> PROM
    PBME --> PROM
    HAE --> PROM
    PROM --> TS
    PROM --> GRAF
    PROM --> ALERT

Metrics Collection

PostgreSQL Metrics

  • Connection Metrics: Active connections, connection limits
  • Query Performance: Query duration, slow queries, locks
  • Replication: Lag, replication slots, WAL generation
  • Database Size: Database size, table sizes, index usage
  • Cache Performance: Buffer hit ratio, cache efficiency

PgBouncer Metrics

  • Pool Metrics: Active connections, pool utilization
  • Query Metrics: Queries per second, average query time
  • Client Metrics: Client connections, wait time
  • Server Metrics: Server connections, server utilization

HAProxy Metrics

  • Backend Health: Server status, health checks
  • Connection Metrics: Active connections, connection rate
  • Response Time: Average response time, error rate
  • Load Balancing: Distribution across backends

Prometheus Configuration

postgres-exporter

# PostgreSQL exporter configuration
DATA_SOURCE_NAME: "postgresql://exporter:exporter123@haproxy:5432/blacklake?sslmode=require"

Key Metrics: - pg_stat_database_* - Database statistics - pg_stat_user_tables_* - Table statistics - pg_stat_user_indexes_* - Index statistics - pg_replication_* - Replication metrics - pg_stat_activity_* - Active connections

pgbouncer-exporter

# PgBouncer exporter configuration
PGBOUNCER_EXPORTER_PGBOUNCER_CONNECTION_STRING: "postgres://pgbouncer_admin:pgbouncer123@pgbouncer:6432/pgbouncer"

Key Metrics: - pgbouncer_pools_* - Pool statistics - pgbouncer_clients_* - Client connections - pgbouncer_servers_* - Server connections - pgbouncer_stats_* - General statistics

Grafana Dashboards

PostgreSQL Overview Dashboard

{
  "dashboard": {
    "title": "PostgreSQL Overview",
    "panels": [
      {
        "title": "Database Connections",
        "type": "stat",
        "targets": [
          {
            "expr": "pg_stat_database_numbackends",
            "legendFormat": "Active Connections"
          }
        ]
      },
      {
        "title": "Query Performance",
        "type": "graph",
        "targets": [
          {
            "expr": "rate(pg_stat_database_tup_returned[5m])",
            "legendFormat": "Tuples Returned/sec"
          }
        ]
      }
    ]
  }
}

PgBouncer Dashboard

{
  "dashboard": {
    "title": "PgBouncer Pooling",
    "panels": [
      {
        "title": "Pool Utilization",
        "type": "gauge",
        "targets": [
          {
            "expr": "pgbouncer_pools_cl_active / pgbouncer_pools_maxwait",
            "legendFormat": "Pool Utilization %"
          }
        ]
      }
    ]
  }
}

Alerting Rules

Critical Alerts

groups:
  - name: database-critical
    rules:
      - alert: DatabaseDown
        expr: up{job="postgres-exporter"} == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL database is down"

      - alert: HighConnectionCount
        expr: pg_stat_database_numbackends / pg_settings_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High connection count: {{ $value }}%"

      - alert: ReplicationLag
        expr: pg_replication_lag > 60
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "Replication lag is {{ $value }} seconds"

Performance Alerts

  - name: database-performance
    rules:
      - alert: SlowQueries
        expr: rate(pg_stat_database_tup_returned[5m]) < 100
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Slow query performance detected"

      - alert: HighCacheMissRatio
        expr: (1 - (pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read))) > 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High cache miss ratio: {{ $value }}%"

Log Management

PostgreSQL Logs

-- Enable comprehensive logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = 1000;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_checkpoints = on;

PgBouncer Logs

# pgbouncer.ini logging configuration
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
log_stats = 1

Log Rotation

# /etc/logrotate.d/postgresql
/var/log/postgresql/*.log {
    daily
    missingok
    rotate 52
    compress
    delaycompress
    notifempty
    create 644 postgres postgres
    postrotate
        /bin/kill -HUP `cat /var/run/postgresql/postgresql.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

Health Checks

Application Health Endpoints

# Database health check
@app.get("/health/db")
async def health_database():
    try:
        with engine.connect() as conn:
            result = conn.execute(text("SELECT 1"))
            return {"status": "healthy", "database": "connected"}
    except Exception as e:
        return {"status": "unhealthy", "database": "error", "error": str(e)}

# HA cluster health check
@app.get("/health/ha")
async def health_ha():
    try:
        # Check Patroni cluster health
        response = requests.get("http://patroni-pg0:8008/health")
        return {"status": "healthy", "ha": "cluster_healthy"}
    except Exception as e:
        return {"status": "unhealthy", "ha": "error", "error": str(e)}

External Health Checks

# Database connectivity
curl -f http://localhost:8000/health/db

# HA cluster health
curl -f http://localhost:8000/health/ha

# Patroni cluster status
curl -f http://localhost:8008/health
curl -f http://localhost:8009/health

Monitoring Setup

Docker Compose Monitoring Stack

version: '3.8'
services:
  prometheus:
    image: prom/prometheus:latest
    ports:
      - "9090:9090"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.path=/prometheus'
      - '--web.console.libraries=/etc/prometheus/console_libraries'
      - '--web.console.templates=/etc/prometheus/consoles'

  grafana:
    image: grafana/grafana:latest
    ports:
      - "3000:3000"
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=admin
    volumes:
      - grafana-storage:/var/lib/grafana
      - ./grafana/dashboards:/etc/grafana/provisioning/dashboards
      - ./grafana/datasources:/etc/grafana/provisioning/datasources

volumes:
  grafana-storage:

Prometheus Configuration

# prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'postgres-exporter'
    static_configs:
      - targets: ['postgres-exporter:9187']
    scrape_interval: 10s

  - job_name: 'pgbouncer-exporter'
    static_configs:
      - targets: ['pgbouncer-exporter:9128']
    scrape_interval: 10s

  - job_name: 'haproxy'
    static_configs:
      - targets: ['haproxy:8404']
    scrape_interval: 10s

  - job_name: 'blacklake-app'
    static_configs:
      - targets: ['blacklake-app:8000']
    scrape_interval: 30s
    metrics_path: '/metrics'

Key Metrics to Monitor

Database Performance

  • Connection Count: pg_stat_database_numbackends
  • Query Rate: rate(pg_stat_database_tup_returned[5m])
  • Cache Hit Ratio: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read)
  • Replication Lag: pg_replication_lag

Pooling Performance

  • Pool Utilization: pgbouncer_pools_cl_active / pgbouncer_pools_maxwait
  • Wait Time: pgbouncer_stats_avg_wait
  • Query Rate: rate(pgbouncer_stats_total_requests[5m])

System Resources

  • CPU Usage: rate(process_cpu_seconds_total[5m])
  • Memory Usage: process_resident_memory_bytes
  • Disk I/O: rate(node_disk_io_time_seconds_total[5m])

Troubleshooting

Common Issues

1. High Connection Count

Symptoms: Connection limit warnings Resolution: 

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Check connection limits
SHOW max_connections;

-- Adjust PgBouncer pool size
ALTER DATABASE blacklake SET pool_size = 100;

2. Replication Lag

Symptoms: Replica falling behind Resolution: 

# Check replication status
curl http://patroni-pg1:8008/replica

# Check WAL generation
SELECT pg_current_wal_lsn();

3. Slow Queries

Symptoms: High query duration Resolution: 

-- Identify slow queries
SELECT query, mean_time, calls
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Check for locks
SELECT * FROM pg_locks WHERE NOT granted;

Performance Tuning

Database Tuning

-- Adjust shared_buffers
ALTER SYSTEM SET shared_buffers = '1GB';

-- Enable query statistics
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

-- Set work memory
ALTER SYSTEM SET work_mem = '64MB';

PgBouncer Tuning

# Adjust pool sizes
default_pool_size = 50
min_pool_size = 5
reserve_pool_size = 20

# Optimize timeouts
server_idle_timeout = 60
query_timeout = 0

Best Practices

Monitoring Strategy

  • Comprehensive Coverage: Monitor all database components
  • Proactive Alerting: Set up alerts before issues occur
  • Performance Baselines: Establish normal performance baselines
  • Regular Reviews: Review metrics and adjust thresholds

Alert Management

  • Severity Levels: Define clear severity levels
  • Escalation Procedures: Define escalation paths
  • Runbook Integration: Link alerts to runbooks
  • False Positive Reduction: Tune alert thresholds

Documentation

  • Runbook Updates: Keep runbooks current
  • Incident Postmortems: Document and learn from incidents
  • Performance Reviews: Regular performance analysis
  • Capacity Planning: Plan for growth

Next Steps