The task is to set up slony cluster. This is what I did, step by step, using the perltools utilities available. I'm sure other people use these tools slightly differently but this is the way I've found most useful. There were setbacks with file permissions--pay attention to who you are postgres, yourself or root.

1. Define the cluster

a.This is a paper task. Sit down. Draw a picture of databases in installations on machines. In this task, we want to replicate from machine aaa01 to machine bbb01. The master database name is fred. We will name the replica on fred2.
b. Next, find the tables you want to replicate.

Use \dt to get a list of tables and \ds to get a list of sequences. Output these to a file. Edit the file so that the table list looks like this:

		'tableone',
		'tabletwo',
		...
	

The sequences should have the same format.

Now review the table list for garbage tables. (You know you have garbage tables: copies, long term temp tables, etc.) Eliminate the garbage tables from the list.. Identify any tables that do not have a primary key. (Bad Designer. You should not have any tables without primary keys.) Separate them out into their own lists. Review the sequences. Eliminate any associated with the garbage tables you are not replicating.

Save these lists. We will use them later.

2. Install Slony1-1.1.0.rc1 on both aaa01 and bbb01.

Anxious to have the best version available, I downloaded the release candidate for 1.1 from gborg.postgresql.org. This version not production at the time of this writing, but there were some changes between slony1-1.1 beta5 and slony1-1.1 rc1 that I wanted to have. These changes include the fix for the problem mentioned by Jan Wieck regarding subtransaction issues.

a. By my convention I down loaded the source to /usr/local/slony/slony1-1.1.0.rc1/
b. ./configure --with-perltools --with-pgbindir=$PG/bin --with-pglibdir=$PG/lib --with-pgincludedir=$PG/include

Because I run several versions of postgresql on the same machine, I used explicit configuration directives with regards to the postgresql source for slony.

c. make
d. sudo make install

Make install needs to run as root in order to install in the appropriate directories.

3. Prepare the empty database fred2 for replication

a. On bbb01, createdb fred2.
b. Perform a schema dump of fred from aaa01.

		pg_dump -h aaa01 -U postgres -s fred > fred.sql
	

c. Load the schema from fred onto fred2.

		psql -U postgres fred2  < fred.sql
	

4. Take a break. Breathe. Take inventory. Slony is on both machines. Databases are prepared. The list of tables and sequences to replicate are prepared.

This is a good time to remember that no DDL will be allowed on the master database without a special procedure. Are you sure your schema is fairly stable?

5. In the bin directory where you installed slony1, there are a set of scripts to help you build slonik scripts.

These scripts will help you generate slonik scripts to do the tasks described by their names. Before you can use these scripts, however, you must edit and place a slon_tools.conf file.

a. Copy file etc/slon_tools.conf-sample to slon_tools.conf. The etc subdirectory should have been created where you installed slony, e.g. /usr/local/etc/
b. Edit the file and fill out:
• the cluster name
• the connection information for each node
• the primary keyed table list
• the non-primary keyed table list
• the sequence list
c. Review. Review again. Read the comments and review again, just in case. What you are filling out should correspond to the layout the design you decided upon in 1. It should make sense to you.

6. Build a script toolkit.

a. Create a toolkit directory in a place that makes sense to your source code control for the database. We will create a set of scripts that can be used as necessary and leave them there. These scripts will be specific to your replication cluster.
b. Create the slonik toolkit scripts. Point to the edited slon_tools.conf file. I have this as a shell script.

		# Create slonik scripts based on slon_tools.conf in SLONYCONF
		export SLONYCONF=/usr/local/etc/slon_tools.conf

		slonik_init_cluster  --config $SLONYCONF > init_cluster.slnk
		slonik_create_set  --config $SLONYCONF 1 > create_set.slnk
		# subscribe set 1 to node 2 (fred2)
		slonik_subscribe_set  --config $SLONYCONF 1 2 > subscribe_set.slnk

		# just in case we want to add a third node
		slonik_store_node  --config $SLONYCONF 3 > store_node3.slnk
		# just in case we want to execute a DDL script
		slonik_execute_script  --config $SLONYCONF -C /tmp/xxx 1 > execute_script_xxx.slnk
		
		# handy to have in advance in case of errors.
		# unsubscribe set 1 on node 2
		slonik_unsubscribe_set  --config $SLONYCONF 1 2 > unsubscribe_set_1_2.slnk
		slonik_drop_node  --config $SLONYCONF 1 > drop_node1.slnk
		slonik_drop_node  --config $SLONYCONF 2 > drop_node2.slnk
		slonik_drop_node  --config $SLONYCONF 3 > drop_node3.slnk
		slonik_drop_set  --config $SLONYCONF 1 > drop_set.slnk
		slonik_uninstall_nodes  --config $SLONYCONF > uninstall_nodes.slnk
		slonik_update_nodes  --config $SLONYCONF > update_nodes.slnk
	

c. Familiarize yourself with these scripts and the actions they take. If the slon_tools.conf file is changed, you may want to regenerate one or all, depending on the state of your cluster.

Script execution is: slonik < scriptname. At this time, also ensure that your slony logfile has been created and is writeable by postgres. It is usually expected to be in /var/log/slony.

a. slonik < init_cluster.slnk Run init_cluster.slnk script

From here on out your cluster is initialized. You should not have to do this again. You will be able to add and remove nodes and perform administration tasks without re-initializing your cluster. However, if something went very wrong, use uninstall_nodes.slnk to remove the cluster definitions and start over.

b. Start slon processes on aaa01 and bbb02.

On the aaa01 machine, invoke

		slon_start --config /usr/local/etc/slon_tools.conf 1
		

and on the bbb01 machine, invoke

		slon_start --config /usr/local/etc/slon_tools.conf 2
		

1 and 2 refer to the node numbers. If you do not want the watchdog program invoked then use --nowatchdog. (Side note: on some platforms there may be a problem with using the watchdog program. The error recieved is about a '-' and the suggestion is to review the FAQ for /procps. This is an unconfirmed bug at the time of this writing.)

c. slonik < create_set.slnk Create the table set

This creates the table set that you listed in your slon_tools.conf file.

d. slonik < subscribe_set.slnk Subscribes node 2 (fred2@bbb01) to the newly created table set.
e. Check your logs in /var/log/slony/slony1/node1 on the aaa01 machine and node2 on the bbb01 machine. Check the tables on fred2. They should be filling up at this time. Maybe they are already caught up if your database is small.

This takes care of starting up a simple two node slony cluster. More complex cluster configurations should be relatively straightforward if you use the same or similiar methodologies.

Contributors: elein at varlena.com