PostgreSQL主备集群读负载均衡最优方案及教程咨询

Hey there! Based on your setup—PostgreSQL 9.6.17 master-slave hot standby on Ubuntu 16.04.12 64-bit, with the core goal of read load balancing to reduce single-node strain and boost read speeds—I’m going to break down the best options and give you a step-by-step guide for the most fitting tool.

Comparing Your Options: Pgpool-II vs. SQL Relay vs. Alternatives #

1. Pgpool-II (Top Recommendation) #

This is hands down the best match for your scenario. Built specifically for PostgreSQL, it’s designed from the ground up to handle load balancing, connection pooling, and failover for PostgreSQL clusters.

• Why it fits: It natively recognizes master (writes) and slave (reads) nodes, automatically routing write traffic to the master and distributing read traffic across slaves. No custom code or hacks needed—it works out of the box with your existing hot standby setup.
• Key perks:
  • Built-in connection pooling to cut down on overhead between your app and databases
  • Supports multiple load balancing algorithms (round-robin, weighted round-robin, etc.)
  • Automatic node health checks—if a slave goes down, it’s removed from the pool instantly, and re-added when it’s back up
  • Transparent to your app: your app only connects to Pgpool-II, no code changes required
• Minor caveat: It’s PostgreSQL-specific, so if you ever plan to add other databases to your stack, you’d need a different tool. But for your current setup, this is a feature, not a bug—it’s optimized for exactly what you’re running.

2. SQL Relay #

SQL Relay is a generic database connection pool and load balancer that supports PostgreSQL (and many other databases).

• Why it might fit: If you have long-term plans to integrate other databases (like MySQL or Oracle), its cross-database support is a plus.
• Why it’s not ideal for you right now: It doesn’t have native support for PostgreSQL’s master-slave hot standby setup. You’d need to write custom scripts or configurations to make it recognize master/slave roles and handle failover—adding unnecessary complexity when Pgpool-II does this automatically.

3. Alternatives (e.g., HAProxy) #

HAProxy is a general-purpose load balancer that can route read traffic to your slave nodes, but it’s not purpose-built for databases.

• Why it might fit: If you already have HAProxy expertise in your team and want to consolidate load balancing tools.
• Why it’s not ideal: It only handles L4/L7 traffic routing—no database-specific connection pooling or native master/slave state detection. You’d need to add custom health check scripts to monitor PostgreSQL node status, which adds more moving parts to your setup.

Final Verdict: Go with Pgpool-II #

It’s the most straightforward, low-maintenance solution that perfectly aligns with your PostgreSQL master-slave cluster and read load balancing goals.

Step-by-Step Pgpool-II Setup for Ubuntu 16.04.12 + PostgreSQL 9.6.17 #

1. Install Pgpool-II #

Ubuntu 16.04’s default repo has an older Pgpool-II version, so we’ll use the official Pgpool repo to get a compatible release (3.6, which works great with PostgreSQL 9.6):

# Add the Pgpool-II official repository
echo "deb http://www.pgpool.net/apt/xenial pgpool-II-3.6 main" | sudo tee /etc/apt/sources.list.d/pgpool.list
# Import the GPG key to verify packages
wget -q -O - http://www.pgpool.net/apt/RPM-GPG-KEY-PGPOOL | sudo apt-key add -

# Update packages and install Pgpool-II
sudo apt-get update
sudo apt-get install pgpool2 pgpool2-extensions

2. Configure Pgpool-II Core Settings #

Edit the main config file /etc/pgpool2/pgpool.conf with these key settings (replace placeholder values with your actual server details):

# Listen on all interfaces, port 9999 (standard Pgpool port)
listen_addresses = '*'
port = 9999

# Configure master node (node 0)
backend_hostname0 = 'your-master-server-ip'
backend_port0 = 5432
backend_weight0 = 0  # Set to 0 if you want NO reads going to master; set to 1 if you want to split reads
backend_data_directory0 = '/var/lib/postgresql/9.6/main'
backend_flag0 = 'ALLOW_TO_WRITE'

# Configure slave node (node 1)
backend_hostname1 = 'your-slave-server-ip'
backend_port1 = 5432
backend_weight1 = 1  # Adjust weight to prioritize this node (higher = more traffic)
backend_data_directory1 = '/var/lib/postgresql/9.6/main'
backend_flag1 = 'DISALLOW_TO_WRITE'

# Enable read load balancing
load_balance_mode = on

# Connection pool settings (tune based on your app's connection needs)
num_init_children = 32
max_pool = 4
child_life_time = 300
client_idle_limit = 0

# Health check settings (auto-detect down nodes)
health_check_period = 5  # Check node status every 5 seconds
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'your-postgres-user-password'
# Failover script (handles promoting slave to master if master goes down)
failover_command = '/usr/lib/pgpool-II/failover.sh %d %h %p %D %m %M %H %P %r %R %N %S'

3. Configure Client Authentication #

Edit /etc/pgpool2/pool_hba.conf to control which clients can connect to Pgpool-II. This works just like PostgreSQL’s pg_hba.conf:

# Allow all clients to connect via MD5 password authentication
host    all         all         0.0.0.0/0             md5

4. Set Up PCP (Pgpool Control) Authentication #

PCP is used to manage Pgpool-II via command line. Generate the password file:

# Create encrypted password entry for the postgres user
sudo pg_md5 -p -u postgres > /etc/pgpool2/pcp.conf
# Enter your postgres user password when prompted
# Lock down the file permissions
sudo chmod 600 /etc/pgpool2/pcp.conf

5. Start and Verify Pgpool-II #

# Start the Pgpool-II service
sudo systemctl start pgpool2
# Set it to start on boot
sudo systemctl enable pgpool2

# Test connecting to Pgpool-II (replace placeholders)
psql -h your-pgpool-server-ip -p 9999 -U postgres -d your-database-name

6. Validate Read Load Balancing #

To confirm reads are being distributed, check the PostgreSQL logs on your master and slave nodes while running read queries:

# On master node
tail -f /var/log/postgresql/postgresql-9.6-main.log

# On slave node
tail -f /var/log/postgresql/postgresql-9.6-main.log

If you set backend_weight0 = 0, all read queries should show up in the slave’s log. If you set it to 1, you’ll see queries alternating between master and slave.

内容的提问来源于stack exchange，提问作者Eng.Bassel