2. Replicating Your First Database

2.3.1. Using slonik command directly

The traditional approach to administering slony is to craft slonik commands directly. An example of this given here.

The script to create the initial configuration for the simple master-slave setup of our pgbench database looks like this:

#!/bin/sh

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';

	#--
	# init the first node.  Its id MUST be 1.  This creates the schema
	# _$CLUSTERNAME containing all replication system specific database
	# objects.

	#--
	init cluster ( id=1, comment = 'Master Node');
 
	#--
	# Slony-I organizes tables into sets.  The smallest unit a node can
	# subscribe is a set.  The following commands create one set containing
	# all 4 pgbench tables.  The master or origin of the set is node 1.
	#--
	create set (id=1, origin=1, comment='All pgbench tables');
	set add table (set id=1, origin=1, id=1, fully qualified name = 'public.accounts', comment='accounts table');
	set add table (set id=1, origin=1, id=2, fully qualified name = 'public.branches', comment='branches table');
	set add table (set id=1, origin=1, id=3, fully qualified name = 'public.tellers', comment='tellers table');
	set add table (set id=1, origin=1, id=4, fully qualified name = 'public.history', comment='history table');

	#--
	# Create the second node (the slave) tell the 2 nodes how to connect to
	# each other and how they should listen for events.
	#--

	store node (id=2, comment = 'Slave node', event node=1);
	store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER');
	store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER');
_EOF_

Is the pgbench still running? If not, then start it again.

At this point we have 2 databases that are fully prepared. One is the master database in which pgbench is busy accessing and changing rows. It's now time to start the replication daemons.

On $MASTERHOST the command to start the replication engine is

slon $CLUSTERNAME "dbname=$MASTERDBNAME user=$REPLICATIONUSER host=$MASTERHOST"

Likewise we start the replication system on node 2 (the slave)

slon $CLUSTERNAME "dbname=$SLAVEDBNAME user=$REPLICATIONUSER host=$SLAVEHOST"

Even though we have the slon running on both the master and slave, and they are both spitting out diagnostics and other messages, we aren't replicating any data yet. The notices you are seeing is the synchronization of cluster configurations between the 2 slon processes.

To start replicating the 4 pgbench tables (set 1) from the master (node id 1) the the slave (node id 2), execute the following script.

#!/bin/sh
slonik <<_EOF_
	 # ----
	 # This defines which namespace the replication system uses
	 # ----
	 cluster name = $CLUSTERNAME;

	 # ----
	 # Admin conninfo's are used by the slonik program to connect
	 # to the node databases.  So these are the PQconnectdb arguments
	 # that connect from the administrators workstation (where
	 # slonik is executed).
	 # ----
	 node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER';
	 node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER';

	 # ----
	 # Node 2 subscribes set 1
	 # ----
	 subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
_EOF_

Any second now, the replication daemon on $SLAVEHOST will start to copy the current content of all 4 replicated tables. While doing so, of course, the pgbench application will continue to modify the database. When the copy process is finished, the replication daemon on $SLAVEHOST will start to catch up by applying the accumulated replication log. It will do this in little steps, initially doing about 10 seconds worth of application work at a time. Depending on the performance of the two systems involved, the sizing of the two databases, the actual transaction load and how well the two databases are tuned and maintained, this catchup process may be a matter of minutes, hours, or eons.

If you encounter problems getting this working, check over the logs for the slon processes, as error messages are likely to be suggestive of the nature of the problem. The tool Section 5.1 is also useful for diagnosing problems with nearly-functioning replication clusters.

You have now successfully set up your first basic master/slave replication system, and the 2 databases should, once the slave has caught up, contain identical data. That's the theory, at least. In practice, it's good to build confidence by verifying that the datasets are in fact the same.

The following script will create ordered dumps of the 2 databases and compare them. Make sure that pgbench has completed, so that there are no new updates hitting the origin node, and that your slon sessions have caught up.

#!/bin/sh
echo -n "**** comparing sample1 ... "
psql -U $REPLICATIONUSER -h $MASTERHOST $MASTERDBNAME >dump.tmp.1.$$ <<_EOF_
	 select 'accounts:'::text, aid, bid, abalance, filler
		  from accounts order by aid;
	 select 'branches:'::text, bid, bbalance, filler
		  from branches order by bid;
	 select 'tellers:'::text, tid, bid, tbalance, filler
		  from tellers order by tid;
	 select 'history:'::text, tid, bid, aid, delta, mtime, filler,
		  "_Slony-I_${CLUSTERNAME}_rowID"
		  from history order by "_Slony-I_${CLUSTERNAME}_rowID";
_EOF_
psql -U $REPLICATIONUSER -h $SLAVEHOST $SLAVEDBNAME >dump.tmp.2.$$ <<_EOF_
	 select 'accounts:'::text, aid, bid, abalance, filler
		  from accounts order by aid;
	 select 'branches:'::text, bid, bbalance, filler
		  from branches order by bid;
	 select 'tellers:'::text, tid, bid, tbalance, filler
		  from tellers order by tid;
	 select 'history:'::text, tid, bid, aid, delta, mtime, filler,
		  "_Slony-I_${CLUSTERNAME}_rowID"
		  from history order by "_Slony-I_${CLUSTERNAME}_rowID";
_EOF_

if diff dump.tmp.1.$$ dump.tmp.2.$$ >$CLUSTERNAME.diff ; then
	 echo "success - databases are equal."
	 rm dump.tmp.?.$$
	 rm $CLUSTERNAME.diff
else
	 echo "FAILED - see $CLUSTERNAME.diff for database differences"
fi

Note that there is somewhat more sophisticated documentation of the process in the Slony-I source code tree in a file called slony-I-basic-mstr-slv.txt.

If this script returns FAILED please contact the developers at http://slony.info/. Be sure to be prepared with useful diagnostic information including the logs generated by slon processes and the output of Section 5.1.

2.3.2. Using the altperl scripts

Using the Section 21.1 scripts is an alternative way to get started; it allows you to avoid writing slonik scripts, at least for some of the simple ways of configuring Slony-I. The slonik_build_env script will generate output providing details you need to build a slon_tools.conf, which is required by these scripts. An example slon_tools.conf is provided in the distribution to get you started. The altperl scripts all reference this central configuration file centralize cluster configuration information. Once slon_tools.conf has been created, you can proceed as follows:

# Initialize cluster:
$ slonik_init_cluster  | slonik 

# Start slon  (here 1 and 2 are node numbers)
$ slon_start 1    
$ slon_start 2

# Create Sets (here 1 is a set number)
$ slonik_create_set 1 | slonik             

# subscribe set to second node (1= set ID, 2= node ID)
$ slonik_subscribe_set 1 2 | slonik

You have now replicated your first database. You can skip the following section of documentation if you'd like, which documents more of a "bare-metal" approach.