Thread: Postgres 8.x on Windows Server in production
Hi all, What are your experiences with Postgres 8.x in production use on Windows Server 2003/2008? Are there any limitations, trade-offs or quirks? My client is accustomed to Windows Server environment, but it seems hard to google good information about these types of installations. Regards, Ognjen
Ognjen,
> What are your experiences with Postgres 8.x in production use on Windows
> Server 2003/2008? Are there any limitations, trade-offs or quirks?
First of all, you need to know that the first *two* digits of a
PostgreSQL version are major version numbers. So 8.3 is not the same
Postgres which 8.1 is.
Here's the top level summary:
PostgreSQL on Windows, compared to Linux, in general:
-- is a bit slower
-- is not as reliable, because the underlying FS and OS are not as
reliable*
-- some of the tools for Postgres which are available on Linux do not
work on Windows (especially performance tools)
-- is less secure, because the OS is less secure
Yet 1000's of users are running PostgreSQL on Windows in production. It
really depends on what kind of application you're running, and what its
demands are. For a CMS or a contact manager or a personnel directory?
No problem. For a central payroll system for 18,000 employees? I'd
use Linux.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On Fri, Apr 10, 2009 at 7:07 PM, Josh Berkus <josh@agliodbs.com> wrote: > Yet 1000's of users are running PostgreSQL on Windows in production. It > really depends on what kind of application you're running, and what its > demands are. For a CMS or a contact manager or a personnel directory? No > problem. For a central payroll system for 18,000 employees? I'd use > Linux. Confirmed from my experience too. On top of that, I would like to add - that using it on windows first, might be a good step ahead. And installing linux on server isn't so hard anymore, and shouldn't be a problem, unlike 8 years ago :) Give it a try, and please tell us what sort of application you want to put on it. -- GJ
We use Postgres 8.x in production on Windows Server 2003. We have not done a direct head-to-head comparison against any *nix environment, so I can't really compare them, but I can still give a few comments. First of all, it seems that some of the popular file systems in *nix are more robust at preventing disk fragmentation than NTFS is. Because of this I definitely recommend have some defragging solution. What we've settled on in O&O (that's the company name) Defrag Server version. Their software has some nice features related to defragging in the background while monitoring system usage so an to impact performance minimally. Second, one big difficulty with running on Windows is that most of the Postgres expertise seems is around *nix environments. This means that when you do need to investigate a performance issue it can be more difficult to get direct advice. For example, perusing this mailing list will show lot's of tips suggesting running various tools to show io performance, etc. Well, on Windows the toolset is different. All in all we've been happy enough with Windows. Certainly we've never considered migrating to *nix because of difficulties with it. --Rainer > -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of Ognjen Blagojevic > Sent: Friday, April 10, 2009 6:47 PM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Postgres 8.x on Windows Server in production > > Hi all, > > What are your experiences with Postgres 8.x in production use on > Windows > Server 2003/2008? Are there any limitations, trade-offs or quirks? > > My client is accustomed to Windows Server environment, but it seems > hard > to google good information about these types of installations. > > Regards, > Ognjen > > -- > Sent via pgsql-performance mailing list (pgsql- > performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
On Sun, Apr 12, 2009 at 5:13 PM, Rainer Mager <rainer@vanten.com> wrote: > We use Postgres 8.x in production on Windows Server 2003. We have not done a > direct head-to-head comparison against any *nix environment, so I can't > really compare them, but I can still give a few comments. Just wondering, what version are you actually running? Big differences from 8.0, 8.1, 8.2, 8.3 and soon 8.4. For people taking your advice on running on windows, it helps them make a decision on whether or not to upgrade. > First of all, it seems that some of the popular file systems in *nix are > more robust at preventing disk fragmentation than NTFS is. Because of this I > definitely recommend have some defragging solution. What we've settled on in Linux file systems still fragment, they just don't tend to fragment as much. As the drive gets closer to being full fragmentation will become more of a problem.
We're running 8.3, but when we started this server about 2 years ago it was an earlier 8.x, I don't remember which. --Rainer > -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of Scott Marlowe > Sent: Monday, April 13, 2009 8:41 AM > To: Rainer Mager > Cc: Ognjen Blagojevic; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Postgres 8.x on Windows Server in production > > On Sun, Apr 12, 2009 at 5:13 PM, Rainer Mager <rainer@vanten.com> > wrote: > > We use Postgres 8.x in production on Windows Server 2003. We have not > done a > > direct head-to-head comparison against any *nix environment, so I > can't > > really compare them, but I can still give a few comments. > > Just wondering, what version are you actually running? Big > differences from 8.0, 8.1, 8.2, 8.3 and soon 8.4. For people taking > your advice on running on windows, it helps them make a decision on > whether or not to upgrade. > > > First of all, it seems that some of the popular file systems in *nix > are > > more robust at preventing disk fragmentation than NTFS is. Because of > this I > > definitely recommend have some defragging solution. What we've > settled on in > > Linux file systems still fragment, they just don't tend to fragment as > much. As the drive gets closer to being full fragmentation will > become more of a problem. > > -- > Sent via pgsql-performance mailing list (pgsql- > performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
On Sun, Apr 12, 2009 at 5:49 PM, Rainer Mager <rainer@vanten.com> wrote: > We're running 8.3, but when we started this server about 2 years ago it was > an earlier 8.x, I don't remember which. Cool. PostgreSQL is one of the few projects where I've always recommended upgrading and keeping on the latest major version as soon as possible after it comes out. This stands in stark contrast to apache 2.0, which was out for over two years before it was worth the effort to migrate to. The improvements just weren't worth the effort to upgrade. I've seen enough performance and capability in each major version of pgsql to make it worth the upgrade since 7.0 came out. I think we've skipped one or two short releases, like 7.1 or 8.2, but in general even those represented useful gains over previous versions.
Hi all, First, thank you all for your answers. Grzegorz Jaśkiewicz wrote: > Give it a try, and please tell us what sort of application you want to > put on it. It is a student database for the college which is a client of ours. The size of the database should be around 1GB, half being binary data (images). Not more than 100 users at the time will be working with the application. I don't worry about the performance, but more about the maintenance under Windows. What file system to use? How to schedule vacuuming and backup? Are there any windows services that should be turned off? Those questions come to my mind when I consider new OS for the RDBMS. Regards, Ognjen
2009/4/13 Ognjen Blagojevic <ognjen@etf.bg.ac.yu>: > Hi all, > > First, thank you all for your answers. > > > Grzegorz Jaśkiewicz wrote: >> >> Give it a try, and please tell us what sort of application you want to >> put on it. > > It is a student database for the college which is a client of ours. The size > of the database should be around 1GB, half being binary data (images). Not > more than 100 users at the time will be working with the application. > > I don't worry about the performance, but more about the maintenance under > Windows. What file system to use? How to schedule vacuuming and backup? Are > there any windows services that should be turned off? Those questions come > to my mind when I consider new OS for the RDBMS. NTFS, use autovacuum, backup nightly (?), Turn off anti-virus software.
2009/4/13 Ognjen Blagojevic <ognjen@etf.bg.ac.yu>: > It is a student database for the college which is a client of ours. The size > of the database should be around 1GB, half being binary data (images). Not > more than 100 users at the time will be working with the application. nice, if you want to store pics, I suggest filesystem - with some nice schema to do it. The way I do it, is using md5 of pics/other data, and three level of directories to lead you to the right file on disc. It is dead easy to implement, and much faster than bytea in DB. (at least on linux, I wouldn't hold my breath so much for ntfs, but you never know). 100 connections is quite a bit, so you need a bit of memory. It also depends on the actual queries, and schema. If you have any questions about that, and performance - we are here to help. > I don't worry about the performance, but more about the maintenance under > Windows. What file system to use? How to schedule vacuuming and backup? Are > there any windows services that should be turned off? Those questions come > to my mind when I consider new OS for the RDBMS. just like scott said, Make sure to either turn off anti-virus, or tell it to stay away from postgresql. (which leaves your machine quite vulnerable, so make sure to secure it well). hth -- GJ
You'll almost certainly want to use NTFS. I suspect you'll want to set the NTFS Allocation Unit Size to 8192 or some integer multiple of 8192, since I believe that is the pg page size. XP format dialog will not allow you to set it above 4096, but the command line format utility will. I do remember setting it as high as 64k for SQL Server on Windows Server 2003 (SQL Server does IO in 8-page units called extents) Someone please correct me if I have that wrong. Do not allow any indexing service activity on the data or transaction log volumes. If this is a dedicated database server you may as well turn indexing service off. Don't enable compression on the data or transaction log volumes either. Pay attention to Automatic Updates - you likely don't want your database server to restart every 4th Wednesday morning or so. Hope this helps, Justin 2009/4/13 Ognjen Blagojevic <ognjen@etf.bg.ac.yu>: > Hi all, > > First, thank you all for your answers. > > > Grzegorz Jaśkiewicz wrote: >> >> Give it a try, and please tell us what sort of application you want to >> put on it. > > It is a student database for the college which is a client of ours. The size > of the database should be around 1GB, half being binary data (images). Not > more than 100 users at the time will be working with the application. > > I don't worry about the performance, but more about the maintenance under > Windows. What file system to use? How to schedule vacuuming and backup? Are > there any windows services that should be turned off? Those questions come > to my mind when I consider new OS for the RDBMS. > > Regards, > Ognjen > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >