[postgis-users] QGIS + PostGIS in production environment

[Cap Diniz]

Thanks for the responses so far!

Adding a bit more information.

We are using Dell PowerEdge-R430 [1] for the PostGIS server with OS Debian
8.7 on a 1gigabit network. For each topographic sheet we use one database,
this means that we have hundreds of databases in production. We divide the
databases in different ports by project, just for organization, I don't
know if that impacts performance.

As for clients we usually use Debian 8.4, Windows 7 or Windows 10 with
latest LTR QGIS. The clients are i7, 8gb RAM, 1gb video card.

We didn't do any tuning in PostgreSQL, I will try out the suggestions. As
configuration goes, we just disabled the Auto Vacuum, and run it by a
script at night (when no one is connected) along with backup.



Then main problem that we are facing is slow saving time at peak hours
(when about 50 clients are digitizing in different databases). Sometimes
can take up to 1 minute to save all layers. We work with autosave in QGIS
that saves every 5 minutes, so slow saving time is not ideal.
The reason that we save so much is that sometimes we have errors while
saving, such as null geometries, that we cannot fix and we have to discard
the edits. (also we have other errors that usually we discard the edits).



[1] https://i.dell.com/sites/doccontent/shared-content/
data-sheets/en/Documents/Dell-PowerEdge-R430-Spec-Sheet.pdf

On Thu, Jun 15, 2017 at 3:38 PM, Regina Obe <lr at pcorp.us> wrote:

> Felipe,
>
>
>
> Are you having problems currently or you just asking a general question
> for future consideration?
>
>
>
> Your PostgreSQL and PostGIS are pretty old.
>
>
>
> PostgreSQL 9.2 is reaching end of life in a couple of months -
> https://www.postgresql.org/support/versioning/
>
> , so you should probably upgrade that soon to something like PostgreSQL
> 9.6, or 10 when it comes out in Sept/October.
>
>
>
> PostGIS 2.3 is the latest version and PostGIS 2.4 we are going to try to
> release around Sept to go along with PostgreSQL 10.
>
>
>
> As far as OS, PostgreSQL/PostGIS runs best on FreeBSD  or Linux.  Most
> high-end PostgreSQL users seem to prefer FreeBSD, but that might be a
> historical thing rather than performance thing, maybe some folks can speak
> to that.
>
>
>
> I've had pretty good performance on windows, but I think as you add more
> users the process spunning (vs. prefer thread spunning on windows) might
> make it less performant. I have certain things that necessitate me running
> often on windows.  For GIS usage, I've found Ubuntu seems to have the best
> menu of packages and most preferred by GIS folk, so probably a good one to
> go with if you are new to Unix/Linux and just want to get stuff from repos
> and have a good balance of performance and availability of prepackaged
> goods.
>
>
>
> That said questions on OS/ Hardware require more consideration than
> performance.  A lot these days is just your comfortability with these
> things.
>
>
>
> For PostgreSQL, whatever you do, you'll probably want SSD disks and RAM
> tends to be more important than CPU especially for PostGIS.
>
>
>
> Hope that helps,
>
> Regina
>
> http://postgis.us
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Cap Diniz
> *Sent:* Thursday, June 15, 2017 2:00 PM
> *To:* postgis-users at lists.osgeo.org
> *Subject:* [postgis-users] QGIS + PostGIS in production environment
>
>
>
> Hello,
>
>
>
> I am from the Cartographic Production Department from the Brazilian Army,
> and we are trying to migrate from ArcGIS to QGIS+PostGIS.
>
>
>
> We are currently using QGIS 2.14.15, PostgreSQL 9.2, PostGIS 2.1 and we
> mostly do data digitizing over an orthoimage. We have about 50 simultaneous
> users in a single PostGIS server, but in different databases and ports (we
> use 5 different ports in production, one per project).
>
>
>
> I would like to know if there are any tips to improve performance and QGIS
> reliability, such as tuning PostgreSQL, changing versions, operating
> systems, hardware recommendations, or QGIS specifics.
>
>
>
>
>
> Regards,
>
> Felipe Diniz
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170615/817368d0/attachment.html>