Thread: Improving Availability

Improving Availability

From
Guido Neitzer
Date:
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

Re: Improving Availability

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


Re: Improving Availability

From
Richard Huxton
Date:
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

Re: Improving Availability

From
Guido Neitzer
Date:
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

Re: Improving Availability

From
Richard Huxton
Date:
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

Re: Improving Availability

From
Scott Marlowe
Date:
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.

Re: Improving Availability

From
Scott Marlowe
Date:
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.

Re: Improving Availability

From
Guido Neitzer
Date:
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

Re: Improving Availability

From
Brendan Duddridge
Date:
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

Re: Improving Availability

From
Guido Neitzer
Date:
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

Attachment