tutorial

How to Monitor pgpool-II PostgreSQL HA with Vigilmon

pgpool-II sits in front of your PostgreSQL cluster and provides connection pooling, load balancing across replicas, and automatic failover — turning a primar...

pgpool-II sits in front of your PostgreSQL cluster and provides connection pooling, load balancing across replicas, and automatic failover — turning a primary-replica pair into a highly available database tier. But that middleware position means pgpool-II is now a single point of failure in front of your database: if pgpool-II stops accepting connections, every application that talks to your database stack goes down simultaneously, even if PostgreSQL itself is perfectly healthy.

This tutorial walks through setting up external uptime monitoring for pgpool-II with Vigilmon, so you detect pgpool-II failures, connection pool exhaustion, and backend node failures the moment they happen — before your application surfaces them as database errors.

Why pgpool-II needs external monitoring

pgpool-II is often treated as infrastructure plumbing that's assumed to be up. That assumption causes slow incident response when it breaks:

  • pgpool-II process crashes — all application connections fail instantly; if you only monitor PostgreSQL directly, your database monitoring shows green while every app is down
  • Connection pool exhaustion — pgpool-II accepts no new connections when the pool is full; existing connections work fine, making this failure mode invisible to connection-level monitors
  • Health check backend failures — pgpool-II's health_check_period detects failed PostgreSQL backends and removes them from the pool; if the health check itself is misconfigured, a failed backend stays in the pool and causes intermittent query failures
  • Watchdog quorum loss — in multi-pgpool-II setups, watchdog ensures only one virtual IP is active; a watchdog misconfiguration can cause split-brain, with two pgpool instances both accepting the VIP
  • Failover execution failures — when the primary PostgreSQL dies, pgpool-II's failover_command runs; if it fails silently, the cluster has no writable primary while pgpool-II reports healthy

Vigilmon monitors from outside your infrastructure, testing whether pgpool-II is actually accepting connections — the same check your applications perform.

What you'll need

  • pgpool-II installed and running (typically on port 5432, or 9999 to avoid conflict with local PostgreSQL)
  • A PostgreSQL backend cluster (primary + one or more standbys)
  • A monitoring database user with minimal privileges
  • A free Vigilmon account

Step 1: Create a pgpool-II health check user

Create a dedicated user for monitoring queries. This user needs minimal permissions:

-- Connect to PostgreSQL directly (not via pgpool-II) for this setup
CREATE USER vigilmon_monitor WITH PASSWORD 'your-secure-password';

-- Grant connect privilege
GRANT CONNECT ON DATABASE your_database TO vigilmon_monitor;

-- pgpool-II can check health with just a connection — no table access needed

For the pgpool-II PCP interface (used for administrative monitoring), add the user to pcp.conf:

# Generate a password hash for pcp.conf
pg_md5 your-pcp-password

# Add to /etc/pgpool-II/pcp.conf
pgpool_monitor:md5hashhere

Step 2: Build a health check endpoint for pgpool-II

pgpool-II doesn't expose an HTTP endpoint natively, so you need a thin wrapper. Here are two approaches:

Option A: Shell script + nginx

#!/bin/bash
# /usr/local/bin/pgpool-health-check.sh
# Returns 0 if pgpool-II is accepting connections, 1 otherwise

PGPASSWORD="your-secure-password" \
psql -h 127.0.0.1 -p 9999 \
     -U vigilmon_monitor \
     -d your_database \
     -c "SELECT 1" \
     -t -A 2>/dev/null | grep -q "^1$"

Expose it via xinetd or a simple nginx script handler. A more portable approach is a small Python HTTP server:

Option B: Python health endpoint

#!/usr/bin/env python3
# pgpool_health_server.py — lightweight HTTP health endpoint for pgpool-II
import subprocess
import os
from http.server import HTTPServer, BaseHTTPRequestHandler

PGPOOL_HOST = os.environ.get("PGPOOL_HOST", "127.0.0.1")
PGPOOL_PORT = os.environ.get("PGPOOL_PORT", "9999")
PGPOOL_USER = os.environ.get("PGPOOL_USER", "vigilmon_monitor")
PGPOOL_DB   = os.environ.get("PGPOOL_DB", "your_database")
PGPOOL_PASS = os.environ.get("PGPOOL_PASS", "your-secure-password")

def check_pgpool():
    env = {**os.environ, "PGPASSWORD": PGPOOL_PASS}
    try:
        result = subprocess.run(
            ["psql", "-h", PGPOOL_HOST, "-p", PGPOOL_PORT,
             "-U", PGPOOL_USER, "-d", PGPOOL_DB,
             "-c", "SELECT 1", "-t", "-A", "--connect-timeout=5"],
            capture_output=True, text=True, timeout=10, env=env
        )
        return result.returncode == 0 and "1" in result.stdout
    except Exception:
        return False

def check_pcp():
    """Check via PCP (pgpool admin interface) for node status"""
    try:
        result = subprocess.run(
            ["pcp_node_count", "-h", "127.0.0.1", "-p", "9898",
             "-U", "pgpool_monitor", "-w"],
            capture_output=True, text=True, timeout=10
        )
        count = int(result.stdout.strip())
        return count > 0
    except Exception:
        return False

class HealthHandler(BaseHTTPRequestHandler):
    def do_GET(self):
        if self.path == "/health":
            pg_ok = check_pgpool()
            pcp_ok = check_pcp()
            if pg_ok and pcp_ok:
                body = b'{"status":"ok","pgpool":"up","pcp":"up"}'
                code = 200
            elif pg_ok:
                body = b'{"status":"degraded","pgpool":"up","pcp":"down"}'
                code = 200
            else:
                body = b'{"status":"down","pgpool":"down"}'
                code = 503
            self.send_response(code)
            self.send_header("Content-Type", "application/json")
            self.end_headers()
            self.wfile.write(body)
        else:
            self.send_response(404)
            self.end_headers()
    def log_message(self, *args): pass

if __name__ == "__main__":
    print("Starting pgpool health server on :8080")
    HTTPServer(("0.0.0.0", 8080), HealthHandler).serve_forever()

Run it as a systemd service:

# /etc/systemd/system/pgpool-health.service
[Unit]
Description=pgpool-II HTTP Health Endpoint
After=pgpool2.service

[Service]
ExecStart=/usr/bin/python3 /usr/local/bin/pgpool_health_server.py
Environment=PGPOOL_HOST=127.0.0.1
Environment=PGPOOL_PORT=9999
Environment=PGPOOL_USER=vigilmon_monitor
Environment=PGPOOL_DB=your_database
EnvironmentFile=/etc/pgpool-monitor.env
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

systemctl enable --now pgpool-health
curl http://localhost:8080/health
# {"status":"ok","pgpool":"up","pcp":"up"}

Step 3: Monitor the pgpool-II health endpoint with Vigilmon

  1. Log in to vigilmon.online and go to Monitors → New Monitor
  2. Choose HTTP / HTTPS
  3. Set the URL to http://your-pgpool-host:8080/health
  4. Set the check interval to 1 minute
  5. Under Expected response:
    • Status code: 200
    • Response body contains: "status":"ok"
  6. Save the monitor

When pgpool-II stops accepting connections, this monitor fires within 60 seconds — before your applications have exhausted their connection retry budgets.

Step 4: Monitor the pgpool-II TCP port directly

Add a TCP monitor for the pgpool-II listening port:

  1. In Vigilmon, go to Monitors → New Monitor
  2. Choose TCP Port
  3. Host: your-pgpool-host, Port: 9999 (or 5432 if pgpool-II takes the standard port)
  4. Save the monitor

The TCP monitor answers "is pgpool-II listening at all?" in under a second. If the TCP monitor fires, the pgpool-II process has crashed or the port is blocked. If only the HTTP health monitor fires, the process is up but failing internal health checks.

Step 5: Monitor pgpool-II PCP port

The PCP interface (port 9898 by default) is used for administrative operations and node management. Monitor it separately:

  1. Add a TCP monitor for your-pgpool-host:9898
  2. Check interval: 2 minutes

A PCP port failure while the main pool port is up indicates a partial pgpool-II internal failure — the connection pooler is working but the management interface is broken, which means automated failover and node reintegration may not work.

Step 6: Monitor the virtual IP (VIP) for watchdog setups

If you run multiple pgpool-II instances with watchdog and a virtual IP:

# pgpool.conf watchdog settings
use_watchdog = on
wd_hostname = 'pgpool-1'
wd_port = 9000
delegate_ip = '192.168.1.100'  # Virtual IP

Add a dedicated Vigilmon monitor for the VIP:

  1. URL: http://192.168.1.100:8080/health (your VIP, not a specific host)
  2. Expected status code: 200
  3. Check interval: 1 minute

This monitor detects VIP failover failures — if the primary pgpool-II goes down and the VIP doesn't migrate to a standby within the expected window, this monitor fires. It's the most important external check for watchdog-enabled setups.

Step 7: Configure alert channels

Database connectivity failures are the highest-severity incidents for most applications. Configure fast alerting:

Email alerts

  1. In Vigilmon, go to Alert Channels → Add Channel → Email
  2. Add your DBA team or on-call rotation email
  3. Assign to all pgpool-II monitors

PagerDuty webhook

  1. Go to Alert Channels → Add Channel → Webhook
  2. Configure a PagerDuty Events API V2 integration URL
  3. Assign to your monitors with no delay — pgpool-II failures warrant immediate paging

Vigilmon's alert payload:

{
  "monitor_name": "pgpool-II Health",
  "status": "down",
  "url": "http://your-pgpool-host:8080/health",
  "started_at": "2026-07-02T08:45:00Z",
  "duration_seconds": 63
}

Step 8: Diagnose pgpool-II failures

When Vigilmon fires an alert:

# 1. Check pgpool-II service status
systemctl status pgpool2
# Or:
pg_ctl status -D /etc/pgpool-II

# 2. Check pgpool-II logs
tail -n 100 /var/log/postgresql/pgpool.log
# or:
journalctl -u pgpool2 -n 100 --no-pager

# 3. Check node status via PCP
pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool_monitor -w -n 0
pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool_monitor -w -n 1
# Status: 2 = up, 3 = down/excluded

# 4. Check pool status via psql
psql -h 127.0.0.1 -p 9999 -U vigilmon_monitor -c "SHOW POOL_NODES"

# 5. Check connection pool saturation
psql -h 127.0.0.1 -p 9999 -U vigilmon_monitor -c "SHOW POOL_PROCESSES"

# 6. Check watchdog status (if using watchdog)
pcp_watchdog_info -h 127.0.0.1 -p 9898 -U pgpool_monitor -w

# 7. Check PostgreSQL backends directly
psql -h primary-host -p 5432 -U postgres -c "SELECT * FROM pg_stat_replication"

Common failure patterns

| Symptom | Likely cause | Fix | |---------|-------------|-----| | pgpool-II TCP port down | Process crashed or OOM killed | Restart pgpool-II, check OOM logs | | Health endpoint 503 | Connection to PostgreSQL failing | Check PostgreSQL status, network | | PCP port unreachable | pgpool-II internal error | Restart pgpool-II service | | VIP not responding | Watchdog failover failed | Check wd logs, manually move VIP | | Intermittent query failures | Failed node still in pool | Run pcp_detach_node to remove bad backend | | New connections rejected | Pool exhausted | Increase max_pool or investigate connection leaks |

Step 9: Create a status page for your database tier

For applications and teams that depend on your PostgreSQL cluster:

  1. In Vigilmon, go to Status Pages → New Status Page
  2. Name it "Database Tier Status"
  3. Add monitors:
    • pgpool-II Health (HTTP)
    • pgpool-II Port (TCP)
    • pgpool-II VIP (if using watchdog)
  4. Publish the page

Expose this to your development teams. When apps report database errors, developers check the status page before diving into application logs — cutting investigation time significantly.

Recommended monitoring setup

| Monitor | Type | Interval | What it catches | |---------|------|----------|-----------------| | http://pgpool-host:8080/health | HTTP | 1 min | Connection pool broken, backend down | | pgpool-host:9999 | TCP | 1 min | pgpool-II process crashed | | pgpool-host:9898 | TCP | 2 min | PCP/management interface down | | http://vip:8080/health | HTTP | 1 min | VIP failover failed (watchdog) |

What's next

With Vigilmon monitoring pgpool-II, you'll know within 60 seconds if your database middle tier fails — long before connection pool exhaustion cascades into application timeouts and error pages. Next steps:

  • Connection pool sizing: track the number of active pool processes from the health endpoint over time; a steady increase toward max_pool is an early warning of connection leaks
  • Automated failover testing: periodically simulate a primary PostgreSQL failure in staging and verify that Vigilmon reports the VIP staying healthy throughout failover (it should dip for at most 20-30 seconds)
  • Backend node alerts: extend the health endpoint to include individual node status and alert if any backend node is excluded from the pool for more than 5 minutes

External uptime monitoring for pgpool-II closes the gap between "the database is up" and "my applications can reach the database" — two different things that your monitoring should never conflate.

Start monitoring your pgpool-II instance in under 2 minutes at vigilmon.online — free tier, no credit card required.

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