Using Postgres with Docker on a Mac

When running PostgreSQL in a Docker container for testing or local development purposes, you can sometimes run into an issue where the postgres server on the host takes precedence over the postgres server running in the container.

You will see an error like "Role 'postgres' does not exist" when you try to connect to the server if that is the case.

This can happen when you run postgresql as a service through Homebrew.

The solution is to check which processes are running on port 5432, stop the service and then clean up any remaining processes:

brew services stop postgresql
lsof -i :5432
kill -9 <pid>

Here's what the output will look like:

% brew services stop postgresql
Stopping `postgresql@14`... (might take a while)
==> Successfully stopped `postgresql@14` (label: homebrew.mxcl.postgresql@14)

% lsof -i :5432
COMMAND     PID    USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
postgres    664 rudolfo    8u  IPv6 0xda1da9a9441be1cf      0t0  TCP localhost:postgresql (LISTEN)
postgres    664 rudolfo    9u  IPv4 0xda1da9a47826a65f      0t0  TCP localhost:postgresql (LISTEN)
com.docke 44908 rudolfo  244u  IPv6 0xda1da9a47921f1cf      0t0  TCP *:postgresql (LISTEN)

% kill -9 664

% lsof -i :5432
COMMAND     PID    USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
com.docke 44908 rudolfo  244u  IPv6 0xda1da9a47921f1cf      0t0  TCP *:postgresql (LISTEN)

After that, any connection to localhost:5432 will be to the postgres server running within the Docker container.

This article was helpful in understanding the underlying issue: "How to Fix the “Role ‘Postgres’ Does Not Exist” Error When Deploying PostgreSQL Docker Container"

Another option is to map the published Docker ports to a higher port range to avoid this issue:

docker run -it --rm -p 105432:5432 postgres:16

Cover image by Bernd 📷 Dittrich on Unsplash