Improving Availability

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

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

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:

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