Pooling de Connexions PostgreSQL PgBouncer

Introduction

Dans ce laboratoire, vous apprendrez à configurer et à utiliser PgBouncer pour le pooling de connexions ( connection pooling ) avec PostgreSQL. PgBouncer agit comme un pooler de connexions léger ( lightweight connection pooler ), se situant entre votre application et la base de données PostgreSQL pour gérer efficacement les connexions. Cela réduit la surcharge liée à la création de nouvelles connexions pour chaque requête, améliorant considérablement les performances, en particulier pour les applications avec des connexions de base de données fréquentes et de courte durée.

Le laboratoire vous guide à travers la configuration de PgBouncer, y compris la création d'un fichier de configuration avec les détails de connexion à la base de données et les paramètres du pool. Vous vous connecterez ensuite à la base de données via PgBouncer, simulerez plusieurs connexions client pour observer le pooling en action, et surveillerez les performances de PgBouncer pour comprendre les avantages du pooling de connexions.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/sys_version("Get Server Version") subgraph Lab Skills postgresql/db_access -.-> lab-550957{{"Pooling de Connexions PostgreSQL PgBouncer"}} postgresql/sys_version -.-> lab-550957{{"Pooling de Connexions PostgreSQL PgBouncer"}} end

Configurer PgBouncer et se connecter à PostgreSQL

Dans cette étape, vous allez configurer PgBouncer pour le pooling de connexions ( connection pooling ) et vous connecter à votre base de données PostgreSQL via celui-ci.

Créer un fichier de configuration PgBouncer :

Tout d'abord, créez un répertoire pour stocker le fichier de configuration PgBouncer :
```
mkdir -p ~/project/pgbouncer
cd ~/project/pgbouncer
```
Ensuite, créez le fichier de configuration pgbouncer.ini en utilisant nano :
```
nano pgbouncer.ini
```
Collez la configuration suivante dans le fichier pgbouncer.ini :
```
[databases]
mydb = host=127.0.0.1 port=5432 dbname=postgres user=postgres

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
user = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL
default_pool_size = 20
max_client_conn = 100
```
- [databases] définit la connexion à la base de données. mydb est un alias.
- [pgbouncer] configure PgBouncer. listen_addr et listen_port sont l'adresse et le port sur lesquels PgBouncer écoute. pool_mode est défini sur transaction.
Appuyez sur Ctrl+O pour enregistrer le fichier, puis sur Ctrl+X pour quitter nano.
Créer un fichier de liste d'utilisateurs :

PgBouncer a besoin d'un fichier de liste d'utilisateurs pour l'authentification. Créez userlist.txt :
```
nano userlist.txt
```
Ajoutez la ligne suivante, en remplaçant labex_password par le mot de passe réel de l'utilisateur postgres (vous pouvez le définir en utilisant sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'labex_password';") :
```
"postgres" "labex_password"
```
Appuyez sur Ctrl+O pour enregistrer le fichier, puis sur Ctrl+X pour quitter nano.
Démarrer PgBouncer :

Démarrez PgBouncer en utilisant le fichier de configuration :
```
pgbouncer pgbouncer.ini
```
Gardez ce terminal ouvert. Ouvrez un nouveau terminal pour les étapes suivantes.
Se connecter à PostgreSQL via PgBouncer :

Dans le nouveau terminal, connectez-vous à PostgreSQL via PgBouncer en utilisant psql :
```
psql -h 127.0.0.1 -p 6432 -U postgres -d postgres
```
Entrez le mot de passe que vous avez défini dans userlist.txt. Si la connexion réussit, vous verrez l'invite psql.
```
psql (13.3, server 1.18.0)
Type "help" for help.

postgres=#
```

Créer un exemple de table et de données

Maintenant que vous êtes connecté à PostgreSQL via PgBouncer, créons un exemple de table et insérons quelques données.

Créer une table :

Exécutez la commande SQL suivante pour créer une table nommée users :
```
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);
```
Vous devriez voir la sortie :
```
CREATE TABLE
```

Insérer des données dans la table :

Insérez des exemples de données dans la table users :

INSERT INTO users (name, email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]');

Vous devriez voir la sortie :

INSERT 0 3

Vérifier les données :

Vérifiez que les données ont été insérées correctement en interrogeant la table :

SELECT * FROM users;

Vous devriez voir les données insérées :

 id |  name   |       email
----+---------+-----------------------
  1 | Alice   | [email protected]
  2 | Bob     | [email protected]
  3 | Charlie | [email protected]
(3 rows)

Simuler plusieurs connexions clientes

Dans cette étape, vous allez simuler plusieurs connexions clientes à la base de données PostgreSQL via PgBouncer.

Créer un script pour se connecter et interroger :

Créez un script nommé connect_and_query.sh dans votre répertoire ~/project :
```
nano ~/project/connect_and_query.sh
```
Collez le script suivant dans le fichier :
```
#!/bin/bash

psql -h 127.0.0.1 -p 6432 -U postgres -d postgres -c "SELECT COUNT(*) FROM users;"
```
Ce script se connecte à la base de données via PgBouncer et exécute une requête pour compter le nombre d'utilisateurs.

Rendez le script exécutable :
```
chmod +x ~/project/connect_and_query.sh
```
Simuler des connexions simultanées :

Utilisez xargs pour exécuter le script plusieurs fois simultanément :
```
seq 5 | xargs -P 5 -I {} ~/project/connect_and_query.sh
```
Entrez le mot de passe lorsque vous y êtes invité. Vous devriez voir le nombre d'utilisateurs affiché plusieurs fois.

Surveiller les performances du pooling (mise en commun)

Dans cette étape, vous allez surveiller les performances du pooling (mise en commun) de PgBouncer.

Se connecter à la base de données d'administration de PgBouncer :

Connectez-vous à la base de données pgbouncer :
```
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer
```
Entrez le mot de passe lorsque vous y êtes invité.
Afficher les statistiques de PgBouncer :

Exécutez la commande SQL suivante pour afficher les statistiques :
```
SHOW STATS;
```
Cela affichera des statistiques sur l'activité de PgBouncer, notamment le nombre total de requêtes, les octets reçus et les octets envoyés.
Afficher les statistiques du pool :

Exécutez la commande SQL suivante pour afficher les statistiques du pool (groupe de connexions) :
```
SHOW POOLS;
```
Cela affichera des statistiques sur le connection pool (pool de connexions), notamment les connexions clientes actives, les connexions clientes en attente, les connexions serveur actives et les connexions serveur inactives.

En examinant ces statistiques, vous pouvez comprendre comment PgBouncer gère les connexions et identifier les goulots d'étranglement potentiels.
Quitter psql :
```
\q
```

Résumé

Dans ce TP (travaux pratiques), vous avez configuré PgBouncer pour le connection pooling (mise en commun de connexions), créé une table et des données d'exemple, simulé plusieurs connexions clientes et surveillé les performances de PgBouncer. Vous avez appris comment PgBouncer gère efficacement les connexions à la base de données, réduisant ainsi la surcharge et améliorant les performances des applications avec des connexions fréquentes et de courte durée. En surveillant les statistiques de PgBouncer, vous pouvez optimiser sa configuration et garantir un connection pooling efficace.