tutorial

Monitoring Azure Database for PostgreSQL with Vigilmon: Flexible Server Health, Read Replica Lag & Application-Layer Uptime Checks

Practical guide to uptime and health monitoring for Azure Database for PostgreSQL Flexible Server — HTTP health endpoints, read replica lag detection, connection pool monitoring, and independent external monitoring with Vigilmon.

Azure Database for PostgreSQL Flexible Server is a fully managed PostgreSQL service with zone-redundant HA, read replicas, and built-in connection pooling via PgBouncer — but your application's uptime depends on more than Azure's infrastructure guarantees. HA failover lag, read replica replication delay, PgBouncer connection pool limits, and Azure AD token expiry can all degrade your application silently while Azure Monitor metrics remain within bounds. Vigilmon adds an independent external layer: HTTP health checks on your Azure PostgreSQL-connected services that probe real database connectivity, detect replica lag, and alert on application-level failures before users notice.

This tutorial shows you how to build meaningful monitoring for Azure Database for PostgreSQL-backed applications using Vigilmon.

What You'll Build

  • A health endpoint that probes real Azure PostgreSQL primary and read replica connectivity
  • Read replica lag detection to catch stale reads
  • PgBouncer connection pool saturation detection
  • A Vigilmon HTTP monitor with appropriate timeout and assertion settings
  • A failover-aware heartbeat and alerting strategy

Prerequisites

  • Azure subscription with Azure Database for PostgreSQL Flexible Server
  • An application connecting to Azure PostgreSQL (any runtime)
  • A free account at vigilmon.online

Step 1: Add a Health Endpoint to Your Azure PostgreSQL Service

Add a /health endpoint that probes both the primary server and any read replicas. Azure PostgreSQL uses standard PostgreSQL wire protocol — any PostgreSQL client works.

Node.js (pg — node-postgres)

// routes/health.js
import pg from 'pg';

// Primary server — Flexible Server FQDN from Azure Portal
const primaryPool = new pg.Pool({
  host: process.env.AZURE_PG_HOST,         // e.g. myserver.postgres.database.azure.com
  user: process.env.AZURE_PG_USER,         // e.g. adminuser@myserver (or just adminuser for Flexible)
  password: process.env.AZURE_PG_PASSWORD,
  database: process.env.AZURE_PG_DATABASE,
  port: 5432,
  ssl: { rejectUnauthorized: true },        // Azure PostgreSQL requires SSL
  max: 3,
  connectionTimeoutMillis: 8000,
  idleTimeoutMillis: 15000,
});

// Read replica — optional
const replicaPool = process.env.AZURE_PG_REPLICA_HOST
  ? new pg.Pool({
      host: process.env.AZURE_PG_REPLICA_HOST,
      user: process.env.AZURE_PG_USER,
      password: process.env.AZURE_PG_PASSWORD,
      database: process.env.AZURE_PG_DATABASE,
      port: 5432,
      ssl: { rejectUnauthorized: true },
      max: 2,
      connectionTimeoutMillis: 8000,
    })
  : null;

export async function healthHandler(req, res) {
  const checks = {};
  let ok = true;
  const start = Date.now();

  // Check 1: Primary server — verify write path
  try {
    const result = await primaryPool.query(`
      SELECT
        pg_is_in_recovery() AS is_standby,
        version() AS pg_version,
        (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') AS active_connections,
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections
    `);
    const row = result.rows[0];
    checks.primaryLatencyMs = Date.now() - start;
    checks.postgresVersion = row.pg_version;
    checks.activeConnections = parseInt(row.active_connections);
    checks.maxConnections = parseInt(row.max_connections);

    const connectionPct = Math.round((row.active_connections / row.max_connections) * 100);
    checks.connectionUsagePct = connectionPct;

    if (row.is_standby) {
      checks.primary = 'unexpectedly in recovery — HA failover may be in progress';
      ok = false;
    } else {
      checks.primary = 'ok';
    }

    // Warn on high connection usage
    const maxConnPct = parseInt(process.env.MAX_CONNECTION_PCT || '80', 10);
    if (connectionPct > maxConnPct) {
      checks.connectionPool = `high: ${connectionPct}% of max_connections used`;
      ok = false;
    } else {
      checks.connectionPool = 'ok';
    }
  } catch (err) {
    checks.primary = `error: ${err.message}`;
    ok = false;
  }

  // Check 2: Read replica — replica lag
  if (replicaPool) {
    try {
      const readerStart = Date.now();
      const result = await replicaPool.query(`
        SELECT
          pg_is_in_recovery() AS is_standby,
          EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS replica_lag_seconds,
          pg_last_xact_replay_timestamp() AS last_replay
      `);
      const row = result.rows[0];
      checks.replicaLatencyMs = Date.now() - readerStart;
      checks.replicaLagSeconds = Math.round((row.replica_lag_seconds || 0) * 10) / 10;
      checks.lastReplayTimestamp = row.last_replay;

      const maxLag = parseFloat(process.env.MAX_REPLICA_LAG_SECONDS || '60');
      if (row.replica_lag_seconds > maxLag) {
        checks.replica = `lag high: ${checks.replicaLagSeconds}s — reads may be stale`;
        ok = false;
      } else {
        checks.replica = 'ok';
      }
    } catch (err) {
      checks.replica = `error: ${err.message}`;
      ok = false;
    }
  } else {
    checks.replica = 'not configured';
  }

  res.status(ok ? 200 : 503).json({
    status: ok ? 'ok' : 'degraded',
    service: 'azure-postgresql-service',
    checks,
  });
}

Python (psycopg2)

# routers/health.py
import os, time, psycopg2, ssl
from psycopg2 import pool as pg_pool
from fastapi import APIRouter
from fastapi.responses import JSONResponse

router = APIRouter()

ssl_context = ssl.create_default_context()

primary_pool = pg_pool.SimpleConnectionPool(
    1, 3,
    host=os.environ['AZURE_PG_HOST'],
    user=os.environ['AZURE_PG_USER'],
    password=os.environ['AZURE_PG_PASSWORD'],
    dbname=os.environ['AZURE_PG_DATABASE'],
    port=5432,
    sslmode='require',
    connect_timeout=8,
)

replica_host = os.environ.get('AZURE_PG_REPLICA_HOST')
replica_pool = pg_pool.SimpleConnectionPool(
    1, 2,
    host=replica_host,
    user=os.environ['AZURE_PG_USER'],
    password=os.environ['AZURE_PG_PASSWORD'],
    dbname=os.environ['AZURE_PG_DATABASE'],
    port=5432,
    sslmode='require',
    connect_timeout=8,
) if replica_host else None

@router.get("/health")
async def health():
    checks = {}
    ok = True
    start = time.monotonic()

    # Check 1: Primary server
    conn = None
    try:
        conn = primary_pool.getconn()
        with conn.cursor() as cur:
            cur.execute("""
                SELECT
                    pg_is_in_recovery(),
                    version(),
                    (SELECT count(*) FROM pg_stat_activity WHERE state = 'active'),
                    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections')
            """)
            is_standby, version, active_conns, max_conns = cur.fetchone()
            checks['primaryLatencyMs'] = int((time.monotonic() - start) * 1000)
            checks['postgresVersion'] = version
            checks['activeConnections'] = int(active_conns)
            checks['maxConnections'] = int(max_conns)

            conn_pct = round(active_conns / max_conns * 100, 1)
            checks['connectionUsagePct'] = conn_pct

            if is_standby:
                checks['primary'] = 'unexpectedly in recovery — failover in progress'
                ok = False
            else:
                checks['primary'] = 'ok'

            max_conn_pct = float(os.environ.get('MAX_CONNECTION_PCT', '80'))
            if conn_pct > max_conn_pct:
                checks['connectionPool'] = f'high: {conn_pct}% of max_connections used'
                ok = False
            else:
                checks['connectionPool'] = 'ok'
    except Exception as e:
        checks['primary'] = f'error: {e}'
        ok = False
    finally:
        if conn:
            primary_pool.putconn(conn)

    # Check 2: Read replica
    if replica_pool:
        conn = None
        try:
            reader_start = time.monotonic()
            conn = replica_pool.getconn()
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT
                        pg_is_in_recovery(),
                        EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag
                """)
                is_standby, lag = cur.fetchone()
                checks['replicaLatencyMs'] = int((time.monotonic() - reader_start) * 1000)
                checks['replicaLagSeconds'] = round(lag or 0, 1)

                max_lag = float(os.environ.get('MAX_REPLICA_LAG_SECONDS', '60'))
                if lag and lag > max_lag:
                    checks['replica'] = f'lag high: {lag:.1f}s'
                    ok = False
                else:
                    checks['replica'] = 'ok'
        except Exception as e:
            checks['replica'] = f'error: {e}'
            ok = False
        finally:
            if conn:
                replica_pool.putconn(conn)
    else:
        checks['replica'] = 'not configured'

    status_code = 200 if ok else 503
    return JSONResponse(status_code=status_code, content={
        'status': 'ok' if ok else 'degraded',
        'service': 'azure-postgresql-service',
        'checks': checks,
    })

Go (pgx)

// internal/health/handler.go
package health

import (
    "context"
    "encoding/json"
    "net/http"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func Handler(primary *pgxpool.Pool, replica *pgxpool.Pool) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        checks := map[string]any{}
        ok := true
        ctx, cancel := context.WithTimeout(r.Context(), 9*time.Second)
        defer cancel()

        // Check 1: Primary
        start := time.Now()
        var isRecovery bool
        var version string
        var activeConns, maxConns int64
        err := primary.QueryRow(ctx, `
            SELECT
                pg_is_in_recovery(),
                version(),
                (SELECT count(*) FROM pg_stat_activity WHERE state = 'active'),
                (SELECT setting::int FROM pg_settings WHERE name = 'max_connections')
        `).Scan(&isRecovery, &version, &activeConns, &maxConns)

        if err != nil {
            checks["primary"] = "error: " + err.Error()
            ok = false
        } else {
            checks["primaryLatencyMs"] = time.Since(start).Milliseconds()
            checks["postgresVersion"] = version
            checks["activeConnections"] = activeConns
            checks["maxConnections"] = maxConns
            checks["connectionUsagePct"] = int64(float64(activeConns) / float64(maxConns) * 100)

            if isRecovery {
                checks["primary"] = "unexpectedly in recovery — failover in progress"
                ok = false
            } else {
                checks["primary"] = "ok"
            }

            connPct := float64(activeConns) / float64(maxConns) * 100
            if connPct > 80 {
                checks["connectionPool"] = "high usage"
                ok = false
            } else {
                checks["connectionPool"] = "ok"
            }
        }

        // Check 2: Replica lag
        if replica != nil {
            readerStart := time.Now()
            var readIsRecovery bool
            var lagSeconds *float64
            err = replica.QueryRow(ctx,
                "SELECT pg_is_in_recovery(), EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))").
                Scan(&readIsRecovery, &lagSeconds)

            if err != nil {
                checks["replica"] = "error: " + err.Error()
                ok = false
            } else {
                checks["replicaLatencyMs"] = time.Since(readerStart).Milliseconds()
                if lagSeconds != nil {
                    checks["replicaLagSeconds"] = *lagSeconds
                    if *lagSeconds > 60 {
                        checks["replica"] = "lag high"
                        ok = false
                    } else {
                        checks["replica"] = "ok"
                    }
                } else {
                    checks["replica"] = "ok"
                }
            }
        } else {
            checks["replica"] = "not configured"
        }

        w.Header().Set("Content-Type", "application/json")
        if !ok {
            w.WriteHeader(http.StatusServiceUnavailable)
        }
        _ = json.NewEncoder(w).Encode(map[string]any{
            "status":  map[bool]string{true: "ok", false: "degraded"}[ok],
            "service": "azure-postgresql-service",
            "checks":  checks,
        })
    }
}

Verify the endpoint before configuring Vigilmon:

curl -i https://your-app.example.com/health
# HTTP/1.1 200 OK
# {"status":"ok","service":"azure-postgresql-service","checks":{"primary":"ok","primaryLatencyMs":7,"connectionPool":"ok","connectionUsagePct":12,"replica":"ok","replicaLagSeconds":0.3}}

Step 2: Configure the Vigilmon HTTP Monitor

  1. Log in to Vigilmon → Add Monitor → HTTP.
  2. URL: your service's health endpoint (e.g. https://api.example.com/health).
  3. Check interval: 60 seconds.
  4. Response timeout: 12 seconds.
  5. Expected status: 200.
  6. JSON assertion: path status, expected value ok.
  7. Save the monitor.

Create a second monitor pointing to a /health/replica endpoint for read replica isolation — this allows you to distinguish complete unavailability from degraded read performance during Azure PostgreSQL HA failover events.

Azure PostgreSQL SSL note: Flexible Server enforces SSL by default. Your connection strings must include sslmode=require (or equivalent). A health endpoint that works locally without SSL will fail when deployed to Azure unless SSL is explicitly configured. The Vigilmon probe hits your application's HTTPS endpoint — the database SSL layer is internal to your application.

Step 3: Heartbeat Monitoring for Azure PostgreSQL-Backed Workers

Background workers that process PostgreSQL data (ETL jobs, scheduled reports, queue consumers) will stall during an Azure PostgreSQL HA failover — the standby is promoted but connection pools need to reconnect. Vigilmon's heartbeat monitors detect silent worker death.

Set Up the Heartbeat Monitor

  1. In Vigilmon, go to Monitors → New Monitor → Heartbeat.
  2. Set the name: azure-pg-worker.
  3. Set the expected interval: 10 minutes (adjust to your job frequency).
  4. Set the grace period: 20 minutes (allow for HA failover and reconnection).
  5. Save — copy the unique heartbeat URL.

Wire It Into Your Worker

Node.js:

import pg from 'pg';
import fetch from 'node-fetch';

const pool = new pg.Pool({
  connectionString: process.env.AZURE_PG_CONNECTION_STRING,
  ssl: { rejectUnauthorized: true },
});

async function runScheduledJob() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const result = await client.query(
      `UPDATE job_queue
       SET status = 'processing', started_at = now()
       WHERE status = 'pending' AND id IN (
         SELECT id FROM job_queue WHERE status = 'pending'
         ORDER BY created_at LIMIT 100
         FOR UPDATE SKIP LOCKED
       )
       RETURNING id`
    );

    for (const row of result.rows) {
      await processJob(row.id, client);
    }

    await client.query('COMMIT');

    // Ping Vigilmon only after successful commit
    await fetch(process.env.VIGILMON_HEARTBEAT_URL).catch(() => {});
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Scheduled job failed — heartbeat NOT sent:', err.message);
  } finally {
    client.release();
  }
}

setInterval(runScheduledJob, 10 * 60 * 1000);

Python:

import os, time, requests, psycopg2

DSN = os.environ['AZURE_PG_CONNECTION_STRING']

def run_scheduled_job():
    with psycopg2.connect(DSN, sslmode='require') as conn:
        with conn.cursor() as cur:
            cur.execute("""
                UPDATE job_queue
                SET status = 'processing', started_at = now()
                WHERE status = 'pending' AND id IN (
                    SELECT id FROM job_queue WHERE status = 'pending'
                    ORDER BY created_at LIMIT 100
                    FOR UPDATE SKIP LOCKED
                )
                RETURNING id
            """)
            jobs = cur.fetchall()
            for (job_id,) in jobs:
                process_job(job_id, cur)
        conn.commit()

    # Ping Vigilmon after successful commit
    requests.get(os.environ['VIGILMON_HEARTBEAT_URL'], timeout=5)

while True:
    try:
        run_scheduled_job()
    except Exception as e:
        print(f"Scheduled job failed — heartbeat NOT sent: {e}")
    time.sleep(600)

Step 4: PgBouncer Connection Pool Monitoring

Azure Database for PostgreSQL Flexible Server includes built-in PgBouncer connection pooling. When your application approaches PgBouncer's max_client_conn limit, new connection attempts queue and eventually timeout — causing cascading failures that look like database downtime to Vigilmon, but are actually connection pool exhaustion.

Surface PgBouncer stats in your health endpoint:

// PgBouncer admin database (port 6432)
const pgbouncerPool = new pg.Pool({
  host: process.env.AZURE_PG_HOST,
  port: 6432, // PgBouncer port on Flexible Server
  user: 'pgbouncer', // PgBouncer admin user
  password: process.env.AZURE_PG_PGBOUNCER_PASSWORD,
  database: 'pgbouncer',
  ssl: { rejectUnauthorized: true },
  max: 1,
});

export async function pgbouncerHealthHandler(req, res) {
  try {
    const result = await pgbouncerPool.query('SHOW POOLS');
    const pools = result.rows.map(row => ({
      database: row.database,
      user: row.user,
      activeClients: parseInt(row.cl_active),
      waitingClients: parseInt(row.cl_waiting),
      activeServers: parseInt(row.sv_active),
      idleServers: parseInt(row.sv_idle),
    }));

    const totalWaiting = pools.reduce((sum, p) => sum + p.waitingClients, 0);
    const ok = totalWaiting === 0;

    res.status(ok ? 200 : 503).json({
      status: ok ? 'ok' : 'degraded',
      totalWaitingClients: totalWaiting,
      pools,
    });
  } catch (err) {
    res.status(503).json({ status: 'error', error: err.message });
  }
}

Configure a separate Vigilmon monitor for /health/pgbouncer with a JSON assertion on totalWaitingClients equals 0.

Step 5: Alerting Strategy

| Alert channel | When to use | |---|---| | PagerDuty | Primary returns 503 (unavailable or in HA failover) | | Slack + Email | Primary is unexpectedly in recovery (failover detected) | | Slack webhook | Read replica lag > 60 seconds (stale reads possible) | | Slack webhook | Connection usage > 80% of max_connections | | PagerDuty | PgBouncer waiting clients > 0 (pool saturation) | | PagerDuty | Heartbeat missed (background worker stopped) | | Status page | Any user-facing service backed by Azure PostgreSQL |

Configure response time thresholds in Vigilmon:

  • Alert at 200ms for primary health endpoint (direct PostgreSQL should be fast)
  • Alert at 500ms for replica health endpoint (replica queries may be slower under lag)
  • Alert at 5s for overall health endpoint response time (connection pool exhaustion can cause slow hangs)

What Vigilmon Catches That Azure Monitor Misses

| Scenario | Azure Monitor | Vigilmon | |---|---|---| | Primary in recovery during HA failover | No application-layer check | /health catches pg_is_in_recovery() | | Read replica lag growing silently | replication_lag metric exists | HTTP assertion on replicaLagSeconds | | Connection pool exhausted | No pool-level metric | Health endpoint probe fails → 503 | | PgBouncer saturation (waiting clients) | No client-wait metric | /health/pgbouncer surfaces wait count | | SSL certificate rotation breaks connections | No connection-layer check | Health endpoint probe catches SSL errors | | Azure AD token expiry breaks auth | No token expiry check | Health endpoint probe catches auth errors | | Worker stalled after failover | No end-to-end check | Heartbeat grace period expires → alert | | Azure Monitor alert delivery fails | Alerts silently lost | Vigilmon is external — unaffected |

Azure Database for PostgreSQL Flexible Server handles the infrastructure — but application-layer failures during HA failover, replica lag, and connection pool exhaustion can degrade your users silently. External health checks with Vigilmon give you the independent signal you need.

Start monitoring your Azure PostgreSQL applications in under 5 minutes — register free at vigilmon.online.

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles