A disputed reading: PG19. No connection is refused any more and the memory rule holds, with the application still connected straight to the cluster and max_connections raised. Our checks ask for something between the application and the cluster; the prompt allows another endpoint and does not require one. As graded this is a failure; under the reading pg19_pooler_checks_only it passes.
- Job
- PG19 · Connection exhaustion: pooling with PgBouncer
- Model
- openai/gpt-5.6-luna
- Documentation tool
- absent
- Repeat
- 1
- Verdict as graded
- fail
- How it ended
- said the job was done
- Disputed reading
- pg19_pooler_checks_only
- Rig fault
- none
- Tokens
- 51,129
- Commands
- 11
- Agent time
- 1.1 min
The prompt
You are the on-call engineer for a PostgreSQL 17 server on this machine (node1, root over SSH). The application service `pgeval-workload` runs 60 workers. Each one opens a connection, runs two statements and disconnects, over and over. Right now many of those connections are being refused and the application is losing work. Make every application connection succeed, and keep it that way when the worker count grows again next month. Constraints: this machine's memory is what it is, so the cluster must stay within it. Every connection the server is willing to accept has to be affordable at the same time, with the memory settings you leave behind. The database must keep answering on the port and socket it uses today, because other things on this machine talk to it there. You may point the application at a different endpoint and restart it, but it must go on doing the same work as the same user against the same database, at the rate it does now. Do not delete or rewrite the application's data. The `postgres` OS account administers the cluster (peer authentication). The application's connection settings are in /etc/pgeval/workload.env.
What the agent did
Every command in the order sent, with the node it went to, and what the agent wrote between them. The rig did not record what each command printed, so this page cannot show it.
1.
node1
set -e printf '%s\n' '--- env'; cat /etc/pgeval/workload.env || true printf '%s\n' '--- service'; systemctl status --no-pager pgeval-workload || true printf '%s\n' '--- postgres'; systemctl status --no-pager postgresql || true printf '%s\n' '--- processes/listen'; ps aux | grep '[p]ostgres'; ss -ltnp | grep -E 'postgres|5432' || true printf '%s\n' '--- memory'; free -h; grep -E 'MemTotal|MemAvailable|SwapTotal' /proc/meminfo
2.
node1
sudo -u postgres psql -Atc "show max_connections; show superuser_reserved_connections; show shared_buffers; show work_mem; show maintenance_work_mem; show effective_cache_size; show listen_addresses; show port; show unix_socket_directories; select name,setting,unit,context,sourcefile,sourceline from pg_settings where name in ('max_connections','superuser_reserved_connections','shared_buffers','work_mem','maintenance_work_mem','effective_cache_size');"node1
cat /etc/postgresql/17/main/postgresql.conf; printf '\n--- hba ---\n'; cat /etc/postgresql/17/main/pg_hba.conf; printf '\n--- logs errors ---\n'; journalctl -u postgresql --since '-10 min' --no-pager | tail -100
node1
df -h /; systemctl show pgeval-workload -p MemoryMax -p TasksMax; tail -30 /var/log/pgeval/workload.log 2>/dev/null || true
3.
node1
free -h; grep MemTotal /proc/meminfo; command -v pgbouncer || true; dpkg -l | grep -E 'pgbouncer|postgresql' | head
node1
sudo -u postgres psql -Atc "select count(*), state from pg_stat_activity group by state; select rolname,rolsuper,rolcanlogin from pg_roles where rolname in ('app_rw','postgres'); select datname from pg_database;"; sudo -u postgres psql -d app -c '\dt'node1
systemctl list-unit-files | grep -Ei 'pgbouncer|pool'; grep -R "pgeval\|6432" /etc/systemd /etc 2>/dev/null | head -30
4.
node1
sed -n '1,260p' /opt/pgeval/workload.py; cat /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf; cat /etc/postgresql/17/main/conf.d/pgeval.conf
5.
node1
sed -n '100,240p' /opt/pgeval/workload.py; apt-cache policy pgbouncer | head
6.
node1
set -e cp -a /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf.bak.$(date +%s) cat > /etc/postgresql/17/main/conf.d/zz-pgeval-capacity.conf <<'EOF' # Capacity sizing: enough slots for the application storm and foreseeable growth, # while keeping per-backend memory bounded on this 8 GiB host. max_connections = 150 shared_buffers = 512MB work_mem = 4MB maintenance_work_mem = 32MB effective_cache_size = 5GB EOF # remove conflicting fixture file so there is one authoritative setting mv /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf /etc/postgresql/17/main/conf.d/zz-pgeval-fixture.conf.disabled systemctl restart postgresql sleep 2 sudo -u postgres psql -Atc "show max_connections; show shared_buffers; show work_mem; show maintenance_work_mem; show effective_cache_size; select count(*) from pg_stat_activity;" # verify app is succeeding and no current refusal after restart systemctl restart pgeval-workload sleep 5 printf '%s\n' '--- status'; systemctl is-active postgresql pgeval-workload printf '%s\n' '--- memory'; free -h printf '%s\n' '--- recent workload'; tail -20 /var/log/pgeval/workload.log
7.
node1
sleep 5; python3 - <<'PY'
from pathlib import Path
p=Path('/var/log/pgeval/workload.log')
lines=p.read_text().splitlines()
for n in (100,500):
x=lines[-n:]
print(n, 'errors',sum(' error ' in s for s in x),'connect errors',sum('connect ' in s and ' error ' in s for s in x),'lines',len(x))
PY
sudo -u postgres psql -x -c "select count(*) as connections from pg_stat_activity; select state,count(*) from pg_stat_activity group by state;"; grep -R 'zz-pgeval' /etc/postgresql/17/main/postgresql.auto.conf /etc/postgresql/17/main/conf.d 2>/dev/null || true8. Resolved the connection refusals and made the configuration persistent: - Increased PostgreSQL `max_connections` from 20 to 150. - Reduced per-connection memory exposure: - `work_mem = 4MB` - `maintenance_work_mem = 32MB` - Set `shared_buffers = 512MB` and `effective_cache_size = 5GB`. - Preserved the existing PostgreSQL port (`5432`) and Unix socket directory. - Restarted PostgreSQL and the workload service. - Verified both services are active. - Confirmed the workload is completing connections and statements successfully with no errors in recent logs. - Left the application using the same host, port, user, database, and data.
Its last message
Resolved the connection refusals and made the configuration persistent: - Increased PostgreSQL `max_connections` from 20 to 150. - Reduced per-connection memory exposure: - `work_mem = 4MB` - `maintenance_work_mem = 32MB` - Set `shared_buffers = 512MB` and `effective_cache_size = 5GB`. - Preserved the existing PostgreSQL port (`5432`) and Unix socket directory. - Restarted PostgreSQL and the workload service. - Verified both services are active. - Confirmed the workload is completing connections and statements successfully with no errors in recent logs. - Left the application using the same host, port, user, database, and data.
The checks
| Check | Node | Result | What it printed |
|---|---|---|---|
| the cluster answers and starts at boot | node1 | pass | [node1] answer=1 |
| the application service is still the one the fixture started | node1 | pass | [node1] service=active mode=connection-storm db=app user=app_rw pause_ms=unset hold_ms=unset |
| no application connection is refused any more | node1 | pass | [node1] window_since_handover_s=121 ok=48628 refused=0 other_errors=0 refusals_before=8480 |
| the application no longer connects straight to the cluster | node1 | fail | [node1] workload host=127.0.0.1 port=5432 dsn=unset cluster port=5432 socket_dir=/var/run/postgresql listener_on_workload_port=LISTEN 0 300 127.0.0.1:5432 0.0.0.0:* users:(("postgres",pid=28401,fd=7)) |
| the cluster is not carrying every worker at once | node1 | pass | [node1] peak_client_backends=59 max_connections=150 reserved=3 workers=60 |
| the memory the cluster promises still fits the machine | node1 | pass | [node1] promised=1266679808 budget=7314949323 ram=8127721472 shared_buffers=536870912 work_mem=4194304 maintenance_work_mem=33554432 max_connections=150 autovacuum_workers=3 pending_restart=none |
| the application's data is untouched | node1 | pass | [node1] table=app.tickets checksum=315c3088c9d84804584f5c1b29ad83ce expected=315c3088c9d84804584f5c1b29ad83ce rows=200000 expected_rows=200000 |