Hi. I have a database running on an Azure Database for PostgreSQL flexible server. I have been managing the database using pgAdmin. Now i am not able to access or login as i am getting the following error, FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role. How can this be resolved ?

Hi @ Abhishek M , Welcome to the Microsoft Q&A Platform! Thank you for asking your question here. We would like to inform you that, The error message, “FATAL: remaining connection slots are reserved for roles with privileges of the pg_use_reserved_connections role, indicates that the server has reached its maximum allowed connections, and only privileged roles can use the remaining reserved slots. To resolve this issue, you can try the following steps: Increase the max_connections parameter in Azure Database for PostgreSQL Flexible Server. You can do this in the Azure portal by navigating to your database, selecting Server Parameters, and adjusting max_connections to a higher value. Check for idle connections that are not being used. You can use the pg_stat_activity view to identify idle connections. You can terminate idle connections using the pg_terminate_backend function. We request you to refer the below mentioned link. https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/why-restarting-azure-database-for-postgresql-took-long/ba-p/1885780#:~:text=Let's%20say%20you%20have%20PID,for%20PostgreSQL%20planned%20server%20restart I hope, This response will address your query and helped you to overcome on your challenges. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

How to fix FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role on an Azure Database for PostgreSQL flexible server.

Accepted answer

Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-10-25T13:52:58.76+00:00
Hi @Abhishek M,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, The error message, “FATAL: remaining connection slots are reserved for roles with privileges of the pg_use_reserved_connections role, indicates that the server has reached its maximum allowed connections, and only privileged roles can use the remaining reserved slots.

To resolve this issue, you can try the following steps:

Increase the max_connections parameter in Azure Database for PostgreSQL Flexible Server. You can do this in the Azure portal by navigating to your database, selecting Server Parameters, and adjusting max_connections to a higher value.

Check for idle connections that are not being used. You can use the pg_stat_activity view to identify idle connections. You can terminate idle connections using the pg_terminate_backend function.

We request you to refer the below mentioned link.

https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/why-restarting-azure-database-for-postgresql-took-long/ba-p/1885780#:~:text=Let's%20say%20you%20have%20PID,for%20PostgreSQL%20planned%20server%20restart

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Vijayalaxmi Kattimani 490 Reputation points Microsoft Vendor

2024-10-29T14:51:24.8933333+00:00

Hi Abhishek M,

Following up to see if the above answer was helpful.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Johnson, Eric M (MNIT) 0 Reputation points

2024-11-01T20:31:58.4466667+00:00

Edit: Originally I thought this isn't a Postgresql server issue, but caused by a new feature of Postgres version 16. Our test server had 75 max_connections and we got this error when a user tries to connect more than once. When I tried to assign the "pg_use_reserved_connections" role I got this error:

ERROR: permission denied to grant role "pg_use_reserved_connections"

SQL state: 42501

Detail: Only roles with the ADMIN option on role "pg_use_reserved_connections" may grant this role.

Increasing the number of max connections to 200 worked for us, at least for now. Our Spring Boot app is using Hikari for connection pooling, so at the moment we won't use pgBouncer like others have suggested. There is documentation for Azure that shows the maximum number of connections depending on your server configuration. In our case we could use 850 connections, but as others have said, it may cause performance issues.

This error is a little misleading, it seems to suggest the user role needs to be assigned the "pg_use_reserved_connections" role, but as far as I can tell that role is meant for super users.

Abhishek M 20 Reputation points

2024-11-05T16:31:39.1033333+00:00

Thank you @Vijayalaxmi Kattimani . Increasing the max_connections worked!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to fix FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role on an Azure Database for PostgreSQL flexible server.

0 additional answers

Your answer