راه‌اندازی PgBouncer برای Connection Pooling در PostgreSQL روی VPS و سرور اختصاصی
اگر اپلیکیشنتان گاهی با خطای «FATAL: too many connections» به PostgreSQL روبرو می‌شود، مشکل معمولا از منابع سرور نیست؛ از تعداد کانکشن‌های باز و بی‌استفاده است. PgBouncer یک لایه سبک بین اپلیکیشن و دیتابیس می‌گذارد و همان چند کانکشن واقعی را بین صدها درخواست همزمان بازچرخانی می‌کند.
چرا PostgreSQL کانکشن‌ها را محدود می‌کند
هر کانکشن به PostgreSQL یک process مستقل در سیستم‌عامل است، نه یک thread سبک. این process حافظه و منابع CPU مصرف می‌کند، حتی وقتی بی‌کار نشسته و منتظر کوئری بعدی است. پارامتر max_connections در postgresql.conf همین سقف را تعیین می‌کند و روی یک VPS با چند گیگابایت رم معمولا بین ۱۰۰ تا ۲۰۰ تنظیم شده.
مشکل از جایی شروع می‌شود که یک اپلیکیشن Node.js یا PHP با چند worker، برای هر درخواست یک کانکشن جدید باز می‌کند یا pool داخلی‌اش خیلی بزرگ است. با چند صد کاربر همزمان، تعداد کانکشن‌های باز از سقف عبور می‌کند و درخواست‌های جدید رد می‌شوند، حتی اگر CPU و RAM سرور هنوز جای کار دارند.
بالا بردن max_connections راه‌حل موقتی است، نه حل مسئله: هر کانکشن اضافه چند مگابایت رم می‌گیرد و از یک حدی به بعد خودِ PostgreSQL برای مدیریت همان کانکشن‌های بی‌کار زمان CPU بیشتری صرف می‌کند. اینجاست که pooling واقعی معنی پیدا می‌کند.
PgBouncer چطور کار می‌کند
PgBouncer بین اپلیکیشن و PostgreSQL می‌نشیند و یک pool کوچک از کانکشن‌های واقعی به دیتابیس نگه می‌دارد. اپلیکیشن به PgBouncer وصل می‌شود (مثل وصل شدن به PostgreSQL معمولی)، PgBouncer همان درخواست را روی یکی از کانکشن‌های آماده در pool می‌فرستد و بعد از پایان کار، کانکشن را برای درخواست بعدی نگه می‌دارد.
نتیجه این است که به‌جای هزار کانکشن باز به دیتابیس، شاید فقط ۲۰ یا ۳۰ کانکشن واقعی لازم باشد. بار حافظه روی خود PostgreSQL پایین می‌آید و دیتابیس می‌تواند منابعش را روی اجرای کوئری بگذارد، نه نگه‌داری کانکشن‌های بی‌کار.
سه حالت pooling و تفاوتشان
PgBouncer سه حالت pooling دارد و انتخاب درست بین آن‌ها مهم‌ترین تصمیم در پیکربندی است:
| حالت | کانکشن دیتابیس آزاد می‌شود | مناسب برای | محدودیت اصلی |
|---|---|---|---|
| session | بعد از قطع اتصال کلاینت | اپلیکیشن‌هایی که از session variables یا prepared statement زیاد استفاده می‌کنند | کمترین صرفه‌جویی در تعداد کانکشن |
| transaction | بعد از پایان هر تراکنش | اکثر وب‌اپلیکیشن‌ها و API ها؛ رایج‌ترین انتخاب | prepared statement در سطح session کار نمی‌کند |
| statement | بعد از هر کوئری | بار خیلی بالا با تراکنش‌های تک‌کوئری | تراکنش چندکوئری‌ای اصلا پشتیبانی نمی‌شود |
برای یک اپلیکیشن معمولی (Laravel، Django، Node.js با ORM) حالت transaction معمولا بهترین تعادل بین صرفه‌جویی در کانکشن و رفتار قابل‌پیش‌بینی را می‌دهد.
نصب PgBouncer روی Ubuntu/Debian
نصب از ریپوی رسمی توزیع ساده است. روی سرور اختصاصی یا VPS با اوبونتو:
sudo apt update
sudo apt install -y pgbouncer
سه فایل اصلی را باید بشناسید:
/etc/pgbouncer/pgbouncer.ini— تنظیمات اصلی/etc/pgbouncer/userlist.txt— کاربرها و پسوردهای هش‌شده/var/log/pgbouncer/pgbouncer.log— لاگ برای عیب‌یابی
پیکربندی pgbouncer.ini
فایل pgbouncer.ini را باز کنید و بخش‌های و را این‌طور تنظیم کنید:
mydb = host=127.0.0.1 port=5432 dbname=mydb
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
نکته کلیدی اینجا تفاوت max_client_conn و default_pool_size است: عدد اول سقف کانکشن‌هایی است که PgBouncer از سمت اپلیکیشن قبول می‌کند (می‌تواند زیاد باشد)، عدد دوم سقف کانکشن‌های واقعی که PgBouncer به PostgreSQL باز می‌کند (باید کوچک بماند). همین دو عدد هستند که مشکل too many connections را حل می‌کنند.
ساخت userlist.txt
PgBouncer احراز هویت خودش را دارد و باید پسورد کاربر PostgreSQL را به‌صورت هش md5 در userlist.txt بگذارید:
echo "md5$(echo -n 'passwordusername' | md5sum | awk '{print $1}')"
خروجی را کنار نام کاربری در فایل قرار دهید:
"myuser" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
بعد از ذخیره، سرویس را ری‌استارت کنید:
sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
وصل کردن اپلیکیشن به PgBouncer
تغییر لازم در سمت اپلیکیشن فقط عوض کردن پورت است، نه بازنویسی کد. در connection string به‌جای پورت ۵۴۳۲ (پورت پیش‌فرض PostgreSQL)، پورت ۶۴۳۲ (پورت PgBouncer) را بگذارید:
postgresql://myuser:mypassword@127.0.0.1:6432/mydb
اگر از ORM ای مثل Prisma یا Sequelize استفاده می‌کنید که روی ارتباط session طولانی یا prepared statement تکیه دارد، حالت transaction ممکن است خطاهایی مثل «prepared statement already exists» بدهد. راه‌حل این است که prepared statement را در ORM خاموش کنید (مثلا در Prisma با pgbouncer=true در connection string) یا موقتا به حالت session برگردید تا رفتار اپلیکیشن را بررسی کنید.
تست و مانیتور کردن
برای چک کردن اینکه PgBouncer درست کار می‌کند، به خودِ PgBouncer وصل شوید (نه به PostgreSQL):
psql -h 127.0.0.1 -p 6432 -U myuser pgbouncer
داخل این کنسول، دو دستور مفید‌ترند:
SHOW POOLS;
SHOW STATS;
SHOW POOLS تعداد کانکشن‌های فعال، بی‌کار و در صف را نشان می‌دهد. اگر ستون cl_waiting مدام عدد بالایی دارد، یعنی default_pool_size برای بار فعلی کم است و باید افزایشش دهید. SHOW STATS سرعت پاسخ‌دهی میانگین هر دیتابیس را می‌دهد و خوب است هر چند روز یک بار آن را چک کنید.
برای زیر نظر گرفتن مداوم همین وضعیت با نمودار، به‌جای اجرای دستی این دستورها هر چند روز یک‌بار، راه‌اندازی مانیتورینگ سرور با Prometheus و Grafana توضیح می‌دهد چطور همین معیارها را روی داشبورد بیاورید.
محدود کردن دسترسی به پورت PgBouncer
پورت ۶۴۳۲ هم مثل پورت اصلی PostgreSQL باید محافظت شود. اگر اپلیکیشن و دیتابیس روی یک سرور هستند، بهتر است listen_addr را روی 127.0.0.1 بگذارید، نه 0.0.0.0، تا PgBouncer فقط از همان سرور قابل دسترسی باشد:
listen_addr = 127.0.0.1
وقتی اپلیکیشن روی سرور دیگری اجرا می‌شود (مثلا یک VPS جدا برای وب و یک سرور اختصاصی برای دیتابیس)، باید پورت ۶۴۳۲ را در فایروال فقط برای IP همان سرور وب باز کنید:
sudo ufw allow from <ip-سرور-وب> to any port 6432
اگر با قواعد ufw آشنا نیستید، نحوه تنظیم یک فایروال با UFW مراحل پایه را توضیح می‌دهد.
بدون این محدودیت، هر کسی که آدرس سرور را پیدا کند می‌تواند مستقیم به PgBouncer وصل شود و همان محافظتی که با بستن دسترسی مستقیم به PostgreSQL ایجاد کرده‌اید، دور زده می‌شود.
چه زمانی PgBouncer کافی نیست
PgBouncer مشکل تعداد کانکشن را حل می‌کند، نه کمبود واقعی CPU یا RAM. اگر SHOW POOLS نشان می‌دهد صف انتظار همیشه پر است حتی بعد از افزایش default_pool_size، یا کوئری‌ها خودشان کند اجرا می‌شوند، مشکل از منابع زیرساخت است، نه از pooling.
در این حالت دو گزینه معمول وجود دارد: اگر بار نوسانی است و گاهی به منابع بیشتر نیاز دارید، یک سرور مجازی با امکان ارتقای سریع پلن جواب می‌دهد. اگر بار دیتابیس پیوسته و سنگین است و I/O دیسک هم درگیر می‌شود، یک سرور اختصاصی با رم و دیسک NVMe اختصاصی گزینه پایدارتری است؛ راهنمای خرید سرور اختصاصی برای دیتابیس جزئیات انتخاب رم، دیسک و CPU را پوشش می‌دهد.
اگر علاوه بر pooling به high availability هم نیاز دارید، راه‌اندازی Replication و Failover خودکار در PostgreSQL موضوع جدای دیگری است که ارزش بررسی دارد.
برای وب‌سایت یا اپلیکیشنی که دیتابیسش هر روز تعداد کانکشن بیشتری می‌طلبد، وقت خوبی است که منابع زیرساخت را هم بازبینی کنید:
مشاهده و خرید سرور اختصاصی آریانت
سؤالات متداول
آیا PgBouncer جایگزین افزایش max_connections است؟ در اکثر موارد بله. PgBouncer همان منابع را بین کانکشن‌های بیشتری بازچرخانی می‌کند، به‌جای اینکه PostgreSQL مجبور شود هزاران process نگه دارد.
کدام حالت pooling برای یک اپلیکیشن Laravel مناسب‌تر است؟
حالت transaction برای اکثر اپلیکیشن‌های وب از جمله Laravel مناسب است، به شرطی که از prepared statement در سطح session زیاد استفاده نکنید.
PgBouncer چقدر تاخیر اضافه می‌کند؟ معمولا کمتر از یک میلی‌ثانیه، چون فقط یک proxy سبک است و خودش کوئری را اجرا نمی‌کند.
روی یک VPS کوچک هم می‌شود PgBouncer نصب کرد؟ بله، PgBouncer خودش مصرف منابع پایینی دارد و حتی روی یک VPS با ۱ گیگابایت رم هم قابل اجراست؛ مشکل فقط وقتی پیش می‌آید که بار خود PostgreSQL از ظرفیت سرور بیشتر شود.
