Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-connections~40 min

Nobody could connect, including the on-call engineer, and max_connections had already been raised twice that year

Reported symptoms

  • ●The application returns FATAL: sorry, too many clients already for every new connection
  • ●The on-call engineer cannot connect either, including by SSH to the database host and psql over the local socket
  • ●The cluster is not down - existing connections continue to work and queries on them complete normally
  • ●CPU is at 12 percent, disk is idle, and there is 40 GB of free memory
  • ●max_connections was raised from 200 to 500 in February and from 500 to 900 in June, each time in response to the same symptom
  • ●The incident began 40 seconds after a rolling restart of the application tier
  • ●Earlier in the same incident, some clients received a different message mentioning pg_use_reserved_connections

Evidence

  • · The server log contains three distinct refusal messages in sequence: first one naming pg_use_reserved_connections, then one naming the SUPERUSER attribute, then sorry, too many clients already
  • · pg_stat_activity taken from an existing session shows 897 client backends, of which 812 are in state idle
  • · Of those 812 idle sessions, 640 have a state_change older than 20 minutes
  • · The application runs 40 pods, each with a pool configured for a maximum of 25 connections, which is 1000 potential connections against max_connections of 900
  • · The pool minimum is also 25, so every pod opens its full allocation at start-up rather than growing into it
  • · The rolling restart brought new pods up before old pods had finished draining, so both generations held connections at once
  • · Average query duration across the cluster is 1.8 ms and the busiest table is 200 MB
  • · shared_buffers is 8 GB, work_mem is 64 MB, and the host has 64 GB of RAM
Diagnosis and resolutionclick to reveal

Root cause

The application requests more connections than the cluster is configured to accept, and it requests them all at once. Forty pods with a pool minimum and maximum of 25 is a demand of exactly 1000 connections against a `max_connections` of 900. That is a shortfall in steady state. During a rolling restart it is worse: new pods open their full 25 before old pods release theirs, so demand briefly approaches double. The connections are not doing work. 812 of 897 sessions are `idle` — not `idle in transaction`, simply `idle`, holding a slot and a backend process while the pool waits for the application to need them. 640 of those have been idle for over twenty minutes. The cluster is serving 1.8 ms queries against a 200 MB working set on an idle machine; it needs a small number of busy connections, not nine hundred quiet ones. Raising `max_connections` twice made the next occurrence worse rather than better. Each PostgreSQL connection is an operating system process with its own memory, and `work_mem` is a per-operation allowance, not a per-server one — so the ceiling on concurrent memory use rises with the setting. More importantly, raising the limit removed the backpressure that would have made the pool misconfiguration visible in February, and let it grow for another six months. The lockout is the part that turned a degradation into an incident with no diagnosis. PostgreSQL 18 reserves slots in tiers, and each tier produces its own message. Once every tier including the superuser reserve is consumed, **every** connection attempt is refused with `sorry, too many clients already` — including connections over the local Unix socket, and including superusers. There is no privileged transport that bypasses a slot limit.

Remediation

You need a connection and you cannot get one. Do not restart the cluster to obtain it — that is a full outage in exchange for a diagnosis you can get without one. Terminate a single backend from the operating system. `SIGTERM` to a backend process is exactly what `pg_terminate_backend()` sends, and it requires no connection at all: ```bash # as root or the postgres OS user on the database host ps -o pid,args -C postgres | grep 'postgres: app ' # then send SIGTERM to exactly one of the pids that listed kill -TERM 12345 ``` Never `SIGKILL` a backend. `kill -9` on a backend causes the postmaster to treat it as a crash, terminate every other session, and enter crash recovery — turning a connection shortage into a full restart. `SIGTERM` is the safe signal and it is the one the SQL function uses. With one slot free, connect over the local socket and measure before acting: ```sql SELECT state, count(*), count(*) FILTER (WHERE state_change < now() - interval '10 minutes') AS idle_over_10m FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY state ORDER BY 2 DESC; SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 20; ``` That second query names the pods holding the slots, which is what you need to make the fix at the right layer. Reclaim capacity by terminating long-idle sessions — not busy ones, and not arbitrarily: ```sql SELECT pg_terminate_backend(pid), usename, client_addr, now() - state_change AS idle_for FROM pg_stat_activity WHERE backend_type = 'client backend' AND state = 'idle' AND state_change < now() - interval '15 minutes'; ``` The pools will reopen them, so this buys minutes, not a fix. Use those minutes to reduce demand at the application: lower the pool maximum per pod so that `pods x pool_max` sits comfortably under `max_connections`, and set the pool **minimum** low so pods do not claim their full allocation at start-up. Do not raise `max_connections` again. It has been raised twice and each raise deferred the real fix.

Verification

New connections succeed, from the application and from an operator. `pg_stat_activity` shows a client backend count well below `max_connections`, with headroom that survives a rolling restart. Test that explicitly — perform a rolling restart and watch the count: ```sql SELECT count(*) FILTER (WHERE backend_type = 'client backend') AS backends, current_setting('max_connections')::int AS max_connections FROM pg_stat_activity; ``` The idle population is small and short-lived. Long-idle sessions are the capacity problem, so measure them directly rather than measuring the total: ```sql SELECT count(*) FROM pg_stat_activity WHERE state = 'idle' AND state_change < now() - interval '10 minutes'; ``` The server log contains no further refusal messages of any of the three kinds. An operator connection succeeds during peak load, using a role that holds `pg_use_reserved_connections`. This is the check that says the reserve tier is configured and works — it is worth testing once rather than discovering during the next incident that the reserve was zero.

Prevention

**Size the pools, not the server.** The number a cluster can serve well is a function of CPU and disk, not of what the pools ask for. A cluster answering 1.8 ms queries needs tens of active connections. `pods x pool_max` must be a number somebody has chosen, checked into configuration, and reviewed when pod counts change. **Set pool minimum low and maximum modest.** A pool minimum equal to its maximum means every pod claims its full allocation at start-up, which makes a rolling restart the worst moment of the day. **Put a connection pooler in front for high pod counts.** A pooler in transaction mode lets many client connections share few server connections — in a measured example, 100 clients were served by 11 backend connections. A pooler is a capacity-shaping tool; it is not a performance optimisation, and it will not make individual queries faster. **Configure the reserve tiers deliberately.** PostgreSQL 18 has two: `superuser_reserved_connections` and `reserved_connections`, the latter usable by any role granted `pg_use_reserved_connections`. Grant it to the operational role your team actually uses, so that "I cannot get in either" stops being part of the incident: ```sql GRANT pg_use_reserved_connections TO ops; ``` **Alert on connection utilisation as a ratio**, at a threshold that leaves room to act — 75 percent of `max_connections`, not 99 percent. Alert separately on the count of sessions idle for more than ten minutes, which is the leading indicator. **Set `idle_session_timeout` for application roles.** It closes sessions that have been idle outside a transaction, which caps the damage from a pool that never releases. Set it per role, not globally, so it does not disturb operator sessions: ```sql ALTER ROLE app SET idle_session_timeout = '10min'; ``` **Know the three refusal messages.** They tell you which tier is gone, and that is free diagnosis at the moment you have least time. **Never `kill -9` a backend.** It costs the whole cluster a crash recovery to save one session.

Reported symptoms

Every new connection returns FATAL: sorry, too many clients already.

The on-call engineer cannot connect either — not over the network, and not with psql over the local socket after SSH-ing to the database host.

The cluster is not down. Existing connections work; queries on them complete normally. CPU is at 12 percent, the disks are idle, and there is 40 GB of free memory.

max_connections was raised from 200 to 500 in February and from 500 to 900 in June, each time in response to this same symptom.

The incident began 40 seconds after a rolling restart of the application tier. Earlier in the same incident, some clients saw a different message mentioning pg_use_reserved_connections.

Evidence provided

Read-only / Safethree distinct refusals, in the order the cluster produced them
$ grep -oE 'FATAL:.*' /var/log/postgresql/postgresql-18-main.log | sort -u
FATAL:  remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
FATAL:  sorry, too many clients already

From an existing session: 897 client backends, 812 of them idle, and 640 of those idle for over twenty minutes.

The application runs 40 pods. Each pool is configured with a minimum and maximum of 25 — a demand of 1000 against a max_connections of 900. The rolling restart brought new pods up before old pods drained.

Average query duration across the cluster is 1.8 ms. The busiest table is 200 MB. shared_buffers is 8 GB on a 64 GB host.

Work the evidence before reading on

  1. Three different messages appeared. What does each one tell you?
  2. 812 sessions are idle, not idle in transaction. Does that distinction matter here?
  3. max_connections has been raised twice. What did each raise achieve?
  4. You have no connection and you need one. What are your options, and what does each cost?

Root cause

The demand is arithmetic, and it does not fit

Forty pods times a pool max of 25 is 1000 against 900. That is a shortfall in steady state. During a rolling restart it is worse: new pods open their full 25 before old pods release theirs, so demand briefly approaches double.

The pool minimum is also 25, so every pod claims its entire allocation the moment it starts. Nothing grows into its allocation; every pod arrives at full size.

The connections are not doing anything

812 of 897 sessions are idle — not idle in transaction, simply idle, holding a slot and a backend process while the pool waits for the application to need them. 640 have been that way for over twenty minutes.

The cluster is answering 1.8 ms queries against a 200 MB working set on a machine at 12 percent CPU. It needs a small number of busy connections, not nine hundred quiet ones.

Raising max_connections made the next occurrence worse

The lockout is why nobody could diagnose it

Resolution

You need a connection and you cannot get one. Do not restart the cluster to obtain it — that is a full outage in exchange for a diagnosis you can get without one.

Terminate a single backend from the operating system. SIGTERM to a backend is exactly what pg_terminate_backend() sends, and it needs no connection:

Service impact possiblefree one slot without a connection
$ ps -o pid,args -C postgres | head && kill -TERM 305
    110 postgres: app postgres 172.17.0.12(60026) SELECT
  112 postgres: app postgres 172.17.0.12(60030) SELECT
  118 postgres: app postgres 172.17.0.12(60042) SELECT
  ...
client_backends | max_connections 
-----------------+-----------------
            15 | 15
(1 row)

With one slot free, measure before acting:

SELECT state, count(*),
       count(*) FILTER (WHERE state_change < now() - interval '10 minutes') AS idle_over_10m
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY 2 DESC;

SELECT usename, application_name, client_addr, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 20;

The second query names the pods holding the slots, which is what you need to fix this at the right layer.

Reclaim capacity from long-idle sessions — not busy ones, and not arbitrarily:

SELECT pg_terminate_backend(pid), usename, client_addr,
       now() - state_change AS idle_for
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'idle'
  AND state_change < now() - interval '15 minutes';

The pools will reopen them, so this buys minutes rather than a fix. Use those minutes to reduce demand: lower the pool maximum so pods x pool_max sits comfortably under max_connections, and set the pool minimum low so pods do not claim their full allocation at start-up.

Do not raise max_connections again.

Verification

New connections succeed, from the application and from an operator.

Backend count sits well below max_connections, with headroom that survives a rolling restart — and the way to know that is to perform one and watch:

SELECT count(*) FILTER (WHERE backend_type = 'client backend') AS backends,
       current_setting('max_connections')::int AS max_connections
FROM pg_stat_activity;

The long-idle population is small, measured directly rather than as part of the total:

SELECT count(*) FROM pg_stat_activity
WHERE state = 'idle' AND state_change < now() - interval '10 minutes';

No further refusal messages of any of the three kinds appear in the log.

An operator connection succeeds during peak load, using a role holding pg_use_reserved_connections. Test that once, deliberately, rather than discovering during the next incident that the reserve was never configured.

Prevention

Size the pools, not the server. What a cluster can serve well is a function of CPU and disk, not of what the pools ask for. pods x pool_max must be a number somebody chose, checked in, and reviews when pod counts change.

Set pool minimum low and maximum modest. A minimum equal to the maximum makes a rolling restart the worst moment of the day.

Put a pooler in front at high pod counts. In transaction mode, many client connections share few server connections — in a measured example, 100 clients were served by 11 backend connections. A pooler shapes capacity; it does not make queries faster, and expecting it to will disappoint you.

Configure the reserve tiers deliberately, and grant the reserve to the role your team actually uses:

GRANT pg_use_reserved_connections TO ops;

Alert on connection utilisation as a ratio, at 75 percent rather than 99. Alert separately on sessions idle over ten minutes — that is the leading indicator.

Set idle_session_timeout per role, so a pool that never releases cannot accumulate indefinitely, without disturbing operator sessions:

ALTER ROLE app SET idle_session_timeout = '10min';

Know the three refusal messages, and never kill -9 a backend.