PostgreSQL has a default limit of 115 concurrent connections, with 15 reserved for superusers and 100 available for regular users. If this limit is exceeded, it triggers the "FATAL: sorry, too many clients already" error, causing incoming connections to be rejected. To address this issue, the maximum number of connections must be increased.
This Postgres blog post will illustrate a practical guide to increasing the maximum number of connections in PostgreSQL.
How to Increase Maximum Connections in Postgres?
In PostgreSQL, the information about the maximum number of concurrent connections is stored in the server variable/parameter named “max_connections”. It is located in the Postgres configuration file named “postgresql.conf”. You can modify this file to increase or decrease the connection limit according to your requirements.
Follow the given steps to increase maximum connection in Postgres:
Step 1: Open Postgres Configuration File
Open the Postgres configuration file located at the following path in Windows: “C:\Program Files\PostgreSQL\{Postgres installed version}\data\postgresql.conf”:
Double-click on the configuration file to open it:
Note: You can execute the following command to see the path where the configuration file is located:
show config_file;
Step 2: Increase Maximum Connections
Locate the “max_connections” variable in the configuration file available under the “CONNECTIONS AND AUTHENTICATION” section. By default, its value would be “100”, as shown below:
Modify the “max_connections” value according to your needs:
After making the necessary modifications, save the changes to the “postgresql.conf” file and close it.
Step 3: Restart the PostgreSQL Server
Restart the PostgreSQL Server to ensure that the new “max_connections” value takes effect. For this purpose, open the “services” app, locate the “PostgreSQL” service, and click on the “restart” button:
Once the PostgreSQL service is restarted, the changes you made will be applied and take effect.
Conclusion
In PostgreSQL, the default limit of concurrent connections is 115, with 15 reserved for superusers and 100 available for regular users. However, users can increase the connection limit according to their needs. To do that, first, open the Postgres configuration file, locate the “max_connections” variable in the configuration file, and modify its value according to your needs. Restart the Postgres Server to implement the desired modifications. This write-up has illustrated how to increase maximum connections in PostgreSQL using practical demonstration.
