Fix PostgreSQL "Too Many Clients Already" Errors

Diagnose and fix the PostgreSQL FATAL: sorry, too many clients already error by finding idle backends, tuning max_connections and adding a connection pooler.

  • Intermediate
  • Linux VPS
  • 3 min read

By RunRacks Team · Published

PostgreSQL returns FATAL: sorry, too many clients already when the number of active backends reaches max_connections, so every new connection is refused until a slot frees up. The fix is rarely "make the number bigger" — it is finding the sessions holding slots open and putting a pooler in front of the database so your application stops opening one connection per request.

This guide walks through counting connections, clearing idle backends, raising max_connections without running the server out of memory, and adding PgBouncer on a Linux VPS.

Before you start

You need:

  • A Linux VPS running PostgreSQL on Ubuntu 22.04/24.04, Debian 12, AlmaLinux 8–9 or Rocky 8–9.
  • sudo or root access, and the ability to restart the PostgreSQL service.
  • psql access as the postgres superuser, or a role with the pg_monitor role granted.
  • Knowledge of how your application connects — host, port, database name and whether it uses its own connection pool.

Confirm the server version and current limit first:

Terminal
sudo -u postgres psql -c "SELECT version();"
sudo -u postgres psql -c "SHOW max_connections;"

Diagnose PostgreSQL too many clients already

Start by seeing how many backends exist and who owns them.

SQL
SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity;

Then group by user, state and client address to find the source:

SQL
SELECT usename,
       application_name,
       client_addr,
       state,
       count(*) AS connections
FROM pg_stat_activity
GROUP BY 1, 2, 3, 4
ORDER BY connections DESC
LIMIT 20;

What you are looking for:

  • A single client_addr with hundreds of connections usually means one app server is not pooling.
  • A large idle count means connections are opened and never closed.
  • idle in transaction is worse: those sessions hold locks and block vacuum.

List the oldest idle sessions with their last query:

SQL
SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - state_change AS idle_for,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state <> 'active'
ORDER BY state_change
LIMIT 25;

Clear idle backends

If you need breathing room right now, terminate the sessions that have been idle the longest. Terminating a backend rolls back its open transaction, so only target sessions that are genuinely idle.

SQL
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '15 minutes'
  AND pid <> pg_backend_pid();

For sessions stuck in a transaction, use pg_cancel_backend(pid) first — it cancels the running query but leaves the session connected. If the session does not clear, terminate it.

To stop idle sessions accumulating again, set timeouts in postgresql.conf or via ALTER SYSTEM. idle_session_timeout exists in PostgreSQL 14 and later; idle_in_transaction_session_timeout works on older releases too.

SQL
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET idle_session_timeout = '30min';
SELECT pg_reload_conf();

Raise max_connections safely

Raising the limit is a valid short-term move, but every connection costs memory. Each backend uses memory for sorting and joins, so max_connections multiplied by work_mem is a useful worst-case estimate.

Check the current values:

SQL
SHOW max_connections;
SHOW superuser_reserved_connections;
SHOW work_mem;

Then raise the limit:

SQL
ALTER SYSTEM SET max_connections = 200;

`max_

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read