Every connection slot is taken

Lab machine

A private Linux machine with the problem already set up. Sessions last up to 60 minutes.

Sal Brennan opened INC-2537 at 07:50SEV-2

The Ledger settlement batch has failed every run since 02:10. The database is up and idle, yet it refuses Ledger with "remaining connection slots are reserved".

Ledger books what Northstar owes each carrier. If the batch does not run, carriers are paid late and invoice Northstar for it.

"Postgres has 20 connections and that box has 2 GB of RAM, so no, we are not raising max_connections to 500 again." (Mara)

Each PostgreSQL connection is a server process with its own memory. The server keeps a few slots for superusers, which is how you can still get in.

Your task

Get the Ledger batch booking again, and make sure the client that caused this cannot take the whole server down the next time it restarts. Keep max_connections at 20 and keep the reconciler running.

On the machine

bin/hostlog -u ledger-settle, var/log/postgresql.log
psql -c "SELECT usename, state, xact_start, query FROM pg_stat_activity"
opt/reconciler/reconciler.py, bin/hostctl list-units

Timeline

01:45Docklight reconciler 3.2.1 deployed.

02:10Ledger batch: "remaining connection slots are reserved". Every run since.

07:30"We are not raising max_connections to 500 again." (Mara)

07:50INC-2537 lands with you. Carriers invoice on Monday.

Done when

The Ledger batch connects and books settlements.
A restarted reconciler cannot use up the server's connections again.
max_connections stays at 20, and the reconciler keeps running.

How it is checked:The checker books a settlement as `ledger`, requires max_connections of 20 and the reconciler running and enabled, restarts the reconciler, waits 25 seconds while it leaks a session every second, and then requires `ledger` to connect and book again.

Passed. Here is how others fixed it

The fix we expected. As `postgres`, run `ALTER ROLE reconciler CONNECTION LIMIT 5;` (and ideally `ALTER ROLE reconciler SET idle_in_transaction_session_timeout = '30s';`), then end its existing sessions with `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'reconciler';`. Ledger connects again, and file a bug for the missing COMMIT.

Other fixes that also pass:

`ALTER ROLE reconciler SET idle_in_transaction_session_timeout = '5s';` then terminate its sessions. The server ends each leaked session before many pile up.

Fixes that look right but are not:

Raising max_connections, which needs a restart, costs memory, and only postpones the outage.
Terminating the sessions once and declaring victory.
Stopping the reconciler, which Docklight needs.

Why it broke. The reconciler opens a session every second, starts a transaction, and never commits or disconnects, so each pass leaves a session "idle in transaction". With max_connections at 20 and three slots kept for superusers, seventeen leaked sessions lock every ordinary role out. Raising the limit only delays the leak and costs memory. A per-role connection limit, or a short idle-in-transaction timeout, contains it until the code is fixed.

How Mara worked it

psql -c "SELECT usename, state, count(*) FROM pg_stat_activity GROUP BY 1, 2 ORDER BY 3 DESC"
Use the superuser slot to see who holds the connections and in which state.
psql -c "SELECT pid, xact_start, query FROM pg_stat_activity WHERE state = 'idle in transaction'"
Old open transactions from one role point at a client that never commits.
ALTER ROLE reconciler CONNECTION LIMIT 5;
Contain the leaking client so it cannot starve everyone else.
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'reconciler';
Free the slots it holds now; the limit stops it taking them back.
psql -U ledger -d ledger -c "SELECT 1" and watch bin/hostlog -u ledger-settle
Confirm Ledger books again, then file the missing COMMIT as a bug.

Hints

Hint 1

You connect as a superuser, so you get a reserved slot. Use it to look at pg_stat_activity.

Hint 2

Group the sessions by user and state. "idle in transaction" is a session holding a transaction open.

Hint 3

Terminating the sessions frees slots for about as long as the reconciler takes to open new ones.

Hint 4

PostgreSQL can limit connections per role, and can end sessions that sit idle in a transaction.

Show the solution

As `postgres`, run `ALTER ROLE reconciler CONNECTION LIMIT 5;` (and ideally `ALTER ROLE reconciler SET idle_in_transaction_session_timeout = '30s';`), then end its existing sessions with `SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'reconciler';`. Ledger connects again, and file a bug for the missing COMMIT.

Databases for Operators

Connections, sessions, and transactions

Every connection is a server process. Read pg_stat_activity to see who holds them and why, and contain clients that leak.

A connection is a process

When a client connects, PostgreSQL forks a backend process for it. That process holds memory for sorting, hashing, and caching, whether the client is busy or not. max_connections is therefore a capacity limit. A small host with a large limit does not get more throughput, it gets swapping. The last superuser_reserved_connections slots are kept for superusers, so when ordinary roles are refused an administrator can still connect and investigate. That is why, during a "too many clients" incident, your psql as postgres still works.

Read before you kill

pg_stat_activity has one row per backend. Group it by usename, application_name, and state, and look at xact_start to see how long each transaction has been open:

SELECT usename, state, count(*), min(xact_start) AS oldest
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY 1, 2 ORDER BY 3 DESC;

idle means connected and doing nothing, which is cheap but still occupies a slot. idle in transaction means a transaction is open and the server is waiting for the client to send the next statement or a COMMIT. Such a session keeps its slot, holds any row locks it took, and pins an old snapshot that stops VACUUM from cleaning up. Dozens of them from one role usually mean a code path that forgets to commit or to return connections to a pool.

Contain the client, then fix it

pg_terminate_backend(pid) ends a session at once. It frees slots, but a leaking client opens new sessions just as fast. Containment means the server enforces limits on the client:

ALTER ROLE reconciler CONNECTION LIMIT 5 caps how many sessions that role may hold. When it hits the cap, only that client gets errors.
ALTER ROLE reconciler SET idle_in_transaction_session_timeout = '30s' makes the server end that role's sessions that sit in an open transaction for too long.
A connection pooler in front of many application servers keeps the number of real backends small and steady.

Raising max_connections is the last resort. It needs a restart, costs memory on every connection, and a leak fills whatever number you pick. Containment buys time. The real fix is in the client, so file the bug with the evidence from pg_stat_activity.

Key terms

Backend: The server process PostgreSQL starts for each client connection.
Idle in transaction: A session that began a transaction and is waiting for the client. It holds its slot, its snapshot, and any locks.
Reserved connections: Slots only superusers may use, so an administrator can still log in when ordinary roles cannot.
Connection pooler: A proxy such as PgBouncer that shares a few server connections among many clients.

Read further

PostgreSQL Documentation, version 17, Chapter "Server Configuration", sections "Connections and Authentication" and "Client Connection Defaults" (Free online (PostgreSQL Licence))
max_connections, superuser_reserved_connections, and the timeouts that end idle or stuck sessions.
PostgreSQL Documentation, version 17, Chapter "Monitoring Database Activity", the pg_stat_activity view (Free online (PostgreSQL Licence))
The state column, xact_start and state_change, and how to group sessions to find the client that holds them.
PostgreSQL Documentation, version 17, SQL command reference, ALTER ROLE (Free online (PostgreSQL Licence))
CONNECTION LIMIT and per-role settings such as idle_in_transaction_session_timeout.

Every connection slot is taken INC-2537