<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>We are trying to set up a slony
slave across two different networks over a WAN connection. Due to network
reliability, we want to first do the initial subscription for the slave on the
same network as the master, and then relocate that machine to the other
network. Then, we want to tweak the slony paths so that we can use a tunnel
through a bridgehead server for the synchronization. <o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>We tried this out with a test
database and it seemed to work, data was replicating and no errors were
reported by slon, but were wondering if anyone can think of reasons why this
might not work or if there is a better way. In particular, we're not sure if
there is a better way than manually editing slony paths, and if it is
acceptable for them to not be consistent between the master and slave(s). Here
are the details of what we tried:<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'># Initial normal
subscription/replication<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Create database
"DB_master" on network A, machine_1_A, and populate with schema and
data<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Create database
"DB_slave"on network A, machine_2_A, with schema only<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Subscribe machine 2
"DB_slave" to machine 1 "DB_master"<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Start slon processes on machine 1 to
replicate initial data set<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Add new data to
"DB_master" and verify changes replicated to "DB_slave"<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'># Now, attempt to 'relocate' the
initial subscription on the slave to another machine<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Stop slon on machine_1_A<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Stop postgres on machine_2_A<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Rsync postgres data directory on
machine_2_A to machine_3_B (on network B)<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Start postgres on machine_3_B, verify
data exists in "DB_slave"<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'># Create a tunnel and Change the
paths to use the tunnel<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>On machine_3_B, port 15432, Create
an ssh tunnel to join network A and B through machine_4_AB<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'># (Here, We are not able to drop
path/store path since the machines cannot see each other without the tunnel)<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>On "DB_master" on
machine_1_A, change the sl_path row for machine_2_A to instead read machine_3_B
<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>On "DB_slave" on
machine_3_B, change the sl_path row for machine_1_A (which it cannot reach) to
point to localhost:15432 (the tunnel to machine_1_A)<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'># Move slon scripts and change slave
name<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Move the slon scripts from
machine_1_A to machine_3_B, and change the slave host to machine_3_B<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Restart slon processes on
machine_3_B<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Thanks for any feedback, it’s
a bit of a kludge, but is the only we've been able to get it to work.<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-autospace:none'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Jeff (sent by co-worker Shahaf)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
</div>
</body>
</html>