1  #!/bin/bash
  2  
  3  # Fix PostgreSQL "too many connections" issue for ECHO project
  4  
  5  set -e
  6  
  7  echo "🔧 Fixing PostgreSQL Connection Issues"
  8  echo "======================================"
  9  echo ""
 10  
 11  # Step 1: Stop all ECHO agent processes
 12  echo "Step 1: Stopping all ECHO agent processes..."
 13  pkill -f "beam.smp.*echo" || true
 14  pkill -f "senior_architect" || true
 15  pkill -f "ceo" || true
 16  pkill -f "cto" || true
 17  sleep 2
 18  echo "✓ Agent processes stopped"
 19  echo ""
 20  
 21  # Step 2: Check current connections
 22  echo "Step 2: Checking PostgreSQL status..."
 23  CONN_COUNT=$(ps aux | grep postgres | grep -v grep | wc -l)
 24  echo "PostgreSQL processes: $CONN_COUNT"
 25  echo ""
 26  
 27  # Step 3: Get PostgreSQL config location
 28  echo "Step 3: Finding PostgreSQL configuration..."
 29  if command -v brew &> /dev/null; then
 30      PG_DATA=$(brew --prefix)/var/postgresql@16
 31      PG_CONF="$PG_DATA/postgresql.conf"
 32  
 33      if [ ! -f "$PG_CONF" ]; then
 34          PG_DATA=$(brew --prefix)/var/postgres
 35          PG_CONF="$PG_DATA/postgresql.conf"
 36      fi
 37  else
 38      PG_CONF="/usr/local/var/postgresql@16/postgresql.conf"
 39  fi
 40  
 41  if [ ! -f "$PG_CONF" ]; then
 42      echo "❌ Could not find postgresql.conf"
 43      echo "Please manually locate it and increase max_connections to 200"
 44      exit 1
 45  fi
 46  
 47  echo "Found config: $PG_CONF"
 48  echo ""
 49  
 50  # Step 4: Backup and update PostgreSQL configuration
 51  echo "Step 4: Updating PostgreSQL configuration..."
 52  cp "$PG_CONF" "$PG_CONF.backup.$(date +%Y%m%d_%H%M%S)"
 53  echo "✓ Config backed up"
 54  
 55  # Update max_connections
 56  if grep -q "^max_connections" "$PG_CONF"; then
 57      sed -i.bak 's/^max_connections = .*/max_connections = 200/' "$PG_CONF"
 58      echo "✓ Updated max_connections to 200"
 59  else
 60      echo "max_connections = 200" >> "$PG_CONF"
 61      echo "✓ Added max_connections = 200"
 62  fi
 63  
 64  # Update shared_buffers if needed
 65  if grep -q "^shared_buffers" "$PG_CONF"; then
 66      sed -i.bak 's/^shared_buffers = .*/shared_buffers = 256MB/' "$PG_CONF"
 67      echo "✓ Updated shared_buffers to 256MB"
 68  else
 69      echo "shared_buffers = 256MB" >> "$PG_CONF"
 70      echo "✓ Added shared_buffers = 256MB"
 71  fi
 72  
 73  echo ""
 74  
 75  # Step 5: Restart PostgreSQL
 76  echo "Step 5: Restarting PostgreSQL..."
 77  if command -v brew &> /dev/null; then
 78      brew services restart postgresql@16 2>/dev/null || brew services restart postgresql
 79      echo "✓ PostgreSQL restarted"
 80  else
 81      echo "❌ Please manually restart PostgreSQL:"
 82      echo "   sudo systemctl restart postgresql"
 83      exit 1
 84  fi
 85  
 86  sleep 3
 87  echo ""
 88  
 89  # Step 6: Verify fix
 90  echo "Step 6: Verifying connection..."
 91  if psql -h localhost -U postgres -c "SELECT version();" > /dev/null 2>&1; then
 92      echo "✓ PostgreSQL connection successful!"
 93  
 94      MAX_CONN=$(psql -h localhost -U postgres -t -c "SHOW max_connections;")
 95      echo "✓ Max connections: $MAX_CONN"
 96  else
 97      echo "⚠️  Still having connection issues. Please check logs:"
 98      echo "   tail -f $(brew --prefix)/var/log/postgresql@16.log"
 99  fi
100  
101  echo ""
102  echo "✅ PostgreSQL fix complete!"
103  echo ""
104  echo "Current configuration:"
105  echo "  max_connections: 200 (was 100)"
106  echo "  shared_buffers: 256MB"
107  echo "  pool_size per agent: 2"
108  echo "  Total possible: 9 agents × 2 = 18 connections (well under 200)"
109  echo ""
110  echo "You can now safely start your ECHO agents."