Is there a way to connect to postgres when have error "sorry, too many clients already "?

Question

I want to see what application is the cause, but even I change to user postgres and run psql, it still throw error

Answer 1

If you want to connect to database any how you need to free some connections on the server

1. Check all the idle postgres connection ps auxwww|grep 'idle in transaction' which will return list of all idle transaction processes with pid .

2. kill "pid" e.g say 10544 process having idle connection to database so kill 10544 will free up a single connection.

Now you can get access to database and check what/whom/when/where is holding your connection using query for monitoring SELECT * FROM pg_stat_activity;

It's better to increase max_connections of your postgresql server if you have more connections and users.

Just follow below steps

1. First find your postgresql.conf file If you don't know where it is, query the database with the sql: SHOW config_file;
   Mine is in: /var/lib/pgsql/data/postgresql.conf

2. Login as root and edit that file. Search for the string: max_connections. You'll see a line that says max_connections=100

3. Set that number bigger, check the limit for your postgresql version.

4. Restart the postgresql database for the changes to take effect service postgresql restart.

Answer 2

Slots reserved for postgres superuser

PostgreSQL reserves a few connections for superusers. If your application is not using superuser connections you can connect as a superuser (eg postgres). Then run diagnostic queries like:

select * from pg_stat_activity;

superuser_reserved_connections setting

See the superuser_reserved_connections setting in the Postgres configuration. Defaults to 3 reserved slots.

To quote the version 11 documentation:

Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only for superusers, and no new replication connections will be accepted.

The default value is three connections. The value must be less than max_connections minus max_wal_senders. This parameter can only be set at server start.