Re: High-availability - Mailing list pgsql-general

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