[ RESOLVED ] PostgreSQL : PSQLException: FATAL: sorry, too many clients already

PSQLException: FATAL: sorry, too many clients already

Image Credit

Exception

Caused by: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
.....

Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
.....

Cause

The application is trying to establish more connections than allowed from the database side.

Possible Solutions

1.  Increase the max connections allowed from PostgreSQL in configurations.

2.  If connections created from the application is higher than expected, minimize it.

3. Manage the connections properly, make sure they are closed after use, better use a connection pool as some answers described here.

We are considering the first solution here.

Resolution

1. Check the current configuration

Login to PostgreSQL using psql command,

          postgres=# show max_connections;

            max_connections 

            -----------------

           100

          (1 row)

2. Open the PostgreSQL config file

vi /var/lib/pgsql/data/postgresql.conf

3. Change the following configurations accordingly

       max_connections = 100                   # (change requires restart)

        # Note:  Increasing max_connections costs ~400 bytes of shared memory per

        ....

        shared_buffers = 400MB        # min 128kB  # (change requires restart)

4. Restart the PostgreSQL server to apply changes

        $:service postgresql restart

       Stopping postgresql service:                               [  OK  ]

       Starting postgresql service:                               [  OK  ]

Reference

01. https://confluence.atlassian.com/bamkb/org-postgresql-util-psqlexception-fatal-sorry-too-many-clients-already-812222088.html