Support PostgreSQL URLs for connection

[mkindahl]

Passing a PostgreSQL URL as a connection string.

timescale-doctor postgres://mats@localhost/foobar

[mkindahl]

We can probably use conninfo to see if a URL is passed.

[JamesGuthrie]

What should the interaction be between the existing connection flags and the postgres URL?

The user can currently specify the following flags:

  -U USER, --username USER
                        user name to connect as
  -d DBNAME, --dbname DBNAME
                        name of the database to connect to
  -p PORT, --port PORT  database server port number
  -h HOSTNAME, --host HOSTNAME
                        database server host or socket directory

If they provide the URL, is it valid to provide those parameters as well?

[mkindahl]

The format described in 34.1.1.2. Connection URIs says that the format is

postgresql://[userspec@][hostspec][/dbname][?paramspec]

And it has the following examples

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

Seems reasonable to support this format.

There is a function conninfo_to_dict that seems to accept any connection string and produce the necessary information as a dictionary, so I assume that we can just use that.

[JamesGuthrie]

Sorry I didn't give a good explanation, so it wasn't clear what I was asking.

Effectively my question was, should the following be valid:

timescale-doctor -U someuser postgres://localhost:5432

and if so, what should the precedence rules be for something like this:

timescale-doctor -U someuser postgres://someotheruser@localhost:5432

[mkindahl]

Ah, IC.

It seems reasonable to do whatever psql is doing. Some tests reveal that options are used if the URI does not specify that field:

mats@fury:~$ psql --user=postgres --port=5555 postgresql://localhost:5432/mats
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  role "postgres" does not exist
mats@fury:~$ psql --user=postgres --port=5555 postgresql://mats@localhost:5432/mats
Expanded display is used automatically.
Null display is "[NULL]".
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1), server 15.3)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
Type "help" for help.

mats=# \conninfo
You are connected to database "mats" as user "mats" on host "localhost" (address "127.0.0.1") at port "5432".