How to Increase Max Connections in PostgreSQL

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 = 100

to

max_connections = 250

You may also need to increase memory allotted for storing cache data. This is controlled by shared_buffers variable. So change

shared_buffers = 24MB

to

shared_buffers = 80MB

You 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.

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!