How do I solve the postgres max connections error?

Question
Answer
Script
Tested by
See also

This error appears in service logs (see How do I find the log files for an environment?) as:

[ERROR] Failed to create connection pool: Connection to postgres failed:
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

It means PostgreSQL has reached its max_connections ceiling. All remaining slots are superuser-reserved, so non-superuser service accounts cannot connect.

Question

How do I diagnose and fix the "remaining connection slots are reserved for roles with the SUPERUSER attribute" error?

Answer

Step 1 — Confirm the error in the service log

grep -i "connection\|fatal\|failed" \
  build/output/linux-clang-debug-make/publish/log/ores.refdata.service.0.log

Step 2 — Check current connection counts and limits

./projects/ores.compass/compass.sh db sql -- -c "
SELECT count(*), state, usename
FROM pg_stat_activity
GROUP BY state, usename
ORDER BY count DESC;"

./projects/ores.compass/compass.sh db sql -- -c "
SELECT setting::int AS max_connections
  FROM pg_settings WHERE name = 'max_connections';
SELECT count(*) AS total_connections
  FROM pg_stat_activity;
SELECT setting::int AS superuser_reserved
  FROM pg_settings WHERE name = 'superuser_reserved_connections';"

If the total exceeds max_connections - superuser_reserved, non-superuser accounts are locked out.

Step 3 — Identify which environments are holding connections

Multiple OreStudio environments (e.g. local2 and local4) each create per-service connection pools. Both running simultaneously easily exhausts the default limit. Look for ores_<env>_*_service usernames in the output above.

Step 4a — Fix: terminate idle connections from the unused environment

Replace local4 with the environment slug you want to clear:

./projects/ores.compass/compass.sh db sql -- -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename LIKE 'ores_local4_%'
  AND state = 'idle';"

Step 4b — Fix: raise max_connections (persistent)

Find the active config file:

./projects/ores.compass/compass.sh db sql -- -c "SHOW config_file;"

Edit postgresql.conf and increase max_connections (e.g. to 400), then reload:

./projects/ores.compass/compass.sh db sql -- -c "SELECT pg_reload_conf();"

A full restart is required if max_connections was raised beyond what a reload can apply. Check the PostgreSQL log after reloading to confirm.

Script

Uses ./projects/ores.compass/compass.sh db sql -- -c "<sql>" throughout. No other wrapper needed.

Tested by

Reproduced during Sprint 19 when both local2 and local4 environments were running simultaneously, pushing active connections above 200 and blocking ores_local2_refdata_service from connecting.