Re: High-availability

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