Обсуждение: productive usage of PostgreSQL with Windows Server

On Tue, Jan 26, 2021 at 11:20:10AM +0000, Dirk Krautschick wrote:
> 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.

Microsoft is running (at least parts of) their Azure Postgres-As-A-
Service offering on Windows; that is probably one of the larger
Postgres-on-Windows deployments.


Michael

-- 
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:  +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz

PostgreSQL on Windows is a viable option, although some extensions aren't available.

If server and client are on Windows, authentication can be done via Active Directory. This has to be configured in pg_hba.conf and makes single sign-on possible.

AFAIK, this is the only real advantage of using Windows. But it shows that the PostgreSQL people offer this, although they had to program Windows-specific code to make it work.

I can tell that there is a company running a couple of thousand PostgreSQL installations on Windows for a couple of years and that there haven't been any problems.

Regards,

Holger

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.E-MailFormatvorlage21 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;}

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

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

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

@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.E-MailFormatvorlage21 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;}

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

Dirk Krautschick schrieb am 26.01.2021 um 12:20:
> 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.

I think the biggest drawback is that many useful tools and extensions are only
available on Linux. For some tools, it might be possible to compile them on Windows,
but it's not as easy and straightforward as doing that on Linux.

Your choice of e.g. backup tools (beyond the built-in tools) is also limited. I think
the only one is pgProBackup - at least it contains information on how to compile
it on Windows.

In my experience, NTFS on Windows doesn't perform as good the alternatives on Linux,
so performance on identical hardware is probably a bit lower when using Windows.

Thomas

Yes, but Micrsosoft itself is switching to Linux with the Flexible Server offering.

https://blog.dbi-services.com/azure-database-for-postgresql/

Regards

Karsten

-----Original Message-----
From: Michael Banck <michael.banck@credativ.de>
Sent: Dienstag, 26. Januar 2021 12:59
To: Dirk Krautschick <Dirk.Krautschick@trivadis.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: productive usage of PostgreSQL with Windows Server

On Tue, Jan 26, 2021 at 11:20:10AM +0000, Dirk Krautschick wrote:
> 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.

Microsoft is running (at least parts of) their Azure Postgres-As-A- Service offering on Windows; that is probably one
ofthe larger Postgres-on-Windows deployments. 


Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:  +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz

Hi,

the main drawback in my eyes ist he concept of the OS.

In Unix/Linux you can use really great files and pipe the data from/into processes optional by changing stuff on the
fly(sed).
 

Even GB and TB are no problem. Done this many times. (AIX, SGI, SUSE, DEBIAN etc)

I often see that Windows user chunk their data into smaller peaces because of main memory limited tools.

regards,

Anton


-----Ursprüngliche Nachricht-----
Von: Thomas Kellerer <shammat@gmx.net> 
Gesendet: Dienstag, 26. Januar 2021 14:30
An: pgsql-admin@lists.postgresql.org
Betreff: Re: productive usage of PostgreSQL with Windows Server

Dirk Krautschick schrieb am 26.01.2021 um 12:20:
> 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.

I think the biggest drawback is that many useful tools and extensions are only available on Linux. For some tools, it
mightbe possible to compile them on Windows, but it's not as easy and straightforward as doing that on Linux.
 

Your choice of e.g. backup tools (beyond the built-in tools) is also limited. I think the only one is pgProBackup - at
leastit contains information on how to compile it on Windows.
 

In my experience, NTFS on Windows doesn't perform as good the alternatives on Linux, so performance on identical
hardwareis probably a bit lower when using Windows.
 

Thomas