Thread: FW: Postgres alongside MS SQL Server
Hi, We've got some clients that are concerned about running Postgresql 7.3.4 on a Win2k Server box, alongside MS SQL Server. I've been running pg on my XP machines for a long time now (with cygwin) and never had any sort of problem. The db is fast and stable. Does anyone have any experience that would give some weight to our client's concerns? Would there be any potential conflict between the postmaster and MS SQL Server? Your experience and advice would be greatly appreciated. -Peter
I'm not ruling out the idea of running with a separate linux box, but there are some strong reasons to stick with the MS box. So, your point is well taken. That aside, however, I still need to draw from various people's experience to get a feel for any problems that may arise when running next to MS SQL Server. I've heard that Postgresql is a task that runs with "Normal" priority, and can therefore not lock up the machine to the point where it's not recoverable. In contrast, I've heard as well that MS SQL Server does indeed run as a high priority task and will take precedence when the OS doles out CPU resources. How is it possible for Postgresql to "freak out" and take out the machine? Replies are greatly appreciated. -Peter -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: April 22, 2004 10:09 AM To: Anony Mous Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: Postgres alongside MS SQL Server Hello, Well it of course depends on what you are doing. Traditionally I would say, "Are you nuts?" but it really depends on what you are doing. It is all about risk... if PostgreSQL freaks out and takes out the machine, what will happen to the MS SQL server? What about cost associated with downtime? The same goes for if the MS SQL server takes out the machine? How important is what PostgreSQL is doing? Considering you could put together a box that will outperform PostgreSQL/Cgwin running Linux for about 700 bucks. Why not just get a new machine and not risk the exposure? Sincerely, Joshua D. Drake Anony Mous wrote: >Hi, > >We've got some clients that are concerned about running Postgresql 7.3.4 on >a Win2k Server box, alongside MS SQL Server. I've been running pg on my XP >machines for a long time now (with cygwin) and never had any sort of >problem. The db is fast and stable. > >Does anyone have any experience that would give some weight to our client's >concerns? Would there be any potential conflict between the postmaster and >MS SQL Server? Your experience and advice would be greatly appreciated. > >-Peter > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Hello, Well it of course depends on what you are doing. Traditionally I would say, "Are you nuts?" but it really depends on what you are doing. It is all about risk... if PostgreSQL freaks out and takes out the machine, what will happen to the MS SQL server? What about cost associated with downtime? The same goes for if the MS SQL server takes out the machine? How important is what PostgreSQL is doing? Considering you could put together a box that will outperform PostgreSQL/Cgwin running Linux for about 700 bucks. Why not just get a new machine and not risk the exposure? Sincerely, Joshua D. Drake Anony Mous wrote: >Hi, > >We've got some clients that are concerned about running Postgresql 7.3.4 on >a Win2k Server box, alongside MS SQL Server. I've been running pg on my XP >machines for a long time now (with cygwin) and never had any sort of >problem. The db is fast and stable. > >Does anyone have any experience that would give some weight to our client's >concerns? Would there be any potential conflict between the postmaster and >MS SQL Server? Your experience and advice would be greatly appreciated. > >-Peter > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
On Thu, Apr 22, 2004 at 09:40:55PM -0600, Anony Mous wrote: > Hi, > > We've got some clients that are concerned about running Postgresql 7.3.4 on > a Win2k Server box, alongside MS SQL Server. I've been running pg on my XP > machines for a long time now (with cygwin) and never had any sort of > problem. The db is fast and stable. > > Does anyone have any experience that would give some weight to our client's > concerns? Would there be any potential conflict between the postmaster and > MS SQL Server? Your experience and advice would be greatly appreciated. No, but it's irrelevant. Your clients are idiots who think they know more about computers than you. You have three choices: 1. Use this as an opportunity to introduce them to low-cost, highly-functional free Unix systems, i.e. Linux or BSD. They'll thank you later. 2. Have "the talk" with them. I've done this twice in the last 10 years. Sit them down in a meeting, and simply ask "If you know more about this than me, then it doesn't make sense that you're paying me $100/hour to tell you stuff, does it? Do you give your attorney legal advice, too?" Depends on the client, worked fine for me and in both cases the clients started treating me very differently after that. I cannot imagine giving my attorney legal advice, I have no idea why people see this differently. 3. Convince them that it'll be okay. This is the last piece of advice because it is the worst. Any time anything goes wrong with the machine your postgres installation will be the culprit. And stuff will go wrong daily, if you know what I mean. It's a difficult situation when you have such clients, but use it as an opportunity to learn more about doing business. Michael -- Michael Darrin Chaney mdchaney@michaelchaney.com http://www.michaelchaney.com/
Anony Mous <A.Mous@shaw.ca> writes:
> How is it possible for Postgresql to "freak out" and take out the machine?
On real operating systems (e.g. Unixen) it can't, because it's an
unprivileged process. I wouldn't care to make a similar statement
about Windows though.
regards, tom lane
OK. Thanks to all who responded. Sounds like no foreseeable problems exist
when running Postgresql alongside another enterprise db on a win box. The
advice against doing that, however, is well taken and I appreciate it all.
The reality of my situation is that the MS SQL Server may only reside on the
same machine (Win2K server) until all data has been transferred from it to
postgres, and then for maybe a month after that. Beyond that, postgres will
be the only RDBMS on the box. It is for the transitional time where the
concerns were raised. I am stuck, however, to running on top of a Win OS at
the moment.
Cheers,
Peter
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: April 22, 2004 11:35 AM
To: Anony Mous
Cc: 'Joshua D. Drake'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] FW: Postgres alongside MS SQL Server
Anony Mous <A.Mous@shaw.ca> writes:
> How is it possible for Postgresql to "freak out" and take out the machine?
On real operating systems (e.g. Unixen) it can't, because it's an
unprivileged process. I wouldn't care to make a similar statement
about Windows though.
regards, tom lane
At 10:19 AM 4/28/2004 -0600, Anony Mous wrote: >I'm not ruling out the idea of running with a separate linux box, but there >are some strong reasons to stick with the MS box. So, your point is well >taken. > >That aside, however, I still need to draw from various people's experience >to get a feel for any problems that may arise when running next to MS SQL >Server. I've heard that Postgresql is a task that runs with "Normal" >priority, and can therefore not lock up the machine to the point where it's >not recoverable. In contrast, I've heard as well that MS SQL Server does >indeed run as a high priority task and will take precedence when the OS >doles out CPU resources. > >How is it possible for Postgresql to "freak out" and take out the machine? How easy/likely is it for a program run as a normal user to blue screen an MS server? How easy/likely is it for a program run as a normal user to do the equiv to a FreeBSD/Linux server? You can also effectively take out a machine by using too much memory and going into swap death-spiral. I'm not sure if it is easy to limit Postgresql memory usage "gracefully" on an MS box. AFAIK you can do memory limits on Linux/Unix boxes. MS built-in task manager doesn't let you kill all processes. You need 3rd party tools to do the equiv of kill -9. But of course you shouldn't kill -9 postgresql. Which brings us to something I don't know the answer of - what's the safe way of terminating postgresql on a MS server? Regards, Link.
It is not that it would (I haven't had it happen) but it could. I am speaking more from a business liabilityServer. I've heard that Postgresql is a task that runs with "Normal" priority, and can therefore not lock up the machine to the point where it's not recoverable. In contrast, I've heard as well that MS SQL Server does indeed run as a high priority task and will take precedence when the OS doles out CPU resources. How is it possible for Postgresql to "freak out" and take out the machine?
standpoint than a technical capability standpoint.
One would not typically in a production environment put to RDMS on the same machine. Your management's
fears are well founded in general. They have nothing to do with PostgreSQL as much as much as they have
to do with the fact that you are:
A. Running Win2000? Which although reasonably stable doesn't scale well.
B. Running MS SQL which is known to be a resource hog.
C. Considering putting PostgreSQL with an emulated environment on top of it all.
My response would be the same if the question would about MySQL or Firebird. It just eeks to bad mojo.
Think about the diagnosing problems!
Sincerely,
Joshua D. Drake
Replies are greatly appreciated. -Peter -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: April 22, 2004 10:09 AM To: Anony Mous Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] FW: Postgres alongside MS SQL Server Hello, Well it of course depends on what you are doing. Traditionally I would say, "Are you nuts?" but it really depends on what you are doing. It is all about risk... if PostgreSQL freaks out and takes out the machine, what will happen to the MS SQL server? What about cost associated with downtime? The same goes for if the MS SQL server takes out the machine? How important is what PostgreSQL is doing? Considering you could put together a box that will outperform PostgreSQL/Cgwin running Linux for about 700 bucks. Why not just get a new machine and not risk the exposure? Sincerely, Joshua D. Drake Anony Mous wrote:Hi, We've got some clients that are concerned about running Postgresql 7.3.4 on a Win2k Server box, alongside MS SQL Server. I've been running pg on my XP machines for a long time now (with cygwin) and never had any sort of problem. The db is fast and stable. Does anyone have any experience that would give some weight to our client's concerns? Would there be any potential conflict between the postmaster and MS SQL Server? Your experience and advice would be greatly appreciated. -Peter ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Anony Mous <A.Mous@shaw.ca> writes:
> The reality of my situation is that the MS SQL Server may only reside on the
> same machine (Win2K server) until all data has been transferred from it to
> postgres, and then for maybe a month after that. Beyond that, postgres will
> be the only RDBMS on the box. It is for the transitional time where the
> concerns were raised. I am stuck, however, to running on top of a Win OS at
> the moment.
Hmm... I had taken your initial post to mean that you intended to run an
experimental Postgres server on the same box as your production server.
If you mean you intend to transition to using PG-on-Cygwin-on-Windows
as a production server, well, I don't think anyone around here will
recommend that as a good idea. That assemblage is not stable enough to
qualify as a production-grade database (bearing in mind that database
geeks have very high standards for "production grade" reliability).
You really ought to reconsider this.
In a year or three we may think that the currently-in-progress native
Windows port is stable enough to be used for production. But the
Cygwin port has never been intended as anything except a playpen for
application authors who wanted to write and test SQL code on their
Windows laptops.
regards, tom lane
Tom Lane wrote: > Anony Mous <A.Mous@shaw.ca> writes: > > The reality of my situation is that the MS SQL Server may only reside on the > > same machine (Win2K server) until all data has been transferred from it to > > postgres, and then for maybe a month after that. Beyond that, postgres will > > be the only RDBMS on the box. It is for the transitional time where the > > concerns were raised. I am stuck, however, to running on top of a Win OS at > > the moment. > > Hmm... I had taken your initial post to mean that you intended to run an > experimental Postgres server on the same box as your production server. > If you mean you intend to transition to using PG-on-Cygwin-on-Windows > as a production server, well, I don't think anyone around here will > recommend that as a good idea. That assemblage is not stable enough to > qualify as a production-grade database (bearing in mind that database > geeks have very high standards for "production grade" reliability). > You really ought to reconsider this. > > In a year or three we may think that the currently-in-progress native > Windows port is stable enough to be used for production. But the > Cygwin port has never been intended as anything except a playpen for > application authors who wanted to write and test SQL code on their > Windows laptops. Though that is Tom's opinion, I think the community opinion is that users should determine for themselves whether Cygwin or the upcoming native Win32 port are ready for production use. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >How is it possible for Postgresql to "freak out" and take out the > >machine? > > How easy/likely is it for a program run as a normal user to > blue screen an MS server? > How easy/likely is it for a program run as a normal user to > do the equiv to > a FreeBSD/Linux server? With *decent drivers* this should be about as hard (in theory impossible on both, but we all know that's not true). Now there are a lot of drivers that let this happen quite easily. Especially graphics drivers. So stay away from any high performance graphics drivers on the server. An important thing to notice here is that you should *NEVER EVER* run the database server as "Local System". This account has way too many rights and permissions. Including modifying some things in kernel level. Always use a specific account for the server (try to stay away from making this account a "local administrator", but even if you have to do that it is miles better than Local System) > I'm not sure if it is easy to limit Postgresql memory usage > "gracefully" on > an MS box. AFAIK you can do memory limits on Linux/Unix boxes. There are ways using what's called a "Job Object". You'll need a frontend for it, though (it's just API calls). And IIRC MS ships a frontend for it only on Datacenter Server. But there are third-party tools available, or you can write yuor own wrapper if you're comfy enough with that (look at CreateJobObject(), SetInformationJobObject() and AssignProcessToJobObject() APIs on MSDN) Doing this, malloc() and friends will return "out of memory". > MS built-in task manager doesn't let you kill all processes. It will let you kill any processes at your own level of permissions, just as Unix will. The difference is that a local admin on the machine is not equal to root, and can still not kill processes owned by "Local System". As a tip, a way to kill a local system process is to use "at /interactive" to schedule a "pviewer.exe" fromt he support tools (they're on the CD, but not installed by default). Then pviewer.exe will run as local system. From that one, you can kill local system processes. > You need 3rd > party tools to do the equiv of kill -9. But of course you > shouldn't kill -9 > postgresql. Which brings us to something I don't know the answer of - > what's the safe way of terminating postgresql on a MS server? I don't know enough about the cygwin version to comment on that one, but for the upcoming native version there will be: * If running as a service, use service control manager * If running in a console window, use Ctrl-C in that window * In either case, use the "pgkill" tool (currently on the win32 status page, not sure what to do about that when we get to release time. If it's to be included, it has to be cleaned up) * Possibly a pg_terminate_backend() function inside a pqsl session //Magnus
At 06:13 PM 4/23/2004 +0200, Magnus Hagander wrote: > > MS built-in task manager doesn't let you kill all processes. > >It will let you kill any processes at your own level of permissions, >just as Unix will. The difference is that a local admin on the machine >is not equal to root, and can still not kill processes owned by "Local >System". Sometimes it won't let me kill notepad or something I'm sure is not owned by Local System (coz normal users shouldn't be able to start stuff as local system ;) ). Need to use another tool. > > postgresql. Which brings us to something I don't know the answer of - > > what's the safe way of terminating postgresql on a MS server? > >I don't know enough about the cygwin version to comment on that one, but >for the upcoming native version there will be: >* If running as a service, use service control manager >* If running in a console window, use Ctrl-C in that window >* In either case, use the "pgkill" tool (currently on the win32 status >page, not sure what to do about that when we get to release time. If >it's to be included, it has to be cleaned up) >* Possibly a pg_terminate_backend() function inside a pqsl session Sounds good. I still think he shouldn't use the cygwin version given that he's in a situation where he has to ask the question he is asking... Link.