Bo Victor, this is an extremely good contribution to this topic. Thanks
you very much!
/H.
On 2019-11-21 11:43, Bo Victor Thomsen wrote:
This reply is rather long and some of the information has already
partly been given in other replies. However, the following list is my
"checklist" for using Postgres/PostGIS with QGIS and other GIS programs.
* Postgres is an excellent platform for creating a multi user
spatial data platform. And - for QGIS - faster than using just
about any other spatial storage technology. But you have prepare
your Postgres setup.
* Memory configuration: Out of the box Postgres is *very*
conservative about using memory. Tune the configuration. If you
don't have the know-how or time use a tool like this:
*https://pgtune.leopard.in.ua/#/* (there is one aber-dabei: Choose
"Linux" as the operating system even if you server is Windows. The
old advise about using only 512 MB shared memory on windows is
obsolete:
*https://momjian.us/main/blogs/pgblog/2018.html#December_17_2018*)
* If you have a serious multi CPU server (And who hasn't ?): Use
Postgres 12 / PostGIS 3 for getting maximum performance using
multiprocessing in Postgres/PostGIS:
*http://blog.cleverelephant.ca/2019/08/postgis-3-parallel.html
*(and tune your Postgres accordingly - see above). Yes I know
about QGIS troubles with Postgres 12. They will hopefully be
resolved quickly.
* If you have a "Windows only" setup i.e both the server and the
clients are Windows, consider using SSPI sign on method:
*https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows*
It's equivalent to "Integrated security" for MS-SQLServer and will
reduce the hassle about user credentials for Postgres, because the
server will reuse the windows logon identity inside Postgres.
* And a little know trick: If you have a lot of large complex
geometries and some extra disk capacity you can speed your queries
considerably by *not* compressing TOAST data for your spatial
tables:
*http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html.
*
* (This information is probably not pertinent for you as this time)
If you have a "large" pool of users where a small part is
"editors" and a large part "read-only" users, consider having 2
Postgres servers:
o One for editing users, where the Postgres setup is tuned for
editing. Only editing user is allowed access to this server.
The server can probably be quite small.
o A second Postgres server tuned for read access. All Postgres
users has only read access to this server. This server is the
larger of the servers.
o Connect the 2 server in a master/slave replication
configuration using the "editor" server as a master. This will
remove any problems for "editor"-users when "read"-users blow
up the read server with some convoluted query (And they will !!)
--
Med venlig hilsen / Kind regards
Bo Victor Thomsen
Den 20-11-2019 kl. 11:10 skrev Hernán De Angelis:
Hello QGIS:ers
I am evaluating setting up a server running PostgreSQL/PostGIS for
use as data sharing/collaborating environment for spatial data. The
user group may consist of up to 15 people, mostly using QGIS but one
or two may use other software (non OS). Data is almost exclusively of
vector type. The use is within a single organization.
I understand some people in this list have experience with this kind
of environment and would appreciate if any of you would share any
useful experience, challenges, thought or things to watch out for. I
understand basic management routines are critical (user management,
user rights), as well as a sound backup and update strategy. I also
understand that proper data management procedures have to be in
place, like rules for table creation and eventual deletion, attribute
selection, etc. But what else can go wrong with this kind of setup if
not managed properly? Thoughts and experiences welcome!
Best regards and thanks in advance
Hernán
_______________________________________________
Qgis-user mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
