Setting up Elixir with Dockerized PostgreSQL

mbski · January 9, 2024, 7:17am

Hello,

I am coming back to Elixir world after one year and I wanted to learn also new stuff so I decided to use Docker to my development.

I pulled pgadmin4 and postgresql, set it up according to documentation/articles. It looks like it works fine. I can log in into pgadmin4 through docker, I can open server with credentials given for postgresql container.

Elixir works fine if I want to play with localhost - I have also desktop version of PGAdmin4 and if I use credentials and localhost I can easily create ecto database.

Problem starts when I want to use locally developed Elixir/Phoenix app with PostgreSQL which is in container. I couldn’t find anything that could have helped me resolved this.

Container name: postgres_db,
Username: postgres,
Password: admin123,
Image: postgres

dev.exs

config :weather, Weather.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "postgres_db",
  port: 5432,
  database: "weather_dev",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

Rest left unchanged from initially created files.

PS F:\weather> mix ecto.create
Compiling 28 files (.ex)
Generated weather app

08:05:39.563 [error] Postgrex.Protocol (#PID<0.416.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (postgres_db:5432): non-existing domain - :nxdomain
** (Mix) The database for Weather.Repo couldn’t be created: connection not available and request was dropped from queue after 2000ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

Ensuring your database is available and that you can connect to it

Tracking down slow queries and making sure they are running fast enough

Increasing the pool_size (although this increases resource consumption)

Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

Thats what i got when i try to create database.

I also tried to use IP address that I put while creating server in postgres, but it still times out.

My another try was also using docker-compose.yml

version: '3.8'

services:
  postgres_db:
    image: postgres
    volumes:
      - /var/lib/postgresql/data
    container_name: weather
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: weather_dev
    ports:
      - 5432:5432

Accordingly I updated dev.exs to fit those variables and stopped container created meanually (to omit port conflicts). Effect remains the same. Its not possible to connect Elixir with dockerized Postgres and I cant create database for my app.

Thanks for any help

gmile · January 9, 2024, 7:30am

In short, in config/dev.exs change postgres_db hostname to localhost and try again - it should work:

diff --git a/config.exs b/config.exs
--- a/config.exs
+++ b/config.exs
@@ -3,3 +3,3 @@ config :weather, Weather.Repo,
   password: "postgres",
-  hostname: "postgres_db",
+  hostname: "localhost",
   port: 5432,

mbski · January 9, 2024, 8:51am

Actually it works for local instance of postgres but not for postgres container in docker.
I set password provided during setting up contaner, as a host (according to docs) i set up container name.

I get errors about non-existing domain.

With local Postgres everything works
With Postgres in docker I cant create database using mix ecto.create.

D4no0 · January 9, 2024, 8:56am

Is your elixir container part of that docker-compose too? If yes your configuration should work, otherwise you need to specify a custom network, as only containers from the same docker-compose are part of the same network automatically.

mbski · January 9, 2024, 9:27am

Actually I didnt put Elixir into container as I didn’t do anything in app. Just started and wanted to connect everything together and even meant to work with app development in container.

I meant to work directly from Windows with developing app but having it connected to containerized database. As it’s first time using docker and learning it I dont get whole concepts yet and how it should be resolved.

D4no0 · January 9, 2024, 9:36am

If your elixir application is not in container, then the only way is to expose the port and connect on localhost, just as it was mentioned previously by others.

mbski · January 9, 2024, 1:36pm

I read a little about that but it still doesn’t work.
To create container and expose port I used command:

docker run --name postgres_db -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres

Using those credentials and IPHost provided while inspecting container I could go into server in pgadmin4. Basically it works.

Now the part that doesn’t work with Elixir
dev.exs

config :weather, Weather.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "postgres_db",
  port: 5432,
  database: "weather_dev",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

As you can see. Credentials are the same. Hostname is container name in this case:
Obviously i got:

14:13:27.670 [error] Postgrex.Protocol (#PID<0.211.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (postgres_db:5432): non-existing domain - :nxdomain

I changed hostname to localhost:

FORMATTER ERROR: bad return value

14:14:18.846 [debug] ** (RuntimeError) bad return value from Logger formatter Logger.Formatter, got [[<<179, 101, 109, 32, 110, 105, 101, 32, 112, 111, 119, 105, 111, 100, 179, 97, 32, 115, 105, 234, 32, 100, 108, 97, 32, 117, 191, 121, 116, 107, 111, 119, 110, 105, 107, 97, 32, 34, 112, 111, 115, 116, 103, 114, 101, 115, 34>>, “\n”] | “\e[0m”] after “\e[31m\n14:14:18.843 [error] Postgrex.Protocol (#PID<0.416.0>) failed to connect: ** (Postgrex.Error) KATASTROFALNY 28P01 (invalid_password) autoryzacja has”
(kernel 9.0.2) logger_h_common.erl:430: :logger_h_common.string_to_binary/1
(kernel 9.0.2) logger_h_common.erl:396: :logger_h_common.do_log_to_binary/2
(kernel 9.0.2) logger_h_common.erl:177: :logger_h_common.log/2
(kernel 9.0.2) logger_backend.erl:51: :logger_backend.call_handlers/3
(db_connection 2.6.0) lib/db_connection/connection.ex:110: DBConnection.Connection.handle_event/4
(stdlib 5.0.2) gen_statem.erl:1379: :gen_statem.loop_state_callback/11
(stdlib 5.0.2) proc_lib.erl:241: :proc_lib.init_p_do_apply/3

FORMATTER ERROR: bad return value

14:14:18.881 [debug] ** (RuntimeError) bad return value from Logger formatter Logger.Formatter, got [[<<179, 101, 109, 32, 110, 105, 101, 32, 112, 111, 119, 105, 111, 100, 179, 97, 32, 115, 105, 234, 32, 100, 108, 97, 32, 117, 191, 121, 116, 107, 111, 119, 110, 105, 107, 97, 32, 34, 112, 111, 115, 116, 103, 114, 101, 115, 34>>, “\n”] | “\e[0m”] after “\e[31m\n14:14:18.881 [error] Postgrex.Protocol (#PID<0.424.0>) failed to connect: ** (Postgrex.Error) KATASTROFALNY 28P01 (invalid_password) autoryzacja has”
(kernel 9.0.2) logger_h_common.erl:430: :logger_h_common.string_to_binary/1
(kernel 9.0.2) logger_h_common.erl:396: :logger_h_common.do_log_to_binary/2
(kernel 9.0.2) logger_h_common.erl:177: :logger_h_common.log/2
(kernel 9.0.2) logger_backend.erl:51: :logger_backend.call_handlers/3
(db_connection 2.6.0) lib/db_connection/connection.ex:110: DBConnection.Connection.handle_event/4
(stdlib 5.0.2) gen_statem.erl:1379: :gen_statem.loop_state_callback/11
(stdlib 5.0.2) proc_lib.erl:241: :proc_lib.init_p_do_apply/3

** (Mix) The database for Weather.Repo couldn’t be created: killed

Error is different - result the same. No connection.
It looks like it tries to go into my local instance of postgres but there are invalid credentials for that.
I dont know how it’s supposed to be working with localhost if its signed to local machine and not docker even if i exposed port and it should be working.

I am out of ideas for a now.

Tyson · January 9, 2024, 2:15pm

Most common cause of mismatched credentials when trying to connect to PG in container is that you still have PG running on the host system and are actually connecting to that. Either shut down PG on the host and restart the container, or bind the container port to something other than 5432 (and then configure Ecto to use the new port).

mbski · January 9, 2024, 2:29pm

And that post with addition of advices above are solutions. I had of course closed Postgres but in the background there were left some tasks. I had to kill them and then Elixir created db and schemas. Perfect.

Other port didnt work by the way, but probably because I hadn’t it opened.

Big thanks for fast reply for silly questions