HA #3: Cài đặt PgPool-II — Connection Clustering và Auto Failover

Hai bài trước đã có replication chạy và connection pool hoạt động. Nhưng application vẫn phải biết: “Đây là write query, gửi đến Master. Đây là read query, gửi đến Slave.” PgPool-II lấy trách nhiệm đó ra khỏi tay application — nó nhìn vào SQL, tự quyết định node nào xử lý, và tự động switch sang Slave khi Master đột ngột biến mất.

PgPool-II làm được gì?

Tính năng	Mô tả
Connection pooling	Tương tự PgBouncer nhưng ít linh hoạt hơn
Query routing	SELECT → Slave, INSERT/UPDATE → Master
Load balancing	Phân phối SELECT đều giữa các Slave
Online recovery	Rebuild Slave bị lỗi tự động
Watchdog	Phát hiện node chết, promote Slave

Khi nào dùng PgPool thay vì PgBouncer? Dùng PgPool khi bạn muốn transparent load balancing và auto-failover mà không thay đổi application. Dùng PgBouncer khi chỉ cần connection pooling đơn giản, hiệu năng cao, ít overhead. Nhiều setup production dùng cả hai: PgPool phía ngoài để route, PgBouncer phía trong để pool.

---

Môi trường

Node	IP	Role
pgpool	192.168.1.9	PgPool-II proxy
master	192.168.1.10	PostgreSQL Master
slave1	192.168.1.11	PostgreSQL Slave 1

---

Cài đặt PgPool-II

sudo apt update
sudo apt install -y pgpool2

Kiểm tra version:

pgpool --version

---

Cấu hình

File cấu hình chính: /etc/pgpool2/pgpool.conf

Cấu hình kết nối backend

sudo nano /etc/pgpool2/pgpool.conf

# --- Backend nodes ---
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/15/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/15/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# --- Listen ---
listen_addresses = '*'
port = 5432

# --- Connection pooling ---
num_init_children = 32
max_pool = 4
connection_cache = on

# --- Load balancing ---
load_balance_mode = on
ignore_leading_white_space = on

# --- Health check ---
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool_health'
health_check_password = 'health_pass'
health_check_database = 'postgres'
health_check_max_retries = 3

# --- Failover ---
failover_command = '/etc/pgpool2/failover.sh %d %h %p %D %m %H %M %P %r %R'
failover_on_backend_error = on

# --- Replication mode ---
replication_mode = off
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'replicator'
sr_check_password = 'strong_password_here'

Tạo user health check trên PostgreSQL

Trên Master, tạo user riêng cho PgPool health check:

CREATE ROLE pgpool_health WITH LOGIN PASSWORD 'health_pass';
GRANT CONNECT ON DATABASE postgres TO pgpool_health;

File pool_hba.conf

Cho phép connection từ application đến PgPool:

sudo nano /etc/pgpool2/pool_hba.conf

host    all     all     0.0.0.0/0     scram-sha-256

File pool_passwd

PgPool cần password file riêng. Tạo bằng công cụ pg_md5:

pg_md5 --md5auth --username=myappuser -p
# Nhập password khi được hỏi

File sẽ được ghi vào /etc/pgpool2/pool_passwd.

---

Script failover

Tạo script được gọi khi PgPool phát hiện Master chết:

sudo nano /etc/pgpool2/failover.sh

#!/bin/bash
# $1: node id của node bị lỗi
# $7: hostname của node sẽ được promote (new master)

FAILED_NODE_HOST=$2
NEW_MASTER_HOST=$7
NEW_MASTER_PORT=$8
NEW_MASTER_DATA=$9

/usr/bin/ssh -T -i /var/lib/postgresql/.ssh/id_rsa \
  postgres@${NEW_MASTER_HOST} \
  "pg_ctl promote -D ${NEW_MASTER_DATA}"

sudo chmod +x /etc/pgpool2/failover.sh

Script này SSH vào Slave và chạy pg_ctl promote. Cần setup SSH key từ pgpool server đến các PostgreSQL node trước.

Setup SSH key

Trên pgpool server:

sudo -u postgres ssh-keygen -t rsa -b 4096 -N "" -f /var/lib/postgresql/.ssh/id_rsa

Copy public key đến từng PostgreSQL node:

sudo -u postgres ssh-copy-id [email protected]
sudo -u postgres ssh-copy-id [email protected]

---

Khởi động PgPool

sudo systemctl enable pgpool2
sudo systemctl start pgpool2
sudo systemctl status pgpool2

---

Xác minh hoạt động

Xem trạng thái nodes

psql -h 127.0.0.1 -p 5432 -U myappuser -d myapp -c "SHOW pool_nodes;"

Output mong đợi:

 node_id |    hostname    | port | status | lb_weight | role
---------+----------------+------+--------+-----------+--------
 0       | 192.168.1.10   | 5432 | up     | 0.500000  | primary
 1       | 192.168.1.11   | 5432 | up     | 0.500000  | standby

Test load balancing

Chạy nhiều SELECT và xem PgPool phân phối:

for i in {1..10}; do
  psql -h 127.0.0.1 -p 5432 -U myappuser -d myapp \
    -c "SELECT inet_server_addr();" -t
done

Kết quả nên xen kẽ giữa 192.168.1.10 và 192.168.1.11.

Test failover

Dừng PostgreSQL trên Master:

# Trên 192.168.1.10
sudo systemctl stop postgresql

Sau vài giây (bằng health_check_period), PgPool sẽ phát hiện và gọi failover.sh. Kiểm tra lại:

psql -h 127.0.0.1 -p 5432 -U myappuser -d myapp -c "SHOW pool_nodes;"

Slave sẽ được promote, hiện role = primary. Application tiếp tục chạy mà không cần thay đổi connection string.

---

Những thứ hay gặp

health_check_user không có quyền CONNECT — PgPool báo node down dù PostgreSQL đang chạy. Kiểm tra pg_hba.conf và grant quyền connect.

Failover script không chạy được do SSH — Kiểm tra SSH key, permission của .ssh/ và authorized_keys. File authorized_keys phải chmod 600, thư mục .ssh/ phải chmod 700.

Query bị route sai node — PgPool parse SQL để quyết định routing. Một số query phức tạp có thể bị nhầm. Dùng /*NO LOAD BALANCE*/ hint để ép query về Master:

/*NO LOAD BALANCE*/ SELECT * FROM sensitive_table;

Split-brain sau failover — Nếu Master cũ phục hồi, đừng để nó tự join lại cluster. Cần rebuild nó thành Slave mới (chạy pg_basebackup từ Master hiện tại).

---

Tóm tắt série HA PostgreSQL

Bài	Công cụ	Giải quyết
#1	Master-Slave Replication	Phân tải read, có bản dự phòng
#2	PgBouncer	Tái sử dụng connection, giảm overhead
#3	PgPool-II	Auto routing, load balancing, failover

Ba lớp này cộng lại tạo thành một PostgreSQL cluster có khả năng chịu tải cao, tự phục hồi khi node chính gặp sự cố, và trong suốt với application.

---

Có câu hỏi hoặc gặp lỗi khác — Facebook: V-CODER LOG