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@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
