ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


Modifying Slony Clusters

by A. Elein Mustain
03/17/2005

Slony logo

Replication clusters have much forethought applied to their creation. However, in the course of systems development, some changes are always necessary sooner or later. This article walks through the steps required to:

These examples will use the replication scheme originally set up in "Introducing Slony."

Figure 1
Figure 1.

Adding a Node

Suppose that in addition to the replica you created for the gb database, you want another replica of the same database for reporting. Here's how to add a replica of the gb table set on a second database in the 8.0 installation. The 7.4 Node 1, database gb, will originate the data set and replicate it directly to Node 2, also database gb, and Node 3, database gb2.

Figure 2
Figure 2.

Before starting, be sure to create gb2 in the 8.0 installation, seeding it with the same empty schema as the other two databases in this cluster. You do not want a dump of the schema of gb as is it now, but rather as it was before you defined the Slony-I cluster.

Next, define Node 3 and ensure there are paths from Node 3 to and from Nodes 1 and 2. From there, enable listening along each path mirroring the expected table set replication. The listening of 2 and 3 via Node 1 reflects this mirroring, rather than having a direct listen path between 2 and 3.

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, two more slonik commands.

#!/bin/sh
slonik << _END_
#
# Define cluster namespace and node connection information
#
cluster name = gbcluster;
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=gb2 host=localhost port=5430 user=postgres';
echo 'Cluster defined, nodes identified';

#
# Initialize the cluster and create the second node
#
store node (id=3, comment='gb2 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=gb2 host=localhost 
     port=5430 user=postgres');
store path (server=3, client=2, conninfo='dbname=gb2 host=localhost 
     port=5430 user=postgres');

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

_END_

Now you are ready to start a new slon process for Node 3, the 8.0 installation with the gb2 database. Because there are two replica databases on the 8.0 installation:

#! /bin/bash
#
# varlena Slony Start Up
# ==============================
slon gb "dbname=gb2 host=localhost port=5430 user=postgres" > slon_gb_3.out 2>&1 &

Once the new slon process is up and running, you can subscribe Table Set 1, originating at Node 1, to Node 3. At this point the log files are invaluable; tail -f the log files to watch for progress and errors. The log files in this case are slon_gb_1.out, slon_gb_2.out, and slon_gb_3.out. If you see any problems, you may have to remove the paths and/or listens, replacing them with corrected ones.

The log file slon_gb_1.out looks like:

CONFIG main: slon version 1.0.5 starting up
CONFIG main: local node id = 1
CONFIG main: loading current cluster configuration
CONFIG storeNode: no_id=2 no_comment='Node 2 dbname=gb host=localhost 
     port=5430 user=postgres'
CONFIG storePath: pa_server=2 pa_client=1 pa_conninfo="dbname=gb host=localhost 
     port=5430 user=postgres" pa_connretry=10
CONFIG storeListen: li_origin=2 li_receiver=1 li_provider=2
CONFIG main: configuration complete - starting threads
CONFIG enableNode: no_id=2
CONFIG storeSet: set_id=1 set_origin=1 set_comment=''
CONFIG storeNode: no_id=3 no_comment='gb2 8.0 5430'
CONFIG enableNode: no_id=3
CONFIG storePath: pa_server=3 pa_client=1 pa_conninfo="dbname=gb2 host=localhost 
     port=5430 user=postgres" pa_connretry=10
CONFIG storeListen: li_origin=3 li_receiver=1 li_provider=3

The one for Node 2, slon_gb_2.out, looks very similar except the paths and listens are appropriate for Node 2.

Now subscribe Node 3 to the table set.

#!/bin/sh
slonik << _END_
#
# Define cluster namespace and node connection information
#
cluster name = gbcluster;
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=gb2 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_

Verification of data is reassuring at this point.

Swapping Providers

If your replication scheme has three or more nodes, you may want to switch providers. This is not the same as failover or promotion of a new master. It simply changes the source of the replication data for a node.

In the example case, Node 1, the origin of set 1, provided the information on set 1 to Node 2. When you added Node 3, you subscribed it to Table Set 1. The Table Set both originated on and was provided by Node 1.

Now the goal is to switch providers so that Node 3 retrieves Table Set 1 information from Node 2, instead of directly from Node 1. Node 1 remains the origin of the Table Set.

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 you subscribed Node 2 to Table Set 1, you also enabled it as a forwarder of Table Set 1.

The listen paths, however, have Node 2 and Node 3 listening to each other via Node 1. One necessary change is to ensure that 2 and 3 listen to each other directly, because Node 2 will now provide the data for Node 1. Once the paths and listens are already set up, simply resubscribe the set, setting the provider to Node 2 instead of Node 1.

Figure 3
Figure 3.

#! /bin/bash
#
# varlena Slony Initialization
# ==============================
slonik << _END_

cluster name =gbcluster;

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=gb2 host=localhost port=5430 user=postgres';
# ==============================

store listen (origin=3, receiver=2, provider=3); 
store listen (origin=2, receiver=3, provider=2);

subscribe set ( id=1, provider=2, receiver=3, forward=yes);

_END_

After running this script, examine the log files to see that Slony stored the listens and updated the subscription.

Switching Masters

In the example, the database called gb (mastered on a PostgreSQL 7.4 installation) replicates to two nodes on 8.0 installation. The decision has been made to move the database forward so that the 8.0 installation is the master. (If you are using Slony-I, be prepared to take advantage of a fast upgrade using master promotion.)

Before you even consider swapping masters, you must have in hand a complete set of steps to 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 after the switchover. 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, I highly recommend 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. Back up your databases before performing any major operations on them. Switching replication masters is a major operation. No excuses. Back up!

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 every node subscribed to that set has confirmed the SYNC event. This ensures that replication data remains available in the system until Slony is sure that no node needs it.

To change the master of a set, you must first ensure that there are listeners for any of the new paths. The example provided listening paths from and to both of the nodes already, so there are no new listener paths 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 you may move the set. Finally, the newly designated replica node, formerly the master node, must subscribe to the set.

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

#!/bin/sh
 
slonik << _EOF_
 
cluster name = gbcluster;
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=gb2 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_

After this script, Slony-I is ready for the change. Restart your application and have it point to the new master for writes. This process should only take seconds.

Failover

The replication example here began as a 7.4 database gb (Node 1), replicating to an 8.0 installation (Node 2). Then you added a third node on the 8.0 database called gb2. Initially the third node replicated directly from Node 1. You switched Node 3's provider to Node 2, then promoted Node 2 to be the master. Now you have an 8.0 master at Node 2 and two replicas, one 7.4 and one in the same installation as gb2. Consider the second replica, gb2, as if it were on another machine or installation.

Suppose the master database failed or lost power and had to be taken offline. (Remember Node 3, for the sake of this discussion, is not on this machine, and so this hypothetical situation treats it as if it were live when the machine died.) What to do next is a human decision. You must prepare for this failover scenario. In this case, you have decided to failover to Node 1 in case of a failure on Node 2.

The applications communicating with the database will also start to fail, so you must take them offline and restart them, pointing at the new master quickly after the master switch takes effect.

The failover procedure for Slony is a combination of a provider change and a master change, both of which "Building and Configuring Slony" covered. Previously, you added Node 3 and had Node 1 provide for it. Then you changed Node 3's provider to Node 2, and finally, promoted Node 2 to master. In the failover case, Slony must do the reverse using the failover command:

Then you can safely remove Node 2 for repair.

#!/bin/sh
 
 slonik <<_EOF_
 
 cluster name = gbcluster;
 node 1 admin conninfo = 'dbname=gb host=localhost user=postgres port=5434';
 node 2 admin conninfo = 'dbname=gb host=localhost user=postgres port=5430';
 node 3 admin conninfo = 'dbname=gb2 host=localhost user=postgres port=5430';
 
 failover (id=2, backup node = 1);
 
 _EOF_

At this point, the slon process for Node 2 should be dead. When you are ready to put Node 2 back into place, add it as a fresh empty database into the replication scheme. (See "Add Node," above.) When it catches up, then you can switch masters so that Node 2 can again be the master of the cluster. (See "Switching Masters.")

While resolving some of the problems that I ran into, I found that it was easiest and clearest for me to drop Node 2. drop node "erases" the node from the entire replication cluster. This is different than uninstall node, which removes the Slony-I schema from a specific database instance. Both tools are useful, but don't confuse them.

Related Reading

Practical PostgreSQL
By John C. Worsley, Joshua D. Drake

The other issue I ran into was that while quickly cutting and pasting, I had mismatched paths, listens, and connection information. It is very worthwhile to check each of these commands by hand to verify that the commands are exactly what you meant. Also, don't forget that the connection information for store path pertains to the server node. Undoing bad paths and listen connections is a delicate operation and it is very tempting to throw the whole thing away and start from scratch.

Schema Changes

Changing the schema of a database being replicated is not simple. The schema changes must occur at the exact transactional point in time. Direct schema changes could lead to serious corruption in Slony-I due to the handling and disabling of triggers, rules, and constraints in the replica.

Slony-I provides a way to execute SQL statements via the Event mechanism. This provides the transactional integrity necessary for schema changes, as well as the trigger, rule, and constraint changes required for the replicas. You must initiate Events on the master node.

To add a "dummy" column to the artfile table, issue an Event to the master Node 1 pointing to the ALTER TABLE script file so as to synchronize it between databases. The EXECUTE SCRIPT command in Slonik will do this. Remember, Slony must be able to find your change script.

#!/bin/sh

#
# Create a new column in a table in the replicated set 
#
echo "Creating new column in the artfile table"
slonik <<_EOF_
cluster name = gbcluster;
node 1 admin conninfo = 'dbname=gb host=localhost user=postgres port=5434';
node 2 admin conninfo = 'dbname=gb host=localhost user=postgres port=5430';
node 3 admin conninfo = 'dbname=gb2 host=localhost user=postgres port=5430';
    
execute script ( SET ID = 1, FILENAME = 'changes20050219.sql', EVENT NODE = 1);
_EOF_

Once this change propagates, you can do an update to populate the new column and verify it is being updated on all replicas.

References

A. Elein Mustain has more than 15 years of experience working with databases, 10 of those working exclusively with object relational database systems.


Return to ONLamp.com.

Copyright © 2009 O'Reilly Media, Inc.