Can't connect to remote PostgreSQL DB from docker-compose

I have the following compose.yaml:

services:
  pg-local:
    image: postgres:14.7
    volumes:
      - pgdata:/var/lib/postgresql/data
    env_file:
      - .env.local_postgres_docker_env
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 30s
      timeout: 5s
      retries: 3

  configuration-manager:
    build:
      context: ..
    ports:
      - "5000:8080"
    volumes:
      - ~/.aws:/root/.aws
    depends_on:
      db-replicator:
        condition: service_started
    env_file:
      - .env.config_manager_docker_env

  db-migrator:
    build:
      context: ..
    volumes:
      - .:/migrations
    depends_on:
      pg-local:
        condition: service_healthy
    env_file:
      - .env.config_manager_docker_env
    command: bash -c 'poetry run flask db upgrade && echo "Migration completed successfully"'

  db-replicator:
    build:
      context: ./postgres
    depends_on:
      db-migrator:
        condition: service_completed_successfully
    env_file:
      - .env.local_postgres_docker_env
      - .env.production_postgres_secrets
    command: >
      bash -c '
      PGPASSWORD=${PROD_POSTGRES_PASSWORD} pg_dump -v --host=${PROD_HOST} --username=${PROD_USER} --dbname=${PROD_DB_NAME} --clean --create --no-password --file=/tmp/dump.sql && 
      PGPASSWORD=${POSTGRES_PASSWORD} psql --host=pg-local --username=${POSTGRES_USER} < /tmp/globals.sql' &&
      PGPASSWORD=${POSTGRES_PASSWORD} psql --host=pg-local --username=${POSTGRES_USER} < /tmp/dump.sql'

volumes:
  pgdata:

pg-local and db-migrator services are working great! However the db-replicator service fails when doing pg_dump with the following error:

pg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

All the environment variables are well-defined for all services (checked with `docker compose config)

I tried to use another network
I tried network_mode: host
I tried running the relevant image with docker run ... (and not docker compose) and it worked.

Solution

Environment variable handling in Compose can be a little complex. It happens in two stages:

Compose substitutes $VARIABLE references everywhere in the file, using the host environment and the .env file, and ignoring any container-specific settings.
Using per-container environment: and env_file: settings, it constructs the environment for the main container process, which could potentially be a shell.

In your setup, if you're running command: bash -c '... $VARIABLE ...', Compose substitutes the $VARIABLE reference in the first step; the shell doesn't see it. If perhaps $PROD_HOST is only set in the .env.local_postgres_docker_env file, then it won't be present when Compose does the substitution based on the host environment, and you'll get an empty string instead.

The direct answer here is to escape the dollar sign by writing $$ every place you need a literal $ character to be passed into the container.

command: >
  bash -c '
  PGPASSWORD=$${PROD_POSTGRES_PASSWORD} pg_dump -v --host=$${PROD_HOST} --username=$${PROD_USER} --dbname=$${PROD_DB_NAME} --clean --create --no-password --file=/tmp/dump.sql && 
  PGPASSWORD=$${POSTGRES_PASSWORD} psql --host=pg-local --username=$${POSTGRES_USER} < /tmp/globals.sql' &&
  PGPASSWORD=$${POSTGRES_PASSWORD} psql --host=pg-local --username=$${POSTGRES_USER} < /tmp/dump.sql'

More practically, I might avoid writing complex scripts like this directly in your Compose file. Since you seem to be building a custom image for this step, I'd write this as a shell script (move those commands into a postgres/replicate.sh file) and make it be the default CMD for your image

# postgres/Dockerfile
...
COPY replicate.sh /usr/local/bin/replicate
CMD ["replicate"]

With the commands in a dedicated script and the script being the Dockerfile default CMD, you won't have special escaping concerns and you don't need a Compose command: override.

I also might consider removing these separate containers entirely, and moving these database-maintenance steps into your main application container's entrypoint wrapper script; see for example this answer.