18
0
Bifurcation 0
wiki/HowtoPgBouncer.md

7.6 KiB

PgBouncer

https://wiki.postgresql.org/wiki/PgBouncer

PgBouncer agit en tant que proxy entre des clients et un ou des serveurs PostgreSQL. Il permet de gérer un pool de connexion et est donc utile lorsque le client ne gère pas soit même son pool, notamment dans le cas de PHP.

Pour PostgreSQL, accepter une nouvelle connexion est très coûteux en terme de ressource car il va forquer un nouveau processus. PgPouncer permet donc de maintenir un pool de connexions actives auprès de PostgreSQL et de les rendre disponibles pour les clients voulant se connecter. PgBouncer a été conçu pour être très peu consommateur de ressources et pouvoir accepter un grand nombre de connexions (pas de fork ni de thread à chaque connexion, programmation évènementielle comme Nginx).

Installation

# apt install pgbouncer

Configuration

La configuration se fait dans le fichier /etc/pgbouncer/pgbouncer.ini.

  • La section [databases] doit contenir la liste des bases de données sur lesquelles les clients pourront se connecter, avec une chaîne de connexion valide.

    Par exemple :

[databases]
mydb = host=192.0.2.42 port=5432

Les connexions sur la base mydb seront renvoyées à l'hôte 192.0.2.42 sur le port 5433 et sur la base db. Toutes les autres seront renvoyées en local sur la base initialement demandée dans la requête.

Si on veux que PgBouncer soit utilisé avec toutes les BDD, on peux le configurer en mode "wildcard" :

[databases]
* = host=192.0.2.42 port=5432

La section pgbouncer contient différents paramètres pour le fonctionnement de PgBouncer. Les paramètres importants à modifier sont :

  • pool_mode : permet de définir quand une connexion peut être réattribuée (session dans le cas d'une déconnexion explicite du client (peu de gain de performance mais permet d'être complètement transparent pour le client), transaction lorsqu'une transaction est terminée, statement lorsque une requête est terminée (les transactions ne peuvent alors plus être utilisées)) ;
  • max_client_conn : le nombre total de client qui peuvent être connectés simultanément à PgBouncer. Exemple : 5000
  • default_pool_size : le nombre total de client qui peuvent être connectés simultanément au serveur PostgreSQL derrière. Il ne faut pas dépasser le max_connections du serveur en question moins une vingtaine (pour les connexions réservées au superadmin). Si default_pool_size est dépassé (mais pas max_client_conn), alors les nouvelles connexions seront mises en attente.
  • auth_type : Permet de définir le type d'authentification utilisé par les utilisateurs SQL. Si la version de PostgreSQL est inférieure a la version 14, il faut utilisé par défaut le type "md5". Si la version de PostgreSQL est supérieure ou égale à la version 14, il faut utilisé par défaut le type "scram-sha-256".
  • listen_addr : Liste les ips sur lesquelles PgBouncer doit écouter, on peux mettre une liste d'ip séparé par une virgule.

Voici une configuration type de PgBouncer en PostgreSQL 15 / Debian 12 :

[databases]
* = host=192.0.2.42 port=5432

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

listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir =

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

admin_users = pgbouncer_monitoring
stats_users = 

# La connexion au serveur redevient libre lorsque le client termine une transaction
# Autres valeurs possibles : session (lorsque le client ferme la session), statement (lorsque la requête se termine)
pool_mode = transaction

# Nombre maximum de connexions entrantes
max_client_conn = 1000

# Nombre de connexion maintenues avec le serveur
default_pool_size = 100

# Ne pas enregistrer les connexions qui se passent bien
log_connections = 0
log_disconnections = 0

Gestion des utilisateurs

PgBouncer a besoin de connaitre les identifiants des utilisateurs SQL, ceci se configure dans le fichier /etc/pgbouncer/userlist.txt

Voici la syntaxe du fichier d'authentification, en fonction des différents formats configuré par la variable auth_file :

"username1" "md5abcdef012342345"
"username2" "SCRAM-SHA-256$4096:gY9bPDkA8bA/bvpb8WyBAw==$22oqvA2nKmtlf1CCxJxf1iGcs5Atyqk1nbEjDbwrm7M=:czBfBzShQf0a9jmTtqVK1cEt1F7EAt+I6NEAzoEsChM="

On peux récupérer le hash du mot de passe d'un utilisateur SQL en faisant une requête sur la table pg_shadow comme ceci :

postgres=# select passwd from pg_shadow where usename='username2';

Monitoring et administration

Connexion à la console pgbouncer

Une des fonctionnalités très intéressante de PgBouncer est de pouvoir s'y connecter avec le client psql est d'exécuter des pseudos requêtes SQL :

# psql -p 6432 -U pgbpostgres pgbouncer
psql (9.2.4, server 1.5.4/bouncer)
WARNING: psql version 9.2, server version 1.5.
         Some psql features might not work.
Type "help" for help.

pgbouncer=# 

La requêtes SHOW HELP; permet de lister les requêtes possibles.

On peux définir un ou des utilisateurs "admin" pour PgBouncer, avec la variable admin_users et un ou des utilisateurs dédiés au statistiques avec la variable stats_users.

Voir les statistiques, connexions établies, etc…

pgbouncer# show pools;
pgbouncer# show clients;
pgbouncer# show servers;
pgbouncer# show stats;

Check NRPE

On peux utilisé le check_postgres pour monitorer les pools de connexions à PgBouncer.

Le check_pgbouncer est packager dans Debian depuis la version 10, on l'installe comme ceci :

# apt install check-postgres

Voici des exemples d'action qu'on peux utilisé pour monitoré PgBouncer :

  • pgb_pool_cl_active : Nombres sur l'état "client actif" établi entre un client et PgBouncer. Certains clients dans cet état exécutent des requêtes, mais d'autres peuvent ne pas le faire. Ça comprend deux "états", un "état" cl_active(EXECUTING) et un "état" cl_active(CONNECTED).

  • pgb_pool_cl_waiting : Le nombre affiché dans SHOW POOLS comme cl_waiting combine en fait le nombre de clients dans ces deux états. La connexion tombe dans l'état cl_waiting, lorsqu'une connexion entre pgbouncer et postgres a été faite, mais que lorsque la connexion a voulu entrer dans l'état CL_ACTIVE(EXECUTING) ou CL_LOGIN(EXECUTING), mais que pgbouncer n'as trouver aucun backend postgres disponible, alors la connexion se mets dans l'état cl_waiting.

  • pgbouncer_sv_active : Nombres de connexion serveur active (c'est-à-dire de PgBouncer à Postgres), cela ne signifie pas nécessairement qu'il exécute activement une requête. La connexion au serveur restera active jusqu'à ce que le client connecté à PgBouncer se déconnecte explicitement.

Plusieurs autres type d'actions sont possible, voici la documentation

Voici un exemple de check_postgres qu'on met en place dans nrpe sur le serveur :

/usr/bin/check_postgres --action=pgb_pool_cl_active -H 127.0.0.1 -p 6432 --dbservice=pgbouncer -w 80 -c 350

L'option --dbservice permet d'utilisé un fichier .pg_service.conf pour l'authentification voir la documentation

FAQ

  • Je vois un pic de connexions qui sont mises en attentant (client_waiting), alors que le default_pool_size est loin d'être dépassé, pourquoi ?

    C'est dû au fonctionnement de PgBouncer : si il reçoit un pic de demande de connexion, afin de ne pas répercuter ce pic sur le backend PostgreSQL, il va ouvrir ses connexions les une après les autres.