Database Connection Error: role 'hbot' does not exist due to conflicting PostgreSQL instances

[fengtality]

Problem Description

When starting the Hummingbot API with make run or ./run.sh --dev, the application fails during startup with the following error:

2025-10-20 10:43:59,248 - database.connection - ERROR - Failed to create database tables: role "hbot" does not exist
asyncpg.exceptions.InvalidAuthorizationSpecificationError: role "hbot" does not exist

Root Cause

This error occurs when multiple PostgreSQL instances are running simultaneously on port 5432:

1. Docker PostgreSQL container (hummingbot-postgres) - Configured correctly with hbot role and hummingbot_api database
2. Local PostgreSQL installation (Homebrew, Postgres.app, or system PostgreSQL) - Does NOT have the hbot role

When the application attempts to connect to localhost:5432, it may connect to the local PostgreSQL instance instead of the Docker container, resulting in the authentication error.

How to Diagnose

Check what's listening on port 5432:

lsof -i :5432

Problem scenario (multiple PostgreSQL instances):

COMMAND     PID USER   FD   TYPE     DEVICE      SIZE/OFF NODE NAME
com.docke 21914 feng  202u  IPv6 0xa16385...      0t0  TCP *:postgresql (LISTEN)
postgres  22842 feng    7u  IPv6 0xeb0158...      0t0  TCP localhost:postgresql (LISTEN)

Correct scenario (only Docker):

COMMAND     PID USER   FD   TYPE     DEVICE      SIZE/OFF NODE NAME
com.docke 21914 feng  202u  IPv6 0xa16385...      0t0  TCP *:postgresql (LISTEN)

Solution

Option 1: Stop Conflicting PostgreSQL Services (Recommended)

For Homebrew PostgreSQL:

# List all PostgreSQL services
brew services list | grep postgres

# Stop each running PostgreSQL service
brew services stop postgresql@15
brew services stop postgresql@17
# Or for unversioned: brew services stop postgresql

For Postgres.app:

• Stop the application from the menu bar or quit the app

For system PostgreSQL:

# Find the process
ps aux | grep postgres

# Stop via systemctl (Linux)
sudo systemctl stop postgresql

# Or kill the process (macOS/Linux)
sudo pkill postgres

Option 2: Use Different Port for Docker

If you need both PostgreSQL instances, modify docker-compose.yml to use a different port:

services:
  postgres:
    ports:
      - "5433:5432"  # Changed from 5432:5432

Then update .env:

DATABASE_URL=postgresql+asyncpg://hbot:hummingbot-api@localhost:5433/hummingbot_api

Verification

After stopping conflicting services, verify the connection works:

# Check only Docker PostgreSQL is running
lsof -i :5432

# Test connection to Docker PostgreSQL
docker exec -i hummingbot-postgres psql -U hbot -d hummingbot_api -c "SELECT current_user;"

Expected output:

 current_user
--------------
 hbot
(1 row)

Prevention

To prevent this issue in the future:

1. Disable auto-start for local PostgreSQL:

   brew services stop postgresql@15
   brew services stop postgresql@17
   # This prevents them from starting on system boot

2. Add to README.md troubleshooting section - Document this common port conflict issue

3. Add health check to setup.sh - Verify Docker PostgreSQL is accessible and warn about port conflicts

Related Files

• .env - Database connection configuration (DATABASE_URL)
• database/connection.py - Database connection logic
• docker-compose.yml - Docker PostgreSQL configuration
• setup.sh - Initial PostgreSQL setup

Environment

• macOS (Darwin 24.6.0)
• Docker PostgreSQL 15
• Homebrew PostgreSQL@15 and PostgreSQL@17 (conflicting)
• Python 3.12
• FastAPI with asyncpg driver