How to increase max connection size of Postgresql in Ubuntu

If you are accessing to Postgresql database using programming language, sometimes, you may get exception such as fatal sorry too many clients already or more precisely the following exception in Java.

org.postgresql.util.psqlexception fatal sorry too many clients already

The reason for such an error is due to the fact that your program perhaps made too many connection to the database and didn’t close the connection, which sometimes due to lack of using of connection pool. It also has other reasons.
Anyhow, to resolve the problem, you are either have to change your program to make less connection or if that’s not a case, you need to increase the number of max_connection.
Bear in mind that I personally recommend to change your code if you think it has some errors.
To increase max_connection size, you ought to modify postgresql.conf file which is most probably located at this location,

/var/lib/pgsql/data

Open the file and then seek for the line max_connection and change it to what you desire. As an example,

max_connection = 120

After you have done the changes, you need to restart the Postgresql with the following commands,

$ sudo /etc/init.d/postgresql restart
$ sudo service postgresql restart

If you have seen that Postgresql didn’t start, you should debug it to find its problem. To do so, run this command,

$ pg_ctl -D /var/lib/pgsql/data_start

Most of the time, increasing max_connection causes another problem which is known as shortage of shared memory, at least in Linux.
In that case, you receive this error message,

FATAL: could not create shared memory segment: Invalid argument

In this case you need to increase the maximum size of a chunk of shared memory Linux kernel allows to allocate at once (known as SHMMAX parameter)
To do this, need to edit,

$ sudo vim /etc/sysctl.conf

And add or increase,

kernel.shmmax=8589934592

Which means 8 GB in this case. Then need to reload the service,

sudo sysctl -p

Leave a Reply