How to Monitor Odyssey PostgreSQL Connection Pooler with Vigilmon

PostgreSQL connection pooling is one of the most effective ways to scale database throughput without adding hardware — but it also introduces a new failure point that sits between your application and your database. Odyssey is a high-performance, multithreaded PostgreSQL connection pooler developed by Yandex, designed for large-scale deployments where PgBouncer's single-threaded architecture becomes a bottleneck. With Odyssey in your stack, monitoring it is no longer optional: if the pooler goes down, every service that talks to PostgreSQL goes down with it.

This tutorial shows you how to deploy Odyssey, configure it for production use, expose a health endpoint, and integrate it with Vigilmon for external uptime monitoring and alerting.

Why connection pooler monitoring matters

Connection poolers are invisible when they're working and catastrophic when they're not:

• Pooler crash — all database connections are immediately lost; applications fail with connection refused errors
• Pool exhaustion — clients queue up waiting for a free connection; queries time out silently
• Stale connections — the pooler holds connections the database has already terminated; queries fail with "connection reset" errors
• Replication lag — if you're routing reads to a replica through the pooler, replica lag means stale reads that can be hard to diagnose

External monitoring with Vigilmon catches the most severe failure mode (the pooler is unreachable) and complements internal metrics for subtler issues like pool exhaustion.

What you'll need

• A Linux host (Ubuntu 22.04 or Debian 12 recommended)
• A running PostgreSQL 13+ instance
• Root/sudo access on the Odyssey host
• A free Vigilmon account

Step 1: Install Odyssey

Build from source (recommended for the latest version):

# Install build dependencies
apt install -y \
  git cmake build-essential libssl-dev libpam-dev \
  libpq-dev postgresql-server-dev-all

# Clone and build
git clone https://github.com/yandex/odyssey.git
cd odyssey
mkdir build && cd build
cmake ..
make -j$(nproc)
make install

Or install from the Yandex packages on Ubuntu:

apt install -y curl
curl -fsSL https://packagecloud.io/yandex/odyssey/gpgkey | apt-key add -
echo "deb https://packagecloud.io/yandex/odyssey/ubuntu/ jammy main" \
  > /etc/apt/sources.list.d/odyssey.list
apt update
apt install odyssey

Verify the installation:

odyssey --version

Step 2: Configure Odyssey

Odyssey uses a single configuration file (typically /etc/odyssey/odyssey.conf). Here's a production-ready configuration:

# /etc/odyssey/odyssey.conf

# Daemon and worker settings
daemonize yes
pid_file "/var/run/odyssey/odyssey.pid"
unix_socket_dir "/var/run/odyssey"
unix_socket_mode "0644"
locks_dir "/tmp/odyssey"
graceful_die_on_errors yes
enable_online_restart no
bindwith_reuseport yes

# Logging
log_format "%p %t %l [%i %s] (%c) %m\n"
log_to_stdout no
log_syslog yes
log_syslog_ident "odyssey"
log_syslog_facility "daemon"
log_debug no
log_config yes
log_session yes
log_query no
log_stats yes
stats_interval 60

# Listen address for client connections
listen {
    host "*"
    port 6432
    backlog 128
    nodelay yes
    keepalive 15
    keepalive_idle 75
    keepalive_interval 15
    keepalive_count 9
    tls "disable"
    compression no
}

# Worker threads (set to number of CPUs)
workers "auto"
resolvers 1

# Connection limits
client_max 0  # 0 = unlimited (limit per-route instead)

# Backend PostgreSQL server
storage "postgres_server" {
    type "remote"
    host "127.0.0.1"  # PostgreSQL host
    port 5432
}

# Connection pooling database route
database "myapp" {
    user "app_user" {
        authentication "none"  # Use "md5" or "scram-sha-256" for auth
        storage "postgres_server"
        storage_db "myapp_production"
        storage_user "app_user"
        storage_password "strong-password"
        
        # Pool mode: "session", "transaction", or "statement"
        pool "transaction"
        
        # Pool size per Odyssey worker
        pool_size 20
        pool_timeout 10000       # ms to wait for a free connection
        pool_ttl 600             # seconds before idle connections are closed
        pool_discard no
        pool_cancel yes
        pool_rollback yes
        pool_client_idle_timeout 0
        pool_idle_in_transaction_timeout 30  # Kill long-idle transactions
        
        # Server-side connection limits
        server_lifetime 3600     # Close and reopen connections every hour
        server_connect_timeout 10000
        server_login_retry 10
        
        quantiles "0.99,0.95,0.5"
    }
}

# Read-only route to a replica
database "myapp" {
    user "readonly_user" {
        authentication "none"
        storage "postgres_replica"
        storage_db "myapp_production"
        storage_user "readonly_user"
        storage_password "replica-password"
        pool "transaction"
        pool_size 30
        pool_timeout 5000
    }
}

storage "postgres_replica" {
    type "remote"
    host "192.168.1.101"  # Replica host
    port 5432
}

# Admin console (for monitoring queries)
database "odyssey" {
    user "admin" {
        authentication "none"
        role "admin"
        pool "session"
    }
}

Start and enable Odyssey:

systemctl enable odyssey
systemctl start odyssey
journalctl -u odyssey -f  # Watch for startup errors

Step 3: Verify Odyssey is accepting connections

Test that clients can connect through the pooler:

psql -h 127.0.0.1 -p 6432 -U app_user -d myapp -c "SELECT version();"

Test the admin console:

psql -h 127.0.0.1 -p 6432 -U admin -d odyssey -c "SHOW POOLS;"

Expected output:

 database | user      | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
 myapp    | app_user  |         3 |          0 |         3 |        17|        0 |          0|        0 |       0

Key columns:

• cl_active — clients currently executing queries
• cl_waiting — clients waiting for a free pool slot (should be 0 normally)
• sv_active — server connections currently in use
• sv_idle — server connections in the pool, available

Step 4: Build a health endpoint for Odyssey

Odyssey doesn't expose an HTTP health endpoint natively. Create a thin proxy that queries the admin console and returns HTTP:

#!/usr/bin/env python3
# odyssey_health.py — HTTP health endpoint for Odyssey

from http.server import HTTPServer, BaseHTTPRequestHandler
import psycopg2
import json
import os

ODYSSEY_HOST = os.getenv("ODYSSEY_HOST", "127.0.0.1")
ODYSSEY_PORT = int(os.getenv("ODYSSEY_PORT", "6432"))
HEALTH_PORT = int(os.getenv("HEALTH_PORT", "9090"))

def check_odyssey():
    try:
        conn = psycopg2.connect(
            host=ODYSSEY_HOST,
            port=ODYSSEY_PORT,
            user="admin",
            dbname="odyssey",
            connect_timeout=5
        )
        cur = conn.cursor()
        cur.execute("SHOW POOLS;")
        pools = cur.fetchall()
        cur.execute("SHOW STATS;")
        stats = cur.fetchall()
        conn.close()
        
        # Check for pool exhaustion
        total_waiting = 0
        pool_data = []
        for row in pools:
            # Row: database, user, cl_active, cl_waiting, sv_active, sv_idle, ...
            total_waiting += row[3] if len(row) > 3 else 0
            pool_data.append({
                "database": row[0],
                "user": row[1],
                "clients_active": row[2],
                "clients_waiting": row[3] if len(row) > 3 else 0,
                "servers_active": row[4] if len(row) > 4 else 0,
                "servers_idle": row[5] if len(row) > 5 else 0,
            })
        
        healthy = total_waiting == 0
        return {
            "status": "ok" if healthy else "degraded",
            "total_clients_waiting": total_waiting,
            "pools": pool_data
        }, 200 if healthy else 503
        
    except Exception as e:
        return {"status": "error", "detail": str(e)}, 503

class HealthHandler(BaseHTTPRequestHandler):
    def do_GET(self):
        if self.path == "/health":
            data, code = check_odyssey()
            body = json.dumps(data).encode()
            self.send_response(code)
            self.send_header("Content-Type", "application/json")
            self.send_header("Content-Length", len(body))
            self.end_headers()
            self.wfile.write(body)
        else:
            self.send_response(404)
            self.end_headers()
    
    def log_message(self, format, *args):
        pass  # Silence request logs

if __name__ == "__main__":
    server = HTTPServer(("0.0.0.0", HEALTH_PORT), HealthHandler)
    print(f"Odyssey health server running on :{HEALTH_PORT}")
    server.serve_forever()

Install psycopg2 and run as a service:

pip install psycopg2-binary

# /etc/systemd/system/odyssey-health.service
[Unit]
Description=Odyssey Health Proxy
After=odyssey.service

[Service]
ExecStart=/usr/bin/python3 /opt/odyssey_health.py
Environment=ODYSSEY_HOST=127.0.0.1
Environment=ODYSSEY_PORT=6432
Environment=HEALTH_PORT=9090
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

systemctl enable odyssey-health
systemctl start odyssey-health

Test:

curl http://localhost:9090/health
# {"status": "ok", "total_clients_waiting": 0, "pools": [...]}

Step 5: Set up Vigilmon monitors

With the health endpoint running, configure Vigilmon to watch Odyssey from outside the server.

Monitor 1: Odyssey health endpoint

1. Log in to vigilmon.online → Monitors → New Monitor
2. Type: HTTP / HTTPS
3. URL: https://your-db-server.example.com:9090/health
4. Expected status code: 200
5. Optional body check: response contains "status": "ok"
6. Check interval: 1 minute
7. Save

Monitor 2: TCP port check on Odyssey's client port

Type: TCP Port
Host: your-db-server.example.com
Port: 6432
Interval: 1 minute

This catches network-level issues (firewall rules, service crash) that the HTTP check might not surface if the health proxy itself is down.

Monitor 3: Database via Odyssey (application-level check)

If your application exposes a /db-health endpoint that tests database connectivity through Odyssey:

# Application route that validates end-to-end connectivity
@app.get("/db-health")
def db_health():
    try:
        db.execute("SELECT 1")
        return {"status": "ok", "pooler": "odyssey"}
    except Exception as e:
        raise HTTPException(503, detail=str(e))

Type: HTTP / HTTPS
URL: https://your-app.example.com/db-health
Expected status: 200

This end-to-end check catches failures in the entire chain: application → Odyssey → PostgreSQL.

Step 6: Configure alert channels

1. In Vigilmon, go to Alert Channels → Add Channel
2. Add at least two channels:
   • Email for immediate notification to the DBA and on-call engineer
   • Slack/webhook for the engineering team channel
3. Assign all three monitors to both alert channels
4. Set notification delay to 2 minutes for the health endpoint (avoids false positives from transient health proxy restarts) and immediate for the TCP check

Step 7: Monitor Odyssey performance metrics

Odyssey's admin console provides rich performance data. Set up a cron job to export metrics to a time-series database:

#!/usr/bin/env python3
# odyssey_metrics.py — export Odyssey stats to InfluxDB

import psycopg2
import requests
import time

ODYSSEY_DSN = "host=127.0.0.1 port=6432 user=admin dbname=odyssey"
INFLUX_URL = "http://localhost:8086/write?db=odyssey"

def collect_and_push():
    conn = psycopg2.connect(ODYSSEY_DSN)
    cur = conn.cursor()
    
    # Pool statistics
    cur.execute("SHOW POOLS;")
    pools = cur.fetchall()
    
    lines = []
    for row in pools:
        db, user = row[0], row[1]
        cl_active = row[2]
        cl_waiting = row[3] if len(row) > 3 else 0
        sv_active = row[4] if len(row) > 4 else 0
        sv_idle = row[5] if len(row) > 5 else 0
        tag = f'database="{db}",user="{user}"'
        ts = int(time.time() * 1e9)
        lines.append(
            f"odyssey_pool,{tag} cl_active={cl_active}i,cl_waiting={cl_waiting}i,"
            f"sv_active={sv_active}i,sv_idle={sv_idle}i {ts}"
        )
    
    conn.close()
    requests.post(INFLUX_URL, data="\n".join(lines))
    print(f"Pushed {len(lines)} pool metrics")

while True:
    try:
        collect_and_push()
    except Exception as e:
        print(f"Error: {e}")
    time.sleep(30)

Key metrics to alert on in Grafana:

| Metric | Alert threshold | Meaning | |--------|----------------|---------| | cl_waiting | > 0 for > 30s | Pool exhaustion — clients queuing | | sv_active / pool_size | > 90% | Pool nearly full | | Health endpoint status | != 200 | Odyssey or health proxy down | | TCP check | Failed | Odyssey port unreachable |

Step 8: Create a public status page for database-dependent services

If your application is customer-facing, a public status page reassures users during outages:

1. Status Pages → New Status Page
2. Add your application's primary service monitors (not the Odyssey-internal monitors)
3. Optionally add a "Database" component that maps to the /db-health check
4. Publish and share with users

Keep the Odyssey-internal monitors (TCP port 6432, health proxy) as internal-only monitors — these are operational signals for your team, not customer-facing status.

Common Odyssey operational issues

Pool exhaustion (cl_waiting > 0):

• Increase pool_size per worker, or add Odyssey workers (workers setting)
• Check for long-running transactions holding connections: SHOW CLIENTS; in the admin console
• Enable pool_idle_in_transaction_timeout to reclaim connections stuck in idle transactions

Connections not closing after client disconnect:

• Set pool_cancel yes and pool_rollback yes in the route configuration
• These ensure in-flight queries are cancelled and transactions rolled back when a client disconnects

Authentication failures:

• Odyssey supports md5, scram-sha-256, cert, and none; match the setting to your PostgreSQL pg_hba.conf
• For scram-sha-256: ensure the password_encryption parameter in postgresql.conf matches

Slow connection acquisition:

• Reduce pool_timeout to fail fast rather than queue indefinitely
• Enable connection pre-warming: set pool_reserve_prepared_statement yes for applications using prepared statements

Odyssey not starting:

• Check logs: journalctl -u odyssey -n 50
• Validate configuration: odyssey --config /etc/odyssey/odyssey.conf --check

Summary

You've deployed Odyssey as a high-performance PostgreSQL connection pooler, configured transaction-mode pooling for efficient connection reuse, built a health endpoint that surfaces pool exhaustion, and integrated with Vigilmon for external uptime monitoring. The alert channels ensure you're notified the moment Odyssey goes down or becomes degraded — before application errors start propagating to users.

Connection poolers are multipliers: they make everything faster, but they also mean a single failure point takes down everything. Monitoring Odyssey with Vigilmon ensures that failure point is watched as closely as your application and database.

Start with a free Vigilmon account and have your first Odyssey monitor running in minutes.