Fix PostgreSQL FATAL sorry, too many clients already

Understand why the limit exists

PostgreSQL reserves a fixed amount of shared memory and worker processes for each connection slot defined by max_connections. Every client connection, including ones from application servers, cron jobs, and admin tools, consumes one slot for as long as it stays open, even if it is idle.

A few slots are always reserved for the superuser via superuser_reserved_connections, so that an administrator can still log in to diagnose problems even when the pool is full. When all remaining slots are taken, every new connection attempt fails immediately with this FATAL error.

Check the current connection count and limit

Connect to PostgreSQL with psql and inspect how many connections are currently open compared to the configured maximum.

sudo -u postgres psql -c "SHOW max_connections;"

sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

Compare the two numbers. If the count from pg_stat_activity is close to or equal to max_connections, you have confirmed the cause of the error.

sudo -u postgres psql -c "SELECT datname, usename, client_addr, state, count(*) FROM pg_stat_activity GROUP BY datname, usename, client_addr, state ORDER BY count(*) DESC;"

Identify idle or leaked connections

Most connection exhaustion comes from applications that open a connection per request and never close it, or from connection pools that are misconfigured with a pool size larger than the database can handle. Look specifically at the idle and idle in transaction states.

sudo -u postgres psql -c "SELECT pid, usename, datname, state, now() - state_change AS idle_for, query FROM pg_stat_activity WHERE state LIKE 'idle%' ORDER BY idle_for DESC LIMIT 20;"

Connections stuck in idle in transaction for a long time usually indicate an application bug where a transaction was opened but never committed or rolled back. These hold locks and slots indefinitely.

Terminate stuck sessions safely

Once you have identified connections that are genuinely idle or stuck, terminate them individually by PID rather than cancelling everything at once.

sudo -u postgres psql -c "SELECT pg_terminate_backend(12345);"

To free up slots quickly during an incident, terminate all connections idle in transaction for more than 10 minutes, but review the list first.

sudo -u postgres psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes';"

This buys you immediate headroom while you fix the root cause in the application or pooler configuration.

Increase max_connections

If legitimate traffic genuinely needs more concurrent connections than the current limit allows, raise max_connections in the PostgreSQL configuration file. Each additional connection consumes roughly a few megabytes of shared memory, so increase gradually and monitor memory usage.

sudo -u postgres psql -c "SHOW config_file;"

Edit the file returned by the command above, typically located under /etc/postgresql/17/main/postgresql.conf on Debian based systems or /var/lib/pgsql/17/data/postgresql.conf on RHEL based systems.

max_connections = 200
shared_buffers = 2GB
superuser_reserved_connections = 5

A common rule of thumb is to keep shared_buffers around 25% of total RAM and scale it up slightly as max_connections increases, since each connection needs its own memory for sorting and work operations.

Raise kernel limits to match

Higher max_connections values require more file descriptors and more shared memory at the OS level. Check and raise the open file limit for the postgres user.

sudo -u postgres bash -c 'ulimit -n'

postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 4096
postgres hard nproc 4096

If PostgreSQL runs under systemd, also raise the limit in the service unit override so it takes effect even if limits.conf is ignored.

sudo systemctl edit postgresql

[Service]
LimitNOFILE=65536

Check shared memory settings on the OS

On some installations, especially AlmaLinux and Rocky Linux with default kernel settings, the shared memory limits are too low for a large max_connections plus shared_buffers combination. Check the current kernel shared memory maximum.

sysctl kernel.shmmax kernel.shmall

sysctl kernel.shmmax kernel.shmall

If shmmax is lower than the memory PostgreSQL needs to allocate, increase it via sysctl.

kernel.shmmax = 17179869184
kernel.shmall = 4194304

sudo sysctl --system

Restart PostgreSQL safely

max_connections and shared_buffers both require a full restart, not just a reload, because they affect shared memory allocation at startup. Schedule this during a maintenance window and verify there are no critical queries running first.

sudo -u postgres psql -c "SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';"

sudo systemctl restart postgresql

Check the PostgreSQL logs immediately after restart to confirm it came back up cleanly with the new settings.

sudo journalctl -u postgresql -n 50 --no-pager