Using Postgres with Docker on a Mac

When running PostgreSQL in a Docker container for testing or local development purposes, you may encounter an issue where the Postgres server on the host takes precedence over the Postgres server running in the container.

If you experience this issue, you will receive an error message such as "Role 'postgres' does not exist" when you attempt to connect to the server.

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

Clean Up: Stopping Postgres on the Host

The solution is straightforward: 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 helped me understand the underlying issue: "How to Fix the “Role ‘Postgres’ Does Not Exist” Error When Deploying PostgreSQL Docker Container"

Re-mapping The Container Port

An alternative approach is to map the published Docker ports to a higher port range to avoid this issue:

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

Check "Understand permission requirements for Docker Desktop on Mac" if there are more permission or port issues.

Cover image by Bernd 📷 Dittrich on Unsplash