Avoid Unix Socket Connections with Postgres PGHOST

09 February 2024

I had a long, meandering journey to get Postgres to properly connect. I was using an Elixir client, Postgrex, but this advice will probably apply generally. Here's how to avoid a particular problem in getting your postgres clients to connect with tcp/ip on localhost.

Defining TCP/IP

By default, if you tell your clients to use localhost as the postgres host, they'll try to use tcp/ip. In other words, they'll connect on localhost:5432.

There's an alternate way that a postgres client could talk to the database, and that's through unix sockets, or inter-process communication (IPC). Sometimes that can be better for performance or security.

But I wanted to connect to postgres using TCP/IP, localhost:5432. So I set up my Ecto config:

config :phx1, Phx1.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "phx1_dev",
  stacktrace: true,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10

Or sometimes I started up iex -S mix, and would use Postgres directly (what Ecto uses as a dependency):

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "postgres")

The problem is that neither worked for me.

The Error

Constantly, consistently, eternally!, i would get this:

13:05:11.485 [error] Postgrex.Protocol (#PID<0.213.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (/tmp/.s.PGSQL.5432): no such file or directory - :enoent

Why in the world would it be trying to connect to /tmp/.s.PGSQL.5432? That is one of the default unix socket locations for Postgres. But I was specifying localhost -- everywhere!

Other oddities in this journey:

  • Many different postgres configurations didn't work (local postgres, docker, unix sockets, shared volumes, egh)

  • It also failed using other drivers, epgsql and pgo.

  • It worked ok using Erlang odbc.

  • It worked ok using a tiny testbed in node-postgres.

  • It worked ok on a separate computer.

  • It worked didn't work ok on a still another computer.

  • It didn't matter whether the odbc link failed or not (after installing unixodbc-dev or odbc-postgresql) on Erlang install.

Then finally, my search for light in the darkness came to a joyful end.

The Solution

I'm not even sure what caused this to come into my mind -- probably the grace of divine intervention -- but I ran:

env | rg PG

And saw:

PGHOST=/tmp

Well. There's a connection: Postgres host specification, and in the same directory as the error specified.

Turns out that I had an old line my .zshrc from some setup long ago:

export PGHOST=/tmp

Once I removed that line and started a new terminal session, my mix ecto.create worked, my Postgrex.start_link worked, and I was exultant.

That is one powerful variable that the Elixir Postgres drivers really respect!