pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIsolated databases or instances
9+ messages / 4 participants
[nested] [flat]
* Isolated databases or instances
@ 2007-02-16 19:18 Henry, Nigel, CYFD <nigel.henry@state.nm.us>
0 siblings, 2 replies; 9+ messages in thread
From: Henry, Nigel, CYFD @ 2007-02-16 19:18 UTC (permalink / raw)
To: pgsql-admin
We are at the beginning of the building of a 3-tier development/test/uat environment. We would like some advice on how the PostgreSQL database should be configured for this environment.
The environments will be installed on three blades of an IBM Blade Center. Due to budget restrictions, we are building the three environments with separate install instances of the HTTP server (3) 1st blade; separate install instances of the application server (3) 2nd blade -- the HTTP server will be installed from the application build rather than as a separate install; and three PostgreSQL database instances on the last blade.
My concern is that building the PostgreSQL databases as instances from a single binary could lead to problems in a development environment. My recommendation is to create three isolated databases (3 binaries) for each environment to mitigate an unforeseen mishaps.
I would like some advice on what would be the best practice for building a development environment, such as described above, in the type of environment described above.
Nigel D. Henry
State of New Mexico
Computer Software Engineer
Children, Youth, & Family Division
Nigel.Henry@state.nm.us
(505) 841-6631
Confidentiality Notice: This e-mail, including all attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided for under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message.
Confidentiality Notice: This e-mail, including all attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message. -- This email has been scanned by the Sybari - Antigen Email System.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Isolated databases or instances
@ 2007-02-16 19:32 Henry, Nigel, CYFD <nigel.henry@state.nm.us>
parent: Henry, Nigel, CYFD <nigel.henry@state.nm.us>
1 sibling, 0 replies; 9+ messages in thread
From: Henry, Nigel, CYFD @ 2007-02-16 19:32 UTC (permalink / raw)
To: pgsql-admin
________________________________
From: Henry, Nigel, CYFD
Sent: Fri 2/16/2007 12:18 PM
To: pgsql-admin@postgresql.org
Subject: Isolated databases or instances
We are at the beginning of the building of a 3-tier development/test/uat environment in SUSE Linux Enterprise v9.3. We would like some advice on how the PostgreSQL database should be configured for this environment.
The environments will be installed on three blades of an IBM Blade Center. Due to budget restrictions, we are building the three environments with separate install instances of the HTTP server (3) 1st blade; separate install instances of the application server (3) 2nd blade -- the HTTP server will be installed from the application build rather than as a separate install; and three PostgreSQL database instances on the last blade.
My concern is that building the PostgreSQL databases as instances from a single binary could lead to problems in a development environment. My recommendation is to create three isolated databases (3 binaries) for each environment to mitigate an unforeseen mishaps.
I would like some advice on what would be the best practice for building a development environment, such as described above, in the type of environment described above.
Nigel D. Henry
State of New Mexico
Computer Software Engineer
Children, Youth, & Family Division
Nigel.Henry@state.nm.us
(505) 841-6631
Confidentiality Notice: This e-mail, including all attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided for under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message.
Confidentiality Notice: This e-mail, including all attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message. -- This email has been scanned by the Sybari - Antigen Email System.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 19:52 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Henry, Nigel, CYFD <nigel.henry@state.nm.us>
1 sibling, 3 replies; 9+ messages in thread
From: Scott Marlowe @ 2007-02-16 19:52 UTC (permalink / raw)
To: Henry, Nigel, CYFD <nigel.henry@state.nm.us>; +Cc: pgsql-admin
On Fri, 2007-02-16 at 13:18, Henry, Nigel, CYFD wrote:
> We are at the beginning of the building of a 3-tier
> development/test/uat environment. We would like some advice on how
> the PostgreSQL database should be configured for this environment.
>
> The environments will be installed on three blades of an IBM Blade
> Center. Due to budget restrictions, we are building the three
> environments with separate install instances of the HTTP server
> (3) 1st blade; separate install instances of the application server
> (3) 2nd blade -- the HTTP server will be installed from the
> application build rather than as a separate install; and
> three PostgreSQL database instances on the last blade.
>
> My concern is that building the PostgreSQL databases as instances from
> a single binary could lead to problems in a development environment.
> My recommendation is to create three isolated databases (3
> binaries) for each environment to mitigate an unforeseen mishaps.
>
> I would like some advice on what would be the best practice for
> building a development environment, such as described above, in the
> type of environment described above.
What exactly are you trying to accomplish with multiple PostgreSQL
instances here? I can't see there being any great advantage to three
separate instances than having three discrete databases defined in one
instance. Use pg_hba.conf to restrict connections to the databases to
the proper users (testing, staging, and production, I assume) and you
should get the same effect with less complexity.
If you really need three discrete instances, you might want to look at
using vmware to set up three separate virtual machines, each with their
own IPs etc... on that one box.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 21:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-02-16 21:16 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Henry, Nigel, CYFD <nigel.henry@state.nm.us>; pgsql-admin
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Fri, 2007-02-16 at 13:18, Henry, Nigel, CYFD wrote:
>> We are at the beginning of the building of a 3-tier
>> development/test/uat environment. We would like some advice on how
>> the PostgreSQL database should be configured for this environment.
> What exactly are you trying to accomplish with multiple PostgreSQL
> instances here? I can't see there being any great advantage to three
> separate instances than having three discrete databases defined in one
> instance.
Separating development and production instances seems perfectly sensible
to me. For instance, if your development code triggers a crash in
Postgres, you don't really want that event to take out your production
sessions. Also, you might want to run Postgres version x+1 for awhile
as development before replacing version x for production.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 21:35 Joshua Kramer <josh@globalherald.net>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 0 replies; 9+ messages in thread
From: Joshua Kramer @ 2007-02-16 21:35 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Henry, Nigel, CYFD <nigel.henry@state.nm.us>; pgsql-admin
> If you really need three discrete instances, you might want to look at
> using vmware to set up three separate virtual machines, each with their
> own IPs etc... on that one box.
This was my thought; depending on how much RAM you have in each blade, you
could have two virtual servers on each blade, one server being HTTP and
one being Postgres.
I'm not sure how big your application is, but if it's not the next SAP
then you might be able to get away with as little as 1G of RAM per blade
(on the dev and QA instances anyway), if you virtualize Linux servers
using XEN. That's the way I'd do it.
Cheers,
-J
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 21:56 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2007-02-16 21:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Henry, Nigel, CYFD <nigel.henry@state.nm.us>; pgsql-admin
On Fri, 2007-02-16 at 15:16, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > On Fri, 2007-02-16 at 13:18, Henry, Nigel, CYFD wrote:
> >> We are at the beginning of the building of a 3-tier
> >> development/test/uat environment. We would like some advice on how
> >> the PostgreSQL database should be configured for this environment.
>
> > What exactly are you trying to accomplish with multiple PostgreSQL
> > instances here? I can't see there being any great advantage to three
> > separate instances than having three discrete databases defined in one
> > instance.
>
> Separating development and production instances seems perfectly sensible
> to me. For instance, if your development code triggers a crash in
> Postgres, you don't really want that event to take out your production
> sessions. Also, you might want to run Postgres version x+1 for awhile
> as development before replacing version x for production.
I agree. (Note that this is dev/test/uat, not prod/dev).
But it really depends on his usage patterns and such. If he's not
planning on doing any functions in C, and assuming that testing of
things like pgsql / jboss / apache will be done on another system
entirely, then it might well be easier to run everything from one
database server.
I think we need to know more about the planned usage to really say one
way or the other.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 22:30 Henry, Nigel, CYFD <nigel.henry@state.nm.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 1 reply; 9+ messages in thread
From: Henry, Nigel, CYFD @ 2007-02-16 22:30 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-admin
More information:
As stated, this is going to be a SLES 9.3 environment -- 64-bit. VMWare, for now, is out of the question because we currently only have IBM HS20 blades which are unable to host the 64-bit environment in VMWare. So we're running the OS on bare metal. As I mentioned in my original post, we are trying to use our blades wisely by creating the three separate, isolated environments across the blades. Much like this: #
separate install instances of the HTTP server (3 -- DEV/Test/UAT) 1st blade;
separate install instances of the application server
(3 -- DEV/Test/UAT) 2nd blade -- the HTTP server will be installed from the application build rather than as a separate install; and
three PostgreSQL database instances (3 -- DEV/Test/UAT) on the last blade.
This is the only way we can create three environments since even though we have three blades, the blade housing databases belongs to the DBA group. The application being developed is in Java and is not a transaction-intensive application.
Nigel D. Henry
State of New Mexico
Computer Software Engineer
Children, Youth, & Family Division
Nigel.Henry@state.nm.us
(505) 841-6631
Confidentiality Notice: This e-mail, including all attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided for under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message.
________________________________
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Fri 2/16/2007 2:50 PM
To: Henry, Nigel, CYFD
Subject: RE: [ADMIN] Isolated databases or instances
There was no chastisement intended. Please, post your reply to the
list, and include the details I asked for (i.e. what exactly are you
trying to accomplish here) and let's all work on it together.
Perhaps multiple pgsql instances are the best answer. Perhaps VMWare is
the best way to accomplish it. We just don't have enough details.
On Fri, 2007-02-16 at 14:09, Henry, Nigel, CYFD wrote:
> Thank you, sir. I stand chastened before my colleagues.
>
> Nigel D. Henry
> State of New Mexico
> Computer Software Engineer
> Children, Youth, & Family Division
> Nigel.Henry@state.nm.us
> (505) 841-6631
>
> Confidentiality Notice: This e-mail, including all attachments, is for
> the sole use of the intended recipient(s) and may contain confidential
> and privileged information. Any unauthorized review, use, disclosure
> or distribution is prohibited unless specifically provided for under
> the New Mexico Inspection of Public Records Act. If you are not the
> intended recipient, please contact the sender and destroy all copies
> of this message.
>
> ______________________________________________________________________
> From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
> Sent: Fri 2/16/2007 12:52 PM
> To: Henry, Nigel, CYFD
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Isolated databases or instances
>
>
>
> On Fri, 2007-02-16 at 13:18, Henry, Nigel, CYFD wrote:
> > We are at the beginning of the building of a 3-tier
> > development/test/uat environment. We would like some advice on how
> > the PostgreSQL database should be configured for this environment.
> >
> > The environments will be installed on three blades of an IBM Blade
> > Center. Due to budget restrictions, we are building the three
> > environments with separate install instances of the HTTP server
> > (3) 1st blade; separate install instances of the application server
> > (3) 2nd blade -- the HTTP server will be installed from the
> > application build rather than as a separate install; and
> > three PostgreSQL database instances on the last blade.
> >
> > My concern is that building the PostgreSQL databases as instances
> from
> > a single binary could lead to problems in a development environment.
> > My recommendation is to create three isolated databases (3
> > binaries) for each environment to mitigate an unforeseen mishaps.
> >
> > I would like some advice on what would be the best practice for
> > building a development environment, such as described above, in the
> > type of environment described above.
>
> What exactly are you trying to accomplish with multiple PostgreSQL
> instances here? I can't see there being any great advantage to three
> separate instances than having three discrete databases defined in one
> instance. Use pg_hba.conf to restrict connections to the databases to
> the proper users (testing, staging, and production, I assume) and you
> should get the same effect with less complexity.
>
> If you really need three discrete instances, you might want to look at
> using vmware to set up three separate virtual machines, each with
> their
> own IPs etc... on that one box.
Confidentiality Notice: This e-mail, including all attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message. -- This email has been scanned by the Sybari - Antigen Email System.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 23:10 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Henry, Nigel, CYFD <nigel.henry@state.nm.us>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2007-02-16 23:10 UTC (permalink / raw)
To: Henry, Nigel, CYFD <nigel.henry@state.nm.us>; +Cc: pgsql-admin
On Fri, 2007-02-16 at 16:30, Henry, Nigel, CYFD wrote:
> More information:
>
> As stated, this is going to be a SLES 9.3 environment -- 64-bit.
> VMWare, for now, is out of the question because we currently only have
> IBM HS20 blades which are unable to host the 64-bit environment in
> VMWare. So we're running the OS on bare metal. As I mentioned in my
> original post, we are trying to use our blades wisely by creating the
> three separate, isolated environments across the blades. Much like
> this: #
> separate install instances of the HTTP server (3 -- DEV/Test/UAT) 1st
> blade;
> separate install instances of the application server
> (3 -- DEV/Test/UAT) 2nd blade -- the HTTP server will be installed
> from the application build rather than as a separate install; and
> three PostgreSQL database instances (3 -- DEV/Test/UAT) on the last
> blade.
> This is the only way we can create three environments since even
> though we have three blades, the blade housing databases belongs to
> the DBA group. The application being developed is in Java and is not
> a transaction-intensive application.
So, would you need to do things like upgrade postgresql one environment
at a time, or would you be ok with all three environments sharing one
pgsql instance with three separate databases in it?
One instance is much easier to setup and maintain, and it is often the
case in a dev/uat/test environment that only one instance is ever
working hard, so the performance will likely be better on a single
instance than on multiples.
You could also have pg_hba.conf set to only let certain users connect
from certain ips to certain databases, limiting the possibility of one
layer (uat/test/dev) hitting the wrong database.
If you need the isolation provided by three separate instances, then you
can easily do it by creating a couple extra users, and putting the dbs
in their home directories running on different ports. That way, the
pg82dev user and the pg82uat user and the pg82test user all run on, say,
ports 5433, 5434, and 5435, and they can't accidentally mess each other
up, because they're completely different. You don't need a special
install or anything. Just keep in mind you'll need to allocate enough
shared memory to make all three instances happy.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Isolated databases or instances
@ 2007-02-16 23:44 Henry, Nigel, CYFD <nigel.henry@state.nm.us>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 9+ messages in thread
From: Henry, Nigel, CYFD @ 2007-02-16 23:44 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-admin
Scott,
We will try the single PostgreSQL instance with 3 databases configured in it.
Nigel D. Henry
State of New Mexico
Computer Software Engineer
Children, Youth, & Family Division
Nigel.Henry@state.nm.us
(505) 841-6631
Confidentiality Notice: This e-mail, including all attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided for under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message.
________________________________
From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
Sent: Fri 2/16/2007 4:10 PM
To: Henry, Nigel, CYFD
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Isolated databases or instances
On Fri, 2007-02-16 at 16:30, Henry, Nigel, CYFD wrote:
> More information:
>
> As stated, this is going to be a SLES 9.3 environment -- 64-bit.
> VMWare, for now, is out of the question because we currently only have
> IBM HS20 blades which are unable to host the 64-bit environment in
> VMWare. So we're running the OS on bare metal. As I mentioned in my
> original post, we are trying to use our blades wisely by creating the
> three separate, isolated environments across the blades. Much like
> this: #
> separate install instances of the HTTP server (3 -- DEV/Test/UAT) 1st
> blade;
> separate install instances of the application server
> (3 -- DEV/Test/UAT) 2nd blade -- the HTTP server will be installed
> from the application build rather than as a separate install; and
> three PostgreSQL database instances (3 -- DEV/Test/UAT) on the last
> blade.
> This is the only way we can create three environments since even
> though we have three blades, the blade housing databases belongs to
> the DBA group. The application being developed is in Java and is not
> a transaction-intensive application.
So, would you need to do things like upgrade postgresql one environment
at a time, or would you be ok with all three environments sharing one
pgsql instance with three separate databases in it?
One instance is much easier to setup and maintain, and it is often the
case in a dev/uat/test environment that only one instance is ever
working hard, so the performance will likely be better on a single
instance than on multiples.
You could also have pg_hba.conf set to only let certain users connect
from certain ips to certain databases, limiting the possibility of one
layer (uat/test/dev) hitting the wrong database.
If you need the isolation provided by three separate instances, then you
can easily do it by creating a couple extra users, and putting the dbs
in their home directories running on different ports. That way, the
pg82dev user and the pg82uat user and the pg82test user all run on, say,
ports 5433, 5434, and 5435, and they can't accidentally mess each other
up, because they're completely different. You don't need a special
install or anything. Just keep in mind you'll need to allocate enough
shared memory to make all three instances happy.
Confidentiality Notice: This e-mail, including all attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited unless specifically provided under the New Mexico Inspection of Public Records Act. If you are not the intended recipient, please contact the sender and destroy all copies of this message. -- This email has been scanned by the Sybari - Antigen Email System.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-02-16 23:44 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-16 19:18 Isolated databases or instances Henry, Nigel, CYFD <nigel.henry@state.nm.us>
2007-02-16 19:32 ` Henry, Nigel, CYFD <nigel.henry@state.nm.us>
2007-02-16 19:52 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-02-16 21:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-16 21:56 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-02-16 21:35 ` Joshua Kramer <josh@globalherald.net>
2007-02-16 22:30 ` Henry, Nigel, CYFD <nigel.henry@state.nm.us>
2007-02-16 23:10 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-02-16 23:44 ` Henry, Nigel, CYFD <nigel.henry@state.nm.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox