In this article we will walk through the steps required to add a node to the scheme, switch data providers of a table set and then promote a replication to master all using the replication scheme originally set up in Issue #85.

Adding a Node

First we define the new Node 3, then we ensure there are paths from Node 3 to and from Nodes 1 and 2. Next we want to enable listening along each path.

(This is a really good time to remember that the connection information in the store path command pertains to the server node. This is also a pretty good time to look up drop path and drop listen another two of the slonik commands.)

#!/bin/sh
slonik << _END_
#
# Define cluster namespace and node connection information
#
cluster name = gb;
node 1 admin conninfo = 'dbname=gb host=localhost port=5434 user=postgres';
node 2 admin conninfo = 'dbname=gb host=localhost port=5430 user=postgres';
node 3 admin conninfo = 'dbname=gb_replica host=localhost port=5430 user=postgres';
echo 'Cluster defined, nodes identified';

#
# Initialize the cluster and create the second node
#
store node (id=3, comment='gb_replica 8.0 5430');

#
# create paths
#
store path (server=1, client=3, conninfo='dbname=gb host=localhost port=5434 user=postgres');
store path (server=2, client=3, conninfo='dbname=gb host=localhost port=5430 user=postgres');
store path (server=3, client=1, conninfo='dbname=gb_replica host=localhost port=5430 user=postgres');
store path (server=3, client=2, conninfo='dbname=gb_replica host=localhost port=5430 user=postgres');

#
# Enable listening along each path
#
store listen (origin=1, client=3, provider=1);
store listen (origin=2, client=3, provider=2);
store listen (origin=3, client=1, profider=3);
store listen (origin=3, client=2, profider=3);

_END_

#!/bin/sh

#
# Start Slony for each node
# 
slon gb_replica "dbname=gb_replica user=postgres port=5430 host=localhost" > slon_gbREP_80.out 2>&1 &
sleep 20;

slonik << _END_
#
# Define cluster namespace and node connection information
#
cluster name = cookie;
node 1 admin conninfo = 'dbname=gb host=localhost port=5434 user=postgres';
node 2 admin conninfo = 'dbname=gb host=localhost port=5430 user=postgres';
node 3 admin conninfo = 'dbname=gb_replica host=localhost port=5430 user=postgres';

subscribe set (id=1, provider=1, receiver=3, forward=yes);
echo 'set 1 of gb tables subscribed by node 3';

_END_

Swapping Providers

Node 3 could have either Node 1 or Node 2 as a provider of that set information. Node 2 is available as a provider because when we subscribed Node 2 to Table Set 1, we also enabled it as a forwarder of Table Set 1. We also provided Listen paths from all Nodes to all Nodes.

Suppose Node 3 were subscribed to Table Set 1 via Node 1 and we wanted to change the provider of Node 3's informatin to Node 2. Then since the Paths and Listens are already set up, we simply resubscribe the set, setting the provider to Node 2 instead of Node 1.

slonik << _END_
#
# Define cluster namespace and node connection information
#
cluster name = cookie;
node 1 admin conninfo = 'dbname=gb host=localhost port=5434 user=postgres';
node 2 admin conninfo = 'dbname=gb host=localhost port=5430 user=postgres';
node 3 admin conninfo = 'dbname=gb_replica host=localhost port=5430 user=postgres';

subscribe set (id=1, provider=2, receiver=3, forward=yes);
echo 'set 1 of gb tables subscribed by node 3';
_END_

Swapping Masters

Before you even consider swapping masters, you must have in hand a complete set of steps you must follow to switch your applications accessing the 7.4 installation to the 8.0 installation. These steps are application dependent and so you are on your own. However, the steps probably consist of stopping each application, changing the connection information (ideally in one place) and bringing the application back up. It is imperative for a smooth and fast switchover that you have the application switch information at hand. Write them down and save the instructions in your source code control mechanism. You will never be sorry you did that.

One more cautionary note, it is highly recommended that you test your application on a copy of the 8.0 database. This copy should not be the replica if your application writes to the database. It should be another copy. Remember that replicas are read only databases. Oh, yes, one more thing. Backup your databases before performing any major operations on them. Switching replication masters is considered a major operation. No Excuses. Backup!

Everything that happens in a Slony-I replication happens because of an Event. One of the important Events is a SYNC Event. Every subscribing node replicates data up to a SYNC event and then commits the changes to the database. All nodes capable of forwarding subscriptions also log those changes until the SYNC event has been confirmed by every node subscribed to that set. This ensures that replication data remains available in the system until Slony is sure it is not needed for any node.

To change the master of a set, you must first ensure that there are listeners for any of the new paths. In our example, we provided listening paths from and to both of the nodes already so no new listener paths are required. Before swapping a master on a subscribed set, the set must be locked to ensure that no updates occur during the swap over part. Then the set may be moved. Finally, the set must be subscribed to by the newly designated replica node that was formerly the master node.

Before you run the script, ensure that write access to your master database is OFF.

#!/bin/sh

slonik << _EOF_

cluster name = gb;
node 1 admin conninfo = 'dbname=gb host=localhost port=5434 user=postgres';
node 2 admin conninfo = 'dbname=gb host=localhost port=5430 user=postgres';
node 3 admin conninfo = 'dbname=gb_replica host=localhost port=5430 user=postgres';

# add listener paths if required

#
# lock and move set
#
lock set (id=1, origin=1);
move set (id=1, old origin=1, new origin=2);

# subscribe set if required
subscribe set (id=1, provider=2, receiver=1, forward=yes);

_END_

Slony-I Schema

References

• The Slony-I Project documentation on GBorg
• Slonik Commands
  
• Jan Wieck's Original Slony-I Talk and Scripts July 2004 in Portland, OR sponsored by Afilias Global Registry Services
• Information from IRC #slony on freenode.net
• Mailing List: Slony1-general@gborg.postgresql.org

The scripts for this article are also available individually.

Contributors: Jan Wieck JanWieck at Yahoo.com, elein at varlena.com