Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4NyD-0007hQ-MV for pgsql-admin@arkaria.postgresql.org; Tue, 26 Jan 2021 13:01:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l4NyB-0001LP-LE for pgsql-admin@arkaria.postgresql.org; Tue, 26 Jan 2021 13:01:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l4NyB-0001Jq-7P for pgsql-admin@lists.postgresql.org; Tue, 26 Jan 2021 13:01:19 +0000 Received: from mail-lj1-x233.google.com ([2a00:1450:4864:20::233]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l4Ny7-0005je-FY for pgsql-admin@lists.postgresql.org; Tue, 26 Jan 2021 13:01:18 +0000 Received: by mail-lj1-x233.google.com with SMTP id f2so14210874ljp.11 for ; Tue, 26 Jan 2021 05:01:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=Sl/MGcioKrTOQs5T1vqtBtBN8v8ZQYyJ5bM9GkSQXGw=; b=HcDJV0kViRqsTtQ2BijZDYW6PjL/KgTqA63+LPRXYjtVCLbPOS5tYg4WvFDxETCpQU sqKfOP9Jn/i/LqPlSuVqXFk26AhvMp1wghYjFdTuRrOeed4n2qKishBgBaY+xVN4dK4Q 142gQOAhFwI9WGXrtq56UZahG080J1/TvUpKQWQ40Njx3E9bovEGCLZ1/UFoR/iBrsbA e5RmftjiHaXr30XoEurywrZFgNBXxmoVm2pgNwjAJDqZKilqo4PJsm1fPEaA+dM06ZnB Li2ZpZCpFzA6XS+qn4GffygK2hbKeFOa1L2YaBVT42550zArNUUJEVwZPHUyY/p5UN3i Z/Dw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=Sl/MGcioKrTOQs5T1vqtBtBN8v8ZQYyJ5bM9GkSQXGw=; b=KKqX0qy6/Ktn88hPqoMd7pGKLmjDGCKGkRJTPRYr3HWV2BNU5yNtctmQONxo19JH1S 4RPCAoiY3vrXcAuUT+tQbelhlNXxu36uXqNjyIFQ1Tc9rWiAds9WsaEFU2kzYsPrB1k9 szljhMSMWqroBN3NyFRt/pzu3Qrq3crgFs6luebKc59nV9FJ+8L/NRgnLI6ggBvGVEbB +mquEZAyI7SWhI48HYDmq/Lm59Rl0ocP2BLFClpDlDwFApMw05rvpNmlmdKI1NDELIdf kHMuEKT/hTQOipgkMwM4CDCk7QsUSdQ1kNxSWDxcubpD5VeAMMqRgmD0O8ZoMbJohnTR GpbA== X-Gm-Message-State: AOAM531LM+sAPC2adqPT9SIpgTOh0nrskuNGtTgBaG9+YU5E41zx3sVV iJamneto3BUDD06qlALcpaBu06cdowSDTw== X-Google-Smtp-Source: ABdhPJx9qoxf0/52zAEqXPAduojO90HtH78AeUcFQnSJWP74hjaaO+KLwrSSqvQWIbFroE/qTSey0g== X-Received: by 2002:a2e:b4a1:: with SMTP id q1mr2777777ljm.484.1611666073028; Tue, 26 Jan 2021 05:01:13 -0800 (PST) Received: from [192.168.1.8] (93-161-90-27-static.dk.customer.tdc.net. [93.161.90.27]) by smtp.gmail.com with ESMTPSA id i16sm996601lfo.111.2021.01.26.05.01.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 26 Jan 2021 05:01:12 -0800 (PST) Subject: Re: productive usage of PostgreSQL with Windows Server To: pgsql-admin@lists.postgresql.org References: From: Bo Victor Thomsen Message-ID: <88c443fa-cb2b-3f59-5685-0ef841050ebd@gmail.com> Date: Tue, 26 Jan 2021 14:01:11 +0100 User-Agent: Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:78.0) Gecko/20100101 Thunderbird/78.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------02C895C9E2911D8C7568BB80" Content-Language: en-GB List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------02C895C9E2911D8C7568BB80 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Hi Dirk - I have installed and used a large number of Postgres installations on Windows for years. Mostly together with PostGIS using the combination as a really powerful and first-class data repository for spatial data. Some points regarding the use of Postgres on a Windows platform. * Performance: On the same hardware and with similar setups, a Linux installation of Postgres will outperform a Windows ditto. Not by much, but it is measurable. On the other hand, it virtually impossible to get a Linux box inside a hard-core Windows enterprise organisation. So using Postgres on Windows is a reasonable compromise in those cases * Installation: I'm using the installation package from EnterpriseDB (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) . It works as a charm. I've used a number of other installers. All have been working at least adequately. So It is no problem installing Postgres on Windows. * Setup: Parameter-vise, the standard setup of Postgres out-of-the-box is *very* conservative - it can work on almost any Windows configuration. To get good performance, you have to change the Postgres setup parameters, especially regarding memory consumption. Postgres tuning is part science and part art. But one advise: If you a using some kind of guide for setup, then use the the *Linux* advice for memory setup. There is a lot of "advice" about not using "to much" shared memory on Windows compared to Linux. My experience - at least when using Postgres for spatial data - is you get a *significant* performance boost when Postgres gets enough memory on Windows to work with. * Security: As others in this thread has mentioned: You can emulate The SQLServer "integrated security" feature on Postgres running on a Windows server by using the SSPI security protocol. It integrates Postgres logon and security procedures into Active Directory, so users automatically gets Postgres privileges based on their Windows username without manually entering username/password when accessing Postgres. And this setup will endear you to both Windows users and administrators ;-) * Last: Postgres is really stable on Windows. I have very seldom experienced any significant trouble running Postgres on Windows. YMMV Med venlig hilsen / Kind regards Bo Victor Thomsen Den 26-01-2021 kl. 12:20 skrev Dirk Krautschick: > > Hi, > > Please do not throw anything to me but I just want to ask if there is > a realistic > > recommendation for the productive usage of PostgreSQL with Windows. Or > > are there any references you may share regarding this. > > I am aware and I always surely recommend Linux but the matter of fact > that there > > are also customers with more Windows knowledge inhouse I always get > the question > > about experience and references which I cannot provide at this time. > > Regarding the build farm status I see that it is pretty much tested > and suppported at > > least. But I am interessted in fundational opinions and expierences. > > Thanks and cheers > > Dirk > --------------02C895C9E2911D8C7568BB80 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Hi Dirk -

I have installed and used a large number of Postgres installations on Windows for years. Mostly together with PostGIS using the combination as a really powerful and first-class data repository for spatial data.

Some points regarding the use of Postgres on a Windows platform.

  • Performance: On the same hardware and with similar setups, a Linux installation of Postgres will outperform a Windows ditto. Not by much, but it is measurable. On the other hand, it virtually impossible to get a Linux box inside a hard-core Windows enterprise organisation. So using Postgres on Windows is a reasonable compromise in those cases

  • Installation: I'm using the installation package from EnterpriseDB (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) . It works as a charm. I've used a number of other installers. All have been working at least adequately. So It is no problem installing Postgres on Windows.

  • Setup: Parameter-vise, the standard setup of Postgres out-of-the-box is *very* conservative - it can work on almost any Windows configuration. To get good performance, you have to change the Postgres setup parameters, especially regarding memory consumption. Postgres tuning is part science and part art. But one advise: If you a using some kind of guide for setup, then use the the *Linux* advice for memory setup. There is a lot of "advice" about not using "to much" shared memory on Windows compared to Linux. My experience - at least when using Postgres for spatial data - is you get a *significant* performance boost when Postgres gets enough memory on Windows to work with.   

  • Security: As others in this thread has mentioned: You can emulate The SQLServer "integrated security" feature on Postgres  running on a Windows server by using the SSPI security protocol. It integrates Postgres logon and security procedures into Active Directory, so users automatically gets Postgres privileges based on their Windows username without manually entering username/password when accessing Postgres. And this setup will endear you to both Windows users and administrators ;-)  

  • Last: Postgres is really stable on Windows. I have very seldom experienced any significant trouble running Postgres on Windows. YMMV

Med venlig hilsen / Kind regards

Bo Victor Thomsen
Den 26-01-2021 kl. 12:20 skrev Dirk Krautschick:

Hi,

 

Please do not throw anything to me but I just want to ask if there is a realistic

recommendation for the productive usage of PostgreSQL with Windows. Or

are there any references you may share regarding this.

 

I am aware and I always surely recommend Linux but the matter of fact that there

are also customers with more Windows knowledge inhouse I always get the question

about experience and references which I cannot provide at this time.

 

Regarding the build farm status I see that it is pretty much tested and suppported at

least. But I am interessted in fundational opinions and expierences.

 

Thanks and cheers

 

Dirk

--------------02C895C9E2911D8C7568BB80--