Technology · PRACTICAL GUIDE
PostgreSQL says “remaining connection slots are reserved”? Build an owner ledger before killing sessions
Prove the user admission budget, group backends by application and age, freeze new churn, then end only a verified stale owner or resize the pool. Do not begin by raising max_connections.
This FATAL message is an admission decision, not proof that every backend is a leak. PostgreSQL protects reserved slots after ordinary user capacity is consumed; the remaining work may belong to active requests, idle pool members, idle transactions, replication or internal processes.
Preserve the error and time window, stop new connection churn if you can, then build a read-only owner ledger. The ledger—not a PID list alone—decides whether to wait, cancel a query, terminate a verified stale session, reduce pool multiplication or change capacity.
Prove the admission budget
Record the exact message, server version, provider, database endpoint and first failure time. From an authorized emergency connection, read max_connections, reserved_connections and superuser_reserved_connections; do not assume provider defaults or subtract only one reserve.
Calculate the ordinary-user budget and current headroom. Separate client backends from internal processes and replication. If no authorized emergency route exists, use the provider's documented control plane or support path instead of repeatedly opening more clients.
Build an owner ledger, not a PID dump
Group pg_stat_activity by database, user, application_name, client address, backend type and state. Retain counts plus oldest backend_start, xact_start and state_change so a twenty-session pool created five minutes ago is distinguishable from one abandoned for days.
Weak or empty application_name values are an observability gap. Correlate client addresses, deployment replica counts and pool configuration before assigning an owner. Never treat a matching IP or an idle label alone as permission to terminate.
Classify work before acting
Active backends may be serving requests; inspect age, wait event and owner. Idle backends may be healthy pool inventory. Idle in transaction can retain a transaction boundary and deserves rapid owner escalation, but age and state still do not prove abandonment.
First freeze the source of new churn: pause a runaway worker, cap autoscaling, reduce a retry storm or drain one replica according to the application's runbook. Otherwise every freed slot can be consumed before the ledger is complete.
Choose the narrowest recovery
Wait for short active work when safe. Cancel only a verified query when the application can handle cancellation. Terminate a backend only when the session owner, business impact and stale condition are confirmed and the runbook authorizes ending the whole session.
If many legitimate clients share too few server slots, reduce per-process pool sizes, bound connection lifetime and use an approved pooler. Sum pools across replicas, jobs, admin tools and migrations. Raising max_connections without memory and concurrency validation moves the failure boundary rather than fixing ownership.
Verify recovery and prevent recurrence
After the action, confirm ordinary-user headroom, successful new admission, stable active counts and no immediate refill. Compare the owner ledger before and after so the evidence identifies which pool or workload changed.
Finish with alerts on admission headroom, connection growth by owner, oldest transaction age and pool wait metrics. The exit condition is not merely a successful login: the leaking owner is stopped or bounded, active work is protected, and capacity remains stable under normal traffic.
