How to fix FATAL: remaining connection slots are reserved for non-replication superuser connections in PostgreSQL

-- View current connection counts
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

-- List all active connections with details
SELECT pid, usename, application_name, state, query_start 
FROM pg_stat_activity 
ORDER BY query_start DESC;

-- Check max_connections and superuser_reserved_connections settings
SHOW max_connections;
SHOW superuser_reserved_connections;

-- Find idle connections (in transaction but doing nothing)
SELECT pid, usename, application_name, state, state_change 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
AND query_start < now() - interval '10 minutes';

-- Terminate a specific connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE pid = <PID>;

-- Terminate all idle connections from a specific application
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE application_name = 'app_name' AND state = 'idle';

-- Terminate connections idle for more than 30 minutes
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE state = 'idle' 
AND state_change < now() - interval '30 minutes'
AND pid <> pg_backend_pid();

ALTER SYSTEM SET max_connections = 200;

-- 'postmaster' context means a restart is required
SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

# Linux (systemd)
sudo systemctl restart postgresql

# macOS Homebrew
brew services restart postgresql

# Or using pg_ctl
pg_ctl -D /path/to/data/directory restart

# Find postgresql.conf location
psql -U postgres -c "SHOW config_file;"

# On Linux/macOS it's typically:
# /etc/postgresql/*/main/postgresql.conf
# /var/lib/pgsql/data/postgresql.conf
# /usr/local/var/postgres/postgresql.conf (macOS Homebrew)

# Original (default)
max_connections = 100

# Increase based on load
max_connections = 300

# Linux
sudo systemctl restart postgresql

# macOS Homebrew
brew services restart postgresql

# Or using pg_ctl
pg_ctl -D /path/to/data/directory restart

# Install PgBouncer
sudo apt install pgbouncer  # Debian/Ubuntu
brew install pgbouncer       # macOS

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5

# BAD: Connection never closed
conn = psycopg2.connect("dbname=mydb")
cursor = conn.cursor()
# ... query code ...
# Missing: conn.close()

# GOOD: Use context manager
import psycopg2
with psycopg2.connect("dbname=mydb") as conn:
    with conn.cursor() as cursor:
        # ... query code ...
        # Connection automatically closed

// BAD: Connection not released
const client = new Client();
await client.connect();
const res = await client.query('SELECT ...');
// Missing: await client.end();

// GOOD: Use pools with automatic release
const pool = new Pool();
const res = await pool.query('SELECT ...');
// Connection automatically returned to pool

# GOOD: Connections managed automatically
ActiveRecord::Base.connection_pool.with_connection do |conn|
  conn.execute('SELECT ...')  # Released when block exits
end

-- Set globally (requires superuser); this parameter supports reload
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

-- Or in postgresql.conf
idle_in_transaction_session_timeout = 600000  # 10 minutes in milliseconds

-- Create a monitoring query to check connection health
CREATE OR REPLACE FUNCTION check_connection_health() RETURNS TABLE (
  total_connections bigint,
  active_connections bigint,
  idle_connections bigint,
  max_allowed bigint,
  reserved_slots bigint,
  available_slots bigint,
  usage_percent numeric
) AS $$
SELECT
  (SELECT count(*) FROM pg_stat_activity) as total_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state != 'idle') as active_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') as idle_connections,
  (SELECT setting::bigint FROM pg_settings WHERE name = 'max_connections') as max_allowed,
  (SELECT setting::bigint FROM pg_settings WHERE name = 'superuser_reserved_connections') as reserved_slots,
  (SELECT setting::bigint FROM pg_settings WHERE name = 'max_connections') - 
  (SELECT count(*) FROM pg_stat_activity) - 
  (SELECT setting::bigint FROM pg_settings WHERE name = 'superuser_reserved_connections') as available_slots,
  ROUND(100.0 * (SELECT count(*) FROM pg_stat_activity)::numeric / 
    (SELECT setting::bigint FROM pg_settings WHERE name = 'max_connections'), 2) as usage_percent;
$$ LANGUAGE SQL;

-- Check connection health
SELECT * FROM check_connection_health();