Confusion between Cluster / Database / Schema for PostgreSQL

ddevienne's profile image ddevienne posted 1 year ago in General Permalink

Hi. I'm trying HeidiSQL for the first time, against PostgreSQL.

I was able to connect OK, via the Session Manager, but I find the UI a bit confusing, for several reasons:

  • forces me to select a version of libpq. Just use a default, like the default (v15 for now).
  • the single database option didn't work. Had to type in a DB explicitly

Regarding the latter, you need to connect once to the DB to list the available DBs, but I was expecting that to happen implicipty, or something like that.

OK, so now I'm in. But rigth away, I see HeidiSQL shows PostgreSQL schemas in its database tab, which is not correct.

In PostgreSQL, the top-level is the Cluster, which listens on a given host+port, and serves several databases. And credentials (username+password) are NOT per-Database, but per-Cluster.

Of course, you cannot connect to a cluster, you must connect to a specific database within that cluster, but since the credentials are cluster-wide, once you are connected, reconnecting to any other DB (of that cluster) that the user as access to should be trivial, w/o needing to go back to the session manager at all.

I guess in the current scheme, one must create an explicit session per-database, while ideally browsing the DBs of a cluster should be done dynamically once connected to any one of them.

Sorry if I sound criticial. I mean well, and just want to report my initial impressions.

Listing schemas (pg_namespace in the PostgreSQL dictionaries) in a Database tab seems wrong to me, especially when I really wanted to see actual (other) PostgreSQL databases of that cluster.

Any chance we could open the whole PostgreSQL cluster, by connecting to one DB and should all others, dynamically/automatically opening more connections (using the same per-Cluster credentials) when those are browsed?

Note that the second time I went to the Session Manager, the list of DBs was shown, when clicking the Database down-arrow, but opens a long popup designed for very few databases apparently, and that list was not even alpha-sorted. Sounds like it does not scale to many databases.

I think HeidiSQL could be a great tool for PostgreSQL if it better embraced the multi-DB nature of PostgreSQL clusters, and not assumed a cluster has a small discrete list of DBs. We can have dozens of databases ourselves, many dynamic (coming and going), so have to go through the Session Manager is not the right paradigm.

One should configure the PostgreSQL *cluster (and a default DB to connect too), once, with the per-Cluster credentials, and all other DBs (that this particular user can CONNECT to) naturally should be available, dynamically, w/o any further configuration. And just use the same Favorites mechanism available elsewhere to filter the DB list to those of interest, if any.

My $0.02. Please take this as constructive feedback.

Thanks, --DD

ansgar's profile image ansgar posted 1 year ago Permalink

HeidiSQL has a focus on MySQL and MariaDB. The other server types (Postgres, MSSQL, SQLite, Firebird/Interbase) are supported only partially. It's unlikely that HeidiSQL will get all these issues fixed or enhanced in the future, to be honest.

ddevienne's profile image ddevienne posted 1 year ago Permalink

Pity :(

But I understand of course.

My own focus is SQLite and PostgreSQL.

I'm a dev too, but I don't know Delphi or Pascal (mostly C/C++), and you don't accept contributions anyways, which I also fully understand too.

Still, a single server/cluster serving more than 1 DB is not unique to PostgreSQL. Oracle does it too with PDBs. I'm surprised MySQL/MariaDB is more limited, to be honest.

Please login to leave a reply, or register at first.