Building and Configuring Slony

Figure 1. Slony mascot

Editor's note: in Introducing Slony, A. Elein Mustain explained the goals of Slony, the replication project for PostgreSQL. This follow-up explains how to install, configure, and start using it.

Building

I am pleased to report that the basic instructions for the download, build, and install of Slony-I release 1.0.5 were perfect.

Slony-I is fairly version independent, but you still need to build it for each PostgreSQL version (7.3 or later) and installation on each machine participating in the replication. The same technique applies when the installations live on different machines.

On one machine, I run several versions of PostgreSQL, each built from source. My plan is to replicate between my 7.4 installation and my 8.0 installation, so I configured and built Slony-I against each of those source trees. That took less than a minute for both.

Repeat these steps for each source tree and installation:

• ./configure -with-pgsourcetree=/local/src/postgresql-version
• make all
• sudo make install

Related Reading Practical PostgreSQL By John C. Worsley, Joshua D. Drake Table of Contents Index Sample Chapter

Setting Up Slony-I

This step-by-step reading of instructions will be applied to replicate a small database named gb. The plan is to replicate from a PostgreSQL 7.4 installation to a PostgreSQL 8.0 installation, making it possible to upgrade the database.

Slonik is the command-line interface that defines the replication system. There will be Slonik scripts to create, update, and change the replication cluster for gb. There are also tools under development to simplify the creation of replication systems with Slony-I; however, this description will explore the underlying Slonik requirements. It is important to learn the basic Slonik commands.

About the database

gb is a simple eight-table database containing issues and articles for my General Bits web site. The database is normalized, and all tables have natural primary keys.

There are several prerequisites:

• Each installation that will participate in replication must have Slony-I built and installed. The Slony-I Project on GBorg gives instructions for building and installing Slony-I. My experience with building Slony-I from source against PostgreSQL 7.4 and 8.0Beta3 was very good. Following the instructions provided a clean and fast builds.

• You need a set of master database tables to replicate and at least one other installation containing the same schema objects. The other installation will be the replica. To achieve this initially, I dumped and restored the schema for the master database on 7.4 into the 8.0 installation:

  pg_dump -p 5434 -C -s gb | pgsql -p 5430

  As you can see, these installations are on the same host and have different port numbers.

• The real-time clocks of the servers hosting the nodes must be in sync. I recommend using NTP.
• The pg_hba.conf files on each installation must allow each machine to contact the other.

Slonik

Slonik is a command-line interface for Slony-I. It can connect to the various databases involved in the replication scheme to perform specific actions. It is an independent helper of Slony-I and of PostgreSQL.

The first commands for most Slonik scripts constitute the identity of a group of databases and servers and the connection parameters for accessing each database in the group. Each database and Slony-I connection is a numbered node. The numbers are simply identifiers. The next parameter is the action you wish to process.

Slonik commands work well when they are embedded in shell scripts, as in this example. (The next section covers the commands to identify the cluster and node connection information.)

#!/bin/bash
slonik << _END_
cluster name = gb_cluster;
node 1 admin connifo = 'dbname=db host=localhost port=5432 user=postgres';
node 2 admin connifo = 'dbname=db host=localhost port=5430 user=postgres';

   ...additional nodes...
   ...slonik commands...
   _END_

Both Slonik commands and Slony-I have full sets of commands.

Node networks

A node is the combination of a database in an installation and one slon process "belonging to" that database. A cluster is a set of nodes cooperating in a replication scheme.

The documentation suggests that all nodes have a path to all other nodes. With only two nodes, this is simple to describe. With more nodes, be sure to include a path to all other nodes, regardless of whether you expect replication to take the paths.

Figure 2. A Slony-I cluster

Our first Slonik script initializes the cluster, defines each node, and defines the paths from each node to every other node. Notice that each node has an identifying number. init cluster defines the cluster on the first node. store node adds each subsequent node. The user is the slony superuser--in this case, postgres. You can choose any privileged user established as the Postgres superuser on each installation.

The path is defined by designating one node as a server and the other as a client for messaging. The terminology does not relate to the replicator/replica relationship; instead it references the possible network path. The connection information in each command belongs to the server node. The client's slon daemon will connect to the server node using that connection information.

#!/bin/bash
#
# 01: Initialize Cluster
#
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';
    
#
# Initialize the cluster and create the second node
#
init cluster (id=1, comment='gb 7.4 5434');
echo 'Initializing gb cluster';
echo 'Node 1 on pgsql74 port 5434 defined';
    
store node (id=2, comment='gb 8.0 5430');
echo 'Node 2 on pggsql80b port 5430 defined';
    
#
# create paths in both directions
#
store path (server=1, client=2, conninfo='dbname=gb host=localhost 
     port=5434 user=postgres');
store path (server=2, client=1, conninfo='dbname=gb host=localhost 
     port=5430 user=postgres');
echo 'path from server node 1 to client node 2 created.';
echo 'path from server node 2 to client node 1 created.';
    
_END_

Using Slonik's echo command can help log and track the commands in any Slonik script.

Listening for events

Events will occur throughout the cluster, and you must tell Slony-I what nodes listen to what nodes to receive these events. The events may be replication information or administrative information that requires propagation throughout the cluster.

In the simple case of two nodes, they listen to each other. In any case, all nodes should be able to listen to all other nodes. The paths' definitions intentionally make this possible.

Specifying the origin identifies which node the receiver is listening for. The origin of an event may or may not provide the event to the receiver; however, the default is to do so. It is possible for node 3 to listen for events initiated on node 1 and have those events provided by node 2 (which, one assumes, is also listening for events from node 1).

In our case, we are having both nodes listen for events on the other, with the events provided by the origin node.

#!/bin/bash
#
# 02: Listen
#
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';
    
#
# make the nodes listen on the paths
# in both directions
#
store listen (origin=1, receiver=2, provider=1);
store listen (origin=2, receiver=1, provider=2);
    
_END_