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
