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

[Hernán De Angelis]

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
>> 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
>
> _______________________________________________
> 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/622848d4/attachment.html>