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.  

 

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:

 

 

# Initial normal subscription/replication

Create database "DB_master" on network A, machine_1_A, and populate with
schema and data

Create database "DB_slave"on network A, machine_2_A, with schema only

Subscribe machine 2 "DB_slave" to machine 1 "DB_master"

Start slon processes on machine 1 to replicate initial data set

Add new data to "DB_master" and verify changes replicated to "DB_slave"

 

# Now, attempt to 'relocate' the initial subscription on the slave to
another machine

Stop slon  on machine_1_A

Stop postgres on machine_2_A

Rsync postgres data directory on machine_2_A to machine_3_B (on network
B)

Start postgres on machine_3_B, verify data exists in "DB_slave"

 

# Create a tunnel and Change the paths to use the tunnel

On machine_3_B, port 15432, Create an ssh tunnel to join network A and B
through machine_4_AB

# (Here, We are not able to drop path/store path since the machines
cannot see each other without the tunnel)

On "DB_master" on machine_1_A, change the sl_path row for machine_2_A to
instead read machine_3_B 

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)

 

# Move slon scripts and change slave name

Move the slon scripts from machine_1_A to machine_3_B, and change the
slave host to machine_3_B

Restart slon processes on machine_3_B

 

 

Thanks for any feedback, it's a bit of a kludge, but is the only we've
been able to get it to work.

 

Jeff (sent by co-worker Shahaf)

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20080912/6bc7a417/attachment.htm