Why am I getting the error "FATAL: remaining connection slots are reserved for non replicate superuser connections" when connecting to my Amazon RDS for PostgreSQL even though I haven't reached the max_connections limit?

Short description

In Amazon RDS for PostgreSQL, the actual maximum number of available connections to non-superusers is calculated as follows:

max_connections - superuser_reserved_connections - rds.rds_superuser_reserved_connections.

The default value for superuser_reserved_connections is 3, and the default value for rds.rds_superuser_reserved_connections is 2.

For example, if you set the value of max_connections to 100, then the actual number of available connections for a non-superuser is calculated as follows:

100 - 3 - 2 = 95.

The Amazon CloudWatch metric DatabaseConnections indicates the number of client network connections to the database instance at the operating system level. This metric is calculated by measuring the actual number of TCP connections to the instance on port 5432. The number of database sessions might be higher than this metric value because the metric value doesn't include the following:

• Backend processes that no longer have a network connection but aren't cleaned up by the database. (For example: The connection is terminated due to network issues but the database isn't aware until it attempts to return the output to the client.)
• Backend processes created by the database engine job scheduler. (For example: pg_cron)
• Amazon RDS connections.

You might get this error because the application that connects to the RDS for PostgreSQL instance abruptly creates and drops connections. This might cause the backend connection to remain open for some time. This condition might create a discrepancy between the values of pg_stat_activity view and the CloudWatch metric DatabaseConnections.

Resolution

Troubleshoot the error

To troubleshoot this error, perform the following checks:

• Review the CloudWatch metric DatabaseConnections.
• Use Performance Insights to view the numbackends counter metric. This value provides information on the number of connections at the time that the error occurred. If you didn't turn on Performance Insights, log in to your instance as the primary user. Then, view the number of backends by running the following query:

SELECT count(*) FROM pg_stat_activity;

If you find some idle connections that can be terminated, you can terminate these backends using the pg_terminate_backend() function. You can view all the idle connections that you want to terminate by running the following query. This query displays information about backend processes with one of the following states for more than 15 minutes: 'idle', 'idle in transaction', 'idle in transaction (aborted)' and 'disabled'.

SELECT * FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;

Note: Be sure to update the query according to your use case.

After identifying all the backend processes that must be terminated, terminate these processes by running the following query.

Note: This example query terminates all backend processes in one of the states mentioned before for more than 15 minutes.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE
AND usename != 'rdsadmin';

To terminate all idle backend processes, run the following query:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND usename != 'rdsadmin';

Note: You can't terminate backend processes that are created with rdsadmin. Therefore, you must exclude them from termination.

Important: If you can't connect to your RDS for PostgreSQL instance with the rds_superuser privileges, then consider closing your application gracefully to free some connections.

Manage the number of database connections

Use connection pooling

In most cases, you can use a connection pooler, such as an RDS Proxy or any third party connection pooler, to manage the number of connections that are open at any one given time. For example, if you set the max_connections value of your RDS for PostgreSQL instance to 500, you can prevent errors related to max_connection by having a connection pooler that's configured for a maximum of 400 connections.

Increase max_connections value

You might consider increasing the value of max_connections depending on your use case. However, setting a very high value for max_connections might result in memory issues based on the workload and instance class of the database instance.

Note: If you increase the value of max_connections, you must reboot the instance for the change to take effect.

Terminate idle connections

You might set the idle_in_transaction_session_timeout parameter to a value that's appropriate for your use case. Any session that's idle within an open transaction for longer than the time specified in this parameter is terminated. For example if you set this parameter to 10 minutes, any query that's idle in transaction for more than 10 minutes is terminated. This parameter helps in managing connections that are stuck in this particular state.

For PostgreSQL versions 14 and later, you can use the idle_session_timeout parameter. After you set this parameter, any session that's idle for more than the specified time, but not within an open transaction, is terminated.

For PostgreSQL versions 14 and later, you can use the client_connection_check_interval parameter. With this parameter, you can set the time interval between optional checks for client connection when running queries. The check is performed by polling the socket. This check allows long-running queries to be ended sooner if the kernel reports that the connection is closed. This parameter helps in situations where PostgreSQL doesn't know about the lost connection with a backend process.

Increase the rds.rds_superuser_reserved_connections value

You might consider increasing the value of the rds.rds_superuser_reserved_connections parameter. The default value for this parameter is set to 2. Increasing the value of this parameter allows for more connections from users with the rds_superuser role attached. With this role, the users can run administrative tasks, such as terminating an idle connection using the pg_terminate_backend() command.

---