PgBouncer ile PostgreSQL Connection Pooling Kurulumu

PostgreSQL ile ciddi bir uygulama geliştiriyorsanız, er ya da geç connection pooling ihtiyacıyla yüz yüze gelirsiniz. Özellikle yüksek trafikli web uygulamalarında, her HTTP isteği için yeni bir veritabanı bağlantısı açmak hem maliyetli hem de sürdürülemez bir yaklaşımdır. PgBouncer, bu soruna getirilen en yaygın ve en olgun çözümlerden biridir. Bu yazıda PgBouncer’ı sıfırdan kurarak production ortamına hazır hale getireceğiz.

Neden Connection Pooling Gerekli?

PostgreSQL’de her bağlantı aslında ayrı bir işlem (process) olarak çalışır. Bu mimari, izolasyon ve güvenilirlik açısından mükemmeldir; ancak her yeni bağlantı için sunucu tarafında ciddi bir kaynak tüketimi söz konusudur.

Somut bir senaryo düşünelim: 500 eş zamanlı kullanıcısı olan bir e-ticaret uygulamanız var. Uygulama sunucunuz her istek için ayrı bir bağlantı açıyor. Bu durumda PostgreSQL 500 ayrı process yönetmek zorunda kalır. Her process yaklaşık 5-10 MB RAM tüketir. Sadece bağlantı yönetimi için 2.5-5 GB RAM harcarsınız. Bunun üzerine context switching maliyetlerini, pg_hba.conf doğrulama sürelerini ve SSL handshake masraflarını ekleyin.

PgBouncer bu bağlantıları havuzda (pool) toplar. Uygulama PgBouncer’a bağlanır, PgBouncer ise PostgreSQL’e sınırlı sayıda bağlantı açar. Ortada akıllı bir aracı gibi çalışır.

PgBouncer Pool Modları

PgBouncer üç farklı modda çalışabilir ve doğru modu seçmek kritik önem taşır.

Session Pooling: Bağlantı, istemci bağlantısı boyunca aynı PostgreSQL bağlantısına atanır. İstemci bağlantısını kesene kadar o bağlantı başka istemcilere verilmez. Bu mod en az verimliyken aynı zamanda en güvenli olanıdır.

Transaction Pooling: Bağlantı, sadece bir transaction süresi boyunca tutulur. Transaction biter bitmez bağlantı havuza geri döner. Çoğu uygulama için ideal mod budur. Ancak SET komutu, advisory locks ve prepared statements gibi session-level özelliklerde sorun çıkarabilir.

Statement Pooling: Her SQL ifadesinden sonra bağlantı havuza döner. Multi-statement transactionlara izin vermez. Oldukça agresif bir moddur ve çok az kullanım senaryosu vardır.

Gerçek dünyada %90 oranında transaction pooling kullanılır. Eğer uygulamanız session-level özellikler kullanıyorsa session pooling’e geçmeniz gerekir.

Kurulum

Ubuntu/Debian sistemlerde kurulum oldukça basittir:

# Ubuntu/Debian
sudo apt update
sudo apt install pgbouncer -y

# RHEL/CentOS/Rocky Linux
sudo dnf install pgbouncer -y

# Servis durumunu kontrol et
sudo systemctl status pgbouncer

Kaynaklardan derleme yapmak isteyenler için:

# Bağımlılıkları kur
sudo apt install libevent-dev libssl-dev pkg-config -y

# Kaynak kodunu indir
wget https://www.pgbouncer.org/downloads/files/1.21.0/pgbouncer-1.21.0.tar.gz
tar xzf pgbouncer-1.21.0.tar.gz
cd pgbouncer-1.21.0

# Derle ve kur
./configure --prefix=/usr/local
make
sudo make install

Temel Yapılandırma

PgBouncer’ın ana yapılandırma dosyası /etc/pgbouncer/pgbouncer.ini konumundadır. Hadi gerçek bir production senaryosu için bu dosyayı yapılandıralım:

sudo nano /etc/pgbouncer/pgbouncer.ini

[databases]
# Format: alias = host=... port=... dbname=...
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_production
readonly_db = host=replica.internal port=5432 dbname=myapp_production

# Birden fazla veritabanı tanımlayabilirsiniz
analytics_db = host=analytics.internal port=5432 dbname=analytics user=analytics_user

[pgbouncer]
# Dinleme adresi ve portu
listen_addr = 0.0.0.0
listen_port = 6432

# Kimlik doğrulama
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool modu - production için transaction önerilir
pool_mode = transaction

# Her veritabanı için maksimum bağlantı sayısı
max_client_conn = 1000
default_pool_size = 25

# Yedek bağlantılar - anlık artışlar için
reserve_pool_size = 5
reserve_pool_timeout = 3

# Minimum pool boyutu - her zaman hazır bağlantı
min_pool_size = 5

# Bağlantı yaşam süresi (saniye)
server_lifetime = 3600
server_idle_timeout = 600
client_idle_timeout = 0

# Log ayarları
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

# Admin erişimi
admin_users = pgbouncer_admin
stats_users = monitoring_user

# TCP ayarları
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5

Kullanıcı Listesi Oluşturma

PgBouncer kimlik doğrulaması için userlist.txt dosyasına ihtiyaç duyar. Bu dosyaya kullanıcı adlarını ve şifrelerini ekleriz.

# SCRAM-SHA-256 hash oluşturmak için PostgreSQL'den şifreyi alın
# Önce PostgreSQL'e bağlanın
sudo -u postgres psql

# Kullanıcının şifre hash'ini alın
SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow WHERE usename = 'myapp_user';

Çıktıyı userlist.txt dosyasına yazın:

sudo nano /etc/pgbouncer/userlist.txt

# Format: "kullanici_adi" "sifre_veya_hash"
"myapp_user" "SCRAM-SHA-256$4096:saltvalue==:hashvalue=="
"analytics_user" "SCRAM-SHA-256$4096:anothersalt==:anotherhash=="
"pgbouncer_admin" "admin_password_hash"
"monitoring_user" "monitoring_password_hash"

Dosya izinlerini düzenleyin:

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

Servisi Başlatma ve Yönetme

# Servisi etkinleştir ve başlat
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

# Log takibi
sudo tail -f /var/log/pgbouncer/pgbouncer.log

# Yapılandırmayı test et (servisi yeniden başlatmadan)
sudo pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Bağlantıyı test etmek için:

# PgBouncer üzerinden PostgreSQL'e bağlan
psql -h 127.0.0.1 -p 6432 -U myapp_user -d myapp_db

# Bağlantının nereye gittiğini doğrula
SELECT inet_server_addr(), inet_server_port();

Admin Konsolu ile İzleme

PgBouncer’ın en güçlü özelliklerinden biri, detaylı istatistik sunan admin konsoludur:

# Admin konsoluna bağlan
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer

# Tüm pool istatistiklerini gör
SHOW POOLS;

# Aktif bağlantıları listele
SHOW CLIENTS;

# Server bağlantılarını listele
SHOW SERVERS;

# Genel istatistikler
SHOW STATS;

# Yapılandırmayı yeniden yükle (servis kapatmadan)
RELOAD;

# Belirli bir veritabanına yeni bağlantıları geçici olarak durdur
PAUSE myapp_db;

# Devam ettir
RESUME myapp_db;

SHOW POOLS çıktısını yorumlamak önemlidir:

cl_active: Şu an aktif sorgu çalıştıran istemci sayısı
cl_waiting: Boş bağlantı bekleyen istemci sayısı
sv_active: Aktif kullanılan server bağlantıları
sv_idle: Havuzda bekleyen boş server bağlantıları
sv_used: Son işlemden sonra havuza dönen bağlantılar
maxwait: En uzun süre bekleyen istemcinin bekleme süresi (saniye)

Eğer cl_waiting sürekli sıfırdan büyükse ve maxwait artıyorsa, pool size’ı artırmanız gerekiyor demektir.

Monitoring Script’i

Bir sistem yöneticisi olarak, PgBouncer metriklerini düzenli izlemek için basit bir script hazırlamak işinizi kolaylaştırır:

#!/bin/bash
# /usr/local/bin/pgbouncer-monitor.sh

PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"
PGBOUNCER_USER="monitoring_user"
ALERT_THRESHOLD_WAIT=10  # Bekleme saniyesi eşiği
ALERT_THRESHOLD_CLIENTS=800  # Maksimum istemci eşiği

check_pgbouncer() {
    local result
    result=$(psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" 
        -U "$PGBOUNCER_USER" -d pgbouncer 
        -t -c "SHOW STATS;" 2>&1)
    
    if [ $? -ne 0 ]; then
        echo "CRITICAL: PgBouncer'a baglanamadi!"
        exit 2
    fi
    
    # Bekleyen istemci sayısını kontrol et
    local waiting_clients
    waiting_clients=$(psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" 
        -U "$PGBOUNCER_USER" -d pgbouncer 
        -t -c "SELECT sum(cl_waiting) FROM pools;" 2>/dev/null | tr -d ' ')
    
    # Maksimum bekleme süresini kontrol et
    local max_wait
    max_wait=$(psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" 
        -U "$PGBOUNCER_USER" -d pgbouncer 
        -t -c "SELECT max(maxwait) FROM pools;" 2>/dev/null | tr -d ' ')
    
    echo "=== PgBouncer Durum Raporu ==="
    echo "Bekleyen istemciler: $waiting_clients"
    echo "Maksimum bekleme: ${max_wait}s"
    
    if [ "${max_wait:-0}" -gt "$ALERT_THRESHOLD_WAIT" ]; then
        echo "UYARI: Yuksek bekleme suresi tespit edildi! (${max_wait}s)"
        # Buraya e-posta veya Slack bildirimi ekleyebilirsiniz
    fi
}

check_pgbouncer

sudo chmod +x /usr/local/bin/pgbouncer-monitor.sh

# Cron ile her 5 dakikada bir çalıştır
echo "*/5 * * * * root /usr/local/bin/pgbouncer-monitor.sh >> /var/log/pgbouncer-monitor.log 2>&1" | sudo tee /etc/cron.d/pgbouncer-monitor

Yaygın Sorunlar ve Çözümleri

Prepared Statements Sorunu

Transaction pooling modunda en çok karşılaşılan sorun prepared statements’tır. Örneğin Django ORM veya bazı JDBC sürücüleri varsayılan olarak prepared statements kullanır.

# PostgreSQL bağlantı string'inde prepared statements'ı devre dışı bırak
# Django settings.py için:
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'options': '-c plan_cache_mode=force_custom_plan'
        },
        'CONN_MAX_AGE': 0,  # Django'nun kendi pooling'ini kapat
    }
}

# JDBC için bağlantı URL'inde:
# jdbc:postgresql://localhost:6432/mydb?prepareThreshold=0

# Node.js pg kütüphanesi için:
# new Pool({ statement_timeout: 30000, query_timeout: 30000 })

SSL Yapılandırması

Production ortamında PgBouncer ile SSL kullanmak önemlidir:

# pgbouncer.ini içinde SSL ayarları
[pgbouncer]
# İstemci tarafı SSL (uygulama -> PgBouncer)
client_tls_sslmode = require
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_ca_file = /etc/pgbouncer/ca.crt

# Server tarafı SSL (PgBouncer -> PostgreSQL)
server_tls_sslmode = require
server_tls_key_file = /etc/pgbouncer/client.key
server_tls_cert_file = /etc/pgbouncer/client.crt

Pool Size Hesaplama

Doğru pool size’ı belirlemek bir sanattır. Pratik bir formül:

# PostgreSQL max_connections değerini öğren
sudo -u postgres psql -c "SHOW max_connections;"

# Önerilen hesaplama:
# default_pool_size = (max_connections - superuser_reserved) / veritabani_sayisi
# Örnek: max_connections=200, 5 DB varsa: (200-10) / 5 = 38

# pgbouncer.ini'yi güncelle
# Ayrıca PostgreSQL'de max_connections'ı da ayarlayın
sudo -u postgres psql -c "ALTER SYSTEM SET max_connections = 200;"
sudo systemctl restart postgresql

PgBouncer ile HAProxy Kombinasyonu

Yüksek erişilebilirlik gerektiren ortamlarda PgBouncer’ı HAProxy ile birlikte kullanabilirsiniz. Bu yapıda HAProxy yazma trafiğini primary’ye, okuma trafiğini replica’lara yönlendirir:

# /etc/haproxy/haproxy.cfg
frontend postgresql_frontend
    bind *:5432
    default_backend postgresql_primary

backend postgresql_primary
    balance leastconn
    option pgsql-check user haproxy_check
    server pgbouncer1 127.0.0.1:6432 check port 6432 inter 5s
    server pgbouncer2 192.168.1.11:6432 check port 6432 inter 5s backup

frontend postgresql_readonly
    bind *:5433
    default_backend postgresql_replicas

backend postgresql_replicas
    balance roundrobin
    option pgsql-check user haproxy_check
    server replica1 192.168.1.12:6432 check inter 5s
    server replica2 192.168.1.13:6432 check inter 5s

Yapılandırma Parametrelerinin Özeti

Yapılandırma dosyasında sıkça kullanılan parametrelerin ne işe yaradığını kısaca özetleyelim:

listen_port: PgBouncer’ın dinlediği port, varsayılan olarak 6432’dir
pool_mode: Havuz modu, session/transaction/statement seçeneklerinden biri
max_client_conn: PgBouncer’ın kabul edeceği toplam maksimum istemci bağlantısı
default_pool_size: Her veritabanı/kullanıcı çifti için varsayılan havuz büyüklüğü
reserve_pool_size: Yoğun dönemlerde ek bağlantı için yedek havuz büyüklüğü
reserve_pool_timeout: Yedek havuzdan bağlantı alınana kadar bekleme süresi
server_lifetime: Bir server bağlantısının maksimum yaşam süresi (saniye)
server_idle_timeout: Boştaki server bağlantısının kapatılma süresi
client_idle_timeout: Boştaki istemci bağlantısının kapatılma süresi
query_timeout: Tek bir sorgu için maksimum süre, aşılırsa bağlantı kapatılır
query_wait_timeout: Boş server bağlantısı için istemcinin bekleyeceği maksimum süre

Gerçek Dünya Senaryosu: Spike Trafiği Yönetimi

Bir kampanya duyurusu sırasında sitenizin trafiği aniden 10 katına çıktı. PgBouncer olmadan bu senaryo veritabanını çökertir. PgBouncer ile şu şekilde yönetirsiniz:

# Anlık durum tespiti
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer -c "SHOW POOLS;"

# Pool size'ı anında artır (servis kapatmadan)
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer 
  -c "SET default_pool_size=50;"

# Ya da pgbouncer.ini'yi düzenleyip reload et
sudo systemctl reload pgbouncer
# veya admin konsolundan:
# RELOAD;

# Yoğun dönem sonrasında pool'u temizle
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer 
  -c "RECONNECT myapp_db;"

Spike sırasında maxwait değeri yükselmeye başlarsa hemen aksiyon almanız gerekir. Eğer maxwait 5 saniyeyi geçiyorsa kullanıcılar bağlantı zaman aşımı hatası almaya başlar.

Sonuç

PgBouncer, PostgreSQL altyapısının ayrılmaz bir parçası haline gelmiştir. Kurulumu kolay, yapılandırması anlaşılır ve sağladığı kazanım muazzamdır. Doğru şekilde yapılandırıldığında on kat daha az PostgreSQL bağlantısıyla aynı yükü taşıyabilirsiniz.

Özetlemek gerekirse: transaction pooling ile başlayın, uygulamanızın session-level özellikler kullanıp kullanmadığını test edin, admin konsolu üzerinden düzenli izleme yapın ve pool size’ı aşamalı olarak artırın. PgBouncer’ı PostgreSQL’in önüne koyduğunuzda, veritabanı sunucunuzun gerçek potansiyelini ilk kez görürsünüz.

Bir sonraki adım olarak Prometheus + Grafana ile PgBouncer metriklerini görselleştirmeyi ve otomatik uyarı sistemi kurmayı düşünebilirsiniz. pgbouncer_exporter bu iş için hazır bir araçtır ve birkaç dakika içinde kurulabilir.

Bağlantı Yönetimi: PgBouncer ile PostgreSQL Connection Pooling