AWS RDS limit połączeń przekroczony: too many connections

# Application error logs show:
# FATAL: too many connections for role "app_user"
# FATAL: remaining connection slots are reserved for non-replication superuser connections

# Check current connection count from a superuser session
# (if you can still connect via psql as superuser)
psql -h my-rds-instance.abcdefg.eu-west-1.rds.amazonaws.com \
  -U master_user -d mydb -c "SELECT count(*) FROM pg_stat_activity;"

# Check max_connections setting
psql -h my-rds-instance.abcdefg.eu-west-1.rds.amazonaws.com \
  -U master_user -d mydb -c "SHOW max_connections;"

# Check via CloudWatch if you can't connect at all
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name DatabaseConnections \
  --dimensions Name=DBInstanceIdentifier,Value=my-rds-instance \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 60 --statistics Maximum \
  --query 'Datapoints | sort_by(@, &Timestamp) | [-5:].[Timestamp,Maximum]'

# Connect as superuser (RDS master user)
psql -h my-rds-instance.abcdefg.eu-west-1.rds.amazonaws.com \
  -U master_user -d mydb

# Check connection breakdown by state and application
SELECT state, application_name, count(*)
FROM pg_stat_activity
GROUP BY state, application_name
ORDER BY count DESC;

# Identify idle connections older than 5 minutes
SELECT pid, usename, application_name, state,
       now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '5 minutes'
ORDER BY idle_duration DESC;

# Terminate idle connections (careful: this kills sessions)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '5 minutes'
  AND usename != 'rdsadmin';

# Verify connections freed
SELECT count(*) FROM pg_stat_activity;

# Create RDS Proxy (via AWS CLI)
aws rds create-db-proxy \
  --db-proxy-name my-service-proxy \
  --engine-family POSTGRESQL \
  --auth Description="proxy auth",AuthScheme=SECRETS,SecretArn=arn:aws:secretsmanager:eu-west-1:123456789012:secret:rds-creds,IAMAuth=DISABLED \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
  --vpc-subnet-ids subnet-abc123 subnet-def456 \
  --vpc-security-group-ids sg-12345678

# Register target (your RDS instance)
aws rds register-db-proxy-targets \
  --db-proxy-name my-service-proxy \
  --db-instance-identifiers my-rds-instance

# Update application connection string to point to the proxy endpoint
# Proxy endpoint: my-service-proxy.proxy-abcdefg.eu-west-1.rds.amazonaws.com

# Check current parameter group
aws rds describe-db-instances \
  --db-instance-identifier my-rds-instance \
  --query 'DBInstances[0].DBParameterGroups[0].DBParameterGroupName'

# Modify max_connections (requires custom parameter group, not default)
aws rds modify-db-parameter-group \
  --db-parameter-group-name my-custom-pg \
  --parameters "ParameterName=max_connections,ParameterValue=300,ApplyMethod=pending-reboot"

# Reboot to apply (causes brief downtime)
aws rds reboot-db-instance --db-instance-identifier my-rds-instance

# 1. Confirm connections are below limit
psql -h my-rds-instance.abcdefg.eu-west-1.rds.amazonaws.com \
  -U master_user -d mydb -c \
  "SELECT count(*) as current, current_setting('max_connections')::int as max FROM pg_stat_activity;"
# Expected: current is well below max

# 2. Verify application can connect
curl -s https://my-app.example.com/health | jq '.database'
# Expected: "connected" or equivalent health check pass

# 3. Check no connection leak pattern in CloudWatch
aws cloudwatch get-metric-statistics \
  --namespace AWS/RDS \
  --metric-name DatabaseConnections \
  --dimensions Name=DBInstanceIdentifier,Value=my-rds-instance \
  --start-time $(date -u -d '30 minutes ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 300 --statistics Average
# Expected: stable connection count, not continuously rising

# 4. If using RDS Proxy, verify it's handling connections
aws rds describe-db-proxy-targets \
  --db-proxy-name my-service-proxy \
  --query 'Targets[0].TargetHealth'
# Expected: State = "AVAILABLE"