PostgreSQL Database Connection Issue Resolution

PostgreSQL Database Connection Issue Resolution

Issue Summary

The bartendie Elixir project was experiencing PostgreSQL database connection issues that prevented the application from starting and accessing the database.

Root Cause Analysis

Initial Problem: Too Many Connections

• Error: FATAL 53300 (too_many_connections) sorry, too many clients already
• Cause: PostgreSQL had reached its maximum connection limit, likely due to:
  • Previous application instances not properly closing connections
  • Connection pool exhaustion
  • Stuck or orphaned database connections

Secondary Problem: Missing PostgreSQL User

• Error: FATAL 28000 (invalid_authorization_specification) role "postgres" does not exist
• Cause: The PostgreSQL user “postgres” was not created in the database system

Resolution Steps Implemented

1. PostgreSQL Service Management

# Restarted PostgreSQL to clear stuck connections
brew services restart postgresql@14

2. Database User Creation

# Created the postgres superuser
createuser -s postgres

3. Database Setup

# Created the main application database
mix ecto.create

# Created the EventStore database
mix event_store.create

# Ran database migrations
mix ecto.migrate

4. Connection Verification

# Tested database connectivity
mix run -e "Bartendie.Repo.query!('SELECT 1 as test')"

Current Database Configuration

Main Database (Bartendie.Repo)

• Host: localhost
• Port: 5432 (default)
• Database: bartendie_dev
• Username: postgres
• Password: postgres
• Pool Size: 10

EventStore Database

• Host: localhost
• Port: 5432 (default)
• Database: bartendie_eventstore
• Username: postgres
• Password: postgres
• Pool Size: 10

Verification Results

✅ Database Connection: Successfully established ✅ Main Database: Created and migrated ✅ EventStore Database: Created and initialized ✅ Connection Pools: Working properly ✅ Application Startup: Database connections successful

Recommendations for Future Prevention

1. Connection Pool Optimization

Consider adjusting pool settings in config/dev.exs:

config :bartendie, Bartendie.Repo,
  # ... other config
  pool_size: 10,
  queue_target: 5000,
  queue_interval: 5000

2. Connection Monitoring

• Monitor active connections: SELECT count(*) FROM pg_stat_activity;
• Check PostgreSQL max_connections: SHOW max_connections;
• Monitor connection pool usage in application logs

3. Graceful Shutdown

Ensure proper application shutdown to close database connections:

# Use Ctrl+C twice for graceful shutdown
# Or use: mix phx.server --no-halt

4. Development Environment Setup

Create a setup script for new developers:

setup_db.sh

#!/bin/bash
createuser -s postgres 2>/dev/null || true
mix ecto.create
mix event_store.create
mix ecto.migrate

5. PostgreSQL Configuration

For development, consider increasing max_connections in postgresql.conf:

max_connections = 100  # Default is often 100

Troubleshooting Guide

If “too many connections” error occurs again:

1. Restart PostgreSQL: brew services restart postgresql@14
2. Check active connections: psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"
3. Kill stuck connections if needed
4. Restart the application

If “role does not exist” error occurs:

1. Create the user: createuser -s postgres
2. Or create with password: createuser -s -P postgres

If database doesn’t exist:

1. Run: mix ecto.create
2. Run: mix event_store.create
3. Run: mix ecto.migrate

Status: ✅ RESOLVED

The PostgreSQL database connection issue has been successfully resolved. Both the main application database and EventStore are now properly configured and accessible.

Date Resolved: 2025-01-16 Resolution Time: ~30 minutes Impact: No data loss, development environment fully functional