3.1.1. Adding a Table To Replication

After your Slony-I cluster is setup and nodes are subscribed to your replication set you can still add more tables to replication. To do this you must first create the table on each node. You can do this using psql (on each node) or using the SLONIK EXECUTE SCRIPT command. Next, you should create a new replication set and add the table (or sequence) to the new replication set. Then you subscribe your subscribers to the new replictation set. Once the subscription process is finished you can merge your new replication set into the old one.

slonik <<_EOF_
	#--
	# define the namespace the replication system uses in our example it is
	# slony_example
	#--
	cluster name = $CLUSTERNAME;

	#--
	# admin conninfo's are used by slonik to connect to the nodes one for each
	# node on each side of the cluster, the syntax is that of PQconnectdb in
	# the C-API
	# --
	node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER';
	node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER';
create set (id=2, origin=1, comment='a second replication set');
set add table (set id=2, origin=1, id=5, fully qualified name = 'public.newtable', comment='some new table');
subscribe set(id=2, provider=1,receiver=2);
wait for event(origin=1, confirmed=all, wait on=1);
merge set(id=1, add id=2,origin=1);

3.1.2. How To Add Columns To a Replicated Table

There are two approaches you can use for adding (or renaming) columns to an existing replicated table.

The first approach involves you using the SLONIK EXECUTE SCRIPT command. With this approach you would

1. Create a SQL script with your ALTER table statements

2. Stop any application updates to the table you are changing (ie have an outage)

3. Use the slonik SLONIK EXECUTE SCRIPT command to run your script

Your table should now be updated on all databases in the cluster.

Alternatively, if you have the altperl scripts installed, you may use slonik_execute_script for this purpose:

slonik_execute_script [options] set# full_path_to_sql_script_file

See slonik_execute_script -h for further options; note that this uses SLONIK EXECUTE SCRIPT underneath.

There are a number of "sharp edges" to note...

• You absolutely must not include transaction control commands, particularly BEGIN and COMMIT, inside these DDL scripts. Slony-I wraps DDL scripts with a BEGIN/COMMIT pair; adding extra transaction control will mean that parts of the DDL will commit outside the control of Slony-I

• Version 2.0 of Slony-I does not explicitly lock tables when performing an execute script. To avoid some race-conditions exposed by MVCC it is important that no other transactions are altering the tables being used by the ddl script while it is running

3.1.3. How to remove replication for a node

You will want to remove the various Slony-I components connected to the database(s).

We will just consider, for now, doing this to one node. If you have multiple nodes, you will have to repeat this as many times as necessary.

Components to be Removed:

• Log Triggers / Update Denial Triggers

• The "cluster" schema containing Slony-I tables indicating the state of the node as well as various stored functions

• slon process that manages the node

• Optionally, the SQL and pl/pgsql scripts and Slony-I binaries that are part of the PostgreSQL build. (Of course, this would make it challenging to restart replication; it is unlikely that you truly need to do this...)

The second approach involves using psql to alter the table directly on each database in the cluster.

1. Stop any application updates to the table you are changing(ie have on outage)

2. Connect to each database in the cluster (in turn) and make the required changes to the table

3.1.4. Adding a Replication Node

To add a node to the replication cluster you should

1. Create a database for the node and install your application schema in it.

   createdb -h $NEWSLAVE_HOST $SLAVEDB
   pg_dump -h $MASTER_HOST -s $MASTERDB | psql -h $NEWSLAVE_HOST $SLAVEDB

2. Create the node with the SLONIK STORE NODE command

   node 5 admin conninfo='host=slavehost dbname=slavedb user=slony password=slony';
   clustername=testcluster;
   
   store node(id=5,comment='some slave node',event node=1);
   wait for event(origin=1, confirmed=all, wait on=1);

3. Create paths between the new node and its provider node with the SLONIK STORE PATH command.

   node 5 admin conninfo='host=slavehost dbname=slavedb user=slony password=slony';
   clustername=testcluster;
   node 1 admin conninfo='host=masterhost dbname=masterdb user=slony password=slony';
   clustername=testcluster;
   store path(server=1,client=5,conninfo='host=masterhost,dbname=masterdb,user=slony,password=slony');
   store path(server=5,client=1,conninfo='host=slavehost,dbname=masterdb,user=slony,password=slony');
   wait for event(origin=1,confirmed=all,wait on=1);
   wait for event(origin=5,confirmed=all,wait on=1);

4. Subscribe the new node to the relevant replication sets

   node 5 admin conninfo='host=slavehost dbname=slavedb user=slony password=slony';
   clustername=testcluster;
   node 1 admin conninfo='host=masterhost dbname=slavedb user=slony password=slony';
   clustername=testcluster;
   subscribe set(id=1,provider=1, receiver=5,forward=yes);
   wait for event(origin=1, confirmed=all, wait on=1);

3.1.5. Adding a Cascaded Replica

In a standard Slony-I configuration all slaves(replicas) communicate directly with the master (origin). Sometimes it is more desirable to have some of your slaves(replicas) feed off of another replica. This is called a cascaded replica and is supported by Slony-I For example you might have a Slony-I cluster with 1 replication set (set id=1) and three nodes. The master (origin) for set 1 (node id=1), a node in a different data center that reads directly from the master (node id=2) and a third node in the same data center as the slave (node id=3). To the subscription sets in this configuration you need to make sure that paths exist between nodes 2 and nodes 3. Then to perform the subscription you could use the following slonik commands.

#Setup path between node 1==>2
store path(server=1,client=2,conninfo='host=masterhost,dbname=masterdb,user=slony,password=slony');
store path(server=2,client=1,conninfo='host=slave2host,dbname=slave2db,user=slony,password=slony');

#Setup path between node 2==>3
store path(server=3,client=2,conninfo='host=slave3host,dbname=slave3db,user=slony,password=slony');
store path(server=2,client=3,conninfo='host=slave2host,dbname=slave2db,user=slony,password=slony');

subscribe set(set id=1, provider=1, receiver=2,forward=yes);
wait for event(origin=1, confirmed=all, wait on=1);
subscribe set (set id=1,provider=2, receiver=3,forward=yes);
wait for event(origin=1,confirmed=all,wait on=1);

In the above example we define paths from 1==>2 and from 2==>3 but do not define a path between nodes 1===>3. If a path between node 1 and 3 was defined the data data for set 1 would still flow through node 2 because node 2 is the origin for set 1. However if node 2 were to fail nodes 1 and 3 would be unable to talk to each other unless a path between nodes 1 and nodes 3 had been defined.

3.1.6. How To Remove Replication For a Node

You will want to remove the various Slony-I components connected to the database(s).

Removing slony from a node involves deleting the slony schema (tables, functions and triggers) from the node in question and telling the remaining nodes that the deleted node no longer exists. The slony SLONIK DROP NODE command does both of these items while hte SLONIK UNINSTALL NODE command only removes the slony schema from the node.

In the case of a failed node (where you used SLONIK FAILOVER to switch to another node), you may need to use SLONIK UNINSTALL NODE to drop out the triggers and schema and functions.

3.1.7. Changing a Nodes Provider

For instance, you might want subscriber node 3 to draw data from node 1, when it is presently drawing data from node 2.

The SLONIK SUBSCRIBE SET command can be used to do this. For existing subscriptions it can revise the subscription information.

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

3.1.8. Moving The Master From One Node To Another

Sometimes you will want to promote one of your replicas (slaves) to become the master and at the same time turn the former master into a slave. Slony-I supports this with the SLONIK MOVE SET command.

You must first pick a node that is connected to the former origin (otherwise it is not straightforward to reverse connections in the move to keep everything connected).

Second, you must run a slonik script with the command SLONIK LOCK SET to lock the set on the origin node. Note that at this point you have an application outage under way, as what this does is to put triggers on the origin that rejects updates.

Now, submit the slonik SLONIK MOVE SET request. It's perfectly reasonable to submit both requests in the same slonik script. Now, the origin gets switched over to the new origin node. If the new node is a few events behind, it may take a little while for this to take place.

LOCK SET(id=1,ORIGIN=1);
MOVE SET(ID=1,OLD ORIGIN=1, NEW ORIGIN=3);
SYNC(ID=3);
WAIT FOR(ORIGIN=1, CONFIRMED=ALL,WAIT ON=1);

It is important to stop all non-Slony application activity against all tables in the replication set before locking the sets. The move set procedure involves obtaining a lock on every table in the replication set. Other activities on these tables can result in a deadlock.