tutorial

Monitoring pg_auto_failover with Vigilmon

pg_auto_failover automates PostgreSQL high availability — but if the monitor node is unreachable or a keeper stops reporting, automatic failover silently stops working. Here's how to monitor your entire pg_auto_failover cluster with Vigilmon.

pg_auto_failover is an open source PostgreSQL extension that provides automatic failover for PostgreSQL primary-standby pairs. Developed by Citus Data and now maintained as a community project, it coordinates failover through a dedicated monitor node: a PostgreSQL instance running the pgautofailover extension that watches the health of each node and orchestrates state transitions using a Finite State Machine (FSM).

The architecture is elegant: a Monitor tracks the cluster state, Keeper processes on each PostgreSQL node report local health to the Monitor, and the Monitor coordinates safe failover — waiting for the standby to receive all committed WAL before promoting it. The result is automated, data-safe failover without split-brain.

But pg_auto_failover introduces monitoring dependencies of its own. If the Monitor goes down, automatic failover stops working entirely — the cluster enters a safe-but-frozen state. If a Keeper stops reporting, the Monitor assumes that node is unhealthy and may initiate an unnecessary failover. Vigilmon monitors every layer of your pg_auto_failover cluster.

What You'll Set Up

  • TCP and HTTP monitors for the pg_auto_failover Monitor node
  • Heartbeat monitors for Keeper process health on each PostgreSQL node
  • Replication lag monitoring for primary-standby synchronization
  • FSM state and failover event alerting

Prerequisites

  • pg_auto_failover Monitor node running with PostgreSQL on port 5432
  • Keeper processes running on each PostgreSQL node (primary and standby)
  • A free Vigilmon account

Step 1: Monitor the pg_auto_failover Monitor Node

The Monitor is the single point of coordination for all automatic failover decisions. If the Monitor PostgreSQL instance is unreachable, the Keepers cannot report health, the FSM cannot transition states, and automatic failover is completely disabled. Your cluster remains available (PostgreSQL primary and standby continue serving traffic) but in a frozen, unrecoverable-on-failure state.

  1. Log in to vigilmon.online and click Add Monitor.
  2. Set Type to TCP Port.
  3. Enter the Monitor node hostname and port 5432.
  4. Set Check interval to 1 minute.
  5. Name it "pg_auto_failover Monitor".
  6. Click Save.

A TCP check confirms the Monitor's PostgreSQL process is listening. Since the Monitor is itself a PostgreSQL instance, add a deeper health probe via a lightweight SQL query:

#!/usr/bin/env python3
"""Probes the pg_auto_failover Monitor with a query to verify the extension is functional."""
import psycopg2
import requests
import sys

MONITOR_DSN = "host=monitor.yourdomain.com port=5432 user=autoctl_node dbname=pg_auto_failover connect_timeout=5"
HEARTBEAT_URL = "https://vigilmon.online/heartbeat/YOUR_MONITOR_ID"

try:
    conn = psycopg2.connect(MONITOR_DSN)
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM pgautofailover.node")
        count = cur.fetchone()[0]
    conn.close()
    requests.get(HEARTBEAT_URL, timeout=5)
except Exception as e:
    print(f"Monitor probe failed: {e}", file=sys.stderr)
    sys.exit(1)

Schedule every 2 minutes. This confirms not just that PostgreSQL is running, but that the pgautofailover schema is accessible — the Monitor is fully operational.

Step 2: Monitor Keeper Process Health on Each Node

Keeper processes run alongside each PostgreSQL instance (primary and standby). A Keeper checks the health of its local PostgreSQL process and reports it to the Monitor every few seconds. If a Keeper stops reporting:

  • The Monitor marks that node as unhealthy after a timeout
  • This may trigger an unnecessary failover if the Keeper on the primary stops (even if PostgreSQL itself is fine)
  • Or, if the Keeper on the standby stops, the Monitor loses visibility into standby health

Add a Keeper heartbeat for each node:

#!/bin/bash
# Run on each PostgreSQL node to verify the keeper process is alive and reporting
HEARTBEAT_URL="https://vigilmon.online/heartbeat/YOUR_KEEPER_NODE1_ID"

# Check keeper process
if pgrep -f "pg_autoctl" > /dev/null; then
    # Verify keeper is reporting to the monitor (check last contact time)
    LAST_CONTACT=$(psql -h localhost -U autoctl_node -d pg_auto_failover -tAc \
        "SELECT EXTRACT(EPOCH FROM (now() - last_report_time)) 
         FROM pgautofailover.node 
         WHERE nodename = '$(hostname)'" 2>/dev/null)
    
    if [ -n "$LAST_CONTACT" ] && [ "$(echo "$LAST_CONTACT < 30" | bc 2>/dev/null)" = "1" ]; then
        curl -s "$HEARTBEAT_URL" > /dev/null
    else
        echo "Keeper last reported ${LAST_CONTACT:-unknown}s ago (threshold: 30s)" >&2
    fi
else
    echo "pg_autoctl process not running" >&2
fi

Deploy this script on both the primary and standby nodes, using separate Vigilmon heartbeat URLs for each. Schedule every 30 seconds (Vigilmon's minimum heartbeat interval) with an alert after 2 missed heartbeats (60-second window).

Step 3: Monitor PostgreSQL Primary Health

The primary node serves all write traffic and most read traffic. Monitor it with both a TCP check and a SQL connectivity probe:

TCP monitor:

  1. Click Add Monitor → TCP Port.
  2. Enter the primary PostgreSQL hostname and port 5432.
  3. Check interval: 1 minute.
  4. Name it "pg_auto_failover Primary PostgreSQL".

Connection probe heartbeat:

#!/usr/bin/env python3
import psycopg2
import requests
import sys

PRIMARY_DSN = "host=primary.yourdomain.com port=5432 user=monitor dbname=postgres connect_timeout=5"
HEARTBEAT_URL = "https://vigilmon.online/heartbeat/YOUR_PRIMARY_ID"

try:
    conn = psycopg2.connect(PRIMARY_DSN)
    with conn.cursor() as cur:
        # Verify this is actually the primary
        cur.execute("SELECT NOT pg_is_in_recovery()")
        is_primary = cur.fetchone()[0]
    conn.close()
    
    if is_primary:
        requests.get(HEARTBEAT_URL, timeout=5)
    else:
        print("This node is in recovery — not the primary", file=sys.stderr)
        sys.exit(1)
except Exception as e:
    print(f"Primary probe failed: {e}", file=sys.stderr)
    sys.exit(1)

Schedule every 2 minutes. The pg_is_in_recovery() check confirms the probe is hitting the actual primary, not a standby. This is important after a failover — the "old primary" host may still be listening but now be in recovery.

Step 4: Monitor Standby Replication Lag

Replication lag is the time the standby is behind the primary in WAL. pg_auto_failover requires synchronous replication by default for zero data loss failover — but the synchronous replication mode can be automatically downgraded to asynchronous when the standby is unhealthy. In asynchronous mode, a failover risks data loss.

Add a replication lag probe that runs from the primary:

#!/usr/bin/env python3
"""
Checks streaming replication lag between primary and standby.
Alert threshold: >30s indicates risk of data loss if failover occurs.
"""
import psycopg2
import requests
import sys

PRIMARY_DSN = "host=primary.yourdomain.com port=5432 user=monitor dbname=postgres connect_timeout=5"
HEARTBEAT_URL = "https://vigilmon.online/heartbeat/YOUR_REPLAG_ID"
MAX_LAG_SECONDS = 30

try:
    conn = psycopg2.connect(PRIMARY_DSN)
    with conn.cursor() as cur:
        cur.execute("""
            SELECT
                application_name,
                state,
                EXTRACT(EPOCH FROM (now() - write_lag)) AS write_lag_seconds,
                EXTRACT(EPOCH FROM (now() - flush_lag)) AS flush_lag_seconds,
                EXTRACT(EPOCH FROM (now() - replay_lag)) AS replay_lag_seconds,
                sync_state
            FROM pg_stat_replication
        """)
        rows = cur.fetchall()
    conn.close()
except Exception as e:
    print(f"Could not connect to primary: {e}", file=sys.stderr)
    sys.exit(1)

if not rows:
    print("No standbys streaming — standby may be disconnected", file=sys.stderr)
    sys.exit(1)

max_lag = 0
for app_name, state, write_lag, flush_lag, replay_lag, sync_state in rows:
    lag = max(write_lag or 0, flush_lag or 0, replay_lag or 0)
    max_lag = max(max_lag, lag)
    if sync_state == "async":
        print(f"WARNING: Standby {app_name} in ASYNC mode — synchronous replication degraded")

if max_lag <= MAX_LAG_SECONDS:
    requests.get(HEARTBEAT_URL, timeout=5)
else:
    print(f"Replication lag {max_lag:.1f}s exceeds threshold {MAX_LAG_SECONDS}s", file=sys.stderr)
    sys.exit(1)

Schedule every 2 minutes. Also alert if any standby shows sync_state = 'async' — this means pg_auto_failover has downgraded to asynchronous replication, indicating the standby is unhealthy or lagging too far behind.

Step 5: Monitor FSM State and Detect Stuck Transitions

pg_auto_failover uses a Finite State Machine with states like primary, secondary, wait_primary, wait_standby, demoted, and stop_replication. Transitional states like wait_primary and wait_standby should resolve within seconds to minutes. A node stuck in a transitional state for >10 minutes indicates a stuck failover — usually because WAL catch-up is taking too long or the Keeper cannot reach the Monitor.

Add an FSM state probe:

#!/usr/bin/env python3
"""
Queries the pg_auto_failover monitor for node states.
Alerts if any node is stuck in a transitional state for >10 minutes.
"""
import psycopg2
import requests
import sys

MONITOR_DSN = "host=monitor.yourdomain.com port=5432 user=autoctl_node dbname=pg_auto_failover connect_timeout=5"
HEARTBEAT_URL = "https://vigilmon.online/heartbeat/YOUR_FSM_ID"
MAX_TRANSITION_MINUTES = 10

TRANSITIONAL_STATES = {
    "wait_primary", "wait_standby", "stop_replication",
    "demote_timeout", "draining", "catchingup"
}

try:
    conn = psycopg2.connect(MONITOR_DSN)
    with conn.cursor() as cur:
        cur.execute("""
            SELECT
                nodename,
                nodeport,
                reportedstate,
                goalstate,
                EXTRACT(EPOCH FROM (now() - reporttime)) / 60 AS minutes_in_state
            FROM pgautofailover.node
        """)
        nodes = cur.fetchall()
    conn.close()
except Exception as e:
    print(f"Monitor query failed: {e}", file=sys.stderr)
    sys.exit(1)

stuck = []
for nodename, nodeport, reported, goal, minutes in nodes:
    if reported in TRANSITIONAL_STATES and (minutes or 0) > MAX_TRANSITION_MINUTES:
        stuck.append(f"{nodename}:{nodeport} in state '{reported}' for {minutes:.0f} minutes")

if not stuck:
    requests.get(HEARTBEAT_URL, timeout=5)
else:
    for s in stuck:
        print(f"Stuck FSM transition: {s}", file=sys.stderr)
    sys.exit(1)

Schedule every 5 minutes.

Step 6: Monitor Failover Events

Every automatic failover is a signal worth investigating — even successful ones. Failovers indicate that the primary was unhealthy enough to trigger promotion. Monitoring failover frequency helps identify recurring instability in your PostgreSQL deployment.

Add a failover event probe:

#!/usr/bin/env python3
"""
Checks the pg_auto_failover event log for recent failover events.
Alerts on any automatic failover in the last 24 hours.
"""
import psycopg2
import requests
import sys
from datetime import datetime, timedelta, timezone

MONITOR_DSN = "host=monitor.yourdomain.com port=5432 user=autoctl_node dbname=pg_auto_failover connect_timeout=5"
HEARTBEAT_URL = "https://vigilmon.online/heartbeat/YOUR_FAILOVER_ID"
LOOKBACK_HOURS = 24

cutoff = datetime.now(timezone.utc) - timedelta(hours=LOOKBACK_HOURS)

try:
    conn = psycopg2.connect(MONITOR_DSN)
    with conn.cursor() as cur:
        cur.execute("""
            SELECT count(*)
            FROM pgautofailover.event
            WHERE eventtime > %s
              AND reportedstate = 'wait_primary'  -- promotion events
        """, (cutoff,))
        failover_count = cur.fetchone()[0]
    conn.close()
except Exception as e:
    print(f"Monitor query failed: {e}", file=sys.stderr)
    sys.exit(1)

# Always ping heartbeat — use Vigilmon alert for investigation, not availability
requests.get(HEARTBEAT_URL, timeout=5)

if failover_count > 0:
    print(f"INFO: {failover_count} failover event(s) in the last {LOOKBACK_HOURS}h — investigate primary stability")

For failover events, send an alert immediately via a separate Vigilmon notification channel. Failovers aren't unavailability events, but they need investigation.

Step 7: Monitor Synchronous Replication Health

pg_auto_failover uses synchronous_standby_names to enforce synchronous replication. If it ever sets this to empty (async mode), a failover could lose committed transactions. Monitor this directly:

#!/bin/bash
HEARTBEAT_URL="https://vigilmon.online/heartbeat/YOUR_SYNC_ID"

SYNC_STANDBY=$(psql -h primary.yourdomain.com -U monitor -d postgres -tAc \
    "SHOW synchronous_standby_names" 2>/dev/null)

if [ -n "$SYNC_STANDBY" ]; then
    curl -s "$HEARTBEAT_URL" > /dev/null
else
    echo "synchronous_standby_names is empty — async mode active" >&2
fi

Schedule every 5 minutes. An empty synchronous_standby_names means write durability guarantees have been downgraded.

Step 8: Configure Alert Routing

| Monitor | Urgency | Alert Action | |---------|---------|-------------| | Monitor TCP down | Critical | Page on-call immediately | | Monitor SQL probe | Critical | Page on-call immediately | | Keeper not reporting | High | Alert within 2 minutes | | Primary PostgreSQL down | Critical | Page on-call (failover in progress) | | Replication lag >30s | High | Alert within 5 minutes | | Standby in async mode | High | Alert within 5 minutes | | FSM stuck in transitional state | High | Alert within 10 minutes | | Failover event detected | Medium | Alert for investigation | | synchronous_standby_names empty | Critical | Page on-call immediately |

In Vigilmon:

  1. Go to Settings → Alerts.
  2. Add PagerDuty, Slack, or email channels.
  3. Apply escalation policies per monitor.

The Monitor going down and synchronous_standby_names becoming empty are your two highest-priority alerts — both silently disable the automatic failover protection that pg_auto_failover exists to provide.

Key Metrics Summary

| Monitor | Type | Interval | Alert Threshold | |---------|------|----------|----------------| | Monitor TCP | TCP | 1 min | 1 failure | | Monitor SQL probe | Heartbeat | 2 min | 1 miss | | Keeper (primary node) | Heartbeat | 30 sec | 2 misses | | Keeper (standby node) | Heartbeat | 30 sec | 2 misses | | Primary PostgreSQL TCP | TCP | 1 min | 1 failure | | Primary connection probe | Heartbeat | 2 min | 1 miss | | Replication lag | Heartbeat | 2 min | 1 miss (>30s) | | FSM state check | Heartbeat | 5 min | 1 miss | | Synchronous standby names | Heartbeat | 5 min | 1 miss |

Conclusion

pg_auto_failover's promise is automatic, data-safe PostgreSQL failover — but that automation depends on a healthy Monitor node and Keeper processes on every database node. If the Monitor goes down, your cluster loses its ability to fail over. If a Keeper stops reporting, the Monitor may trigger an unnecessary failover or lose visibility into a real failure.

Vigilmon adds the external visibility layer that pg_auto_failover's internal FSM doesn't provide: continuous checks on the Monitor, Keepers, replication lag, FSM state, and synchronous replication mode. You'll know the moment your HA automation is compromised, long before the first real failure tests it.

Get started 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