Setup PostgreSQL with Docker for Phoenix

This skill sets up PostgreSQL 18 Alpine and pgAdmin4 containers for Phoenix Framework development. It audits your existing configuration, identifies mismatches, and creates or updates docker-compose files with proper credential synchronization.

Workflow Overview

Audit Phase - Read config files, discover existing docker-compose setup, identify mismatches
Decision Phase - Ask user about file choices, credentials, and sync preferences
Setup Phase - Create/update docker-compose, .env file, and config files
Verify Phase - Start containers and optionally test database connection

Step 1: Audit Existing Configuration

Run the discovery script to audit the current database setup:

elixir phx-setup-pg/scripts/audit_db.exs [project_root]

Or manually perform the audit:

1.1 Read Phoenix Config Files

Check config/dev.exs for database configuration:

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "my_app_dev",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

Also check config/test.exs and config/runtime.exs if present.

1.2 Discover Docker Compose Files

Search for existing compose files:

ls -la docker-compose.yml docker-compose.dev.yml 2>/dev/null

1.3 Check for .env Files

Look for existing environment files:

ls -la .env .env.example .envrc 2>/dev/null

1.4 Identify Mismatches

Compare the configuration and report:

Hostname mismatch: Config uses localhost but docker uses service name (e.g., db or postgres)
Credential mismatch: Username/password differ between config and docker-compose
Database name mismatch: Different database names
Port mismatch: Config expects different port than docker exposes
Missing containers: No postgres service defined
Missing pgAdmin: No pgAdmin for database management

Step 2: Ask User Questions

Before making any changes, ask the user these questions:

2.1 Docker Compose File Choice

Question: Which docker-compose file should I create or update?

docker-compose.dev.yml - Development-only services (recommended)
docker-compose.yml - Main compose file
Extend existing file - Add to current setup

Show the user what files exist:

Existing compose files:
- docker-compose.yml (found)
- docker-compose.dev.yml (not found)

2.2 Credential Strategy

Question: How should database credentials be managed?

Options:

Use .env file (recommended) - Store credentials in .env, referenced by docker-compose
Update config/dev.exs - Hardcode credentials in Phoenix config to match docker
Both - Use .env AND update config to read from system env vars
Custom credentials - User provides specific username/password

2.3 Credential Values

Question: Use generated credentials or provide your own?

Generate random - Create secure random passwords (recommended for dev)
Use defaults - postgres/postgres (simple for local dev)
Custom - User specifies username and password

2.4 Port Configuration

Question: Use default ports or customize?

PostgreSQL: Default 5432 (or customizable, e.g., 5433 if port in use)
pgAdmin: Default 5050 (or customizable)

Step 3: Create or Update Docker Compose

3.1 New Docker Compose File

If no compose file exists, create one with PostgreSQL 18 Alpine and pgAdmin4:

version: '3.8'

services:
  db:
    image: postgres:18-alpine
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
      POSTGRES_DB: ${POSTGRES_DB:-my_app_dev}
    ports:
      - "${POSTGRES_PORT:-5432}:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - db_network

  pgadmin:
    image: dpage/pgadmin4:latest
    restart: unless-stopped
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL:-admin@example.com}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD:-admin}
    ports:
      - "${PGADMIN_PORT:-5050}:80"
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    networks:
      - db_network
    depends_on:
      - db

volumes:
  postgres_data:
  pgadmin_data:

networks:
  db_network:
    driver: bridge

3.2 Update Existing Docker Compose

If a compose file exists, add or update services:

If postgres service exists:

Update image to postgres:18-alpine if different
Ensure environment variables match user preferences
Add pgAdmin service if missing
Add volumes and networks if missing

If postgres service missing:

Add postgres service from template above
Add pgAdmin service
Add volumes and networks sections

3.3 Create .env File

If user chooses .env strategy, create .env file:

# PostgreSQL Configuration
POSTGRES_USER=postgres
POSTGRES_PASSWORD=<generated_or_custom_password>
POSTGRES_DB=my_app_dev
POSTGRES_PORT=5432

# pgAdmin Configuration
PGADMIN_EMAIL=admin@example.com
PGADMIN_PASSWORD=admin
PGADMIN_PORT=5050

Remind user to add .env to .gitignore if not already present.

Step 4: Update Phoenix Configuration

4.1 If Updating config/dev.exs

Update the repo configuration to match docker setup:

When using .env (recommended):

config :my_app, MyApp.Repo,
  username: System.get_env("POSTGRES_USER", "postgres"),
  password: System.get_env("POSTGRES_PASSWORD", "postgres"),
  hostname: System.get_env("POSTGRES_HOST", "localhost"),
  database: System.get_env("POSTGRES_DB", "my_app_dev"),
  port: System.get_env("POSTGRES_PORT", "5432") |> String.to_integer(),
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

When hardcoding credentials:

config :my_app, MyApp.Repo,
  username: "postgres",
  password: "<password_from_env_or_user>",
  hostname: "localhost",
  database: "my_app_dev",
  port: 5432,
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

4.2 Update Hostname for Docker Network

If the Phoenix app will run inside Docker (e.g., in a docker-compose with the db), update hostname to the service name:

# When running Phoenix in Docker
hostname: System.get_env("DB_HOST", "db")

For local development (Phoenix running on host, DB in Docker), use localhost.

Step 5: Verify Setup

5.1 Start Containers

docker compose up -d

Wait for containers to be healthy:

docker compose ps

5.2 Test Database Connection (Optional)

Ask user if they want to test the connection:

# Create database (for Phoenix apps)
mix ecto.create

# Or test connection directly
psql -h localhost -p 5432 -U postgres -d my_app_dev

5.3 Verify pgAdmin Access

Tell user:

pgAdmin is available at http://localhost:5050
Default credentials: admin@example.com / admin
To connect to PostgreSQL server in pgAdmin:
- Host: db (service name, when inside Docker network)
- Port: 5432
- Username/Password: as configured

Output

After completion, report to the user:

✅ PostgreSQL 18 Alpine container started
✅ pgAdmin4 container started on port 5050
✅ Docker compose file created/updated: <filename>
✅ Environment file created: .env (added to .gitignore)
✅ Phoenix config updated: config/dev.exs

Next steps:
1. Access pgAdmin at http://localhost:5050
2. Run 'mix ecto.create' to create your database
3. Run 'mix ecto.migrate' to run migrations

Edge Cases

Existing Docker Services

If the compose file has other services (redis, app, etc.):

Add postgres/pgadmin to existing services
Connect to existing networks instead of creating new ones
Don't duplicate volume definitions

Port Conflicts

If default ports are in use:

Check with lsof -i :5432 and lsof -i :5050
Suggest alternative ports (5433, 5051, etc.)
Update both docker-compose and Phoenix config

Multiple Environments

For projects with multiple environments:

docker-compose.dev.yml - Development (this skill's focus)
docker-compose.test.yml - Test environment
docker-compose.yml - Production/staging

Keep them separate unless user requests otherwise.

Existing PostgreSQL

If user already has PostgreSQL installed locally:

Ask if they want to migrate to Docker or keep local
If migrating, help export/import data
Update config to point to new Docker instance

Guidelines

Always ask before modifying files - Show planned changes first
Preserve existing configuration - Only change database-related settings
Use environment variables - Prefer .env approach for flexibility
Document changes - Tell user exactly what was modified
Verify with user - Confirm containers are running before finishing

Troubleshooting

Container won't start

Check for port conflicts: lsof -i :5432
Check Docker daemon: docker info
Check existing volumes: docker volume ls

Can't connect from Phoenix

Verify hostname is localhost (not db service name) for local Phoenix
Check port matches in both config and docker-compose
Ensure container is running: docker compose ps

pgAdmin can't connect to PostgreSQL

Inside pgAdmin, use hostname db (service name)
Verify credentials match
Ensure both containers are on same network