<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>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. <br>
</p>
<ul>
<li>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.<br>
<br>
</li>
<li>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: <b><a class="moz-txt-link-freetext" href="https://pgtune.leopard.in.ua/#/">https://pgtune.leopard.in.ua/#/</a></b>
(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: <b><a class="moz-txt-link-freetext" href="https://momjian.us/main/blogs/pgblog/2018.html#December_17_2018">https://momjian.us/main/blogs/pgblog/2018.html#December_17_2018</a></b>)
<br>
<br>
</li>
<li> 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: <b><a class="moz-txt-link-freetext" href="http://blog.cleverelephant.ca/2019/08/postgis-3-parallel.html">http://blog.cleverelephant.ca/2019/08/postgis-3-parallel.html</a>
</b>(and tune your Postgres accordingly - see above). Yes I know
about QGIS troubles with Postgres 12. They will hopefully be
resolved quickly. <br>
<br>
</li>
<li>If you have a "Windows only" setup i.e both the server and the
clients are Windows, consider using SSPI sign on method: <b><a class="moz-txt-link-freetext" href="https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows">https://wiki.postgresql.org/wiki/Configuring_for_single_sign-on_using_SSPI_on_Windows</a></b>
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.<br>
<br>
</li>
<li>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 <b>not</b> compressing TOAST data for
your spatial tables: <b><a class="moz-txt-link-freetext" href="http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html">http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html</a>.
<br>
<br>
</b></li>
<li>(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: <br>
<br>
<ul>
<li>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.<br>
<br>
</li>
<li>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. <br>
<br>
</li>
<li>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 !!) </li>
</ul>
</li>
</ul>
<pre class="moz-signature" cols="72">--
Med venlig hilsen / Kind regards
Bo Victor Thomsen</pre>
<p><br>
</p>
<div class="moz-cite-prefix">Den 20-11-2019 kl. 11:10 skrev Hernán
De Angelis:<br>
</div>
<blockquote type="cite"
cite="mid:a6601581-7934-8896-aab8-b27869d3bbdb@comhem.se">Hello
QGIS:ers
<br>
<br>
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.
<br>
<br>
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!
<br>
<br>
Best regards and thanks in advance
<br>
<br>
Hernán
<br>
<br>
<br>
<br>
<br>
<br>
_______________________________________________
<br>
Qgis-user mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<br>
List info: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
<br>
Unsubscribe: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
</blockquote>
<pre class="moz-signature" cols="72">
</pre>
</body>
</html>