Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 12: Connection Pooling mit PgBouncer

Ziel: PgBouncer läuft auf pg1, authentifiziert per SCRAM über eine auth_query, bündelt viele Clients auf wenige Server-Verbindungen, und du kennst die Fallen des Transaction-Modus aus eigener Anschauung. · Dauer: ca. 60 min · Voraussetzung: Tag 3

Briefing

  • Jede PostgreSQL-Verbindung ist ein eigener Prozess mit Anmeldung, Speicher und Cache-Aufwärmen. Hunderte Clients mit kurzen Abfragen verschwenden das meiste davon.
  • PgBouncer nimmt Client-Verbindungen an und verteilt sie auf einen kleinen Pool echter Server-Verbindungen.
  • pool_mode: session (Server-Verbindung für die ganze Sitzung), transaction (nur für eine Transaktion – der Normalfall), statement (für eine Anweisung).
  • Im Transaction-Modus gehört dir die Server-Verbindung zwischen zwei Transaktionen nicht mehr: Sitzungszustand (SET, SQL-PREPARE, Advisory Locks) wandert mit ihr.
  pg2 (Clients, 50x pgbench)                pg1
  ------------------------->  :6432 PgBouncer --10 Verb.--> :5432 PostgreSQL
  Port 6432, SCRAM            auth_query über tag12_pgbouncer

Alle Befehle laufen auf pg1, außer es steht pg2 dran.

Drill 1: PgBouncer aus PGDG installieren

apt-cache madison pgbouncer
sudo apt-get install -y pgbouncer
pgbouncer --version | head -1
systemctl is-active pgbouncer
sudo ss -tlnp | grep 6432

Erwartet (gekürzt):

 pgbouncer | 1.26.1-1.pgdg24.04+1 | https://apt.postgresql.org/pub/repos/apt noble-pg...
 pgbouncer | 1.26.0-4.pgdg24.04+1 | https://apt.postgresql.org/pub/repos/apt noble-pg...
 pgbouncer | 1.26.0-1.pgdg24.04+1 | https://apt.postgresql.org/pub/repos/apt noble-pg...
 pgbouncer | 1.22.0-1build4 | http://archive.ubuntu.com/ubuntu noble/universe amd64 ...
PgBouncer 1.26.1
active
LISTEN 0  128   127.0.0.1:6432   0.0.0.0:*  users:(("pgbouncer",pid=20417,fd=9))
LISTEN 0  128       [::1]:6432      [::]:*  users:(("pgbouncer",pid=20417,fd=8))

Ubuntu selbst hat 1.22; PGDG liefert die aktuelle 1.26 mit allen Fehlerkorrekturen seitdem. Der Dienst läuft als postgres, Konfiguration in /etc/pgbouncer, Log in /var/log/postgresql/pgbouncer.log.

Fortschritt speichern? Kostenlos registrieren

Drill 2: Rollen, Datenbank und auth_query

Eine Anwendungsrolle, eine Testdatenbank und eine Rolle nur für PgBouncer. Alle Passwörter sind Lab-Werte:

psql -c "CREATE ROLE tag12_app LOGIN PASSWORD 'lab-tag12'" \
  -c "CREATE ROLE tag12_pgbouncer LOGIN PASSWORD 'lab-tag12-bouncer'"
createdb -O tag12_app tag12
PGPASSWORD=lab-tag12 pgbench -h 127.0.0.1 -U tag12_app -i -s 10 -q tag12

Damit nicht jede Anwendungsrolle in einer Datei bei PgBouncer stehen muss, fragt er die SCRAM-Hashes per auth_query bei PostgreSQL ab. pg_authid darf nur ein Superuser lesen – deshalb eine SECURITY DEFINER-Funktion, die genau eine Zeile liefert und nur von tag12_pgbouncer aufgerufen werden darf:

psql -d tag12 <<'EOF'
CREATE SCHEMA pgbouncer AUTHORIZATION tag12_pgbouncer;
CREATE FUNCTION pgbouncer.get_auth(p_usename text)
  RETURNS TABLE (username text, password text)
  LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$ SELECT rolname::text, rolpassword::text FROM pg_authid
   WHERE rolname = p_usename AND rolcanlogin $$;
REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO tag12_pgbouncer;
EOF
psql -d tag12 -Atc "SELECT username, left(password, 20)
  FROM pgbouncer.get_auth('tag12_app')"

Erwartet:

CREATE SCHEMA
CREATE FUNCTION
REVOKE
GRANT
tag12_app|SCRAM-SHA-256$4096:X

Die Funktion muss in jeder Datenbank liegen, die PgBouncer bedient – die auth_query läuft in der Zieldatenbank. Jetzt die Konfiguration. [databases] steht absichtlich am Ende, dann lassen sich Einträge später einfach anhängen:

sudo cp -a /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig
sudo -u postgres tee /etc/pgbouncer/pgbouncer.ini >/dev/null <<'EOF'
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = tag12_pgbouncer
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)
admin_users = tag12_pgbouncer
pool_mode = transaction
max_client_conn = 500
default_pool_size = 10

[databases]
tag12 = host=127.0.0.1 port=5432 dbname=tag12
EOF

In userlist.txt steht nur auth_user. Naheliegend wäre sein SCRAM-Hash aus pg_authid – probieren wir es:

psql -Atc "SELECT format('\"%s\" \"%s\"', rolname, rolpassword) FROM pg_authid
  WHERE rolname = 'tag12_pgbouncer'" \
  | sudo -u postgres tee /etc/pgbouncer/userlist.txt >/dev/null
sudo systemctl restart pgbouncer

Auf pg2:

export PGPASSWORD=lab-tag12
B="host=172.16.0.182 port=6432 dbname=tag12 user=tag12_app"
psql "$B" -Atc 'SELECT current_user, inet_client_addr(), inet_server_port()'

Erwartet:

psql: error: connection to server at "172.16.0.182", port 6432 failed: FATAL:  server
login failed: wrong password type

Das Log auf pg1 erklärt es:

sudo grep 'cannot do SCRAM' /var/log/postgresql/pgbouncer.log | tail -1

Erwartet (gekürzt):

... ERROR S-0x5e17e82e3c80: tag12/[email protected]:5432 cannot do SCRAM
authentication: password is SCRAM secret but client authentication did not provide
SCRAM keys

Mit einem gespeicherten SCRAM-Hash kann PgBouncer sich nur dann beim Server anmelden, wenn vorher ein Client per SCRAM die Schlüssel mitgebracht hat. auth_user meldet sich aber selbst an, bevor es Clients gibt – er braucht sein Klartext-Passwort. Die Datei gehört postgres und ist für andere Benutzer nicht lesbar (640):

echo '"tag12_pgbouncer" "lab-tag12-bouncer"' \
  | sudo -u postgres tee /etc/pgbouncer/userlist.txt
sudo systemctl reload pgbouncer

Auf pg2 – nach einem Fehlschlag merkt sich PgBouncer den Fehler bis zu 15 s lang (server_login_retry), also kurz warten:

psql "$B" -Atc 'SELECT current_user, inet_client_addr(), inet_server_port()'

Erwartet:

tag12_app|127.0.0.1|5432

Für PostgreSQL kommt tag12_app von 127.0.0.1 – die Client-IP von pg2 sieht nur PgBouncer. pg_hba.conf musste dafür nicht angefasst werden: Die Zeile für 127.0.0.1 mit scram-sha-256 gab es schon.

Fortschritt speichern? Kostenlos registrieren

Drill 3: Admin-Konsole

Die virtuelle Datenbank pgbouncer versteht SHOW-Befehle (kein SQL):

export PGPASSWORD=lab-tag12-bouncer
A="host=127.0.0.1 port=6432 dbname=pgbouncer user=tag12_pgbouncer"
psql "$A" -Atc 'SHOW CONFIG' \
  | grep -E '^(pool_mode|max_prepared_statements|server_reset_query)\|'
psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10

Erwartet (gekürzt):

max_prepared_statements|200|200|yes
pool_mode|transaction|session|yes
server_reset_query|DISCARD ALL|DISCARD ALL|yes
  database  |      user       | cl_active | cl_waiting | sv_active | sv_idle
------------+-----------------+-----------+------------+-----------+---------
 pgbouncer  | pgbouncer       |         1 |          0 |         0 |       0
 tag12      | tag12_app       |         0 |          0 |         0 |       1
 tag12      | tag12_pgbouncer |         0 |          0 |         0 |       1

SHOW CONFIG zeigt aktuellen Wert, Standard und ob er per Reload änderbar ist. Ein Pool gehört zu einem Paar aus Datenbank und Rolle; cl_ sind Clients, sv_ Server-Verbindungen. Weitere nützliche Befehle: SHOW CLIENTS, SHOW SERVERS, SHOW STATS, RELOAD, PAUSE/RESUME (für Wartung), RECONNECT.

Fortschritt speichern? Kostenlos registrieren

Drill 4: max_client_conn gegen default_pool_size

Zwei Grenzen: max_client_conn (500) begrenzt die Clients an PgBouncer, default_pool_size (10) die Server-Verbindungen pro Pool. Die Summe aller Pools muss unter max_connections von PostgreSQL bleiben. Zum Vergleich begrenzen wir tag12_app direkt am Server auf 20 Verbindungen:

psql -c 'ALTER ROLE tag12_app CONNECTION LIMIT 20'
PGPASSWORD=lab-tag12 pgbench -h 127.0.0.1 -p 5432 -U tag12_app -c 50 -j 4 -T 15 tag12 \
  2>&1 | grep -m1 FATAL

Erwartet (gekürzt):

pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  too
many connections for role "tag12_app"

Dieselben 50 Clients von pg2 über PgBouncer:

pgbench -h 172.16.0.182 -p 6432 -U tag12_app -c 50 -j 4 -T 30 tag12 \
  | grep -E 'failed|latency average|^tps'

Während der Test läuft, auf pg1:

psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10 | grep -E 'database|tag12_app'
psql -Atc "SELECT count(*) FROM pg_stat_activity WHERE usename = 'tag12_app'"

Erwartet (gekürzt):

  database  |      user       | cl_active | cl_waiting | sv_active | sv_idle
 tag12      | tag12_app       |        10 |         40 |        10 |       0
10

Und auf pg2 am Ende:

number of failed transactions: 0 (0.000%)
latency average = 92.426 ms
tps = 540.973176 (without initial connection time)

50 Clients, 10 Server-Prozesse, 40 warten kurz in der Schlange – und kein einziger Fehler. Genau dafür ist der Pooler da: Die Datenbank arbeitet mit wenigen Prozessen effizient, die Warteschlange liegt im billigen PgBouncer.

Fortschritt speichern? Kostenlos registrieren

Drill 5: Lasttest mit Verbindungsaufbau

Am meisten bringt PgBouncer Anwendungen, die für jede Anfrage neu verbinden (PHP, Skripte, Serverless). pgbench -C simuliert das – einmal direkt, einmal über PgBouncer, beide Male von pg1 über TCP:

export PGPASSWORD=lab-tag12
pgbench -h 127.0.0.1 -p 5432 -U tag12_app -C -S -c 20 -j 4 -T 20 tag12 \
  | grep -E 'average connection|^tps'
pgbench -h 127.0.0.1 -p 6432 -U tag12_app -C -S -c 20 -j 4 -T 20 tag12 \
  | grep -E 'average connection|^tps'

Erwartet (Werte schwanken je nach Last):

average connection time = 65.787 ms
tps = 60.556349 (including reconnection times)
average connection time = 44.870 ms
tps = 88.845360 (including reconnection times)

Über PgBouncer entfällt der Prozessstart und die zweite Anmeldung am Server, die Server-Verbindung ist schon warm. Den großen Rest frisst SCRAM: 4096 Hash-Runden pro Anmeldung, die auch PgBouncer prüfen muss. Ohne -C (Verbindung bleibt offen) misst pgbench nur noch die Abfragen – dann liegen direkt und gepoolt nah beieinander.

Fortschritt speichern? Kostenlos registrieren

Drill 6: Fallen im Transaction-Modus

Drei Pool-Modi nebeneinander: Ein Eintrag in [databases] darf den Modus überschreiben. Anhängen und neu laden:

cat <<'EOF' | sudo -u postgres tee -a /etc/pgbouncer/pgbouncer.ini
tag12_session = host=127.0.0.1 port=5432 dbname=tag12 pool_mode=session
tag12_stmt = host=127.0.0.1 port=5432 dbname=tag12 pool_mode=statement
EOF
sudo systemctl reload pgbouncer
PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'SHOW DATABASES' | cut -d'|' -f1,6,10 \
  | grep -E 'name|tag12'

Erwartet:

     name      | pool_size | pool_mode
 tag12         |        10 |
 tag12_session |        10 | session
 tag12_stmt    |        10 | statement

Leer heißt: globaler Wert (transaction). Jetzt auf pg2 – im Hintergrund läuft Last, damit die Server-Verbindungen wirklich wechseln:

pgbench -h 172.16.0.182 -p 6432 -U tag12_app -S -c 30 -j 4 -T 15 tag12 \
  >/dev/null 2>&1 &
psql "$B" -c "SET work_mem = '1GB'" -c 'SHOW work_mem' -c 'SHOW work_mem' \
  -c 'SHOW work_mem' | grep -E 'MB|GB'
psql "$B" -c 'PREPARE q(int) AS SELECT abalance FROM pgbench_accounts WHERE aid = $1' \
  -c 'EXECUTE q(1)' -c 'EXECUTE q(2)' -c 'EXECUTE q(3)' 2>&1 \
  | grep -E 'PREPARE|ERROR|^ +0'
wait

Erwartet (Muster variiert):

 1GB
 4MB
 4MB
PREPARE
ERROR:  prepared statement "q" does not exist
ERROR:  prepared statement "q" does not exist
        0

Jedes -c ist eine eigene Transaktion und landet auf irgendeiner Server-Verbindung. Schlimmer als das verlorene SET: Die Verbindung mit work_mem = 1GB geht jetzt an fremde Clients weiter – PgBouncer setzt im Transaction-Modus nichts zurück (server_reset_query gilt nur im Session-Modus). Der Gegencheck mit session:

psql "host=172.16.0.182 port=6432 dbname=tag12_session user=tag12_app" \
  -c "SET work_mem = '1GB'" -c 'SHOW work_mem' -c 'SHOW work_mem' | grep -E 'MB|GB'
psql "host=172.16.0.182 port=6432 dbname=tag12_stmt user=tag12_app" \
  -c 'BEGIN' -c 'SELECT 1' 2>&1 | head -1

Erwartet:

 1GB
 1GB
FATAL:  transaction blocks not allowed in statement pooling mode

Regeln für den Transaction-Modus:

  • Sitzungseinstellungen nur als SET LOCAL innerhalb einer Transaktion oder per ALTER ROLE … SET – nie nacktes SET.
  • Prepared Statements nur auf Protokollebene (Treiber wie pgx, JDBC, psycopg 3): PgBouncer verfolgt sie seit 1.21 selbst, wenn max_prepared_statements > 0 ist (Standard 200). SQL-PREPARE/EXECUTE funktioniert nicht.
  • Kein LISTEN, keine Session-Advisory-Locks, keine temporären Tabellen über Transaktionsgrenzen.

Den Nachweis für Protokoll-Prepared-Statements liefert pgbench -M prepared:

pgbench -h 172.16.0.182 -p 6432 -U tag12_app -M prepared -S -c 20 -j 4 -T 10 tag12 \
  | grep -E 'failed|^tps'
unset PGPASSWORD

Erwartet (gekürzt):

number of failed transactions: 0 (0.000%)
tps = 4471.762379 (without initial connection time)

Auf pg1 die verschmutzte Verbindung entsorgen – RECONNECT schließt die Server-Verbindungen des Pools nach ihrer laufenden Transaktion:

PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'RECONNECT tag12'

Erwartet: RECONNECT

Fortschritt speichern? Kostenlos registrieren

Übung: Speed-Drills

  1. Zeige alle Pools mit wartenden Clients. Zeitziel: 30 s
  2. Zeige, wie viele Transaktionen PgBouncer für tag12 vermittelt hat. Zeitziel: 30 s
  3. Lade die PgBouncer-Konfiguration neu, ohne Clients zu trennen. Zeitziel: 10 s
  4. Finde im PgBouncer-Log den letzten Anmeldefehler. Zeitziel: 30 s

Lösung

export PGPASSWORD=lab-tag12-bouncer
psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4
psql "$A" -c 'SHOW STATS' | cut -d'|' -f1,3 | grep -E 'database|tag12 '
sudo systemctl reload pgbouncer
sudo grep -E 'failed|error' /var/log/postgresql/pgbouncer.log | tail -1
unset PGPASSWORD

Erwartet (gekürzt):

   database    | total_xact_count
 tag12         |           248814

systemctl reload schickt SIGHUP, genau wie RELOAD in der Admin-Konsole.

Fortschritt speichern? Kostenlos registrieren

Kontrollpunkt

systemctl is-active pgbouncer

Erwartet: active

sudo ss -tlnp | grep 6432

Erwartet: 0.0.0.0:6432 und [::]:6432.

PGPASSWORD=lab-tag12-bouncer psql "$A" -Atc 'SHOW CONFIG' | grep '^pool_mode|'

Erwartet: pool_mode|transaction|session|yes

Auf pg2:

PGPASSWORD=lab-tag12 psql "$B" -Atc 'SELECT inet_client_addr()'

Erwartet: 127.0.0.1 – PostgreSQL sieht PgBouncer, nicht pg2.

Challenge

Verkleinere den Pool für neue Server-Verbindungen auf 5 – ohne Neustart und ohne die Datei zu ändern – und weise unter Last von 30 Clients nach, dass nie mehr als 5 Server-Verbindungen aktiv sind.

Hinweis: Die Admin-Konsole kennt SET für Konfigurationsparameter.

Lösung

PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'SET default_pool_size = 5'

Auf pg2:

PGPASSWORD=lab-tag12 pgbench -h 172.16.0.182 -p 6432 -U tag12_app -S -c 30 -j 4 \
  -T 10 tag12 >/dev/null 2>&1 &

Auf pg1, während der Test läuft:

PGPASSWORD=lab-tag12-bouncer psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10 \
  | grep -E 'database|tag12_app'

Erwartet:

   database    |      user       | cl_active | cl_waiting | sv_active | sv_idle
 tag12         | tag12_app       |         9 |         21 |         5 |       0

SET in der Admin-Konsole gilt bis zum nächsten Neustart oder RELOAD – dann zählt wieder die Datei. Dauerhaft: pool_size=5 am Datenbankeintrag oder default_pool_size in pgbouncer.ini.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

PgBouncer zurück auf den Paketzustand (nur localhost, keine Datenbanken), Testdaten und Rollen weg:

sudo cp -a /etc/pgbouncer/pgbouncer.ini.orig /etc/pgbouncer/pgbouncer.ini
sudo -u postgres truncate -s 0 /etc/pgbouncer/userlist.txt
sudo rm /etc/pgbouncer/pgbouncer.ini.orig
sudo systemctl restart pgbouncer
dropdb tag12
psql -c 'DROP ROLE tag12_app' -c 'DROP ROLE tag12_pgbouncer'
sudo ss -tlnp | grep 6432

Erwartet (gekürzt):

DROP ROLE
DROP ROLE
LISTEN 0  128   127.0.0.1:6432   0.0.0.0:*  users:(("pgbouncer",pid=...,fd=9))
LISTEN 0  128       [::1]:6432      [::]:*  users:(("pgbouncer",pid=...,fd=8))

Interview-Notiz

  • „Warum nicht einfach max_connections auf 2000?“ – Jede Verbindung ist ein Prozess mit eigenem Speicher; viele aktive Prozesse kämpfen um CPU und Sperren. Durchsatz steigt bis etwa einige Verbindungen pro CPU-Kern, danach sinkt er. Ein Pooler hält die Zahl klein.
  • „Session- oder Transaction-Modus?“ – Transaction für Web-Anwendungen mit kurzen Transaktionen; Session, wenn die Anwendung Sitzungszustand braucht (SET, LISTEN, Advisory Locks, temporäre Tabellen).
  • „Wie authentifiziert PgBouncer gegen PostgreSQL?“ – Mit auth_user und auth_query holt er die SCRAM-Hashes; die Client-Anmeldung per SCRAM liefert die Schlüssel, mit denen er sich beim Server anmeldet.

Enterprise-Notiz

  • PgBouncer nah an der Anwendung (Sidecar, eigener Dienst) oder vor der Datenbank; mehrere Instanzen hinter einem Load Balancer, so_reuseport für mehrere Prozesse auf einem Host (PgBouncer ist single-threaded).
  • TLS auf beiden Seiten: client_tls_sslmode/client_tls_cert_file für Clients, server_tls_sslmode = verify-full zur Datenbank.
  • auth_user-Passwort aus einem Secret-Store; alternativ auth_type = cert oder hba mit eigener auth_hba_file.
  • Überwachen: SHOW POOLS (cl_waiting, maxwait) per pgbouncer_exporter; ein wachsendes maxwait heißt, der Pool ist zu klein oder die Abfragen zu langsam.
  • In Kubernetes übernimmt der CNPG-Pooler genau diese Rolle (Tag 17).