Skip to main content

Error with Postgres DB: Remaining connection slots are reserved for roles with the SUPERUSER attribute

Updated

Problem 

There will be cases where you see the following error in the nexus.log:

Remaining connection slots are reserved for roles with the SUPERUSER attribute

This can happen when running tasks or performing other actions in Nexus Repository that involve a Postgres database.

Symptoms 

  • Errors when running tasks (e.g., "Admin - Cleanup Unused Asset Blobs").
  • Issues with search functionality.

Possible causes

This error indicates that the number of active connections to your PostgreSQL database has reached the maximum limit set by the max_connections parameter in your postgresql.conf file. This can happen if:

  • Insufficient max_connections: The configured max_connections value is too low for the current workload and concurrent connections.
  • High maximumPoolSize in Nexus: The maximumPoolSize property in your nexus-store.properties file (which controls the connection pool size for Nexus) is too high, approaching or exceeding the max_connections limit.

Solution 

Option 1: Increase max_connections (Recommended)

  1. Shutdown Nexus Repository: Stop Nexus to prevent new connections to the database.
  2. Shutdown PostgreSQL: Stop the PostgreSQL service gracefully to avoid data corruption.
  3. Locate postgresql.conf: This file is usually located in the PostgreSQL data directory (e.g., /var/lib/pgsql/data on Linux).
  4. Increase max_connections: Edit postgresql.conf and increase the max_connections value. Doubling the current value is a reasonable starting point. 
    1. Note: If you're already at a value such as 400+ please open a support ticket instead.
  5. Adjust maximumPoolSize: Edit the nexus-store.properties file (located in <data-dir>/etc/fabric) and set maximumPoolSize to a value that is 50-100 less than your new max_connections value. This provides some headroom for other database connections.
  6. Restart PostgreSQL: Start the PostgreSQL service to apply the new configuration.
  7. Restart Nexus Repository: Start Nexus.

Option 2: Decrease maximumPoolSize (Less Ideal)

If increasing max_connections is not feasible due to resource constraints, you can try decreasing maximumPoolSize in nexus-store.properties. However, this can negatively impact performance if you have a high-traffic Nexus instance.

 

 

Related articles