Re: productive usage of PostgreSQL with Windows Server

[Bo Victor Thomsen <bo.victor.thomsen@xxxxxxxxx>]

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