By default, PostgreSQL supports 115 concurrent connections, 15 for superusers and 100 connections for other users. However, sometimes you may need to increase max connections in PostgreSQL to support greater concurrency. If there are too many concurrent connections to PostgreSQL database, it might give the “FATAL: sorry, too many clients already” error message and reject incoming connections. Here’s how to increase max connections in PostgreSQL.
How to Increase Max Connections in PostgreSQL
It is quite easy to increase max connections in PostgreSQL. The number of max connections in PostgreSQL is stored in server variable max_connections. We will look at how to modify this variable.
1. Open PostgreSQL configuration
Open PostgreSQL configuration file at
Linux: /var/lib/pgsql/{version_number}/data/postgresql.conf
Windows: C:\Program Files\PostgreSQL\{version_number}\data\postgresql.conf
For example, if you have PostgreSQL 9.3, then its configuration file will be at
Linux: /var/lib/pgsql/9.3/data/postgresql.conf Windows: C:\Program Files\PostgreSQL\9.3\data\postgresql.conf
Bonus Read : PostgreSQL Performance Tuning Tips
2. Increase Max Connections in PostgreSQL
Let’s say you want to increase max connections to 250. In that case, change
max_connections = 100to
max_connections = 250You may also need to increase memory allotted for storing cache data. This is controlled by shared_buffers variable. So change
shared_buffers = 24MBto
shared_buffers = 80MBYou can change the above memory value as per your requirement.
Bonus Read : Top 5 PostgreSQL Query Monitoring Tools
3. Restart PostgreSQL Server
Restart PostgreSQL server to apply changes
$ sudo service restart postgresql
OR
# /etc/init.d/postgresql restart
Hopefully, this article will help you increase max connections in PostgreSQL.
