[Qgis-user] Experiences using QGIS + PostgreSQL/PostGIS in a multiuser environment?

[Bo Victor Thomsen]

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
> Qgis-user at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20191121/a922a015/attachment-0001.html>