PostgreSQL URIs versus Unix Domain Sockets

I recently saw https://mydbanotebook.org/post/cant-connect/g/post/cant-connect/ which presents a nice little flowchart for debugging why you might not be able to connect to your PostgreSQL database.

I was recently struggling with setting database connections inside the context of Gitlab CI, where my regression test needed to connect to a “sidecar” PostgreSQL instance. (See the repo… https://gitlab.com/cbbrowne/mahout/-/blob/master/.gitlab-ci.yml)

I have been trying to migrate my connection usages to exclusively use URIs (where possible)… The concept is nicely documented in the standard documentation here https://www.postgresql.org/docs/12/libpq-connect.html#LIBPQ-CONNSTRING, where a URI commonly looks like: postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

In the Docker context where I was doing this, I needed to use Unix Domain Sockets, where the URI will omit the host, doing something like: postgresql://%2Fvar%2Flib%2Fpostgresql/dbname

Something about this would maddeningly lead to refusals to connect. I could instead use a “traditional connection string,” and ultimately gave up on trying to use URIs in this context. My URI is "host=postgres user=runner port=5432 dbname=${POSTGRES_DB}" for anyone interested 😉