PostgreSQL connection URI works, but with parameters fails

Question

I found a weird behaviour connection from command line. My server is a docker container with postgres user and example as password.

if I connect with URI

psql postgres://postgres:example@localhost:5432

Works pretty fine

but If specify the --dbname

psql postgres://postgres:example@localhost:5432 --dbname=postgres

Fails with the following error:

psql: error: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

anybody know why this weird behaviour?

Update:

If I use this format

psql postgres://postgres:example@localhost:5432 metric 

Works fine.

Answer 1

A connection string URI is just an elaboration on a dbname. Since you explicitly specified a dbname with the --dbname flag, then the positional argument (that is, the URI) gets interpreted as something else (in this case a username, but that doesn't matter for you as you don't get far enough to fail for that reason).

So as far as psql is concerned, you told it to connect to the database 'postgres', as the user 'postgres://postgres:example@localhost:5432', and to use the default port and host. But the default host means it is trying to use the Unix-domain socket, which psql thinks should be in /tmp but apparently it is not actually there. (this suggests that maybe psql and postgres itself were installed by different methods--normally they should agree on where the Unix-domain socket should be)

The correct way to specify the database name when using an URI is to put it into the URI after a slash:

psql postgres://postgres:example@localhost:5432/postgres

You could instead do:

psql --dbname=postgres://postgres:example@localhost:5432/postgres

In the one that you report as "works", 'metric' is being ignored. Normally the second positional argument is interpreted as the username, but since the URI already specified a username there was nothing left to for another positional argument to do.

Answer 2

perhaps you need to do this:

psql --dbname=postgres postgres://postgres:example@localhost:5432 

The official documentation states usage is:

psql [option...] [dbname [username]]

That indicates you need to put the options prior to the server name.

• jjanes: Windows demands the options to be first, but on Linux the getopt library accepts the options out of order.
• Hannah Vernon: Ahh, good to know. I upvoted your answer.