DocumentationpgBalancer Documentation

Configuration Reference

⚙️ Configuration File Format

pgBalancer uses .conf file format (same as pgpool-II and PostgreSQL). Configuration file location:/etc/pgbalancer/pgbalancer.conf

Connection Settings

Basic Connection Configuration

#------------------------------------------------------------------------------
# CONNECTION SETTINGS
#------------------------------------------------------------------------------

# Listen address for client connections
listen_addresses = '*'
# * = all interfaces, localhost = local only, or specific IP

# Listen port
port = 9999

# Upstream Postgres primary/backup nodes
backend_hostname0 = 'postgres-primary'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = 'postgres-standby'
backend_port1 = 5432
backend_weight1 = 1
backend_status1 = 'standby'

# Authentication to PostgreSQL
backend_user = 'pgbalancer'
backend_password = 'StrongPassword123!'

Backend Server Configuration

Defining PostgreSQL Backends

#------------------------------------------------------------------------------
# BACKEND SERVERS (0-indexed)
#------------------------------------------------------------------------------

# Backend 0 - Primary
backend_hostname0 = 'db1.example.com'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/16/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'primary_db'

# Backend 1 - Replica
backend_hostname1 = 'db2.example.com'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/16/replica1'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'replica1_db'

# Backend 2 - Replica
backend_hostname2 = 'db3.example.com'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/16/replica2'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'replica2_db'

# Add more backends as needed (up to 128 backends supported)

Backend Parameters:

  • backend_hostname: Server hostname or IP address
  • backend_port: PostgreSQL port number
  • backend_weight: Load balancing weight (higher = more queries)
  • backend_data_directory: PostgreSQL data directory path
  • backend_flag: ALLOW_TO_FAILOVER or ALWAYS_PRIMARY or DISALLOW_TO_FAILOVER
  • backend_application_name: Identifier for monitoring

AI Load Balancing Configuration

Machine Learning Routing Settings

#------------------------------------------------------------------------------
# AI LOAD BALANCING (Machine Learning Routing)
#------------------------------------------------------------------------------

# Enable AI-based load balancing
enable_ai_load_balance = on

# Learning rate for weight updates (0.0 to 1.0)
# Higher = faster adaptation, Lower = more stable
ai_learning_rate = 0.01

# Exploration rate for trying different backends (0.0 to 1.0)
# Higher = more exploration, Lower = more exploitation
ai_exploration_rate = 0.1

# Weight update interval in seconds
# How often AI updates backend weights based on performance
ai_weight_update_interval = 60

# Minimum sample size before AI routing kicks in
ai_min_sample_size = 100

# Performance metric to optimize
# Options: response_time, throughput, error_rate
ai_optimization_metric = 'response_time'

🎯 AI Tuning Guidelines

  • • Conservative: learning_rate=0.005, exploration_rate=0.05 (stable production)
  • • Balanced: learning_rate=0.01, exploration_rate=0.1 (recommended)
  • • Aggressive: learning_rate=0.05, exploration_rate=0.2 (fast adaptation)
  • • Update interval: 60s for production, 10s for testing

REST API Configuration

HTTP/JSON API Settings

#------------------------------------------------------------------------------
# REST API CONFIGURATION
#------------------------------------------------------------------------------

# Enable REST API server
enable_rest_api = on

# API server port (default: 8080)
rest_api_port = 8080

# Enable JWT authentication
rest_api_auth = on

# Secret key for JWT signing (change this!)
rest_api_secret = 'your-very-long-random-secret-key-here'

# JWT token expiration time in seconds (default: 3600 = 1 hour)
rest_api_token_expiry = 3600

# CORS settings for web applications
rest_api_cors_enabled = on
rest_api_cors_origins = '*'

# API request logging
rest_api_log_requests = on

Available API Endpoints (17 total):

  • GET /api/health - Health check
  • GET /api/backends - Backend status with AI health scores
  • GET /api/stats - Pool statistics
  • GET /api/processes - Active processes
  • GET /api/config - Configuration info
  • POST /api/login - JWT authentication
  • POST /api/node/{id}/attach - Attach backend node
  • POST /api/node/{id}/detach - Detach backend node
  • ... and 9 more endpoints for cluster management

MQTT Event Streaming Configuration

Real-Time Event Publishing

#------------------------------------------------------------------------------
# MQTT EVENT STREAMING
#------------------------------------------------------------------------------

# Enable MQTT event publishing
enable_mqtt = on

# MQTT broker connection
mqtt_broker_address = 'localhost'
mqtt_broker_port = 1883
mqtt_client_id = 'pgbalancer_01'

# MQTT authentication (if broker requires)
mqtt_username = ''
mqtt_password = ''

# Topic prefix for all events
mqtt_topic_prefix = 'pgbalancer'

# QoS level (0, 1, or 2)
mqtt_qos = 1

# Events to publish
mqtt_publish_node_status = on      # Node up/down events
mqtt_publish_failover = on         # Failover events
mqtt_publish_health_check = on     # Health check results
mqtt_publish_connection = off      # Connection events (verbose)

# Publish interval for status updates (seconds)
mqtt_publish_interval = 30

📡 Event Topics

  • • pgbalancer/node/status - Node status changes
  • • pgbalancer/failover - Failover events
  • • pgbalancer/health - Health check results
  • • pgbalancer/stats - Periodic statistics

Load Balancing Configuration

Load Balancing Modes and Settings

#------------------------------------------------------------------------------
# LOAD BALANCING
#------------------------------------------------------------------------------

# Enable load balancing mode
load_balance_mode = on

# Ignore leading whitespace in queries
ignore_leading_white_space = on

# Read query routing to replicas
black_function_list = ''
white_function_list = ''

# Database and table white/black lists
black_query_pattern_list = ''
white_query_pattern_list = ''

# Statement level load balancing
statement_level_load_balance = off

# Primary routing query patterns
primary_routing_query_pattern_list = 'COPY;LOCK'

# Disable load balancing on specific queries
disable_load_balance_on_write = 'transaction'

# Database redirect preferences
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''

Health Check Configuration

Backend Health Monitoring

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------

# Health check period in seconds (0 = disable)
health_check_period = 10

# Health check timeout in seconds
health_check_timeout = 5

# Health check max retries before marking backend down
health_check_max_retries = 3

# Health check retry delay in seconds
health_check_retry_delay = 1

# Database user for health checks
health_check_user = 'postgres'
health_check_password = ''

# Database for health checks
health_check_database = 'postgres'

# Connect timeout for health check (milliseconds)
connect_timeout = 10000

# Log health check errors
log_health_check = off

Health Check Best Practices

  • • Set health_check_period between 5-30 seconds
  • • Use dedicated health check user with minimal permissions
  • • Set timeout shorter than period to avoid overlap
  • • Enable log_health_check for debugging only

Recommended Settings

  • • Production: period=10, timeout=5, retries=3
  • • Testing: period=5, timeout=2, retries=1
  • • High latency: period=30, timeout=10, retries=5

Failover and Watchdog Configuration

High Availability Settings

#------------------------------------------------------------------------------
# FAILOVER
#------------------------------------------------------------------------------

# Failover on backend error
fail_over_on_backend_error = on

# Search primary node timeout (seconds)
search_primary_node_timeout = 300

# Failover command (executed when backend fails)
failover_command = '/usr/local/bin/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'

# Failback command (executed when failed backend comes back)
failback_command = ''

# Follow primary command
follow_primary_command = ''

# Detach false primary
detach_false_primary = off

#------------------------------------------------------------------------------
# WATCHDOG (Cluster Manager)
#------------------------------------------------------------------------------

# Enable watchdog for virtual IP failover
use_watchdog = on

# Watchdog communication settings
wd_hostname = 'localhost'
wd_port = 9000
wd_authkey = ''

# Virtual IP settings
delegate_ip = '192.168.1.100'
if_up_cmd = '/usr/bin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/ip addr del $_IP_$/24 dev eth0'
arping_cmd = '/usr/bin/arping -U $_IP_$ -w 1 -I eth0'

# Watchdog monitoring
wd_monitoring_interfaces_list = ''
wd_interval = 10
wd_priority = 1

Logging Configuration

Logging and Debug Settings

#------------------------------------------------------------------------------
# LOGGING
#------------------------------------------------------------------------------

# Where to send logs
log_destination = 'stderr'
# Options: stderr, syslog

# Log directory (if using file logging)
logdir = '/var/log/pgbalancer'

# Log filename pattern
log_filename = 'pgbalancer-%Y-%m-%d_%H%M%S.log'

# Log line prefix
log_line_prefix = '%t: pid %p: '

# Rotation settings
log_rotation_age = 1d
log_rotation_size = 10MB

# What to log
log_connections = off
log_hostname = off
log_statement = off
log_per_node_statement = off
log_client_messages = off

# Syslog settings
syslog_facility = 'LOCAL0'
syslog_ident = 'pgbalancer'

# Debug level (0 = off, higher = more verbose)
log_min_messages = warning
client_min_messages = notice
log_error_verbosity = default

Authentication Configuration

Client and PCP Authentication

#------------------------------------------------------------------------------
# AUTHENTICATION
#------------------------------------------------------------------------------

# Enable pool_hba for client authentication
enable_pool_hba = on
pool_passwd = 'pool_passwd'

# Authentication method
authentication_timeout = 60

# SSL/TLS settings
ssl = off
ssl_cert = '/etc/pgbalancer/server.crt'
ssl_key = '/etc/pgbalancer/server.key'
ssl_ca_cert = '/etc/pgbalancer/ca.crt'
ssl_ca_cert_dir = ''

# PCP authentication file
pcp_socket_dir = '/var/run/pgbalancer'

Setting up pool_passwd file:

# Format: username:md5password
# Generate MD5 password hash
pg_md5 your_password

# Add to /etc/pgbalancer/pool_passwd
postgres:md5d8578edf8458ce06fbc5bb76a58c5ca4
appuser:md5a1b2c3d4e5f6789012345678901234

Advanced Features

Query Cache

# Memory query cache
memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_total_size = 64MB
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 400KB

Replication Mode

# Replication mode settings
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''

# Streaming replication
sr_check_period = 10
sr_check_user = 'postgres'
delay_threshold = 10000000

In-Memory Query Cache

# Enable in-memory query result caching
memory_cache_enabled = on

# Cache storage method
memqcache_method = 'shmem'
# Options: shmem (shared memory) or memcached

# Cache size limit
memqcache_total_size = 64MB

# Maximum number of cached entries
memqcache_max_num_cache = 1000000

# Cache expiration time (0 = no expiration)
memqcache_expire = 0

# Auto invalidation on table updates
memqcache_auto_cache_invalidation = on

Complete Configuration Example

Production-Ready Configuration

#==============================================================================
# pgBalancer Production Configuration
# Modern AI-Powered PostgreSQL Connection Pooler
#==============================================================================

#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 5432
socket_dir = '/var/run/pgbalancer'
pcp_listen_addresses = 'localhost'
pcp_port = 9898
num_init_children = 64
max_pool = 4
child_life_time = 300

#------------------------------------------------------------------------------
# BACKENDS (3-node cluster: 1 primary + 2 replicas)
#------------------------------------------------------------------------------
backend_hostname0 = 'db-primary.prod.local'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY'

backend_hostname1 = 'db-replica1.prod.local'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'db-replica2.prod.local'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILOVER'

#------------------------------------------------------------------------------
# AI LOAD BALANCING
#------------------------------------------------------------------------------
enable_ai_load_balance = on
ai_learning_rate = 0.01
ai_exploration_rate = 0.1
ai_weight_update_interval = 60
ai_optimization_metric = 'response_time'

#------------------------------------------------------------------------------
# REST API (Port 8080)
#------------------------------------------------------------------------------
enable_rest_api = on
rest_api_port = 8080
rest_api_auth = on
rest_api_secret = 'change-this-to-a-long-random-string'
rest_api_token_expiry = 3600

#------------------------------------------------------------------------------
# MQTT EVENTS
#------------------------------------------------------------------------------
enable_mqtt = on
mqtt_broker_address = 'mqtt.prod.local'
mqtt_broker_port = 1883
mqtt_topic_prefix = 'pgbalancer/prod'
mqtt_publish_node_status = on
mqtt_publish_failover = on

#------------------------------------------------------------------------------
# LOAD BALANCING
#------------------------------------------------------------------------------
load_balance_mode = on
ignore_leading_white_space = on
statement_level_load_balance = off

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------
health_check_period = 10
health_check_timeout = 5
health_check_max_retries = 3
health_check_user = 'health_check'
health_check_database = 'postgres'

#------------------------------------------------------------------------------
# FAILOVER
#------------------------------------------------------------------------------
fail_over_on_backend_error = on
failover_command = '/etc/pgbalancer/scripts/failover.sh %d %h %p'
search_primary_node_timeout = 300

#------------------------------------------------------------------------------
# AUTHENTICATION
#------------------------------------------------------------------------------
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60

#------------------------------------------------------------------------------
# LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'
logdir = '/var/log/pgbalancer'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_connections = on
log_min_messages = info

Configuration Validation

Validate and Reload Configuration

# Test configuration file syntax (dry-run)
pgbalancer -f /etc/pgbalancer/pgbalancer.conf -n

# Check for configuration errors
pgbalancer -f /etc/pgbalancer/pgbalancer.conf -n -d 2>&1 | grep -i error

# Reload configuration without restart
pgbalancer reload

# Or using bctl
bctl reload

# Or via systemd
sudo systemctl reload pgbalancer

Configuration Parameters Reference

CategoryKey ParametersDefaultDescription
Connectionsnum_init_children32Pre-forked child processes
AI Routingenable_ai_load_balanceoffEnable machine learning routing
AI Routingai_learning_rate0.01Learning rate for weight updates
REST APIenable_rest_apioffEnable HTTP/JSON API server
REST APIrest_api_port8080API server port
MQTTenable_mqttoffEnable MQTT event publishing
Health Checkhealth_check_period0Health check interval (seconds)
Load Balancingload_balance_modeoffDistribute SELECT queries to replicas
PreviousGetting Started
NextConnection Pooling Setup