Thread: Improving Availability
Hi. I have two server boxes (Apple Xserve G5). WebServers and applications are installed on both machines, the load is distributed by a loadbalancer to both machines. How can I make a setup in a way that the database is also tolerant against failure? What I actually need is a mechanism that keeps both machines synchronized. What is the way to go for this? Thanks. cug
Attachment
Guido Neitzer wrote: > Hi. > > I have two server boxes (Apple Xserve G5). WebServers and > applications are installed on both machines, the load is distributed > by a loadbalancer to both machines. > > How can I make a setup in a way that the database is also tolerant > against failure? What I actually need is a mechanism that keeps both > machines synchronized. > > What is the way to go for this? Slony-I, I would say Mammoth Replicator but we won't support XServe until they release the intel versions. Sincerely, Joshua D. Drake > > Thanks. > > cug -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
Guido Neitzer wrote: > Hi. > > I have two server boxes (Apple Xserve G5). WebServers and applications > are installed on both machines, the load is distributed by a > loadbalancer to both machines. > > How can I make a setup in a way that the database is also tolerant > against failure? What I actually need is a mechanism that keeps both > machines synchronized. > > What is the way to go for this? It depends on how "failsafe" you want to be. You could use log-shipping and PITR (see manuals) to make sure machine 2 can be restored to where machine 1 was "recently". You could use replication (e.g. Slony) To copy transactions from machine 1 to machine 2. You could use something like pgpool to copy queries to both servers. You need to make sure you understand the implications of this as regards timestamps and auto-generated numbers etc. It might be worth having pgpool in place anyway for your setup. You could have a dual-attached disk system, and then when one machine fails, the other could start up its postgresql server (but make sure both never try to run at once). I'd be tempted to look at pgpool sitting between your application and the database, and then replication from the one machine to the other. -- Richard Huxton Archonet Ltd
On 14.12.2005, at 16:46 Uhr, Richard Huxton wrote: > I'd be tempted to look at pgpool sitting between your application > and the database, and then replication from the one machine to the > other. Right, I thought the same, BUT: I have two application servers (one on each machine) connecting to a network URL via JDBC. When pgpool is involved, I assume it only runs on one server, right? Or can I have pgpool running on both servers, each knowing about the two servers and the applications just connect to "localhost"? cug
Attachment
Guido Neitzer wrote: > On 14.12.2005, at 16:46 Uhr, Richard Huxton wrote: > >> I'd be tempted to look at pgpool sitting between your application and >> the database, and then replication from the one machine to the other. > > Right, I thought the same, BUT: I have two application servers (one on > each machine) connecting to a network URL via JDBC. When pgpool is > involved, I assume it only runs on one server, right? > > Or can I have pgpool running on both servers, each knowing about the two > servers and the applications just connect to "localhost"? That's the sort of thing I was thinking of. Keep the application ignorant of the actual setup. -- Richard Huxton Archonet Ltd
On Wed, 2005-12-14 at 10:19, Guido Neitzer wrote: > On 14.12.2005, at 16:46 Uhr, Richard Huxton wrote: > > > I'd be tempted to look at pgpool sitting between your application > > and the database, and then replication from the one machine to the > > other. > > Right, I thought the same, BUT: I have two application servers (one > on each machine) connecting to a network URL via JDBC. When pgpool is > involved, I assume it only runs on one server, right? > > Or can I have pgpool running on both servers, each knowing about the > two servers and the applications just connect to "localhost"? If you are using as a read load balancer, with slony doing the replication, it can really only be in one place. But, if you were running it in replication mode, without slony in the background, I'd think that setup could work.
On Wed, 2005-12-14 at 10:29, Scott Marlowe wrote: > On Wed, 2005-12-14 at 10:19, Guido Neitzer wrote: > > On 14.12.2005, at 16:46 Uhr, Richard Huxton wrote: > > > > > I'd be tempted to look at pgpool sitting between your application > > > and the database, and then replication from the one machine to the > > > other. > > > > Right, I thought the same, BUT: I have two application servers (one > > on each machine) connecting to a network URL via JDBC. When pgpool is > > involved, I assume it only runs on one server, right? > > > > Or can I have pgpool running on both servers, each knowing about the > > two servers and the applications just connect to "localhost"? > > If you are using as a read load balancer, with slony doing the > replication, it can really only be in one place. But, if you were > running it in replication mode, without slony in the background, I'd > think that setup could work. This setup I'm talking about would have pgpool on each db server. If you meant pgpool running on both application servers, that would work fine with slony in the background and pgpool in load balancing mode, or with pgpool doing the replication.
Hi Scott, hi Richard, On 14.12.2005, at 17:30 Uhr, Scott Marlowe wrote: > This setup I'm talking about would have pgpool on each db server. > > If you meant pgpool running on both application servers, that would > work > fine with slony in the background and pgpool in load balancing > mode, or > with pgpool doing the replication. Okay, just that I get this right (have to write a business paper on that and they will take me by the word ...): Setup would be: Machine 1: - web server - application server connecting to "localhost --> pgpool" - PostgreSQL installed and accessed only via pgpool - pgpool installed and knowing of machine 1 and machine 2 (replication mode) Machine 2: - web server - application server connecting to "localhost --> pgpool" - PostgreSQL installed and accessed only via pgpool - pgpool installed and knowing of machine 1 and machine 2 (replication mode) If one machine fails, the replication is cut off, and pgpool works with the other machine. Okay so far. The applications only know the connection to the local pgpool, so they are fault tolerant as far as pgpool accepts requests. If one machine fails, the service is not down because as far as all the services on the remaining machines are working properly. To get everything back, we will have to shut down all apps and all databases, sync the db data directories from the working machine to the machine that has failed, start the dbs, start pgpool, start the applications. Everything correct? cug
Attachment
Hi Guido / Richard / Scott, What about Sequoia? Is that better or worse than pgpool? Thanks ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Dec 14, 2005, at 9:51 AM, Guido Neitzer wrote: > Hi Scott, hi Richard, > > On 14.12.2005, at 17:30 Uhr, Scott Marlowe wrote: > >> This setup I'm talking about would have pgpool on each db server. >> >> If you meant pgpool running on both application servers, that >> would work >> fine with slony in the background and pgpool in load balancing >> mode, or >> with pgpool doing the replication. > > Okay, just that I get this right (have to write a business paper on > that and they will take me by the word ...): > > Setup would be: > > Machine 1: > - web server > - application server connecting to "localhost --> pgpool" > - PostgreSQL installed and accessed only via pgpool > - pgpool installed and knowing of machine 1 and machine 2 > (replication mode) > > Machine 2: > - web server > - application server connecting to "localhost --> pgpool" > - PostgreSQL installed and accessed only via pgpool > - pgpool installed and knowing of machine 1 and machine 2 > (replication mode) > > If one machine fails, the replication is cut off, and pgpool works > with the other machine. Okay so far. > > The applications only know the connection to the local pgpool, so > they are fault tolerant as far as pgpool accepts requests. > > If one machine fails, the service is not down because as far as all > the services on the remaining machines are working properly. To get > everything back, we will have to shut down all apps and all > databases, sync the db data directories from the working machine to > the machine that has failed, start the dbs, start pgpool, start the > applications. > > Everything correct? > > cug
Attachment
On 15.12.2005, at 9:10 Uhr, Brendan Duddridge wrote: > What about Sequoia? Is that better or worse than pgpool? That looks interesting, I haven't seen it yet. Has anyone experiences to share? cug