Setting Up Slony 1.1
PerlTools for Slony 1.1
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:
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
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/
./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.
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
- 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.
7. Using the toolkit scripts to get started.
- 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
- 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
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.
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.)
slonik < create_set.slnk Create the table set
This creates the table set that you listed in your slon_tools.conf file.
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
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.
elein at varlena.com