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
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
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
2. Setup streaming replication for 5432 cluster and run it on 5433.
To setup streaming replication follow the PostgreSQL
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);
master=# select _myrep.storepath(2,1,‘host=127.0.0.1 dbname=slave user=postgres port=5432’,10);
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);
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.