Re: PostgreSQL URI

Hi Tom,

> On 25. Feb, 2021, at 16:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Experimenting, it does let you omit the host and specify a port:
>
> $ psql -d postgresql://:5433
> 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.5433"?
>
> So the original syntax diagram is not wrong. We could add brackets
> to clarify the repeatable part:
>
> postgresql://[user[:password](at)][[host][:port][,...]][/dbname][?param1=value1&...]
>
> but I'm less sure that that's an improvement.

hmm, the following indeed connects me to the primary, leaving out the host part completely:

$ psql -d postgresql://:5432,:5433/postgres?target_session_attrs=read-write
psql (13.2, server 12.6)
Type "help" for help.

postgres=# select user, current_setting('data_directory');
user | current_setting
----------+----------------------
postgres | /data/pg01/cdb01b/db
(1 row)

remark: cdb01a currently is replica and cdb01b is currently primary of a local Patroni test cluster, replicating between /data/pg01/cdb01a/db and /data/pg01/cdb01b/db.

So, my suggestion is:

postgresql://[user[:password](at)][[host][:port]][,...][/dbname][?param1=value1&...]

Still, I think that it's an improvement, because it makes clear that not only the port, but also the host may be repeated.

Cheers,
Paul