Troubleshooting Postgrex

My container application is not connecting to PostgreSQL on the host machine. To troubleshoot the issue, I want to rule out some variables such as the container networking.

I add postgresql-client to the Dockerfile and build a new temporary image for troubleshooting. I launch a container with this new image and attempt to connect to the PostgreSQL server:

$ docker run -it --entrypoint /bin/bash --add-host=host.docker.internal:host-gateway minotaur:tmp
$ psql -h host.docker.internal -d minotaur_production -U minotaur_application
psql: error: could not connect to server: Connection timed out
        Is the server running on host "host.docker.internal" (172.17.0.1) and accepting
        TCP/IP connections on port 5432?

Immediately after reading the error message, I realize that I never configured PostgreSQL to accept connections from the Docker bridge network.

Configure PostgreSQL networking

I update pg_hba.conf of the host PostgreSQL service to allow connections from the default Docker bridge network subnet.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    minotaur_prod   minotaur_app    172.17.0.0/16           md5

I then update postgresql.conf to listen on all addresses instead of the default localhost.

listen_addresses = '*'

I then restart the PostgreSQL service to apply these configuration changes.

$ sudo systemctl restart postgresql

I connect back to the troubleshooting container and try to connect to PostgreSQL server again. The connection still times out.

My guess is the connection is being blocked by the firewall since I have not explicitly granted access to the PostgreSQL port. I allow connections to port 5432 from addresses within the default Docker bridge network subnet.

$ sudo ufw allow from 172.17.0.0/16 to any port 5432

Back within the troubleshooting container, I run the psql command again to connect and I am prompted for a password! I plug in the password for the application user and the connection is successful.

I am now confident that Ecto can connect to the database through the Postgrex module. I stop the troubleshooting container and again launch a standalone container for the Phoenix webserver:

docker run -e SECRET_KEY_BASE=$MINOTAUR_SECRET_KEY_BASE -e DATABASE_URL=$MINOTAUR_DATABASE_URL --add-host=host.docker.internal:host-gateway minotaur:ecto

The logs show the application starts up, the Phoenix endpoint is listening, and there are no error logs from Postgrex. Ecto is now working in production! At least for containers running outside the Swarm service.

PostgreSQL access from Docker Swarm

I need to configure access to the PostgreSQL server from within the Swarm containers. I check the subnet for the Docker Swarm bridge network with docker network inspect docker_gwbridge and see it is 172.18.0.0/16. I need to allow connections to PostgeSQL from IPs within this network.

I update pg_hba.conf to accept connections from the subnet for the application user. I also add a new firewall rule with ufw to allow the subnet to access the default PostgreSQL port 5432.

To the service docker-compose.yml, I add the DATABASE_URL environment variable and extra_hosts mapping for the host IP address. I also update the image tag with the most recent build which includes the Ecto migrate script.

services:
  minotaur:
    # …
    Image: "minotaur:ecto"
    environment:
      SECRET_KEY_BASE: ${MINOTAUR_SECRET_KEY_BASE}
      DATABASE_URL: ${MINOTAUR_DATABASE_URL}
      DNS_CLUSTER_QUERY: tasks.minotaur-stack_minotaur
    extra_hosts:
      - "host.docker.internal:host-gateway"

I run docker stack deploy with the updated compose file and the existing stack name. Once the service is stable with the latest changes, I check the container logs which show no connection errors. This means the container successfully opened connections to the PostgreSQL server on the host!

The next steps are to create a workflow for running migrations in production.