Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

New connections to RDS PostgreSQL are refused because every normal slot is taken

ErrorFATAL: remaining connection slots are reserved for non-replication superuser connections

Short answer

PostgreSQL keeps the last few connection slots for superusers, so when ordinary clients have used max_connections minus that reserve, new ones are refused. On RDS, see who holds the slots in pg_stat_activity, close the idle ones, put a pool or RDS Proxy in front, and only then raise max_connections or move to a larger instance class.

  • Applies to Amazon RDS for PostgreSQL 17, PostgreSQL 18 manual
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Connect as the master user, which can still use the reserved slots, and count sessions by user, application and state. The group with the most rows is where to look.

    SHOW max_connections;
    SELECT usename, application_name, state, count(*)
    FROM pg_stat_activity
    GROUP BY 1, 2, 3
    ORDER BY count(*) DESC;
  2. 2Match what you see, then jump to its fix

    1. 1Most rows show state idle or idle in transaction, from one application_nameIdle or leaked sessions from application code are holding the slotsFix
    2. 2Most sessions are active or normal idle from many app servers, and the total sits at max_connections minus the reserveToo many clients, each with its own connection, for this instance classFix

What does the error look like?

  • FATAL: remaining connection slots are reserved for non-replication superuser connections
  • New connections fail while existing ones keep working, and only the master user can still log in

Cause 1 of 2

Idle or leaked sessions from application code are holding the slots

1Check

A session in state idle in transaction is inside a transaction but not running a query. A leaking app or pool leaves many of these open. Sort the oldest by when their state last changed.

SELECT pid, usename, application_name, client_addr, state, state_change
FROM pg_stat_activity
WHERE state LIKE 'idle%'
ORDER BY state_change
LIMIT 20;

2Fix

Close the stale sessions now, then fix the code or pool that opens them. Use pg_terminate_backend with the pid; the RDS master user may use it. To stop it coming back, set idle_in_transaction_session_timeout for the application's role (0 means off, the default). Be careful with idle_session_timeout on connections that go through a pooler.

SELECT pg_terminate_backend(12345);
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

3Verify

Count the sessions again; the idle group should shrink and stay small.

SELECT count(*) FROM pg_stat_activity;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 2

Too many clients, each with its own connection, for this instance class

1Check

On RDS for PostgreSQL the default max_connections comes from the instance memory (LEAST of memory divided by 9531392 and 5000), so a small instance class has a small limit. Each connection uses memory, so raising the number blindly can hurt more than it helps.

SHOW max_connections;

2Fix

Put connection pooling between the app and the database: Amazon recommends RDS Proxy when apps open and close connections often or keep many long-lived ones. Lower each app's pool size so all pools together stay under the limit. If load really needs more, choose an instance class with more memory, or set max_connections in a custom parameter group; static parameters take effect after a reboot.

aws rds modify-db-instance --db-instance-identifier mydb \
  --db-instance-class db.m6g.large --apply-immediately

3Verify

Run the normal load and watch the session count against the limit.

SELECT count(*) AS sessions, current_setting('max_connections') AS max FROM pg_stat_activity;

Confirmed by docs.aws.amazon.com

Did this fix it?

How do I stop it coming back?

  • Add up the pool sizes of every app server and keep the sum below max_connections minus the reserved slots.
  • Use RDS Proxy or a pooler for serverless functions and bursty workers.
  • Close connections in a finally block, and set idle_in_transaction_session_timeout for application roles.
  • Alert on the DatabaseConnections metric before the limit is reached.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.