Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Richard, Jeroen, list; On Thu, Feb 4, 2021 at 2:02 AM Richard Duivenvoorde wrote: > On 2/4/21 10:16 AM, Groene Bij wrote: > > Take this example: > > I would like to see DB Manager as just a shell or GUI which will work > with > > every postgresql and postgis version the same way, but in fact it isn't. > I > > found DB manager to work differently from qgis3.10 to 3.12 and above. > > Something changed and as a result, simple select queries can take up 10 > to a > > 100 times longer in qgis3.12 and up compared to qgis3.10 > > (see also https://github.com/qgis/QGIS/issues/40643) > > > > So, I am mostly looking for some kind of reassurance. Things like: > qgis3.16 > > will work fine with postgresql version a to d and postgis version f to > l. Or > > qgis will work fine with any postgresql above version x and postgis above > > version y. I do realize these are difficult statements to make (how to > test > > them?) > > Hi Jeroen, > > I think you are complicating things :-) > > There are postgres/gis SERVER versions > And postgres/gis/ CLIENT LIBRARY version > > In general: IF you have a SERVER version you should try to have the same > CLIENT (=library) version. > BUT: only to be able to do the new things introduced in the SERVER version > OR to have a better client version because they fixed bugs in that > client(library) version. > In general: you can probably also connect to a new SERVER version with an > older CLIENT (unless off course you want to do things that at the time of > the CLIENT version was just not possible). > > My guess: your issue above has anything to do with the pg client libraries > used, or the combi server/client. > Chances are bigger that either QGIS is now running other SQL queries OR > the logic (of loops for example) in QGIS has changed. > Just to complement what Richard is saying here, I mostly use PostgreSQL separately from QGIS, for non-spatial purposes. On my laptop running Ubuntu 20.10, I have three versions of PostgreSQL installed: 10.7, 11.7 and 12.5. When I'm running PostgreSQL locally (ie same machine client and server, the default configuration is 12.5 client talking to 10.7 server. I didn't do that on purpose; it's just ended up that way, probably due to upgrading my O/S every six months without doing anything about moving the databases stored there from one version to the next. Using my laptop to connect remotely (with the --host option) to a customer's server running Ubuntu 18.04, I use the same 12.5 client to talk to a 10.15 server. On a different customer's machine, also running Ubuntu 18.04, where I cannot connect remotely, I use ssh to gain access to the server and run PostgreSQL locally there, where I have a 10.15 client talking to a 10.15 server. And so on. In my mmm 15 or so years of experience with PostgreSQL, it's somewhat unusual to find oneself connecting clients and servers of the same version, and I don't recall ever having problems with this situation. Of course, I'm far from exercising the leading-edge capabilities in PostgreSQL, so your mileage may vary and all that. If you are concerned about those new features coming out with each release, a bit of googling will lead you to useful articles like this one: https://www.postgresql.org/about/news/postgresql-12-released-1976/ I hope this helps. -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Richard Duivenvoorde writes: > I think you are complicating things :-) > > There are postgres/gis SERVER versions > And postgres/gis/ CLIENT LIBRARY version > > In general: IF you have a SERVER version you should try to have the same > CLIENT (=library) version. > BUT: only to be able to do the new things introduced in the SERVER version OR > to have a better client version because they fixed bugs in that > client(library) version. > In general: you can probably also connect to a new SERVER version with > an older CLIENT (unless off course you want to do things that at the > time of the CLIENT version was just not possible). (I definitely do not mean to take issue with your suggestion about the speed changes, at all.) I do think it's a fair question, though, as interop across postgresql-client and postgresql-server is non-obvious from first principles. It really depends on the rules of the postgresql world about the client/server protocol and how well those rules are followed. As well as how much compat code there is to deal with older servers, and how well newer servers behave with older clients. As you say with any luck everything is fine, and postgresql has a long history of adult software engineering, so it's a good bet. I am blissfully unaware of even the policy of postgresql as I build everything from source (via pkgsrc, where I maintain the qgis package as well as postgis, proj, and a few others). So locally the versions match, and I tend to run consistent builds on multiple machines. The other advice I might give the Jeroen is that if using Windows, qgis, and postgis and running the server on the same machine is to use the installer that includes postgis as well, because any trouble there has probably been noticed and fixed. But that's theoretical, as I don't use Windows or these binary distributions. signature.asc Description: PGP signature ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Hi Richard,
I am definitely complicating things, that is so me
But, are you now saying there is a relation between popstgresql client version
and postresql server version? Because Nyall Dawson said there are unrelated.
Or, are both of you actually trying to say to me 'it doesn't matter that much,
it will work'. Because I know it does work. Qgis3.16 with postgresql client
11.5 does work with postgresql server 12.x and potsgis 3.1 I am 'just'
wondering if there are best matches.
And I will test the DB manager issue following your three steps and put the
result in the github issue. It has to be something within the total package of
qgis, because I have not made changes to my postgresql server version and
postgis version.
Jeroen Hovens
-Oorspronkelijk bericht-
Van: Richard Duivenvoorde
Verzonden: donderdag 4 februari 2021 11:02
Aan: Groene Bij
CC: 'qgis-user'
Onderwerp: Re: [Qgis-user] What is postgresql client version and does it have
anything to do with different postgresql versions
On 2/4/21 10:16 AM, Groene Bij wrote:
> Take this example:
> I would like to see DB Manager as just a shell or GUI which will work
> with every postgresql and postgis version the same way, but in fact it
> isn't. I found DB manager to work differently from qgis3.10 to 3.12 and above.
> Something changed and as a result, simple select queries can take up
> 10 to a
> 100 times longer in qgis3.12 and up compared to qgis3.10 (see also
> https://github.com/qgis/QGIS/issues/40643)
>
> So, I am mostly looking for some kind of reassurance. Things like:
> qgis3.16 will work fine with postgresql version a to d and postgis
> version f to l. Or qgis will work fine with any postgresql above
> version x and postgis above version y. I do realize these are
> difficult statements to make (how to test
> them?)
Hi Jeroen,
I think you are complicating things :-)
There are postgres/gis SERVER versions
And postgres/gis/ CLIENT LIBRARY version
In general: IF you have a SERVER version you should try to have the same CLIENT
(=library) version.
BUT: only to be able to do the new things introduced in the SERVER version OR
to have a better client version because they fixed bugs in that client(library)
version.
In general: you can probably also connect to a new SERVER version with an older
CLIENT (unless off course you want to do things that at the time of the CLIENT
version was just not possible).
My guess: your issue above has anything to do with the pg client libraries
used, or the combi server/client.
Chances are bigger that either QGIS is now running other SQL queries OR the
logic (of loops for example) in QGIS has changed.
If you really want to know:
- enable logging in postgresql so you can see all queries the QGIS is sending
- check if there are differences
- run the same queries from withing pgsql on the server and do timing there.
Else we try to find a simple solution ("just use the right version") for a
rather complex performance issue, which can have it's cause on many (other)
levels... :-)
Groetjes,
Richard Duivenvoorde
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
On 2/4/21 10:16 AM, Groene Bij wrote:
> Take this example:
> I would like to see DB Manager as just a shell or GUI which will work with
> every postgresql and postgis version the same way, but in fact it isn't. I
> found DB manager to work differently from qgis3.10 to 3.12 and above.
> Something changed and as a result, simple select queries can take up 10 to a
> 100 times longer in qgis3.12 and up compared to qgis3.10
> (see also https://github.com/qgis/QGIS/issues/40643)
>
> So, I am mostly looking for some kind of reassurance. Things like: qgis3.16
> will work fine with postgresql version a to d and postgis version f to l. Or
> qgis will work fine with any postgresql above version x and postgis above
> version y. I do realize these are difficult statements to make (how to test
> them?)
Hi Jeroen,
I think you are complicating things :-)
There are postgres/gis SERVER versions
And postgres/gis/ CLIENT LIBRARY version
In general: IF you have a SERVER version you should try to have the same CLIENT
(=library) version.
BUT: only to be able to do the new things introduced in the SERVER version OR
to have a better client version because they fixed bugs in that client(library)
version.
In general: you can probably also connect to a new SERVER version with an older
CLIENT (unless off course you want to do things that at the time of the CLIENT
version was just not possible).
My guess: your issue above has anything to do with the pg client libraries
used, or the combi server/client.
Chances are bigger that either QGIS is now running other SQL queries OR the
logic (of loops for example) in QGIS has changed.
If you really want to know:
- enable logging in postgresql so you can see all queries the QGIS is sending
- check if there are differences
- run the same queries from withing pgsql on the server and do timing there.
Else we try to find a simple solution ("just use the right version") for a
rather complex performance issue, which can have it's cause on many (other)
levels... :-)
Groetjes,
Richard Duivenvoorde
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Greg, And list, That is actually a very useful comment. First, because I did not realize there could be different builds and that would mean the info screen within qgis would show different values. Secondly, because yes, I am using the installer for windows from qgis.org and there are actually two of them, the osgeo4w and the standalone installer, although I don't think there is any difference in the actual build when choosing the same version number. This might actually be the main reason for my questions. I know qgis is build using different libraries and such and the whole idea should be that I should not really care about it. When I download the LTR it should work and it should work well with other programs. However, since qgis is so dependent on other libraries and is very honest and open in telling me this (the info screen), I get this feeling it could also be 'sensitive' to other programs. The same goes for postgresql and postgis. All of these programs are itself dependent on and are build using different libraries, some of which are also used in qgis (like geos, gdal/ogr). That's why I have this idea that some combinations of versions might work better with each other. However, there is very little information to be found on this subject. Take this example: I would like to see DB Manager as just a shell or GUI which will work with every postgresql and postgis version the same way, but in fact it isn't. I found DB manager to work differently from qgis3.10 to 3.12 and above. Something changed and as a result, simple select queries can take up 10 to a 100 times longer in qgis3.12 and up compared to qgis3.10 (see also https://github.com/qgis/QGIS/issues/40643) So, I am mostly looking for some kind of reassurance. Things like: qgis3.16 will work fine with postgresql version a to d and postgis version f to l. Or qgis will work fine with any postgresql above version x and postgis above version y. I do realize these are difficult statements to make (how to test them?) Jeroen Hovens -Oorspronkelijk bericht- Van: Greg Troxel Verzonden: woensdag 3 februari 2021 19:04 Aan: Groene Bij CC: 'chris hermansen' ; 'qgis-user' Onderwerp: Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions "Groene Bij" writes: > Hence my search for information on dependencies and whether QGIS 3.16 > (or later) favors any postgresql and postgis version above another Probably not a useful comment to you, but as I understand the qgis sources can be built against a number of postgresql client libraries. I think you are talking about, as I gather most windows users do, not just about qgis sources but about binaries: particular distributions of already-built qgis. There, I find "qgis 3.16 does x" to be a bit on shaky ground, as there could be multiple such binaries even for Windows, and there are certainly various builds for other systems. You are probably talking about the installer on qgis.org. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
"Groene Bij" writes: > Hence my search for information on dependencies and whether QGIS 3.16 > (or later) favors any postgresql and postgis version above another Probably not a useful comment to you, but as I understand the qgis sources can be built against a number of postgresql client libraries. I think you are talking about, as I gather most windows users do, not just about qgis sources but about binaries: particular distributions of already-built qgis. There, I find "qgis 3.16 does x" to be a bit on shaky ground, as there could be multiple such binaries even for Windows, and there are certainly various builds for other systems. You are probably talking about the installer on qgis.org. signature.asc Description: PGP signature ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Thank for your reply and the links. I use win10 as OS. On my current pc I use postgresql11 and postgis 2.5. I am however replacing both my pc and laptop for new equipment, so that got me thinking on maybe upgrading to postgresql12 and postgis 3.1 as well. Hence my search for information on dependencies and whether QGIS 3.16 (or later) favors any postgresql and postgis version above another Nyall Dawson pointed out the postgresql client version used in qgis is unrelated to postgresql versioning, if I understand him correctly. Jeroen Hovens Van: chris hermansen Verzonden: dinsdag 2 februari 2021 23:00 Aan: Groene Bij CC: qgis-user Onderwerp: Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions Jeroen and list, On Tue, Feb 2, 2021 at 12:37 PM Groene Bij mailto:m...@groenebij.nl> > wrote: Hi list, PostgreSQL has this interesting versioning where they try to keep like 5 main branches up to date. At this point you can choose any version from 9.6.x up to 13.x Also, PostGIS is now slowly but probably steadily moving from 2.5.x to 3.1.x So, setting up a new PostgreSQL database environment makes me wonder which one to choose. Since I will only be using this database with QGIS and QGIS 3.16 becoming the new LTR, does QGIS favor one of the PostgreSQL and PostGIS version above another? I did notice in the info screen for qgis3.16.3 it says PostgreSQL Client version 11.5 Does that mean QGIS3.16 might work best with PostgreSQL 11.x? Or are they completely unrelated? What O/S do you use? I ask because if it's Linux of some sort, your repositories may be a good indicator. This is worth a quick look https://www.postgresql.org/developer/roadmap/ Also this https://www.postgresql.org/support/versioning/ -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
On Wed, 3 Feb 2021 at 06:37, Groene Bij wrote: > I did notice in the info screen for qgis3.16.3 it says PostgreSQL Client > version 11.5 > > Does that mean QGIS3.16 might work best with PostgreSQL 11.x? Or are they > completely unrelated? They are unrelated -- the client version refers to the library which QGIS uses to access any postgres database. It doesn't have to be matched to the version of the database you connect to at all. QGIS should connect perfectly to later database versions too! Nyall > > > > > > Kind regards, > > Jeroen Hovens > > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Jeroen and list, On Tue, Feb 2, 2021 at 12:37 PM Groene Bij wrote: > Hi list, > > > > PostgreSQL has this interesting versioning where they try to keep like 5 > main branches up to date. > > At this point you can choose any version from 9.6.x up to 13.x > > Also, PostGIS is now slowly but probably steadily moving from 2.5.x to > 3.1.x > > > > So, setting up a new PostgreSQL database environment makes me wonder > which one to choose. > > Since I will only be using this database with QGIS and QGIS 3.16 becoming > the new LTR, does QGIS favor one of the PostgreSQL and PostGIS version > above another? > > > > I did notice in the info screen for qgis3.16.3 it says PostgreSQL Client > version 11.5 > > Does that mean QGIS3.16 might work best with PostgreSQL 11.x? Or are they > completely unrelated? > > > What O/S do you use? I ask because if it's Linux of some sort, your repositories may be a good indicator. This is worth a quick look https://www.postgresql.org/developer/roadmap/ Also this https://www.postgresql.org/support/versioning/ -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] What is postgresql client version and does it have anything to do with different postgresql versions
Hi list, PostgreSQL has this interesting versioning where they try to keep like 5 main branches up to date. At this point you can choose any version from 9.6.x up to 13.x Also, PostGIS is now slowly but probably steadily moving from 2.5.x to 3.1.x So, setting up a new PostgreSQL database environment makes me wonder which one to choose. Since I will only be using this database with QGIS and QGIS 3.16 becoming the new LTR, does QGIS favor one of the PostgreSQL and PostGIS version above another? I did notice in the info screen for qgis3.16.3 it says PostgreSQL Client version 11.5 Does that mean QGIS3.16 might work best with PostgreSQL 11.x? Or are they completely unrelated? Kind regards, Jeroen Hovens ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
