It finally happened. I blew the max connection limit in my new PostgreSQL install by starting too many grid nodes with associated connection pools for a system I am writing.
The default limit of 100 connections is far too few, especially with two people doing database intensive tasks. This article explains how to check the number of connections used as a standard user and administrator and how the administrator can change the connection limit.
Default Connection Limit and Buffer Size
By default, PostgreSQL has a relatively low number of maximum allowed connections. The default limit is 100.
The limit is related to the size of the shared buffers. Connections utilize the memory in the shared buffers. By default, the shared buffer size is set to 8 gigabytes.
PostgreSQL is a versatile database. Amazon built Redshift on the system. However, the maximum sizes are low and should remain so to encourage developers and students not to abuse their system’s resources.
Common Error When Exceeding Size Limit
With an ever increasing amount of data available and still growing need for technology, connection limits will be exceeded on many systems. PostgreSQL throws the following error when this occurs:
psql: FATAL: remaining connection slots are reserved for non-replication superuser connections
In my case, the connection pool failed to reserve the required ten to twenty connections per highly database intensive job and released what connections it did acquire back to the database instantly. This left me with 96 used connections and four that were unattainable. Some of the 96 connections were by an ETL tool or PG Admin.
Querying the Connection Limit
It is possible for non-administrators to check the number of connections in use with the following query:
SELECT count(distinct(numbackends)) FROM pg_stat_database
This query puts together a view of database statistics and counts the number of currently active connections.
Administrators have the option of querying connections from the psql command line using:
This query asks the database to return the value of the max_connections configuration variable described below.
Most configuration variables can be queried in psql. Some can even be set from the command line console. The max_connections variable cannot be set from the command line due to an associated increase or decrease in memory usage.
Setting Connection Related Variables
While the initial settings are low by todays settings, they can be set in your postgresql.conf file. The file is typically found at a location such as:
/var/lib/pgsql/X.X/data/postgresql.conf or /usr/local/postgres/postgresql.conf
The default location is the data directory under your PostgreSQL installation.
Look for the following variables in the file:
shared_buffers = 8000MB max_connections = 100
Choosing Size Limits
It is important to set size limits relevant to the size of the shared buffers. Dividing the RAM usage by the current number of connections in use or the maximum number allotted will give a nice over-estimate of total memory use per connection. Ensure that this multiple does not exceed the shared buffer size and the capacity of your RAM. The shared buffer size should generally be less than the amount of RAM in your machine.
The equality to check with is:
shared_buffers < (RAM - padding) && (current_ram_use / max_connections) * desired_connections < (RAM - padding) && (current_ram_use / max_connections) * desired_connections < shared_buffers
Basically, ensure that you have enough RAM and that the potential memory use does not exceed RAM and the shared buffer size. This inequality uses the max_connections settings instead of the actual number of used connections for some extra assurance.
Reloading and Restarting
The configuration file can be reloaded in an active session without a restart using the sql command line console or through PG Admin with the query:
This query does not require administrator privileges but will not work when setting the maximum number of allotted connections or buffer size. Any variable in the configuration file proceeded by the following comment requires restarting the database server:
# (change requires restart)
Both max_connections and shared_buffers cause a change in memory usage which cannot be reworked on the fly. From the Linux command line, restart the server with the command:
On Ubuntu, the restart script normally resides at /etc/init.d.
If PostgreSQL is setup as a service, use:
service postgresql restart
In today’s world where data is bountiful, it is easy to exceed the connection limit in PostgreSQL. This article reviewed how to reset the connection limit and considerations for resetting the limit and associated shared buffer size.