Skip to content

README.md

Latest commit

 

History

History
213 lines (169 loc) · 8.05 KB

README.md

File metadata and controls

213 lines (169 loc) · 8.05 KB

Pgpool-II PostgreSQL Read Load Balancing Setup

This project demonstrates read load balancing for PostgreSQL using Pgpool-II and Docker Compose. It distributes SELECT queries across multiple streaming replicas while directing all write operations to the primary server.

Note: This setup focuses on read scaling and connection pooling. It does not include automatic primary failover (promoting a replica to primary).

🏗️ Architecture

                    ┌─────────────────┐
                    │   Application   │
                    └────────┬────────┘
                             │
                             ▼
                    ┌─────────────────┐
                    │    Pgpool-II    │ ← Port 5433 (Load Balancer)
                    │  (Connection    │
                    │   Pooler +      │
                    │   Read LB)      │
                    └────────┬────────┘
                             │
         ┌───────────────────┼───────────────────┐
         │                   │                   │
         ▼                   ▼                   ▼
┌─────────────────┐ ┌──────────────────┐ ┌──────────────────┐
│   pg-primary    │ │   pg-replica1    │ │   pg-replica2    │
│  (Write Only)   │ │   (Read Only)    │ │   (Read Only)    │
│   weight = 0    │ │   weight = 1     │ │   weight = 1     │
└─────────────────┘ └──────────────────┘ └──────────────────┘

✨ Features

  • ⚖️ Read Load Balancing: SELECT queries distributed across replicas (primary has weight 0)
  • 🔄 Streaming Replication: WAL-based replication using physical replication slots
  • 🏥 Health Check: Backend health monitoring every 1 second
  • 🕒 Replication Lag Detection: Replicas with lag > 1 second are excluded from load balancing
  • 🔁 Auto Failback: Recovered replicas automatically re-added to the read pool
  • 🏊 Connection Pooling: 64 child processes, 2 connections per backend each (max 384 connections)
  • 🐳 Custom Dockerfile: Full control over Pgpool configuration

🐳 Container Images

Container Image Description
pg-primary postgres:17 Official PostgreSQL 17 image (primary)
pg-replica1 postgres:17 Official PostgreSQL 17 image (streaming replica)
pg-replica2 postgres:17 Official PostgreSQL 17 image (streaming replica)
pgpool-read Custom build postgres:17-alpine + pgpool (ARM64/AMD64 compatible)

Why Custom Pgpool Image?

  • Official pgpool/pgpool: Only AMD64, not ARM64 compatible (fails on M1/M2 Macs)
  • Bitnami bitnami/pgpool: Discontinued from Docker Hub (Aug 2025)
  • Our solution: Custom Dockerfile based on postgres:15-alpine with pgpool package

Pgpool-Read Dockerfile

FROM postgres:17-alpine
RUN apk add --no-cache pgpool pgpool-openrc bash
COPY pgpool.conf pool_hba.conf pool_passwd pcp.conf /etc/pgpool-II/
EXPOSE 5432 9898

This provides:

  • Full control over pgpool.conf
  • ARM64 & AMD64 compatibility
  • No dependency on third-party images

📋 Prerequisites

  • Docker & Docker Compose
  • psql client (for test scripts)

🚀 Getting Started

1. Start the Cluster

docker-compose up -d

This starts:

  • pg-primary - Primary PostgreSQL (Port 5432)
  • pg-replica1 - Streaming replica
  • pg-replica2 - Streaming replica
  • pgpool-read - Custom-built Pgpool-II load balancer (Port 5433)

2. Check Cluster Status

PGPASSWORD=secret psql -h localhost -p 5433 -U postgres -d appdb -c "SHOW POOL_NODES"

📡 Connection

# Via Pgpool (recommended)
PGPASSWORD=secret psql -h localhost -p 5433 -U postgres -d appdb

# Direct to Primary
PGPASSWORD=secret psql -h localhost -p 5432 -U postgres -d appdb

🧪 Test & Monitor Scripts

Script Description
./monitor_load_balancing.sh Real-time query distribution monitor
./monitor_connections.sh Backend connection stats (idle/active)
./check_lag.sh Detailed replication lag info
./check_replication.sh Full replication test
./check_pgpool_nodes.sh Pgpool node status
./test_failover_timing.sh Failover detection timing
./test_artificial_lag.sh Test lag detection on one replica
./test_replica_down.sh Test replica down scenario
./test_both_replicas_down.sh Test all replicas down
./test_both_replicas_lag.sh Test lag on all replicas

⚙️ Configuration

Pgpool Settings (pgpool/pgpool.conf)

Parameter Value Description
num_init_children 64 Max concurrent client connections
max_pool 2 Connections per backend per child
connection_life_time 600 Connection lifetime (10 min)
health_check_period 1 Health check interval (seconds)
health_check_max_retries 1 Retries before marking down
sr_check_period 1 Replication lag check interval
delay_threshold_by_time 1000 Max allowed lag (1 second = 1000ms)
prefer_lower_delay_standby on Prefer replica with lower lag
auto_failback on Auto re-add recovered replicas

Backend Weights

Primary has weight 0 = read queries only go to replicas:

backend_weight0 = 0  # pg-primary (write only)
backend_weight1 = 1  # pg-replica1 (read)
backend_weight2 = 1  # pg-replica2 (read)

📁 Project Structure

pgpool/
├── docker-compose.yml          # Container orchestration
├── README.md
│
├── pg-primary/                 # Primary PostgreSQL
│   ├── init.sql                # Replication user, slots, test table
│   ├── 01_hba.sh               # pg_hba.conf settings
│   └── postgresql.conf
│
├── replica/                    # Replica configuration
│   ├── init_replica.sh         # Base backup and replication setup
│   ├── postgresql.conf
│   └── recovery.conf
│
├── pgpool/                     # Custom Pgpool-Read build
│   ├── Dockerfile              # Custom ARM64-compatible image
│   ├── entrypoint.sh           # Startup script
│   ├── pgpool.conf             # Full configuration
│   ├── pool_hba.conf           # Client authentication
│   ├── pool_passwd             # User passwords
│   └── pcp.conf                # PCP admin config
│
└── scripts (root)
    ├── monitor_load_balancing.sh   # Real-time LB monitor
    ├── monitor_connections.sh      # Connection stats monitor
    ├── check_lag.sh                # Replication lag check
    ├── check_replication.sh        # Full replication test
    ├── check_pgpool_nodes.sh       # Pool nodes status
    ├── test_failover_timing.sh     # Failover timing test
    ├── test_artificial_lag.sh      # Lag detection test
    ├── test_replica_down.sh        # Replica down test
    ├── test_both_replicas_down.sh  # All replicas down test
    └── test_both_replicas_lag.sh   # All replicas lag test

🔧 Troubleshooting

View Logs

docker-compose logs -f pgpool-read
docker-compose logs -f pg-primary

Reset Everything

docker-compose down -v
docker-compose up -d --build

Check Replication Slots

docker exec pg-primary psql -U postgres -c "SELECT * FROM pg_replication_slots;"

📚 References

📄 License

This project is intended for educational and development purposes.