My title might be slight contradictory, as per Slony-I, swapping can be achieved among nodes, if nodes are connected to each other by anyway as PROVIDER or RECEIVER and replicating. If you see in my diagram, “DR-Prod” is nowhere related to Slony replicating nodes, still swapping is possible(with some extra care). Here are some valueable inputs from Steve & Jan. Thanks.


When we need such kind of swapping, if you are planning to move “PROD” from one data center to another or from existing disk volumn to another (many reasons). Most of the features in PostgreSQL 9.1 WAL-Level replication suits such kind of shuffling, but what if your Prod(master) only to move without disturbing the existing Partial replication(Slony) slaves. Overall concept is to reuse the replicated Slony schema (_myrep) on  “DR-Prod” side and then point to Slony Slave when its promoted as Master. 

Note: My steps are only for Asynchronous replication and controlled Switchover. It won’t function properly if there is unplanned failure, because ‘slave’ node might be further ahead via slony than your ‘DR-Prod’ node via asynchronous streaming replication.

Let me give a simple demo on local setup similar to diagram. Assume “Prod” and “Slony Slave” as master & slave databases running on 5432. Setup streaming replication as “DR-Prod” on port 5433 for 5432.

1. Setup master/slave slony replication on localhost on port 5432 and replicating only one table as shown here.

2. Setup streaming replication for 5432 cluster and run it on 5433. To setup streaming replication follow the PostgreSQL wiki.

3. Important steps :

• Stop Slony daemons of master/slave on port 5432.

• Promote “DR-Prod” as master, i.e., on 5433.

• Change the pointers on both the ends i.e., DR-Prod (which is now acts as PROD) and Slony Slave of _myrep.sl_path table using storepath() function. As shown below:

  On 5433, change the pointer to Slave database running on 5432 as shown below. master=# select _myrep.storepath(1,2,‘host=127.0.0.1 dbname=master user=postgres port=5433’,10); storepath

  5000000093 (1 row) master=# select _myrep.storepath(2,1,‘host=127.0.0.1 dbname=slave user=postgres port=5432’,10); storepath

  5000000094 (1 row)
  On 5432, change the pointer to Master database running on 5433 as shown below. slave=# select _myrep.storepath(1,2,‘host=127.0.0.1 dbname=master user=postgres port=5433’,10); storepath

  5000000085 (1 row)

4. Now start the slon daemon on 5433 for Master database and on 5432 for Slave Database.

5. Hereafter any inserts on 5433 Master would replicate to Slave database on 5432.

Cheers
Raghav