How to Monitor CrateDB Cluster Health and Node Availability with Vigilmon

CrateDB is a distributed SQL database designed for time-series and IoT workloads, offering a PostgreSQL-compatible wire protocol on top of a shared-nothing cluster — but when a node leaves the cluster due to a network partition, shard reallocation stalls because the cluster is at minimum master nodes, or a bulk INSERT load drives write rejection rates above the circuit breaker threshold, queries fail with connection errors and there is no built-in outbound alerting. CrateDB exposes cluster health and node stats via a REST API, but these require active polling and give no push notifications when availability degrades.

Vigilmon gives you external visibility into CrateDB through HTTP monitors on its cluster health API, a sidecar that checks node count and shard state, and heartbeat monitors that confirm your IoT data ingestion pipeline is writing successfully. This tutorial covers both.

Why CrateDB Needs External Monitoring

CrateDB's built-in tooling (the Admin UI, sys.nodes, sys.shards, and the cluster health API) requires a human operator or internal Prometheus scraper actively polling those endpoints. External monitoring with Vigilmon adds:

• SQL endpoint availability alerting when CrateDB becomes unreachable before your IoT pipeline starts dropping writes
• Cluster health checks detecting yellow/red shard state caused by node failures or replication under-capacity
• Node count monitoring alerting when a node leaves the cluster unexpectedly
• Response time monitoring catching bulk-write-induced latency spikes before circuit breakers trip
• End-to-end ingestion heartbeats confirming your IoT data pipeline is successfully inserting and querying rows

These layers matter because CrateDB failures are often partial: a three-node cluster with one departed node continues to serve reads and writes for fully-replicated shards while failing intermittently for shards whose primary was on the departed node, making data loss non-obvious without cluster-level health monitoring.

Step 1: Probe CrateDB's Built-in Health Endpoints

CrateDB exposes a REST API for cluster health and node status:

# Check cluster health (green/yellow/red)
curl -s http://cratedb.example.com:4200/_cluster/health

# Expected response:
# {"cluster_name":"crate","status":"green","number_of_nodes":3,
#  "number_of_data_nodes":3,"active_primary_shards":...,"unassigned_shards":0}

# Check individual node status
curl -s http://cratedb.example.com:4200/_nodes/stats

# Query cluster info via SQL HTTP endpoint
curl -s -H 'Content-Type: application/json' \
     -d '{"stmt": "SELECT name, health FROM sys.cluster"}' \
     http://cratedb.example.com:4200/_sql

Point a Vigilmon monitor directly at http://cratedb.example.com:4200/_cluster/health for immediate cluster availability alerting. Configure it to check for "status":"green" in the response body.

Step 2: Build a CrateDB Health Sidecar

For comprehensive health checks — shard assignment status, node count, and write availability — build a sidecar that interprets CrateDB's cluster API.

Python Health Sidecar

# cratedb_health.py
from flask import Flask, jsonify
import requests
import os

app = Flask(__name__)

CRATEDB_URL    = os.environ.get('CRATEDB_URL',    'http://localhost:4200')
MIN_NODES      = int(os.environ.get('MIN_NODES',  '3'))
CRATEDB_USER   = os.environ.get('CRATEDB_USER',   '')
CRATEDB_PASS   = os.environ.get('CRATEDB_PASS',   '')

def auth():
    if CRATEDB_USER:
        return (CRATEDB_USER, CRATEDB_PASS)
    return None

@app.route('/health/cratedb')
def cratedb_health():
    try:
        r = requests.get(f'{CRATEDB_URL}/_cluster/health', auth=auth(), timeout=5)
        if r.status_code != 200:
            return jsonify({'status': 'down', 'error': f'http_{r.status_code}'}), 503
        health = r.json()
        cluster_status = health.get('status', 'unknown')
        if cluster_status == 'red':
            return jsonify({'status': 'down', 'cluster_status': 'red',
                            'unassigned_shards': health.get('unassigned_shards', 0)}), 503
    except Exception as e:
        return jsonify({'status': 'down', 'error': str(e)}), 503

    return jsonify({'status': 'ok' if cluster_status == 'green' else 'degraded',
                    'cluster_status': cluster_status,
                    'number_of_nodes': health.get('number_of_nodes', 0)}), 200

@app.route('/health/cratedb/nodes')
def cratedb_nodes():
    try:
        r = requests.get(f'{CRATEDB_URL}/_cluster/health', auth=auth(), timeout=5)
        r.raise_for_status()
        health = r.json()
    except Exception as e:
        return jsonify({'status': 'down', 'error': str(e)}), 503

    current_nodes = health.get('number_of_nodes', 0)
    if current_nodes < MIN_NODES:
        return jsonify({'status': 'down', 'error': f'only_{current_nodes}_of_{MIN_NODES}_nodes_available',
                        'number_of_nodes': current_nodes}), 503
    return jsonify({'status': 'ok', 'number_of_nodes': current_nodes}), 200

@app.route('/health/cratedb/shards')
def cratedb_shards():
    try:
        r = requests.get(f'{CRATEDB_URL}/_cluster/health', auth=auth(), timeout=5)
        r.raise_for_status()
        health = r.json()
    except Exception as e:
        return jsonify({'status': 'down', 'error': str(e)}), 503

    unassigned = health.get('unassigned_shards', 0)
    if unassigned > 0:
        return jsonify({'status': 'degraded', 'unassigned_shards': unassigned,
                        'cluster_status': health.get('status')}), 200
    return jsonify({'status': 'ok', 'unassigned_shards': 0,
                    'active_shards': health.get('active_shards', 0)}), 200

@app.route('/health/cratedb/write')
def cratedb_write():
    """Test a SQL INSERT to CrateDB to confirm write availability."""
    import time
    payload = {'stmt': 'SELECT 1'}
    try:
        start = time.time()
        r = requests.post(f'{CRATEDB_URL}/_sql', json=payload, auth=auth(), timeout=10)
        elapsed_ms = int((time.time() - start) * 1000)
        if r.status_code != 200:
            return jsonify({'status': 'down', 'error': f'sql_http_{r.status_code}'}), 503
    except Exception as e:
        return jsonify({'status': 'down', 'error': str(e)}), 503

    return jsonify({'status': 'ok', 'sql_latency_ms': elapsed_ms}), 200

if __name__ == '__main__':
    app.run(port=8097)

Node.js Health Sidecar

// cratedb-health.js
const express = require('express');
const axios   = require('axios');

const app = express();

const CRATEDB_URL  = process.env.CRATEDB_URL  || 'http://localhost:4200';
const MIN_NODES    = parseInt(process.env.MIN_NODES || '3', 10);
const CRATEDB_USER = process.env.CRATEDB_USER || '';
const CRATEDB_PASS = process.env.CRATEDB_PASS || '';

const authCfg = CRATEDB_USER
  ? { auth: { username: CRATEDB_USER, password: CRATEDB_PASS } }
  : {};

app.get('/health/cratedb', async (req, res) => {
  try {
    const r = await axios.get(`${CRATEDB_URL}/_cluster/health`, { timeout: 5000, ...authCfg });
    const status = r.data?.status;
    if (status === 'red') {
      return res.status(503).json({ status: 'down', cluster_status: 'red',
                                    unassigned_shards: r.data?.unassigned_shards });
    }
    return res.status(200).json({ status: status === 'green' ? 'ok' : 'degraded',
                                  cluster_status: status,
                                  number_of_nodes: r.data?.number_of_nodes });
  } catch (e) {
    return res.status(503).json({ status: 'down', error: e.message });
  }
});

app.get('/health/cratedb/nodes', async (req, res) => {
  try {
    const r = await axios.get(`${CRATEDB_URL}/_cluster/health`, { timeout: 5000, ...authCfg });
    const current = r.data?.number_of_nodes || 0;
    if (current < MIN_NODES) {
      return res.status(503).json({ status: 'down', error: `only_${current}_nodes`, number_of_nodes: current });
    }
    return res.status(200).json({ status: 'ok', number_of_nodes: current });
  } catch (e) {
    return res.status(503).json({ status: 'down', error: e.message });
  }
});

app.listen(3013, () => console.log('cratedb-health listening on 3013'));

Step 3: Configure Vigilmon HTTP Monitor for CrateDB

1. Log in to vigilmon.online and go to Monitors → New Monitor
2. Choose HTTP / HTTPS
3. Set the URL to http://cratedb.example.com:4200/_cluster/health
4. Set the check interval to 1 minute
5. Under Expected response, configure:
   • Status code: 200
   • Response body contains: "status":"green"
   • Response time threshold: 3000ms
6. Under Alert channels, assign your database on-call channel
7. Save the monitor

Add a node count monitor using the sidecar:

• URL: https://your-app.example.com/health/cratedb/nodes
• Expected: 200, body contains "status":"ok"
• Interval: 2 minutes
• Alert channel: infrastructure operations Slack channel

Add a shard health monitor:

• URL: https://your-app.example.com/health/cratedb/shards
• Expected: 200, body contains "unassigned_shards":0
• Interval: 2 minutes
• Alert channel: database operations channel

Add a SQL write path monitor:

• URL: https://your-app.example.com/health/cratedb/write
• Expected: 200
• Interval: 1 minute
• Alert channel: IoT data pipeline channel

If you run CrateDB clusters across multiple availability zones, create separate sidecar endpoints per cluster and label them cratedb-az1, cratedb-az2, etc. in Vigilmon.

Step 4: Heartbeat Monitoring for CrateDB IoT Ingestion Pipelines

Cluster health checks confirm CrateDB is configured correctly — but they don't verify that your IoT data pipeline (sensor collector → CrateDB INSERT → dashboard query) is functioning end-to-end. A cluster can be green while your ingestion client has stalled due to a misconfigured table partition or an expired authentication token.

Vigilmon heartbeat monitors detect these failures: your ingestion pipeline pings Vigilmon after successfully inserting and querying back a test row.

Set Up the Heartbeat Monitor

1. In Vigilmon, go to Monitors → New Monitor → Heartbeat
2. Set the name: cratedb-iot-ingestion
3. Set the expected interval: 5 minutes
4. Set the grace period: 15 minutes
5. Save — copy the heartbeat URL, e.g. https://vigilmon.online/heartbeat/abc123xyz

Wire It Into a CrateDB Ingestion Probe

# cratedb_ingestion_probe.py — insert a test row and ping heartbeat on success
import requests
import time
import os

CRATEDB_URL  = os.environ['CRATEDB_URL']
VIGILMON_HB  = os.environ['VIGILMON_HEARTBEAT_URL']
CRATEDB_USER = os.environ.get('CRATEDB_USER', '')
CRATEDB_PASS = os.environ.get('CRATEDB_PASS', '')

auth = (CRATEDB_USER, CRATEDB_PASS) if CRATEDB_USER else None
ts   = int(time.time() * 1000)  # milliseconds for CrateDB timestamp column

# Ensure the probe table exists
create_stmt = {
    'stmt': ('CREATE TABLE IF NOT EXISTS vigilmon_probe '
             '(id BIGINT, ts TIMESTAMP WITH TIME ZONE) '
             'WITH (number_of_replicas = 0)')
}
requests.post(f'{CRATEDB_URL}/_sql', json=create_stmt, auth=auth, timeout=10)

# Insert a probe row
insert_stmt = {'stmt': 'INSERT INTO vigilmon_probe (id, ts) VALUES (?, ?)',
               'args': [ts, ts]}
r = requests.post(f'{CRATEDB_URL}/_sql', json=insert_stmt, auth=auth, timeout=10)
if r.status_code != 200:
    raise RuntimeError(f'CrateDB INSERT failed: {r.status_code} {r.text}')

print(f'Probe row inserted (ts={ts}).')

# Refresh table so the row is immediately queryable
requests.post(f'{CRATEDB_URL}/_sql',
              json={'stmt': 'REFRESH TABLE vigilmon_probe'},
              auth=auth, timeout=10)

# Query back the row to confirm the read path
query_stmt = {'stmt': 'SELECT COUNT(*) FROM vigilmon_probe WHERE id = ?', 'args': [ts]}
q = requests.post(f'{CRATEDB_URL}/_sql', json=query_stmt, auth=auth, timeout=10)
if q.status_code != 200:
    raise RuntimeError(f'CrateDB SELECT failed: {q.status_code} {q.text}')

count = q.json()['rows'][0][0]
if count < 1:
    raise RuntimeError(f'Probe row not found after insert (count={count}).')

print(f'Probe row verified (count={count}).')

# Ping Vigilmon heartbeat
requests.get(VIGILMON_HB, timeout=10)
print('Vigilmon heartbeat sent.')

Step 5: Alert Routing for CrateDB Failures

Configure alert routing in Vigilmon:

| Monitor | Alert Channel | Priority | |---|---|---| | CrateDB /_cluster/health | Slack + PagerDuty | P1 | | Node count sidecar | Slack + PagerDuty | P1 | | Shard health sidecar | Slack | P2 | | SQL write path sidecar | Slack + PagerDuty | P1 | | Heartbeat: IoT ingestion | Slack + PagerDuty | P1 |

Set response time thresholds for early warning:

• Alert at 2000ms for the cluster health endpoint (slow health checks precede full write unavailability)
• Alert at 5000ms for the SQL write sidecar (elevated SQL latency indicates bulk-write pressure or shard rebalancing)

For production IoT applications where CrateDB stores sensor data driving real-time dashboards or automated control systems, configure tight grace periods on heartbeats: an IoT pipeline that runs every minute should alert within 5 minutes of silence, not on the next human review cycle.

Summary

CrateDB failures span SQL endpoint availability, cluster node count, shard assignment health, and end-to-end ingestion. External monitoring catches all four:

| Monitor Type | What It Covers | |---|---| | HTTP monitor on /_cluster/health | Cluster green/yellow/red status | | HTTP monitor on node count sidecar | Node departure detection | | HTTP monitor on shard sidecar | Unassigned shard detection | | HTTP monitor on write sidecar | SQL write path availability | | Heartbeat monitor on ingestion probe | End-to-end insert-and-query cycle |

Get started free at vigilmon.online — your first CrateDB availability monitor is running in under two minutes.