Re: High-availability - Mailing list pgsql-general

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