Why does Phoenix (ecto/Postgresx) fail to Connect in dev

Question

I am beginning my Elixir/Phoenix journey and having some trouble with my postgres connection.

When I start up my server I get:

 $ mix phoenix.server
 [error] Postgrex.Protocol (#PID<0.214.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.217.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.218.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.211.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.215.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.219.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.216.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.213.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.212.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [error] Postgrex.Protocol (#PID<0.210.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused
 [info] Running Rumbl.Endpoint with Cowboy using http://localhost:4000
 [error] Postgrex.Protocol (#PID<0.215.0>) failed to connect: ** (Postgrex.Error) tcp connect: connection refused - :econnrefused

Being new to Elixir, Phoenix, and Ecto I am unclear how to debug this problem. Any suggestions as to what my problem is or how I might go about debugging it would be much appreciated.

My app's set up

I have a basic app

mix phoenix.new rumbl
cd rumbl
mix deps.get
mix deps.compile

My config/dev.exs has the following db setup

# Configure your database
config :rumbl, Rumbl.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "elixir",
  database: "rumbl_dev",
  hostname: "localhost",
  pool_size: 10

When I run mix ecto.create there are no errors and I can see the rumbl_dev when I run \l in psql. It is owned by the elixir user too.

Running mix ecto.migrate throws the same connections errors

My pg_hba.conf file has the following

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Here is what I see when I log in with psql

$ psql --dbname=rumbl_dev --username=elixir --host=127.0.0.1 --password
Password for user elixir:
psql (9.4.5)
Type "help" for help.

rumbl_dev=>

Mileage

Can you try adding the password to the config and see if that helps. config :rumbl, Rumbl.Repo, adapter: Ecto.Adapters.Postgres, username: "elixir", database: "rumbl_dev", hostname: "localhost", password: "***password***" pool_size: 10 – Mauricio

I tried that too but get the same results - thanks for the suggestion @Mauricio – Mileage

can you try running the following command from the terminal: psql --dbname=rumbl_dev --username=elixir --host=127.0.0.1 --password – Mauricio

hostname: "localhost" ⇒ hostname: "127.0.0.1" might help. – Razzia

@Mauricio I appended the result of that to my question. It lets me in no problem. – Mileage

@mudasobwa I had tried that but same result – Mileage

After all, do you have {:phoenix_ecto, "~> 3.0"}, {:postgrex, ">= 0.0.0"} in your mix.exs’ deps? – Razzia

is this line somewhere in your config/dev.exs? --- config :rumbl, ecto_repos: [Rumbl.Repo], if not can you add it and try ecto migration then? – Mauricio

@Mauricio that was in my config/config.exs file but I tried it in config/dev.exs also but got the same result unfortunately – Mileage

@mudasobwa my mix.exs has exactly those in the lock they come through as "postgrex": {:hex, :postgrex, "0.11.2", ...} and "phoenix_ecto": {:hex, :phoenix_ecto, "3.0.1", ...} – Mileage

Answer 1

Ok, so I figured it out. It is a simple mistake on my part in the end. Though an easy one to make.

I am using Boxen on my Mac and it changes the port to 15432 for some reason.

I may have landed on this sooner if the mix ecto.create had failed. Not sure why that works.

Hopefully this will help others in the future

Mileage

glad it worked. don't know why mix ecto.create part works though. you could check it on the console too for the port psql --dbname=rumbl_dev --username=elixir --host=127.0.0.1 --port=5432 --password – Mauricio

@Mauricio - it does not work on the console with port=5432. It is odd that the create works. – Mileage

great! thanks so much, I'm using boxen as well. specifying port: 15432 on config.dev.exs solved it. – Chemurgy

Answer 2

This happened to me twice, after macOS crashed.

Most recently I used this:

Fix 1

1. Run postgres -D /usr/local/var/postgres

2. Copy the number after PID if you see something like this:

   FATAL:  lock file "postmaster.pid" already exists 
   HINT:   Is another postmaster (PID 379) running in data directory "/usr/local/var/postgres"?
   

3. kill -9 PID with the process # in place of PID.

---

The steps that worked for me previously:

Fix 2

brew update
brew upgrade
brew postgresql-upgrade-database

Haygood

Fix 1 worked for me (after macOS crashed). Thank you. – Mangum

Same here. My macOS crashed. Seems to be happening more recently. Came back to postgres giving the same problem and this Fix 1 fixed it for me. The accepted answer seems like a different issue. – Apprise

Answer 3

• Delete the database rumbl_dev in postgresql to make a fresh start.

• If you like, you can try md5 auth for the dev version on localhost by adding the following line to the pg_hba.conf

  # host DATABASE USER ADDRESS METHOD
  
  host rumbl_dev elixir localhost md5
  

  Note: See here for more on setting up the /etc/postsgresql/9.4/pg_hba.conf and change the settings as you see fit.

• Add the complete settings with password to dev.exs:

  config :rumbl, Rumbl.Repo, 
  adapter: Ecto.Adapters.Postgres, 
  username: "elixir", 
  database: "rumbl_dev", 
  hostname: "localhost", 
  password: "***password***",
  pool_size: 10
  

• Try running mix do ecto.create, ecto.migrate and see how it goes.

Hope this helps, if not, I'm out of ideas here.

Mauricio

same result I am afraid :( – Mileage

Answer 4

You need to include your database user's password within your config block.

config :rumbl, Rumbl.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "elixir",
  password: "???",
  database: "rumbl_dev",
  hostname: "localhost",
  pool_size: 10

Duchess

Answer 5

I have the same problem every time I don't start Postgres before calling mix phoenix.server. I use https://postgresapp.com to kick it off.

Episodic