Skip to content

Installation Guide

Complete installation instructions for pgbalancer on various platforms.

Prerequisites

System Requirements

  • Operating System: Linux (Ubuntu 20.04+, CentOS 8+, Rocky Linux 8+), macOS 10.15+
  • PostgreSQL: Version 13, 14, 15, 16, 17, or 18
  • Memory: Minimum 512MB RAM, recommended 2GB+
  • Disk Space: 100MB for installation, additional space for logs

Build Dependencies

Ubuntu/Debian: 

sudo apt-get update
sudo apt-get install -y \
  build-essential \
  autoconf \
  automake \
  libtool \
  pkg-config \
  libpq-dev \
  libssl-dev \
  libpam0g-dev \
  libldap2-dev \
  libmemcached-dev \
  git

CentOS/RHEL/Rocky Linux: 

sudo dnf groupinstall -y "Development Tools"
sudo dnf install -y \
  autoconf \
  automake \
  libtool \
  pkgconfig \
  postgresql-devel \
  openssl-devel \
  pam-devel \
  openldap-devel \
  libmemcached-devel \
  git

macOS (Homebrew): 

brew install autoconf automake libtool pkg-config
brew install postgresql openssl pam openldap libmemcached

Installation Methods

Step 1: Clone Repository 

git clone https://github.com/pgelephant/pgbalancer.git
cd pgbalancer

Step 2: Generate Configure Script 

# Generate configure script if it doesn't exist
autoreconf -fi

Step 3: Configure Build 

# Basic configuration
./configure

# With all optional features
./configure \
  --with-openssl \
  --with-pam \
  --with-ldap \
  --with-memcached \
  --prefix=/usr/local/pgbalancer

Step 4: Build 

make

Step 5: Install 

sudo make install

Step 6: Verify Installation 

pgbalancer --version
bctl --version

Method 2: Package Installation

RPM Packages (CentOS/RHEL/Rocky): 

# Download appropriate RPM for your PostgreSQL version
wget https://github.com/pgelephant/pgbalancer/releases/download/v1.0.0/pgbalancer-1.0.0-1.el9.x86_64.rpm
sudo dnf install pgbalancer-1.0.0-1.el9.x86_64.rpm

DEB Packages (Ubuntu/Debian): 

# Download appropriate DEB for your PostgreSQL version
wget https://github.com/pgelephant/pgbalancer/releases/download/v1.0.0/postgresql-17-pgbalancer_1.0.0-1_amd64.deb
sudo apt install ./postgresql-17-pgbalancer_1.0.0-1_amd64.deb

PostgreSQL Configuration

Step 1: Configure PostgreSQL

Edit postgresql.conf: 

# Enable connection pooling
max_connections = 200

# Enable streaming replication (for HA setups)
wal_level = replica
max_wal_senders = 10
hot_standby = on

# Enable SSL (optional)
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Step 2: Configure pg_hba.conf 

# Allow pgbalancer connections
host    all             all             127.0.0.1/32            md5
host    all             all             10.0.0.0/8             md5

# Allow replication (for HA setups)
host    replication     all             127.0.0.1/32            md5
host    replication     all             10.0.0.0/8             md5

Step 3: Restart PostgreSQL 

# Linux (systemd)
sudo systemctl restart postgresql

# macOS
pg_ctl restart -D /usr/local/var/postgres

pgbalancer Configuration

Step 1: Create Configuration Directory 

sudo mkdir -p /etc/pgbalancer
sudo chown postgres:postgres /etc/pgbalancer

Step 2: Create Configuration File 

sudo cp /usr/local/share/doc/pgbalancer/pgbalancer.conf.sample /etc/pgbalancer/pgbalancer.conf
sudo chown postgres:postgres /etc/pgbalancer/pgbalancer.conf

Step 3: Basic Configuration

Edit /etc/pgbalancer/pgbalancer.conf: 

# Basic connection settings
listen_addresses = '*'
port = 5432
socket_dir = '/tmp'

# Backend PostgreSQL servers
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
child_max_connections = 0
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 = 'your-secret-key-here'
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'

Service Configuration

Step 1: Create Systemd Service (Linux)

Create /etc/systemd/system/pgbalancer.service: 

[Unit]
Description=pgbalancer - PostgreSQL Connection Pooler
After=postgresql.service
Requires=postgresql.service

[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/local/bin/pgbalancer -f /etc/pgbalancer/pgbalancer.conf -D
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -TERM $MAINPID
PIDFile=/var/run/pgbalancer/pgbalancer.pid
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

Step 2: Enable and Start Service 

sudo systemctl daemon-reload
sudo systemctl enable pgbalancer
sudo systemctl start pgbalancer
sudo systemctl status pgbalancer

Step 3: Verify Installation 

# Check pgbalancer status
bctl status

# Check REST API
curl http://localhost:8080/api/v1/status

# Check MQTT events (if mosquitto is installed)
mosquitto_sub -h localhost -t 'pgbalancer/#' -v

Troubleshooting Installation

Common Issues:

  1. Configure script not found 

    autoreconf -fi

  2. Missing PostgreSQL headers 

    # Ubuntu/Debian
sudo apt-get install libpq-dev

# CentOS/RHEL
sudo dnf install postgresql-devel

  3. Permission denied errors 

    sudo chown -R postgres:postgres /etc/pgbalancer
sudo chmod 600 /etc/pgbalancer/pgbalancer.conf

  4. Port already in use 

    # Check what's using port 5432
sudo netstat -tlnp | grep 5432

# Change pgbalancer port in configuration
port = 5433

  5. Backend connection failed 

    # Test PostgreSQL connection
psql -h localhost -p 5433 -U postgres

# Check PostgreSQL is running
sudo systemctl status postgresql

Log Files: - pgbalancer logs: /var/log/pgbalancer/ - System logs: journalctl -u pgbalancer - PostgreSQL logs: /var/log/postgresql/

Next Steps