PgBouncer: ทำ Connection Pooling ให้ PostgreSQL บน Ubuntu

ติดตั้ง PgBouncer บน Ubuntu ทำ Connection Pooling ให้ PostgreSQL เลือก pool_mode ให้เหมาะกับแอป ตั้งค่า SCRAM เปลี่ยน connection string และเฝ้าดูด้วย SHOW POOLS

โดย ทีมงาน THAI DATA CLOUD20 ต.ค. 2569 09:50 น. อัปเดต 21 ต.ค. 2569 อ่าน 11 นาที

แชร์Facebook X LINE LinkedIn

PgBouncer — PgBouncer: ทำ Connection Pooling ให้ PostgreSQL บน Ubuntu — PgBouncer: ทำ Connection Pooling ให้ PostgreSQL บน Ubuntu

คำตอบสั้น ๆ: ติดตั้ง PgBouncer บน Ubuntu ทำ Connection Pooling ให้ PostgreSQL เลือก pool_mode ให้เหมาะกับแอป ตั้งค่า SCRAM เปลี่ยน connection string และเฝ้าดูด้วย SHOW POOLS

คู่มือนี้อธิบาย PgBouncer ทีละขั้นตอน พร้อมคำสั่งที่ใช้ได้จริงและปัญหาที่พบบ่อย

PgBouncer คือ Connection Pooler ขนาดเล็กที่คั่นกลางระหว่างแอปกับ PostgreSQL ทุกการเชื่อมต่อของ PostgreSQL คือโปรเซสหนึ่งตัวที่ใช้หน่วยความจำของตัวเอง เมื่อแอป PHP, Node.js หรือ worker หลายร้อยตัวเปิดการเชื่อมต่อพร้อมกัน เซิร์ฟเวอร์จะเสียทรัพยากรไปกับการเปิดปิดโปรเซสมากกว่าการรัน Query จริง และสุดท้ายผู้ใช้จะเจอข้อความ FATAL: sorry, too many clients already

PgBouncer แก้ปัญหานี้ด้วยการรับการเชื่อมต่อจากแอปได้นับพัน แล้วแบ่งใช้การเชื่อมต่อจริงไปยังฐานข้อมูลเพียงไม่กี่สิบเส้น เมื่อทำตามคู่มือนี้ครบ คุณจะได้ตัวพูลที่ยืนยันตัวตนแบบ SCRAM, ตั้ง pool_mode เหมาะกับแอป และมีวิธีเฝ้าดูว่าพูลเพียงพอหรือไม่

แผนภาพแอปหลายตัวเชื่อมต่อ PgBouncer พอร์ต 6432 ที่รับได้ 1,000 ไคลเอนต์ แล้วส่งต่อไป PostgreSQL ผ่านพูลเพียง 25 การเชื่อมต่อ — PgBouncer รับการเชื่อมต่อทั้งหมดจากแอป แล้วแบ่งใช้การเชื่อมต่อจริงที่มีจำนวนจำกัด

สิ่งที่ต้องเตรียม

Cloud Server ที่ใช้ Ubuntu 22.04 หรือ 24.04 และติดตั้ง PostgreSQL 14 ขึ้นไปแล้ว ดู คู่มือติดตั้ง PostgreSQL บน Ubuntu
บัญชีที่ใช้ sudo ได้ และผู้ใช้กับฐานข้อมูลของแอปที่สร้างไว้แล้ว ในตัวอย่างคือ appuser และ appdb
รหัสผ่านของผู้ใช้เก็บแบบ scram-sha-256 ซึ่งเป็นค่าเริ่มต้นตั้งแต่ PostgreSQL 14
รู้ว่าแอปใช้ฟีเจอร์ระดับ session หรือไม่ เช่น SET, LISTEN/NOTIFY, advisory lock หรือตารางชั่วคราว เพราะมีผลต่อการเลือก pool_mode
ช่วงเวลาที่เปลี่ยน connection string ของแอปได้ และแผนย้อนกลับไปพอร์ต 5432 เดิม

ขั้นตอนที่ 1: ติดตั้ง PgBouncer

sudo apt update
sudo apt install pgbouncer

แพ็กเกจของ Ubuntu วางไฟล์คอนฟิกไว้ในโฟลเดอร์ของบริการใต้ /etc และรันบริการด้วยผู้ใช้ postgres ถ้าต้องการรุ่นใหม่กว่าที่ Ubuntu มีให้ ติดตั้งจาก apt repository ของ PostgreSQL (PGDG) ได้ จดหมายเลขรุ่นที่ apt แสดงตอนติดตั้งไว้ เพราะการรองรับ prepared statement ในโหมด transaction เริ่มที่รุ่น 1.21

ขั้นตอนที่ 2: เตรียมไฟล์ผู้ใช้แบบ SCRAM

ตัวพูลต้องตรวจรหัสผ่านของแอปก่อนส่งต่อไปยังฐานข้อมูล วิธีที่ปลอดภัยคือคัดลอก SCRAM secret จาก PostgreSQL มาใช้ ไม่ต้องเก็บรหัสผ่านแบบข้อความธรรมดา

sudo -u postgres psql -Atc "SELECT rolpassword FROM pg_authid WHERE rolname = 'appuser';"

นำผลลัพธ์ที่ขึ้นต้นด้วย SCRAM-SHA-256$4096: ไปใส่ในไฟล์ userlist.txt ในโฟลเดอร์เดียวกัน หนึ่งบรรทัดต่อหนึ่งผู้ใช้ ทั้งชื่อและ secret ครอบด้วยเครื่องหมายคำพูดคู่ ทำแบบเดียวกันกับผู้ใช้สำหรับดูสถิติ ซึ่งสร้างใน PostgreSQL ด้วย CREATE ROLE pgb_stats LOGIN PASSWORD 'StrongPassword'; โดยไม่ต้องให้สิทธิ์ใดเพิ่ม

"appuser" "SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>"
"pgb_stats" "SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>"

cd /etc/pgbouncer
sudo chown postgres:postgres userlist.txt
sudo chmod 640 userlist.txt

เมื่อ secret ตรงกับของเซิร์ฟเวอร์ทุกตัวอักษรและฝั่งไคลเอนต์ยืนยันตัวตนแบบ SCRAM ระบบจะใช้ secret เดียวกันล็อกอินเข้า PostgreSQL แทนแอปได้ ข้อควรจำคือทุกครั้งที่เปลี่ยนรหัสผ่านของผู้ใช้ ต้องคัดลอก secret ใหม่มาใส่ไฟล์นี้ด้วย

ขั้นตอนที่ 3: ตั้งค่าไฟล์คอนฟิกหลัก

จากโฟลเดอร์เดิม เปิดไฟล์คอนฟิกหลักด้วย sudo nano pgbouncer.ini แล้วแก้ค่าในสองส่วนนี้ ค่าอื่นที่แพ็กเกจตั้งไว้ เช่น logfile และ pidfile ให้คงไว้ตามเดิม

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
max_prepared_statements = 200
stats_users = pgb_stats

อย่าใส่ user= ในบรรทัดของ [databases] เพราะจะทำให้ใช้ SCRAM secret ของแอปล็อกอินเข้าฐานข้อมูลไม่ได้ ถ้าแอปอยู่คนละเครื่อง ให้เปลี่ยน listen_addr เป็น IP ภายใน เช่น 10.0.0.5 แล้วเปิดพอร์ต 6432 เฉพาะเครื่องแอปที่ไฟร์วอลล์

ค่าที่ต้องคำนวณคือจำนวนการเชื่อมต่อจริงไปยัง PostgreSQL ซึ่งเท่ากับจำนวนพูล (หนึ่งพูลต่อหนึ่งคู่ฐานข้อมูลกับผู้ใช้) คูณ default_pool_size บวก reserve_pool_size ผลรวมนี้ต้องน้อยกว่า max_connections ของ PostgreSQL พอสมควร เพื่อเหลือช่องให้ผู้ดูแลระบบและงานสำรองข้อมูล

sudo systemctl restart pgbouncer

ถ้าบริการไม่ขึ้น ให้ดูสาเหตุจากไฟล์ log ในโฟลเดอร์ /var/log/postgresql ซึ่งมักบอกบรรทัดคอนฟิกที่ผิดตรง ๆ

ขั้นตอนที่ 4: เลือก pool_mode ให้เหมาะกับแอป

pool_mode กำหนดว่าการเชื่อมต่อจริงจะถูกคืนเข้าพูลเมื่อใด โหมด transaction ประหยัดที่สุดและเหมาะกับเว็บและ API ส่วนใหญ่ แต่มีเงื่อนไขว่าแอปห้ามพึ่งสถานะระดับ session เพราะ transaction ถัดไปอาจไปอยู่บนการเชื่อมต่อคนละเส้น

ตารางเปรียบเทียบ pool_mode ของ PgBouncer แบบ session, transaction และ statement ทั้งจังหวะคืนการเชื่อมต่อและข้อจำกัด — เว็บและ API ส่วนใหญ่ใช้ transaction ได้ ถ้าแอปไม่พึ่งสถานะระดับ session

สิ่งที่ใช้ไม่ได้อย่างปลอดภัยในโหมด transaction ได้แก่ SET ค่าระดับ session, LISTEN/NOTIFY, advisory lock ระดับ session, ตารางชั่วคราวที่ใช้ข้าม transaction, cursor แบบ WITH HOLD และคำสั่ง PREPARE ระดับ SQL ถ้าแอปต้องใช้สิ่งเหล่านี้ ให้แยกฐานข้อมูลอีกชื่อใน [databases] ที่ตั้ง pool_mode=session เฉพาะงานนั้น เช่น appdb_session = host=127.0.0.1 dbname=appdb pool_mode=session

ขั้นตอนที่ 5: เปลี่ยน connection string ของแอป

ทดสอบจากเครื่องแอปก่อนว่าเชื่อมต่อผ่านพอร์ต 6432 ได้จริง

psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser" -c "SELECT current_user, now();"

จากนั้นเปลี่ยนเฉพาะพอร์ตในค่าตั้งของแอปจาก 5432 เป็น 6432 เช่น postgresql://appuser:[email protected]:6432/appdb หรือ DB_PORT=6432 ในไฟล์ .env ของ Laravel ส่วนพูลการเชื่อมต่อฝั่งแอปควรลดขนาดลง เพราะการเปิดเชื่อมต่อใหม่ผ่านตัวพูลมีต้นทุนต่ำมากแล้ว ทยอยเปลี่ยนทีละบริการและเฝ้าดู log ในชั่วโมงแรก

ขั้นตอนที่ 6: เฝ้าดูพูลด้วย SHOW POOLS

ตัวพูลมีฐานข้อมูลเสมือนสำหรับดูสถานะ ชื่อเดียวกับโปรแกรม ผู้ใช้ที่อยู่ใน stats_users เข้าได้ด้วยคำสั่งนี้

psql -h 127.0.0.1 -p 6432 -U pgb_stats pgbouncer
\x
SHOW POOLS;
SHOW STATS;

หน้าจอคอนโซลของ PgBouncer แสดงผล SHOW POOLS แบบ expanded ที่มีไคลเอนต์ 182 ตัวใช้การเชื่อมต่อจริง 14 เส้น — cl_waiting และ maxwait เป็นศูนย์ แปลว่าพูลรองรับโหลดขณะนั้นได้ทัน

ค่าที่ต้องดูคือ cl_waiting จำนวนไคลเอนต์ที่รอการเชื่อมต่อ และ maxwait เวลาที่ไคลเอนต์ซึ่งรอนานที่สุดรออยู่เป็นวินาที ถ้าสองค่านี้เป็นศูนย์เกือบตลอดเวลา พูลเพียงพอ ถ้า maxwait เพิ่มขึ้นต่อเนื่อง แปลว่าพูลรับไม่ทัน เมื่อแก้คอนฟิกแล้วให้โหลดค่าใหม่ด้วย sudo systemctl reload pgbouncer ซึ่งไม่ตัดการเชื่อมต่อของแอป

ปัญหาที่พบบ่อยเกี่ยวกับ PgBouncer

แอปขึ้น prepared statement does not exist หลังเปลี่ยนมาใช้ PgBouncer ทำไม?

ไดรเวอร์ของแอปสร้าง prepared statement บนการเชื่อมต่อเส้นหนึ่ง แต่ในโหมด transaction คำสั่งถัดไปถูกส่งไปอีกเส้น แก้ด้วยการใช้รุ่น 1.21 ขึ้นไปและตั้ง max_prepared_statements มากกว่า 0 หรือปิด server-side prepare ในไดรเวอร์ เช่น prepareThreshold=0 ของ JDBC หรือเปิด emulate prepares ของ PDO

เชื่อมต่อแล้วขึ้น unsupported startup parameter แก้อย่างไร?

ไดรเวอร์บางตัวส่งพารามิเตอร์ตอนเริ่มเชื่อมต่อที่ตัวพูลไม่ได้ติดตาม เช่น extra_float_digits ของ JDBC ให้เพิ่มชื่อนั้นในบรรทัด ignore_startup_parameters = extra_float_digits ในส่วนเดียวกับค่าอื่นของไฟล์คอนฟิก แล้ว reload บริการ

cl_waiting สูงและ maxwait เพิ่มขึ้นเรื่อย ๆ ต้องทำอย่างไร?

อย่าเพิ่งเพิ่ม default_pool_size ทันที ให้ดูก่อนว่ามี Query ช้าหรือ transaction ที่ค้างนานใน pg_stat_activity หรือไม่ เพราะการเชื่อมต่อที่ถูกยึดไว้นานคือสาเหตุหลัก ถ้า Query ปกติดีแล้วจึงค่อยขยายพูลโดยยังให้ผลรวมต่ำกว่า max_connections ดูแนวทางปรับเครื่องได้ใน คู่มือปรับแต่ง PostgreSQL เบื้องต้น

ล็อกอินผ่าน PgBouncer ไม่ได้ทั้งที่รหัสผ่านถูก เกิดจากอะไร?

สาเหตุที่พบบ่อยคือ secret ใน userlist.txt เก่ากว่ารหัสผ่านปัจจุบัน ใส่ user= ไว้ในบรรทัด [databases] หรือ pg_hba.conf ไม่อนุญาตการเชื่อมต่อจาก 127.0.0.1 ดูข้อความใน /var/log/postgresql/pgbouncer.log ซึ่งบอกว่าล้มเหลวที่ฝั่งไคลเอนต์หรือฝั่งเซิร์ฟเวอร์ แล้วตรวจกฎตาม คู่มือ pg_hba.conf

รายการตรวจก่อนเปลี่ยนแอปมาเชื่อมต่อผ่าน PgBouncer ทั้งไฟล์ผู้ใช้ SCRAM pool_mode ขนาดพูล และแผนย้อนกลับ — แก้ข้อที่เป็นสีแดงและเหลืองก่อนเปลี่ยนพอร์ตของแอปในระบบจริง

ถ้าฐานข้อมูลของคุณรับโหลดหนักและต้องการผู้ช่วยวางระบบ Connection Pooling หรือแยกเครื่องฐานข้อมูล ติดต่อทีมงาน THAI DATA CLOUD หรือดูเครื่องที่ Cloud Server ค่าตั้งทั้งหมดอ่านได้ที่ เอกสารการตั้งค่า PgBouncer และคำสั่งของคอนโซลที่ คู่มือการใช้งานคอนโซล

อ่านเพิ่มเติมเกี่ยวกับ PgBouncer

แชร์Facebook X LINE LinkedIn

มีคำถามเรื่องบริการ? ติดต่อทีมงาน