Skip to content

Configuration Guide

Complete guide to configuring pgbalancer with all parameters explained in detail.

Configuration File Format

pgbalancer uses standard .conf file format compatible with pgpool-II. The configuration file is typically located at /etc/pgbalancer/pgbalancer.conf.

File Structure: 

# Comments start with #
# Parameters use key = value format
# Values can be quoted or unquoted
# Boolean values: on, off, true, false, yes, no, 1, 0

# Basic connection settings
listen_addresses = '*'
port = 5432

# Backend servers
backend_hostname0 = 'localhost'
backend_port0 = 5433

Basic Connection Settings

listen_addresses

Type: String
Default: 'localhost'
Description: IP addresses to listen on

# Listen on all interfaces
listen_addresses = '*'

# Listen on specific interfaces
listen_addresses = '127.0.0.1,192.168.1.100'

# Listen on localhost only
listen_addresses = 'localhost'

port

Type: Integer
Default: 5432
Description: Port number to listen on

# Default PostgreSQL port
port = 5432

# Custom port
port = 5433

socket_dir

Type: String
Default: '/tmp'
Description: Directory for Unix domain sockets

socket_dir = '/tmp'
socket_dir = '/var/run/pgbalancer'

Backend Server Configuration

backend_hostnameN

Type: String
Description: Hostname or IP address of backend server N

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

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

# Backend 2
backend_hostname2 = 'pg-replica'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

backend_portN

Type: Integer
Description: Port number of backend server N

backend_weightN

Type: Integer
Default: 1
Description: Weight for load balancing (higher = more connections)

backend_data_directoryN

Type: String
Description: Data directory path of backend server N

backend_flagN

Type: String
Description: Flags for backend server N

Available flags: - ALLOW_TO_FAILOVER - Allow automatic failover - DISALLOW_TO_FAILOVER - Disable automatic failover - BACKEND_TYPE_PRIMARY - Mark as primary server - BACKEND_TYPE_STANDBY - Mark as standby server

Connection Pooling

num_init_children

Type: Integer
Default: 32
Description: Number of child processes to start

# Light load
num_init_children = 16

# Medium load
num_init_children = 32

# Heavy load
num_init_children = 64

max_pool

Type: Integer
Default: 4
Description: Maximum number of connections per child process

# Conservative
max_pool = 2

# Default
max_pool = 4

# Aggressive
max_pool = 8

child_life_time

Type: Integer
Default: 300
Description: Lifetime of child process in seconds

# Short-lived processes
child_life_time = 300

# Long-lived processes
child_life_time = 3600

child_max_connections

Type: Integer
Default: 0
Description: Maximum connections per child (0 = unlimited)

connection_cache

Type: Boolean
Default: on
Description: Enable connection caching

connection_cache = on
connection_cache = off

Load Balancing

load_balance_mode

Type: Boolean
Default: off
Description: Enable load balancing

load_balance_mode = on
load_balance_mode = off

ignore_leading_white_space

Type: Boolean
Default: on
Description: Ignore leading whitespace in queries

statement_level_load_balance

Type: Boolean
Default: off
Description: Enable statement-level load balancing

Health Checking

health_check_period

Type: Integer
Default: 0
Description: Health check interval in seconds (0 = disabled)

# Check every 30 seconds
health_check_period = 30

# Check every 5 minutes
health_check_period = 300

# Disable health checks
health_check_period = 0

health_check_timeout

Type: Integer
Default: 20
Description: Health check timeout in seconds

health_check_user

Type: String
Description: Username for health checks

health_check_user = 'postgres'
health_check_user = 'pgbalancer'

health_check_password

Type: String
Description: Password for health checks

health_check_password = 'postgres'
health_check_password = 'secure-password'

health_check_database

Type: String
Default: 'postgres'
Description: Database name for health checks

health_check_max_retries

Type: Integer
Default: 0
Description: Maximum retries before marking backend as down

Failover and Recovery

failover_on_backend_error

Type: Boolean
Default: off
Description: Enable automatic failover on backend errors

failover_on_backend_error = on
failover_on_backend_error = off

detach_false_primary

Type: Boolean
Default: off
Description: Detach false primary servers

recovery_user

Type: String
Description: Username for online recovery

recovery_password

Type: String
Description: Password for online recovery

recovery_1st_stage_command

Type: String
Description: First stage recovery command

recovery_2nd_stage_command

Type: String
Description: Second stage recovery command

AI Load Balancing (NEW)

ai_load_balancing

Type: Boolean
Default: off
Description: Enable AI-powered load balancing

ai_load_balancing = on
ai_load_balancing = off

ai_learning_rate

Type: Float
Default: 0.01
Range: 0.001 - 0.1
Description: Learning rate for AI algorithm

# Conservative learning
ai_learning_rate = 0.001

# Default learning
ai_learning_rate = 0.01

# Aggressive learning
ai_learning_rate = 0.1

ai_exploration_rate

Type: Float
Default: 0.1
Range: 0.01 - 0.5
Description: Exploration rate for AI algorithm

# Low exploration
ai_exploration_rate = 0.01

# Default exploration
ai_exploration_rate = 0.1

# High exploration
ai_exploration_rate = 0.5

ai_health_weight

Type: Float
Default: 0.4
Range: 0.0 - 1.0
Description: Weight for health score in AI decisions

ai_response_time_weight

Type: Float
Default: 0.3
Range: 0.0 - 1.0
Description: Weight for response time in AI decisions

ai_load_weight

Type: Float
Default: 0.3
Range: 0.0 - 1.0
Description: Weight for current load in AI decisions

Note: The three weight parameters should sum to 1.0: 

ai_health_weight = 0.4
ai_response_time_weight = 0.3
ai_load_weight = 0.3
# Total: 1.0

REST API Server (NEW)

rest_api_enabled

Type: Boolean
Default: off
Description: Enable REST API server

rest_api_enabled = on
rest_api_enabled = off

rest_api_port

Type: Integer
Default: 8080
Description: Port for REST API server

rest_api_port = 8080
rest_api_port = 9090

rest_api_jwt_secret

Type: String
Description: Secret key for JWT token generation

rest_api_jwt_secret = 'your-secure-secret-key-here'
rest_api_jwt_secret = 'production-secret-key-change-this'

rest_api_jwt_expiry

Type: Integer
Default: 3600
Description: JWT token expiry time in seconds

# 1 hour
rest_api_jwt_expiry = 3600

# 24 hours
rest_api_jwt_expiry = 86400

# 1 week
rest_api_jwt_expiry = 604800

rest_api_jwt_enabled

Type: Boolean
Default: off
Description: Enable JWT authentication for REST API

rest_api_jwt_enabled = on
rest_api_jwt_enabled = off

MQTT Event Publishing (NEW)

mqtt_enabled

Type: Boolean
Default: off
Description: Enable MQTT event publishing

mqtt_enabled = on
mqtt_enabled = off

mqtt_broker

Type: String
Default: 'localhost'
Description: MQTT broker hostname or IP address

mqtt_broker = 'localhost'
mqtt_broker = 'mqtt.example.com'
mqtt_broker = '192.168.1.100'

mqtt_port

Type: Integer
Default: 1883
Description: MQTT broker port

# Standard MQTT
mqtt_port = 1883

# MQTT over TLS
mqtt_port = 8883

# Custom port
mqtt_port = 1884

mqtt_client_id

Type: String
Default: 'pgbalancer'
Description: MQTT client identifier

mqtt_client_id = 'pgbalancer'
mqtt_client_id = 'pgbalancer-prod'
mqtt_client_id = 'pgbalancer-cluster-1'

mqtt_topic_prefix

Type: String
Default: 'pgbalancer'
Description: Prefix for all MQTT topics

mqtt_topic_prefix = 'pgbalancer'
mqtt_topic_prefix = 'pgbalancer/prod'
mqtt_topic_prefix = 'pgbalancer/cluster1'

mqtt_username

Type: String
Description: MQTT broker username

mqtt_username = 'pgbalancer'
mqtt_username = 'mqtt-user'

mqtt_password

Type: String
Description: MQTT broker password

mqtt_password = 'secure-password'
mqtt_password = 'mqtt-password'

mqtt_tls

Type: Boolean
Default: off
Description: Enable TLS for MQTT connections

mqtt_tls = on
mqtt_tls = off

mqtt_qos

Type: Integer
Default: 0
Range: 0 - 2
Description: MQTT Quality of Service level

# At most once
mqtt_qos = 0

# At least once
mqtt_qos = 1

# Exactly once
mqtt_qos = 2

Watchdog Configuration

use_watchdog

Type: Boolean
Default: off
Description: Enable watchdog for high availability

use_watchdog = on
use_watchdog = off

wd_hostname

Type: String
Description: Hostname for watchdog

wd_port

Type: Integer
Default: 9000
Description: Port for watchdog

wd_authkey

Type: String
Description: Authentication key for watchdog

SSL/TLS Configuration

ssl

Type: Boolean
Default: off
Description: Enable SSL connections

ssl_cert

Type: String
Description: SSL certificate file

ssl_key

Type: String
Description: SSL private key file

ssl_ca_cert

Type: String
Description: SSL CA certificate file

Authentication Configuration

authentication_timeout

Type: Integer
Default: 60
Description: Authentication timeout in seconds

enable_pool_hba

Type: Boolean
Default: off
Description: Enable pool_hba.conf for authentication

pool_passwd

Type: String
Description: Password file for authentication

Performance Tuning

log_connections

Type: Boolean
Default: off
Description: Log connection attempts

log_hostname

Type: Boolean
Default: off
Description: Log hostnames instead of IP addresses

log_statement

Type: Boolean
Default: off
Description: Log SQL statements

log_per_node_statement

Type: Boolean
Default: off
Description: Log statements per node

log_standby_delay

Type: String
Description: Log standby delay information

Configuration Examples

Basic Single Backend

listen_addresses = '*'
port = 5432

backend_hostname0 = 'localhost'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

num_init_children = 32
max_pool = 4
child_life_time = 300
connection_cache = on

load_balance_mode = on

health_check_period = 30
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'

High Availability with AI

listen_addresses = '*'
port = 5432

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

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

# Standby 2
backend_hostname2 = 'pg-standby2'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

num_init_children = 64
max_pool = 8
child_life_time = 600
connection_cache = on

load_balance_mode = on
failover_on_backend_error = on

health_check_period = 30
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'
health_check_max_retries = 3

# AI Load Balancing
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
rest_api_enabled = on
rest_api_port = 8080
rest_api_jwt_secret = 'production-secret-key'
rest_api_jwt_expiry = 3600
rest_api_jwt_enabled = on

# MQTT Events
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'
mqtt_qos = 1

# Watchdog
use_watchdog = on
wd_hostname = 'pgbalancer-node1'
wd_port = 9000
wd_authkey = 'watchdog-secret-key'

# SSL
ssl = on
ssl_cert = '/etc/ssl/certs/pgbalancer.crt'
ssl_key = '/etc/ssl/private/pgbalancer.key'
ssl_ca_cert = '/etc/ssl/certs/ca.crt'

# Logging
log_connections = on
log_hostname = on
log_statement = on

Configuration Validation

Test configuration: 

pgbalancer -f /etc/pgbalancer/pgbalancer.conf -t

Reload configuration: 

bctl reload
# or
curl -X POST http://localhost:8080/api/v1/control/reload

Next Steps