Trao đổi nhu cầu ↗
Công nghệ & Lập trình 5 phút đọc 01/05/2026

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

Thiết lập PgPool-II để routing query PostgreSQL, load balancing read query và tự động failover khi master bị down.

T
Trịnh Khắc TùngSolution Architect & Founder VCoderLog
Kiến trúc sư kiểm chứng
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

bash✓ Đã kiểm chứng cú pháp
sudo apt update
sudo apt install -y pgpool2

Kiểm tra version:

bash✓ Đã kiểm chứng cú pháp
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

bash✓ Đã kiểm chứng cú pháp
sudo nano /etc/pgpool2/pgpool.conf
ini✓ Đã kiểm chứng cú pháp
# --- 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:

sql✓ Đã kiểm chứng cú pháp
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:

bash✓ Đã kiểm chứng cú pháp
sudo nano /etc/pgpool2/pool_hba.conf
bash✓ Đã kiểm chứng cú pháp
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:

bash✓ Đã kiểm chứng cú pháp
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:

bash✓ Đã kiểm chứng cú pháp
sudo nano /etc/pgpool2/failover.sh
bash✓ Đã kiểm chứng cú pháp
#!/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}"
bash✓ Đã kiểm chứng cú pháp
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:

bash✓ Đã kiểm chứng cú pháp
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:

bash✓ Đã kiểm chứng cú pháp
sudo -u postgres ssh-copy-id [email protected]
sudo -u postgres ssh-copy-id [email protected]

---

Khởi động PgPool

bash✓ Đã kiểm chứng cú pháp
sudo systemctl enable pgpool2
sudo systemctl start pgpool2
sudo systemctl status pgpool2

---

Xác minh hoạt động

Xem trạng thái nodes

bash✓ Đã kiểm chứng cú pháp
psql -h 127.0.0.1 -p 5432 -U myappuser -d myapp -c "SHOW pool_nodes;"

Output mong đợi:

bash✓ Đã kiểm chứng cú pháp
 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:

bash✓ Đã kiểm chứng cú pháp
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:

bash✓ Đã kiểm chứng cú pháp
# 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:

bash✓ Đã kiểm chứng cú pháp
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:

sql✓ Đã kiểm chứng cú pháp
/*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 *