Fix PostgreSQL 'FATAL: remaining connection slots are reserved' Error

You hit the wall — here's the fix

You're staring at FATAL: remaining connection slots are reserved for non-replication superuser connections. Your app's down. The cause is simple: PostgreSQL maxed out its max_connections limit. Every slot is occupied, most by idle connections your app left hanging.

Here's the immediate fix that works right now:

-- Step 1: Find the PostgreSQL process ID (PID) of each idle connection
SELECT pid, usename, state, query FROM pg_stat_activity WHERE state = 'idle';

-- Step 2: Kill those idle connections one by one
SELECT pg_terminate_backend(pid);

-- Or do all idle ones in one shot (careful — this kills your own session too if it's idle)
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE state = 'idle' AND pid != pg_backend_pid();

That's it. Run that last query as a superuser (usually postgres) and you'll free up connections instantly. Your app will start accepting connections again within seconds.

Why this works

What's actually happening here is that PostgreSQL reserves a handful of connection slots — by default, 3 — exclusively for superuser connections. The error message says "remaining connection slots are reserved for non-replication superuser connections" because those 3 slots are the only ones left, and your app (a non-superuser) can't use them. That's by design: it lets an admin log in and clean up when things go sideways.

The function pg_terminate_backend(pid) sends a SIGTERM signal to the backend process. It's not a graceful shutdown — the backend drops whatever it's doing and dies. But for idle connections, there's nothing to lose. The connection pooler (if you have one) or your app will see the broken pipe and reconnect automatically.

The reason step 3 works is we filter out your own connection (pid != pg_backend_pid()). Without that, you'd kill the session running the query, and the operation would stop halfway through. That'd be bad.

One gotcha: if your app's connection pooler or ORM has a statement_timeout or idle_in_transaction_session_timeout set too high, connections can sit idle for hours. In that case, the fix only lasts until the pool fills up again. You'll need a permanent solution.

Less common variations of the same issue

Variation 1: The error appears during a sudden traffic spike

If you're running a web app that gets slammed — say, 1000 concurrent users on a PostgreSQL instance configured for 100 connections — you'll hit this error fast. The fix above works, but the real problem is you didn't plan for the load. You need to either increase max_connections or, better, add a connection pooler like PgBouncer between your app and the database.

Variation 2: The error shows up after deploying a new version of your app

You push code that opens a new connection per request instead of reusing one from a connection pool. Now every HTTP request creates a new database connection and never closes it. Classic leak. The fix is the same kill-all-idle query, but then you must hunt down the code that's leaking connections. Look for places where you psycopg2.connect() or new PDO() without a matching close() or without using a pool.

Variation 3: The error occurs on a shared PostgreSQL server (e.g., a managed cloud instance)

Some cloud providers (like Heroku's free tier) enforce a hard max_connections of 20. You can't change it. The only escape is to use a connection pooling add-on or upgrade to a plan with more connections. The kill query still works, but you'll hit the same ceiling again quickly if you have many apps or processes connecting.

Variation 4: The error persists even after killing all idle connections

If you kill every idle connection and still can't connect, check if you're actually a superuser. If your user doesn't have superuser privileges, you can't run pg_terminate_backend on connections owned by other users. You'll need a real superuser (like postgres) to do it. If you're the admin, verify you're logged in with the right role: SELECT current_user;

How to prevent this from happening again

Three concrete steps, in order of priority:

1. Set a sane idle_in_transaction_session_timeout. This is the single most effective setting. It kills idle connections after a specified time. Set it to 30 seconds in your postgresql.conf:

   idle_in_transaction_session_timeout = 30000

   That's 30 seconds. Any connection sitting in an open transaction with no activity gets killed automatically.
2. Use a connection pooler. PgBouncer or Pgpool-II sit between your app and PostgreSQL. They multiplex many client connections over a small number of database connections. For example, 200 app connections become 20 database connections. This prevents max_connections from being reached in the first place. Configure PgBouncer with a pool_size of 10-20 and max_client_conn of 200.
3. Increase max_connections as a last resort. Don't just bump it to 1000. Each connection uses memory — about 2-10 MB per connection depending on your settings. At 1000 connections, that's potentially 10 GB of RAM just for connection overhead. That'll crash your server. Instead, raise it modestly (say from 100 to 200) while implementing steps 1 and 2.

Here's what a practical postgresql.conf section looks like:

# Connection settings
max_connections = 200
idle_in_transaction_session_timeout = 30000
statement_timeout = 60000  # optional: kill queries running longer than 60 seconds

Also, audit your application code. If you're using an ORM like SQLAlchemy or Django ORM, they already have connection pool built in. Make sure you're using it and not bypassing it. For raw drivers (psycopg2, pgx, node-postgres), enable the built-in pooling or wrap them with a pooler library.

One final opinionated take: never rely on pg_terminate_backend as a habit. It's a sledgehammer. If you're running it daily, something is fundamentally broken in your application's connection management. Fix the root cause — the idle connections should never accumulate.