Tag 12: Connection Pooling mit PgBouncer
Ziel: PgBouncer läuft auf
pg1, authentifiziert per SCRAM über eineauth_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 6432Erwartet (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 tag12Damit 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
EOFIn 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 pgbouncerAuf 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 -1Erwartet (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 pgbouncerAuf 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,10Erwartet (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 FATALErwartet (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'
waitErwartet (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 -1Erwartet:
1GB
1GB
FATAL: transaction blocks not allowed in statement pooling mode
Regeln für den Transaction-Modus:
- Sitzungseinstellungen nur als
SET LOCALinnerhalb einer Transaktion oder perALTER ROLE … SET– nie nacktesSET. - 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/EXECUTEfunktioniert 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 PGPASSWORDErwartet (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
- Zeige alle Pools mit wartenden Clients. Zeitziel: 30 s
- Zeige, wie viele Transaktionen PgBouncer für
tag12vermittelt hat. Zeitziel: 30 s - Lade die PgBouncer-Konfiguration neu, ohne Clients zu trennen. Zeitziel: 10 s
- 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 PGPASSWORDErwartet (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 pgbouncerErwartet: active
sudo ss -tlnp | grep 6432Erwartet: 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 6432Erwartet (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_userundauth_queryholt 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_reuseportfür mehrere Prozesse auf einem Host (PgBouncer ist single-threaded). - TLS auf beiden Seiten:
client_tls_sslmode/client_tls_cert_filefür Clients,server_tls_sslmode = verify-fullzur Datenbank. auth_user-Passwort aus einem Secret-Store; alternativauth_type = certoderhbamit eigenerauth_hba_file.- Überwachen:
SHOW POOLS(cl_waiting,maxwait) perpgbouncer_exporter; ein wachsendesmaxwaitheißt, der Pool ist zu klein oder die Abfragen zu langsam. - In Kubernetes übernimmt der CNPG-
Poolergenau diese Rolle (Tag 17).