Linux  ·  critical  ·  Web, PHP & databases

PostgreSQL: "too many clients", pg_hba refusals and a database that will not start

PostgreSQL is explicit about why it refuses a connection. The message names the rule or the limit, and the log is authoritative.

What you see

Applications report a connection error naming one of these messages, or the service will not start after a crash or a disk-full event.

What is actually wrong

The client limit, a missing or mismatched pg_hba.conf rule, or write-ahead log damage after an unclean shutdown.

Codes and articles

FATAL: sorry, too many clients alreadyno pg_hba.conf entry for hostFATAL: password authentication failedcould not connect to serverPANIC: could not locate a valid checkpoint record

Fixes (3)

Add pooling rather than raising the limit
Root shell40 minutesmedium riskreversible

"sorry, too many clients already".

  1. See the limit and what is connected.

    Shell
    sudo -u postgres psql -c 'SHOW max_connections'sudo -u postgres psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state"

  2. Look for connections idle in transaction — these hold locks and are far worse than idle ones.

    Shell
    sudo -u postgres psql -c "SELECT pid, usename, state, now()-state_change AS age, LEFT(query,60) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 20"

    "Idle in transaction" for minutes means the application opened a transaction and went away. It blocks vacuum and holds locks, and no amount of extra connections fixes the underlying bug.

  3. Terminate a stuck session only when you understand what it was doing.

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

  4. Install a connection pooler rather than raising max_connections. PostgreSQL uses a process per connection and does not scale the way a threaded server does.

    Shell
    sudo apt install pgbouncer

    Raising max_connections to several hundred costs memory per connection and increases contention. PgBouncer in transaction mode lets hundreds of application connections share a few dozen real ones, which is the standard answer.

  5. Set an idle transaction timeout so this cannot recur silently.

    Shell
    sudo -u postgres psql -c "ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s'"sudo -u postgres psql -c 'SELECT pg_reload_conf()'
Confirm it workedConnection counts stay low and no session sits idle in transaction.
Shell
sudo -u postgres psql -c "SELECT state,count(*) FROM pg_stat_activity GROUP BY state"
If you need to undo itALTER SYSTEM RESET idle_in_transaction_session_timeout, then reload.
Write the right pg_hba rule
Root shell25 minutesmedium riskreversible

"no pg_hba.conf entry for host" or an authentication failure.

  1. Find the file in use — there may be more than one installation.

    Shell
    sudo -u postgres psql -c 'SHOW hba_file'sudo -u postgres psql -c 'SHOW config_file'

  2. Read the current rules. They are evaluated top to bottom and the first match wins, so a broad reject above your rule silences it.

    Shell
    sudo grep -vE '^\s*#|^\s*$' $(sudo -u postgres psql -Atc 'SHOW hba_file')

    First match wins is the part that catches people. Adding a correct rule at the bottom of the file does nothing if a earlier line already matched that host and method.

  3. Add a specific rule with the narrowest address range that works.

    Shell
    echo 'host    appdb    appuser    10.0.0.0/24    scram-sha-256' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

  4. Reload rather than restart — pg_hba is re-read without dropping connections.

    Shell
    sudo -u postgres psql -c 'SELECT pg_reload_conf()'

  5. Check the server is listening on the address at all — listen_addresses defaults to localhost.

    Shell
    sudo -u postgres psql -c 'SHOW listen_addresses'sudo ss -tlnp | grep 5432
Confirm it workedThe application connects and pg_hba_file_rules shows the rule with no error.
Shell
sudo -u postgres psql -c 'SELECT line_number,type,database,user_name,address,auth_method,error FROM pg_hba_file_rules'
If you need to undo itRemove the added line and reload.
Recover a cluster that will not start
Root shell60 minuteshigh risknot reversible

The service fails to start after a crash, a disk-full event or a power loss.

  1. Read the log. PostgreSQL states the reason clearly and the right action depends entirely on which reason it is.

    Shell
    sudo journalctl -u postgresql@16-main -n 60 --no-pagersudo tail -60 /var/log/postgresql/postgresql-16-main.log

  2. If the disk is full, free space before anything else — PostgreSQL cannot recover while it cannot write.

    Shell
    df -hsudo du -sh /var/lib/postgresql/16/main/pg_wal

  3. Take a full filesystem-level copy of the data directory before any repair attempt.

    Shell
    sudo systemctl stop postgresqlsudo tar -C /var/lib/postgresql/16 -czf /root/pgdata-backup.tar.gz main

    Everything below can make things worse. With this copy, a failed repair costs an hour; without it, it can cost the database.

  4. Do not run pg_resetwal as a first response. It discards transactions and produces a cluster that starts but may be inconsistent — it is a last resort for a database with no backup, and the correct next step afterwards is to dump and reload into a fresh cluster.

    This command appears in a lot of quick answers and it is genuinely dangerous. It makes a broken cluster start, which looks like success and can hide silent corruption for weeks.

  5. Restore from backup if one exists — that is faster and safer than any repair.

    Shell
    sudo -u postgres pg_restore --list /backups/latest.dump | head

  6. If pg_resetwal is genuinely the only option, dump everything immediately afterwards and reload into a new cluster.

    Shell
    sudo -u postgres pg_dumpall > /root/emergency-dump.sql
Confirm it workedThe cluster starts, and a full pg_dumpall completes without error — which is the practical test of consistency.
Shell
sudo systemctl status postgresql --no-pagersudo -u postgres psql -c 'SELECT count(*) FROM pg_database'
If you need to undo itRestore /root/pgdata-backup.tar.gz over the data directory to return to the pre-repair state.

Related faults

MySQL: "Too many connections" or "Access denied for user"LinuxSQL Server error 18456 — login failed for userWindows Server

Where this stops. This write-up was written and checked by hand. It says what each step changes, how to confirm it worked and how to reverse it, and anything destructive is flagged before you reach it. If it does not match what your machine is doing, search the Support Centre for the exact code or message — and when something needs a person, get in touch.