PgPool-II for PostgreSQL: Installation, Configuration, and Connection Pooling

# Add PGDG APT repository if not already present
sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg
echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

sudo apt update

# Install PgPool-II 4.5 with PostgreSQL 16 extensions
sudo apt install -y pgpool2 pgpool2-dev postgresql-16-pgpool2

# Verify installation
pgpool --version
# pgpool-II version 4.5.x (akamagari)

# RHEL 9 / Rocky Linux 9
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql

sudo dnf install -y pgpool-II-pg16 pgpool-II-pg16-extensions

# Start and enable
sudo systemctl enable pgpool
sudo systemctl start pgpool

# Install build dependencies (Debian/Ubuntu)
sudo apt install -y build-essential libpq-dev libssl-dev libpam-dev

# Download and extract
wget https://www.pgpool.net/download.php?f=pgpool-II-4.5.3.tar.gz -O pgpool-II-4.5.3.tar.gz
tar xzf pgpool-II-4.5.3.tar.gz
cd pgpool-II-4.5.3

# Configure with PostgreSQL prefix and SSL support
./configure \
  --prefix=/usr/local/pgpool \
  --with-pgsql=/usr/pgsql-16 \
  --with-openssl

make -j$(nproc)
sudo make install

# Install pgpool_recovery extension into PostgreSQL
cd src/sql/pgpool-recovery
make
sudo make install

# ─── Network ────────────────────────────────────────────────────────────────
listen_addresses = '*'          # Or a specific IP: '10.0.1.5'
port = 5432                     # PgPool-II listener port (match what apps expect)
socket_dir = '/var/run/pgpool'  # Unix socket directory

# ─── Process Model ──────────────────────────────────────────────────────────
# num_init_children: number of pre-forked child processes = max concurrent clients
# Each child handles exactly ONE client connection at a time.
# This is your hard ceiling on client connections.
num_init_children = 200

# max_pool: cached server connections per child process, per backend
# A child with max_pool=4 can reuse up to 4 server connections (one per user/db combo)
# Total server connections ≤ num_init_children × max_pool
# That total MUST be less than PostgreSQL max_connections on each backend
max_pool = 4

# child_life_time: recycle child processes after this many idle seconds (0 = never)
child_life_time = 300

# child_max_connections: recycle child after serving this many connections (0 = never)
# Prevents memory growth from long-lived children
child_max_connections = 1000

# connection_life_time: close cached server connections idle longer than this (seconds)
connection_life_time = 600

# client_idle_limit: disconnect clients idle longer than this (seconds, 0 = disabled)
client_idle_limit = 0

# Authentication method for client connections to PgPool-II
# Options: trust, reject, md5, scram-sha-256, cert, pam
enable_pool_hba = on
pool_hba_filename = '/etc/pgpool2/pool_hba.conf'

# PgPool-II's internal password file for backend authentication
# Format mirrors PostgreSQL's pg_hba.conf entries
pool_passwd = '/etc/pgpool2/pool_passwd'

# Allow PgPool-II to authenticate to backends without storing passwords locally.
# PgPool-II forwards client credentials to the backend (requires backend pg_hba.conf
# to trust the PgPool-II host, or use SCRAM passthrough).
allow_clear_text_frontend_auth = off  # Keep off unless required for legacy clients

# backend_clustering_mode controls the overall clustering behavior:
#   streaming_replication — for PostgreSQL streaming replication clusters (most common)
#   logical_replication   — for logical replication setups
#   slony                 — for Slony-I replication
#   raw                   — pass-through with no pooling logic
#   native_replication    — PgPool-II manages replication itself (legacy, avoid)
backend_clustering_mode = 'streaming_replication'

# connection_cache: enable server connection caching (pooling)
# Set to off only for debugging — always on in production
connection_cache = on

# ─── Backend 0: Primary ──────────────────────────────────────────────────────
backend_hostname0 = '10.0.1.10'
backend_port0 = 5432
backend_weight0 = 1             # Weight for load balancing (writes always go to primary)
backend_data_directory0 = '/var/lib/postgresql/16/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pgpool_primary'

# ─── Backend 1: Standby Replica 1 ────────────────────────────────────────────
backend_hostname1 = '10.0.1.11'
backend_port1 = 5432
backend_weight1 = 1             # Equal weight with backend 2 for read distribution
backend_data_directory1 = '/var/lib/postgresql/16/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pgpool_standby1'

# ─── Backend 2: Standby Replica 2 (higher-spec server, gets more read traffic) ──
backend_hostname2 = '10.0.1.12'
backend_port2 = 5432
backend_weight2 = 2             # This replica gets 2x more read traffic than backend 1
backend_data_directory2 = '/var/lib/postgresql/16/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'pgpool_standby2'

# Script executed when a backend node fails. PgPool-II passes substitution variables:
#   %d = failed node ID
#   %h = failed node hostname
#   %p = failed node port
#   %D = failed node data directory
#   %m = new primary node ID (after promotion)
#   %H = new primary hostname
#   %M = old primary node ID

failover_command = '/etc/pgpool2/scripts/failover.sh %d %h %p %D %m %H %M'
follow_primary_command = '/etc/pgpool2/scripts/follow_primary.sh %d %h %p %D %m %H %P %r %R'

#!/bin/bash
# /etc/pgpool2/scripts/failover.sh
# Minimal failover script — promote a Patroni-managed standby

FAILED_NODE_ID=$1
FAILED_HOST=$2
NEW_PRIMARY_ID=$5
NEW_PRIMARY_HOST=$6

logger -t pgpool_failover "Node ${FAILED_NODE_ID} (${FAILED_HOST}) failed. Promoting node ${NEW_PRIMARY_ID} (${NEW_PRIMARY_HOST})"

# Trigger Patroni failover via REST API
curl -s -XPATCH http://${NEW_PRIMARY_HOST}:8008/switchover \
  -H "Content-Type: application/json" \
  -d '{"leader": "'"${FAILED_HOST}"'"}'

exit 0

# ─── Health Check ────────────────────────────────────────────────────────────
health_check_period = 10          # Seconds between health checks per backend
health_check_timeout = 20         # Seconds to wait for health check response
health_check_user = 'pgpool_hc'   # Dedicated low-privilege user for health checks
health_check_password = ''        # Leave empty to use pool_passwd file
health_check_database = 'postgres'

# Retry logic: how many consecutive failures before declaring backend down
health_check_max_retries = 3
health_check_retry_delay = 1      # Seconds between retries

# Per-backend health check overrides (useful for cross-AZ latency)
health_check_period0 = 10
health_check_period1 = 10
health_check_period2 = 10

-- Create the health check user on each PostgreSQL backend
-- Minimal permissions: connect only
CREATE ROLE pgpool_hc WITH LOGIN PASSWORD 'hc_secure_password' CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE postgres TO pgpool_hc;

# ─── Watchdog ────────────────────────────────────────────────────────────────
use_watchdog = on

# Virtual IP address that clients connect to
delegate_ip = '10.0.1.100'

# Network interface on which to assign the VIP
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'

# This node's watchdog listening address (the IP of this PgPool-II host)
wd_hostname = '10.0.1.20'    # Change per node
wd_port = 9000
wd_priority = 1              # Higher value = preferred as leader (set differently per node)

# ─── Peer watchdog nodes ─────────────────────────────────────────────────────
other_pgpool_hostname0 = '10.0.1.21'
other_pgpool_port0 = 5432
other_wd_port0 = 9000

# Heartbeat settings
wd_heartbeat_keepalive = 2    # Seconds between heartbeat packets
wd_heartbeat_deadtime = 30    # Seconds of missed heartbeats before peer declared dead

# ─── Load Balancing ───────────────────────────────────────────────────────────
load_balance_mode = on

# Distribute read queries across all backends by weight.
# Writes always go to the primary (backend with smallest ID in streaming_replication mode).

# Queries sent to primary even in load balance mode:
# - Any DML (INSERT/UPDATE/DELETE/MERGE)
# - DDL (CREATE/ALTER/DROP)
# - Queries inside explicit transactions
# - Temporary table queries
# - Queries using functions in black_function_list

# Allow PgPool-II to send SELECT queries to standbys even when
# there are active write connections (safe for replication lag-tolerant reads)
ignore_leading_white_space = on

# Statement-level load balancing: each SELECT is independently load-balanced
statement_level_load_balance = on

# white_function_list: functions that return stable/immutable results —
# calls to these are safe to route to standbys
white_function_list = 'currval,lastval,nextval,setval'

# black_function_list: functions that modify state or read session variables —
# calls to these always go to primary
black_function_list = 'nextval,setval,get_current_timestamp'

# black_query_pattern_list: regex patterns — matching queries always go to primary
# Useful for application code that uses custom session variables before reads
black_query_pattern_list = 'SET LOCAL.*;SELECT pg_advisory_lock.*'

# database_redirect_preference_list: per-database routing overrides
# Route all analytics DB reads to backend 2 regardless of load balancing
database_redirect_preference_list = 'analytics:standby2'

-- Connect through PgPool-II and check which backend your query runs on
SHOW pool_nodes;

-- Output:
-- node_id | hostname   | port | status | lb_weight | role    | select_cnt
-- --------+------------+------+--------+-----------+---------+-----------
-- 0       | 10.0.1.10  | 5432 | up     | 0.333     | primary | 412
-- 1       | 10.0.1.11  | 5432 | up     | 0.333     | standby | 3891
-- 2       | 10.0.1.12  | 5432 | up     | 0.333     | standby | 3944

-- Confirm current backend for this session
SELECT inet_server_addr(), pg_is_in_recovery();