Optimiser les connexions PostgreSQL avec PgBouncer : installation, configuration et bonnes pratiques

📌 Introduction

PostgreSQL est une base de données robuste
 mais pas toujours taillée pour absorber des centaines de connexions simultanées.
Dans des architectures modernes avec plusieurs front-ends, cette limite peut entraĂźner saturation, lenteurs, voire crashes.

Heureusement, il existe une solution simple, légÚre et redoutablement efficace : PgBouncer, un proxy de connexions PostgreSQL.

🎯 ProblĂšme rencontrĂ©

Dans un environnement avec :

  • 4 front-ends applicatifs
  • 500+ connexions simultanĂ©es
  • PostgreSQL 16 sur 16 vCPU / 24 Go RAM
  • Charges massives en INSERT / UPDATE


 nous constations une consommation mémoire excessive, une charge CPU anormale, et des temps de réponse aléatoires.

Solution : PgBouncer

PgBouncer agit comme un pooler de connexions. Il intercepte les connexions client, et les mutualise vers PostgreSQL, en maintenant un nombre limité de connexions physiques.

Avantages :

  • Connexions PostgreSQL rĂ©duites de 80%
  • StabilitĂ© mĂ©moire/CPU
  • MontĂ©e en charge sans augmenter max_connections

đŸ› ïž Installation de PgBouncer (Debian/Ubuntu)





sudo apt update
sudo apt install pgbouncer

⚙ Configuration de base

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 600
default_pool_size = 80
reserve_pool_size = 40

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
logfile = /var/log/pgbouncer/pgbouncer.log

server_idle_timeout = 600
client_idle_timeout = 600
query_timeout = 300

admin_users = postgres, pgbouncer
stats_users = postgres, pgbouncer

Fichier /etc/pgbouncer/userlist.txt

« appuser » « md5 »

🔧 PostgreSQL : ajustements nĂ©cessaires

pg_hba.conf

host    all    all    127.0.0.1/32    md5

Redémarrer PostgreSQL :

sudo systemctl restart postgresql

Connexion cÎté application (JDBC)

Les versions récentes du driver PostgreSQL envoient automatiquement un paramÚtre non supporté par PgBouncer : extra_float_digits.

Il est indispensable de l’ajouter à l’URL JDBC :

url= »jdbc:postgresql://10.252.50.29:6432/appdb?extra_float_digits=0″

📈 Journalisation et logs

Les logs PgBouncer sont essentiels pour :

  • Suivre les connexions entrantes
  • DĂ©tecter les erreurs de pool
  • Analyser la charge avec SHOW STATS
  • Fichier de log par dĂ©faut :
    • /var/log/pgbouncer/pgbouncer.log
  • Surveillance en live :
    • tail -f /var/log/pgbouncer/pgbouncer.log

đŸ§Ș Commandes utiles (depuis psql -p 6432 -d pgbouncer)

SHOW clients;
SHOW servers;
SHOW pools;
SHOW stats;

Afficher les connexions actives :

psql -U pgbouncer -h 127.0.0.1 -p 6432 -d pgbouncer

📊 RĂ©sultats obtenus

📉 RĂ©duction de la latence perçue

đŸ”» Connexions PostgreSQL rĂ©duites de 500 Ă  ~100

⚡ StabilitĂ© CPU et RAM

đŸ§˜â€â™‚ïž Temps de rĂ©ponse linĂ©aires, mĂȘme sous charge

Architecture :

🎓 En rĂ©sumĂ©

Ne laissez pas votre PostgreSQL se noyer sous les connexions.
Utilisez PgBouncer pour rationaliser, stabiliser et faire évoluer votre architecture en toute sérénité.


đŸ€ Besoin d’accompagnement ?

Nous pouvons vous aider Ă  :

  • DĂ©ployer PgBouncer sur vos serveurs
  • Ajuster vos paramĂštres PostgreSQL
  • IntĂ©grer la supervision et les alertes
  • Former vos Ă©quipes Dev & Ops

📧 Contactez notre Ă©quipe pour une mission d’audit ou de mise en production

contact@dba-expert-consulting.fr

Post Comment

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *