"the part that suffers most from this flakiness is the subscription
process.  If that gets interrupted, that requires restarting the copying
of data"

That's exactly what we noticed.  It kept restarting over and over, and
never made it through the initial bulk-copy process.  My DB is in the
tens of gigabytes and we have all tables in a single big set.  However,
even if I broke it up into multiple sets it still wouldn't work.  For
one, some of my tables have foreign key relationships to each other so
technically they need to be in the same set.  That aside, I do have some
pretty large tables that would likely fail on their own.

Other people suggested doing the initial subscription on some machine in
the LAN, then copying the entire /data directory to the new location
(across the WAN using rsync or whatnot), then updating the path info on
the master and kicking off replication to catch up on all the changes
that happened during the move.  That sounds like a great idea - I bet
it'll work.

--S


-----Original Message-----
From: Christopher Browne [mailto:cbbrowne at ca.afilias.info] 
Sent: Friday, August 22, 2008 7:46 AM
To: Shahaf Abileah
Cc: slony1-general at lists.slony.info
Subject: Re: [Slony1-general] Slony over a WAN?

Shahaf Abileah <shahaf at redfin.com> writes:
> Actually, in my case Slony does work well in LAN but does not work
> at all over WAN.  As I understand it, at least one of the slon
> processes (perhaps both) need to communicate with both the master
> and the slave DB.  If the slon process runs in the same location as
> the master, then it needs to communicate with the slave over WAN.
> If it runs in the same location as the slave then it needs to
> communicate with the master over WAN.  One way or another you'll
> have a slon process talking across the WAN to some DB.  And in our
> case that simply didn't work.  There was enough flakiness in the
> network between the slon process and the DB that replication never
> got going.

Ouch!  Yes, if the WAN connection is really flakey, it won't turn out
well.  Fortunately (maybe!) that will show up immediately, as the part
that suffers most from this flakiness is the subscription process.  If
that gets interrupted, that requires restarting the copying of data.
(You could subscribe 1 table at a time, so that the "restart" is only
going back to the table Slony-I's working on, but if you've got one
"dominant" large table, as is fairly common, the difference isn't too
material...)

Once you have a subscription working, "network flakiness" should only
be an inconvenience.

We've been running Slony-I across a WAN ever since the very first day
it was put into production.  The comments about network flakiness in
the "best practices" do come from behaviour that I have directly seen.

FYI, I wouldn't expect to find that log shipping helps terribly much;
it writes out the same quantity of data in similarly sized "blocks."
If you can't get the initial subscription to go off across the WAN, I
wouldn't expect you'd find it that much easier to get the initial
logshipping data dump across the WAN, either.
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://cbbrowne.com/info/emacs.html
He's not dead. He's electroencephalographically challenged.