Thread: FW: Postgres alongside MS SQL Server

FW: Postgres alongside MS SQL Server

From
Anony Mous
Date:
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


Re: FW: Postgres alongside MS SQL Server

From
Anony Mous
Date:
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


Re: FW: Postgres alongside MS SQL Server

From
"Joshua D. Drake"
Date:
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


Re: FW: Postgres alongside MS SQL Server

From
Michael Chaney
Date:
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/

Re: FW: Postgres alongside MS SQL Server

From
Tom Lane
Date:
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

Re: FW: Postgres alongside MS SQL Server

From
Anony Mous
Date:
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


Re: FW: Postgres alongside MS SQL Server

From
Lincoln Yeoh
Date:
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.

Re: FW: Postgres alongside MS SQL Server

From
"Joshua D. Drake"
Date:

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? 
It is not that it would (I haven't had it happen) but it could. I am speaking more from a business liability
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

Re: FW: Postgres alongside MS SQL Server

From
Tom Lane
Date:
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

Re: FW: Postgres alongside MS SQL Server

From
Bruce Momjian
Date:
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

Re: FW: Postgres alongside MS SQL Server

From
"Magnus Hagander"
Date:
> >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

Re: FW: Postgres alongside MS SQL Server

From
Lincoln Yeoh
Date:
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.