Skip to content

Quick Start Guide

Get pgbalancer running in 5 minutes with AI load balancing, REST API, and MQTT events.

Prerequisites

  • PostgreSQL 13+ running
  • pgbalancer installed (see Installation Guide)
  • Basic understanding of PostgreSQL

Step 1: Basic Configuration

Create minimal configuration: 

sudo mkdir -p /etc/pgbalancer
sudo tee /etc/pgbalancer/pgbalancer.conf > /dev/null <<INNEREOF
# Basic settings
listen_addresses = '*'
port = 5432
socket_dir = '/tmp'

# Single backend (adjust hostname/port as needed)
backend_hostname0 = 'localhost'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

# Connection pooling
num_init_children = 32
max_pool = 4
child_life_time = 300
connection_cache = on

# Load balancing
load_balance_mode = on

# Health checking
health_check_period = 30
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'

# AI Load Balancing (NEW)
ai_load_balancing = on
ai_learning_rate = 0.01
ai_exploration_rate = 0.1
ai_health_weight = 0.4
ai_response_time_weight = 0.3
ai_load_weight = 0.3

# REST API Server (NEW)
rest_api_enabled = on
rest_api_port = 8080
rest_api_jwt_secret = 'quickstart-secret-key'
rest_api_jwt_expiry = 3600

# MQTT Event Publishing (NEW)
mqtt_enabled = on
mqtt_broker = 'localhost'
mqtt_port = 1883
mqtt_client_id = 'pgbalancer'
mqtt_topic_prefix = 'pgbalancer'
INNEREOF

Step 2: Start pgbalancer

Start in foreground (for testing): 

pgbalancer -f /etc/pgbalancer/pgbalancer.conf -n

Start as daemon: 

pgbalancer -f /etc/pgbalancer/pgbalancer.conf -D

Step 3: Verify Installation

Check pgbalancer status: 

bctl status

Expected output: 

Server: pgbalancer
Status: running
Version: 1.0.0
Uptime: 30 seconds
Connections: 0
Nodes: 1
Processes: 32

Test PostgreSQL connection through pgbalancer: 

psql -h localhost -p 5432 -U postgres -d postgres

Step 4: Use REST API

Get server status: 

curl http://localhost:8080/api/v1/status

Expected response: 

{
  "status": "running",
  "uptime": 60,
  "connections": 0,
  "nodes": 1,
  "healthy_nodes": 1,
  "ai_enabled": true,
  "rest_api_enabled": true,
  "mqtt_enabled": true
}

List backend nodes: 

curl http://localhost:8080/api/v1/nodes

Get AI load balancing statistics: 

curl http://localhost:8080/api/v1/ai-stats

Step 5: Monitor with MQTT

Install Mosquitto client (optional): 

# Ubuntu/Debian
sudo apt-get install mosquitto-clients

# CentOS/RHEL
sudo dnf install mosquitto-clients

# macOS
brew install mosquitto

Subscribe to all events: 

mosquitto_sub -h localhost -t 'pgbalancer/#' -v

Subscribe to specific events: 

# Node status changes
mosquitto_sub -h localhost -t 'pgbalancer/nodes/status' -v

# Health check results
mosquitto_sub -h localhost -t 'pgbalancer/health' -v

# AI load balancing decisions
mosquitto_sub -h localhost -t 'pgbalancer/ai/decisions' -v

Step 6: Test AI Load Balancing

Create test workload: 

# Connect to PostgreSQL through pgbalancer
psql -h localhost -p 5432 -U postgres -d postgres


-- Create test table
CREATE TABLE test_data (id SERIAL PRIMARY KEY, data TEXT);

-- Insert test data
INSERT INTO test_data (data) 
SELECT 'test_data_' || generate_series(1, 1000);

-- Run queries to generate load
SELECT COUNT(*) FROM test_data WHERE id % 2 = 0;
SELECT AVG(id) FROM test_data;
SELECT * FROM test_data WHERE id BETWEEN 100 AND 200;

Monitor AI decisions: 

# Check AI statistics
curl http://localhost:8080/api/v1/ai-stats | jq '.'

# Watch MQTT events
mosquitto_sub -h localhost -t 'pgbalancer/ai/#' -v

Step 7: Advanced Configuration

Add multiple backends: 

# Backend 0 - Primary
backend_hostname0 = 'pg-primary'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

# Backend 1 - Standby
backend_hostname1 = 'pg-standby'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

Enable JWT authentication: 

# REST API with JWT
rest_api_enabled = on
rest_api_port = 8080
rest_api_jwt_secret = 'your-secure-secret-key'
rest_api_jwt_expiry = 3600
rest_api_jwt_enabled = on

Configure MQTT with authentication: 

# MQTT with authentication
mqtt_enabled = on
mqtt_broker = 'mqtt.example.com'
mqtt_port = 8883
mqtt_username = 'pgbalancer'
mqtt_password = 'secure-password'
mqtt_tls = on
mqtt_client_id = 'pgbalancer-prod'
mqtt_topic_prefix = 'pgbalancer/prod'

Step 8: Production Considerations

Security: - Change default passwords - Use SSL/TLS for connections - Enable JWT authentication - Configure firewall rules

Performance: - Tune num_init_children based on load - Adjust max_pool for connection reuse - Configure appropriate child_life_time - Monitor AI learning parameters

Monitoring: - Set up Prometheus metrics - Configure Grafana dashboards - Enable MQTT event monitoring - Set up alerting rules

High Availability: - Configure watchdog - Set up multiple pgbalancer instances - Enable automatic failover - Configure online recovery

Next Steps