HA #2: Cài đặt PgBouncer — Connection Pooler cho PostgreSQL

PostgreSQL xử lý mỗi connection bằng một process riêng. Ở mức mặc định, max_connections = 100. Khi traffic tăng, application mở thêm connection — và database bắt đầu chậm dần, rồi từ chối kết nối mới. PgBouncer là lớp đứng giữa, gom tất cả connection lại và tái sử dụng một pool nhỏ thực sự kết nối vào database.

Tại sao connection pool lại quan trọng?

Mỗi PostgreSQL backend process tiêu tốn khoảng 5–10MB RAM chỉ để tồn tại — chưa kể query. Với 200 connection đồng thời, bạn đã dùng 1–2GB chỉ cho overhead.

Hơn nữa, việc mở một TCP connection đến PostgreSQL không rẻ — handshake, authentication, cấp phát process. Nếu application mở-đóng connection liên tục (như PHP-FPM hay serverless functions), chi phí này cộng dồn rõ rệt.

PgBouncer giải quyết cả hai: giữ một pool nhỏ connection luôn mở sẵn với PostgreSQL, và cho phép hàng trăm client “dùng chung” pool đó.

---

Các chế độ pooling

PgBouncer có 3 chế độ, khác nhau ở thời điểm connection được trả lại pool:

Mode	Trả connection về pool khi	Phù hợp
session	Client ngắt kết nối	Application cần session-level state
transaction	Kết thúc transaction	Hầu hết web apps
statement	Kết thúc mỗi câu SQL	Không hỗ trợ multi-statement transaction

transaction mode là lựa chọn phổ biến nhất — hiệu quả cao, tương thích tốt với hầu hết ORM và framework.

Giới hạn transaction mode: Không dùng được SET session variables, prepared statements theo kiểu PostgreSQL protocol (nhưng PREPARE trong SQL vẫn OK), hoặc advisory locks kéo dài qua nhiều transaction.

---

Cài đặt

Môi trường

PgBouncer thường chạy trên cùng server với application, hoặc trên một node riêng đứng trước cluster PostgreSQL.

Bài này: PgBouncer chạy trên 192.168.1.10 (cùng server với Master), lắng nghe port 5432, forward đến PostgreSQL trên port 5433.

Để không xung đột port, ta sẽ đổi PostgreSQL sang port 5433 hoặc để PgBouncer chạy trên một port khác. Ở đây dùng cách đơn giản hơn: PgBouncer dùng port 6432, PostgreSQL giữ 5432.

Cài PgBouncer

sudo apt update
sudo apt install -y pgbouncer

---

Cấu hình

/etc/pgbouncer/pgbouncer.ini

sudo nano /etc/pgbouncer/pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 600
client_idle_timeout = 0

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

pidfile = /var/run/postgresql/pgbouncer.pid
logfile = /var/log/postgresql/pgbouncer.log

Các param quan trọng:

• max_client_conn — số client tối đa được kết nối vào PgBouncer
• default_pool_size — số connection thực giữ với PostgreSQL, per database+user pair
• reserve_pool_size — connection dự phòng khi pool chính đầy
• server_idle_timeout — đóng connection đến PG nếu idle quá lâu

/etc/pgbouncer/userlist.txt

File này chứa danh sách user được phép kết nối qua PgBouncer, kèm password hash.

Lấy password hash của user từ PostgreSQL:

sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'myappuser';"

Copy hash đó vào userlist.txt:

"myappuser" "SCRAM-SHA-256$..."

Phân quyền file:

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

---

Khởi động và kiểm tra

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

Test kết nối qua PgBouncer

psql -h 127.0.0.1 -p 6432 -U myappuser -d myapp

Nếu connect được và query bình thường — pool đang hoạt động.

Xem trạng thái pool

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

Output của SHOW POOLS sẽ cho thấy số connection đang active, idle, và waiting.

---

Cập nhật connection string

Sau khi PgBouncer chạy, application chỉ cần đổi connection string từ:

postgresql://myappuser:[email protected]:5432/myapp

thành:

postgresql://myappuser:[email protected]:6432/myapp

Không cần thay đổi gì trong application code.

---

Reload config không cần restart

sudo systemctl reload pgbouncer
# hoặc
kill -HUP $(cat /var/run/postgresql/pgbouncer.pid)

---

Những thứ hay gặp

auth_type không khớp — PgBouncer auth_type phải tương thích với pg_hba.conf trên PostgreSQL. Nếu PG dùng scram-sha-256, PgBouncer cũng phải dùng scram-sha-256.

prepared statement bị lỗi với transaction mode — Một số ORM (Sequelize, ActiveRecord ở một số version) dùng prepared statements theo PostgreSQL wire protocol. Cần bật server_reset_query = DISCARD ALL hoặc disable server-side prepared statements ở phía ORM.

Pool size quá nhỏ — Nếu waiting trong SHOW POOLS tăng liên tục, tăng default_pool_size. Nhưng nhớ rằng default_pool_size × số database × số user = số connection thực đến PostgreSQL.

---

Bước tiếp theo

PgBouncer giải quyết bài toán connection overhead. Nhưng nó không tự biết đâu là Master, đâu là Slave — application vẫn phải kết nối đúng node cho đúng loại query.

Bài tiếp theo sẽ cài PgPool-II — một layer thông minh hơn, tự động route query đến đúng node, cân bằng tải read, và xử lý failover khi Master chết.