Обсуждение: High-availability

Hi all,

   After realizing that 'clustering' in the PgSQL docs means multiple
DBs behind one server, and NOT multple machines, I am back at square
one, feeling somewhat the fool. :P

   Can anyone point me to docs/websites that discuss options on
replicating in (as close as possible to) realtime? Ideally with load
balancing while both/all servers are up, and failover/resyncing when a
member fails and is restored.

   Is this even possible on PostgreSQL?

   Being a quite small company, proprietary hardware and fancy software
licenses are not possible (ie: 'use oracle' won't help).

   I've looked at slony, but it looks more like a way to push occasional
copies to slaves, and isn't meant to be real time. Am I wrong by chance?

   Thanks for any help/tips/pointers!

Madi

On 6/1/07, Madison Kelly <linux@alteeve.com> wrote:
>    After realizing that 'clustering' in the PgSQL docs means multiple
> DBs behind one server, and NOT multple machines, I am back at square
> one, feeling somewhat the fool. :P

I remember being similarly disappointed in this rampant co-opting of
the word "cluster" back in 7.4 or so. :) A gaggle of geese, a murder
of crows, a cluster of databases, I guess.

>    Can anyone point me to docs/websites that discuss options on
> replicating in (as close as possible to) realtime? Ideally with load
> balancing while both/all servers are up, and failover/resyncing when a
> member fails and is restored.

The PostgreSQL documentation gives a pretty good overview of the options:

  http://www.postgresql.org/docs/8.2/interactive/high-availability.html

That said, there is to my knowledge no single, integrated product that
will do all you ask. None are capable of anything near real-time,
automatic failover tends to be left as an exercise for the reader, and
there is a lot of work to get it up and running, and requires
particular care in maintenance and monitoring once it's up.

There are several commercial (Mammoth Replicator comes to mind) and
several open-source projects. Among the open-source ones (Slony-I,
pgpool, PGCluster), I believe Slony-I is the most mature. There are a
few in-progress attempts (pgpool-II, PGCluster 2, PostgreSQL-R) that
are not ready for prime time yet; of these, I believe pgpool-II is the
most promising.

As mentioned in a different thread today, work is being done to
implement WAL-based master-slave replication, which I think should
prove more scalable and more transparent than the current third-party
products:

  http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php

>    I've looked at slony, but it looks more like a way to push occasional
> copies to slaves, and isn't meant to be real time. Am I wrong by chance?

Slony is indeed intended for near-real-time replication; it's
asynchronous, so slaves always lag behind the master. The amount of
discrepancy depends on a bunch of factors -- individual node
performance, network performance, and system load.

Alexander.

On 6/3/07, Madison Kelly <linux@alteeve.com> wrote:
> > Slony is indeed intended for near-real-time replication; it's
> > asynchronous, so slaves always lag behind the master. The amount of
> > discrepancy depends on a bunch of factors -- individual node
> > performance, network performance, and system load.
>
> That was *exactly* the kind of link I was trying to find.

You're welcome.

As a side-note, I sat up pgpool-II today, and was pleasantly surprised
about how easy it all was; within two minutes I had two databases in
perfect sync on my laptop. It has limitations (such as in its handling
of sequences), but compared to Slony it's like a breath of fresh
mountain air.

Pgpool-II also supports table partitioning, where you define each
database to have a subset of the data. Pgpool-II then intercepts every
SQL statement and routes it to the correct server. It doesn't work
with referential integrity, I think, which is a major limitation, but
it's the nature of the beast.

Alexander.

On 6/3/07, Alexander Staubo <alex@purefiction.net> wrote:
> As a side-note, I sat up pgpool-II today, and was pleasantly surprised
> about how easy it all was; within two minutes I had two databases in
> perfect sync on my laptop. It has limitations (such as in its handling
> of sequences), but compared to Slony it's like a breath of fresh
> mountain air.

Err, the setup is, I mean. Once you have Slony up and running, it's
pretty smooth.

Alexander.

Alexander Staubo wrote:
> On 6/1/07, Madison Kelly <linux@alteeve.com> wrote:
>>    After realizing that 'clustering' in the PgSQL docs means multiple
>> DBs behind one server, and NOT multple machines, I am back at square
>> one, feeling somewhat the fool. :P
>
> I remember being similarly disappointed in this rampant co-opting of
> the word "cluster" back in 7.4 or so. :) A gaggle of geese, a murder
> of crows, a cluster of databases, I guess.
>
>>    Can anyone point me to docs/websites that discuss options on
>> replicating in (as close as possible to) realtime? Ideally with load
>> balancing while both/all servers are up, and failover/resyncing when a
>> member fails and is restored.
>
> The PostgreSQL documentation gives a pretty good overview of the options:
>
>  http://www.postgresql.org/docs/8.2/interactive/high-availability.html
>
> That said, there is to my knowledge no single, integrated product that
> will do all you ask. None are capable of anything near real-time,
> automatic failover tends to be left as an exercise for the reader, and
> there is a lot of work to get it up and running, and requires
> particular care in maintenance and monitoring once it's up.
>
> There are several commercial (Mammoth Replicator comes to mind) and
> several open-source projects. Among the open-source ones (Slony-I,
> pgpool, PGCluster), I believe Slony-I is the most mature. There are a
> few in-progress attempts (pgpool-II, PGCluster 2, PostgreSQL-R) that
> are not ready for prime time yet; of these, I believe pgpool-II is the
> most promising.
>
> As mentioned in a different thread today, work is being done to
> implement WAL-based master-slave replication, which I think should
> prove more scalable and more transparent than the current third-party
> products:
>
>  http://archives.postgresql.org/pgsql-hackers/2007-03/msg00050.php
>
>>    I've looked at slony, but it looks more like a way to push occasional
>> copies to slaves, and isn't meant to be real time. Am I wrong by chance?
>
> Slony is indeed intended for near-real-time replication; it's
> asynchronous, so slaves always lag behind the master. The amount of
> discrepancy depends on a bunch of factors -- individual node
> performance, network performance, and system load.
>
> Alexander.

That was *exactly* the kind of link I was trying to find.

Thank you!

Madi

Alexander Staubo wrote:
>> As a side-note, I sat up pgpool-II today, and was pleasantly surprised
>> about how easy it all was; within two minutes I had two databases in
>> perfect sync on my laptop. It has limitations (such as in its handling
>> of sequences), but compared to Slony it's like a breath of fresh
>> mountain air.
>
> Err, the setup is, I mean. Once you have Slony up and running, it's
> pretty smooth.

I wonder what the OP means by "real-time".  The standard definition is "within
a deterministic time bound".

Replication implies latency.  Ignoring latency or wishing it away will not help.

It is possible to manage latency.  One strategy is to minimize it.  There are
others.

Also remember the ancient proverb, applicable when two or more nodes are
trying to agree on what time it is:
"Man with two watches never knows correct time."

I think of this category of issue as the Special Relativity of information.

--
Lew

Madison Kelly wrote:
>   Being a quite small company, proprietary hardware and fancy software
> licenses are not possible (ie: 'use oracle' won't help).

How much data do you put in the DB?  Oracle has a free version, but it has
size limits.

(Ducking the slings and arrows of outraged PG fans: I prefer Postgre, I really
do.)

--
Lew

Lew wrote:
> Madison Kelly wrote:
>>   Being a quite small company, proprietary hardware and fancy software
>> licenses are not possible (ie: 'use oracle' won't help).
>
> How much data do you put in the DB?  Oracle has a free version, but it
> has size limits.
>
> (Ducking the slings and arrows of outraged PG fans: I prefer Postgre, I
> really do.)
>

   Hrm, it's hard to say as we're (hoping!) to grow. At the moment, a
few hundred megs. If the company gets off the ground, possibly much
more. also, we've got a few (dozen or so) side projects that each have
their own DBs.

   I think the risk of running into a barrier like a size limit would be
too much. Even if we get off the ground, the storage needs of the DB
will outgrow our revenue. I'd hate to be in a position where I am
dependent on a (potentially) very expensive invoice while we are still
running on a shoe-string.

   Thanks for the suggestion though! I will poke at the free/trial
version and, if I am unable to load-balance pgSQL and we run into
performance problems, I will have a better idea of what options I have
(ie: bigger iron vs. an oracle license).

   Thanks!

Madi

On Sun, Jun 03, 2007 at 01:35:49PM -0400, Lew wrote:
> How much data do you put in the DB?  Oracle has a free version, but it has
> size limits.

AFAIK, Oracle's free version doesn't include RAC, which is what would
be needed to satisfy the request anyway.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
        --Damien Katz

Madison Kelly wrote:

Hi all,

  After realizing that 'clustering' in the PgSQL docs means multiple DBs behind one server, and NOT multple machines, I am back at square one, feeling somewhat the fool. :P

  Can anyone point me to docs/websites that discuss options on replicating in (as close as possible to) realtime? Ideally with load balancing while both/all servers are up, and failover/resyncing when a member fails and is restored.

If you're interested in the "less than ideal" case (no load balancing, but synchronous replication in a "warm standby" type mode), there are several options, such as shared disk (two systems sharing a SAN or NAS with heartbeat-style fail over - shared disk scenario), or DRBD (where block level changes to one device are mirrored in real-time over to another, with heartbeat style fail over - this is a "shared nothing" type scenario).  It's not too hard to put together a "warm standby" synchronous replication mechanism with overhead that isn't too much more than what you incur by enabling PITR...  Such systems can also have very fast failover on failure detection (via heartbeat2), and be synchronous.

I think you'll typically find that you can get one or the other - synchronous replication, or load balancing...but not both.  On the other hand, if you were really serious about having close to both, you could have a three node setup - two (a provider and subscriber) that run using Slony-I (and async replication) and one that runs using one of the aforementioned methods (i.e., DRBD and warm-standby synchronous replication).  In such cases a "failover" would mean switching to the synchronous replication system.  You should even be able to get SLONY to continuing to avail you with load balancing in such a case, without having to re-sync - though I haven't tried this myself...  You'd still have a potential query that got stale data (when it went to a Slony-I subscriber), but you would never lose a committed transaction.  You'd have the added benefit of a "shared nothing" environment as well...

As a side plug, we discuss and implement a few of these options in our PostgreSQL performance tuning course.. http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47&cat_id=8

  Is this even possible on PostgreSQL?

  Being a quite small company, proprietary hardware and fancy software licenses are not possible (ie: 'use oracle' won't help).

  I've looked at slony, but it looks more like a way to push occasional copies to slaves, and isn't meant to be real time. Am I wrong by chance?

  Thanks for any help/tips/pointers!

Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Expert PostgreSQL Training - On-Site and Public Enrollment

Madi

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Chander Ganesan wrote:
> Madison Kelly wrote:
>> Hi all,
>>
>>   After realizing that 'clustering' in the PgSQL docs means multiple
>> DBs behind one server, and NOT multple machines, I am back at square
>> one, feeling somewhat the fool. :P
>>
>>   Can anyone point me to docs/websites that discuss options on
>> replicating in (as close as possible to) realtime? Ideally with load
>> balancing while both/all servers are up, and failover/resyncing when a
>> member fails and is restored.
> If you're interested in the "less than ideal" case (no load balancing,
> but synchronous replication in a "warm standby" type mode), there are
> several options, such as shared disk (two systems sharing a SAN or NAS
> with heartbeat-style fail over - shared disk scenario), or DRBD (where
> block level changes to one device are mirrored in real-time over to
> another, with heartbeat style fail over - this is a "shared nothing"
> type scenario).  It's not too hard to put together a "warm standby"
> synchronous replication mechanism with overhead that isn't too much more
> than what you incur by enabling PITR...  Such systems can also have very
> fast failover on failure detection (via heartbeat2), and be synchronous.
>
> I think you'll typically find that you can get one or the other -
> synchronous replication, or load balancing...but not both.  On the other
> hand, if you were really serious about having close to both, you could
> have a three node setup - two (a provider and subscriber) that run using
> Slony-I (and async replication) and one that runs using one of the
> aforementioned methods (i.e., DRBD and warm-standby synchronous
> replication).  In such cases a "failover" would mean switching to the
> synchronous replication system.  You should even be able to get SLONY to
> continuing to avail you with load balancing in such a case, without
> having to re-sync - though I haven't tried this myself...  You'd still
> have a potential query that got stale data (when it went to a Slony-I
> subscriber), but you would never lose a committed transaction.  You'd
> have the added benefit of a "shared nothing" environment as well...
>
> As a side plug, we discuss and implement a few of these options in our
> PostgreSQL performance tuning course..
> http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47&cat_id=8
>
>>
>>   Is this even possible on PostgreSQL?
>>
>>   Being a quite small company, proprietary hardware and fancy software
>> licenses are not possible (ie: 'use oracle' won't help).
>>
>>   I've looked at slony, but it looks more like a way to push
>> occasional copies to slaves, and isn't meant to be real time. Am I
>> wrong by chance?
>>
>>   Thanks for any help/tips/pointers!
>>
> Chander Ganesan
> Open Technology Group, Inc.
> One Copley Parkway, Suite 210
> Morrisville, NC  27560
> Phone: 877-258-8987/919-463-0999
> http://www.otg-nc.com
> *Expert PostgreSQL Training - On-Site and Public Enrollment*
>
>> Madi
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>

Thank you for your reply!

The more I learn, the more I am leaning towards the DRBD/shared-nothing
setup. Our loads are not terribly heavy at this point. I hate the idea
of having a nice server sitting there doing nothing 99% of the time, but
it looks like the most viable way of setting up HA at this point. Given
that I am learning as I go, I think the three-way setup you describe
would be a bit too ambitious for me just now. That said, I do have a
spare third server that I could use for just such a setup, should I feel
comfortable enough down the road.

Madi

On Mon, Jun 04, 2007 at 04:21:32PM +0200, Chander Ganesan wrote:
> I think you'll typically find that you can get one or the other -
> synchronous replication, or load balancing...but not both.  On the other

Hi,

I am in very similar position, but I am more failover oriented. I am
considering using pgcluster, which shall resolve both at the cost of
slight transaction overhead. Does anyone have any experience with this?
What problems may I expect in this setup?

Kind regards,
Bohdan

On Mon, 2007-06-04 at 10:21 -0400, Chander Ganesan wrote:

> It's not too hard to put together a "warm standby" synchronous
> replication mechanism with overhead that isn't too much more than what
> you incur by enabling PITR...  Such systems can also have very fast
> failover on failure detection (via heartbeat2), and be synchronous.

Do you have any performance measurements of either the replication
overhead or the failover time? I'm interested in how well we cope with
high transaction rates. Thanks.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com

Simon Riggs wrote:

On Mon, 2007-06-04 at 10:21 -0400, Chander Ganesan wrote:
 

It's not too hard to put together a "warm standby" synchronous
replication mechanism with overhead that isn't too much more than what
you incur by enabling PITR...  Such systems can also have very fast
failover on failure detection (via heartbeat2), and be synchronous.   

Do you have any performance measurements of either the replication
overhead or the failover time? I'm interested in how well we cope with
high transaction rates. Thanks.
 

Aside from a bunch of customized pgbench benchmarks (on the 9.6 GB sample database we use), which are "better than nothing, but far from the best", not really.  In my experience, the larger the database; slower the commit rate; and less frequently the checkpoints - the better the performance of synchronous warm-replication.  In our tests, higher commit rates and more frequent checkpoints incur a higher penalty.  Basically, the more WAL activity the higher the cost.

If I have time I'll see if we can run a more meaningful metric (need to generate a smaller database for that) the next time we have a performance tuning class (in August).

The failover time is tunable to some extent...via heartbeat2 (incurs < 1% performance penalty, but with sub-second failover this can go up a bit), and can be pretty quick (I usually set it up with around a 3 second failover time on node failure, then factor that in with the amount of time required for WAL auto-recovery)...it really depends a lot on what your metric is for "failure" (since node failover is probably the "worst worst case").

-- 
Chander Ganesan
The Open Technology Group
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Although I rarely see it mentioned, Skype has some replication tools that
they opensourced.

https://developer.skype.com/SkypeGarage/DbProjects/SkyTools