There is a set of scripts to simplify administration
of set of Slony-I instances. The scripts support having arbitrary numbers of
nodes. They may be installed as part of the installation process:

./configure --with-perltools

This will produce a number of scripts with the prefix
slonik_. They eliminate tedium by always referring
to a central configuration file for the details of your site
configuration. A documented sample of this file is provided in
altperl/slon_tools.conf-sample. Most also include
some command line help with the "--help" option, making them easier to
learn and use.

Most generate Slonik scripts that are printed to STDOUT.
At one time, the commands were passed directly to slonik for execution.
Unfortunately, this turned out to be a pretty large calibre
"foot gun", as minor typos on the command line led, on a
couple of occasions, to pretty calamitous actions. The savvy administrator should review the script
before piping it to slonik.

The UNIX environment variable SLONYNODES is used
to determine what Perl configuration file will be used to control the
shape of the nodes in a Slony-I cluster. If it is not provided, a
default slon_tools.conf location will be
referenced.

What variables are set up.

$CLUSTER_NAME=orglogs; # What is the name of the replication cluster?

$LOGDIR='/opt/OXRS/log/LOGDBS'; # What is the base directory for logs?

$APACHE_ROTATOR="/opt/twcsds004/OXRS/apache/rotatelogs"; # If set, where to find Apache log rotator

foldCase # If set to 1, object names (including schema names) will be
folded to lower case. By default, your object names will be left
alone. Note that PostgreSQL itself folds object names to lower case;
if you create a table via the command CREATE TABLE
SOME_THING (Id INTEGER, STudlYName text);, the result will
be that all of those components are forced to lower case, thus
equivalent to create table some_thing (id integer,
studlyname text);, and the name of table and, in this case,
the fields will all, in fact, be lower case.

You then define the set of nodes that are to be replicated
using a set of calls to add_node().

The UNIX environment variable SLONYSET is used to
determine what Perl configuration file will be used to determine what
objects will be contained in a particular replication set.

Unlike SLONYNODES, which is essential for
all of the slonik-generating
scripts, this only needs to be set when running
create_set, as that is the only script used to
control what tables will be in a particular replication set.

Generates Slonik script to drop a replication set
(e.g. - set of tables and sequences) from a
Slony-I cluster.

This represents a pretty big potential "foot gun"
as this eliminates a replication set all at once. A typo that points
it to the wrong set could be rather damaging. Compare to Section 6.1.1.25 and Section 6.1.1.6; with both of those, attempting to drop a
subscription or a node that is vital to your operations will be
blocked (via a foreign key constraint violation) if there exists a
downstream subscriber that would be adversely affected. In contrast,
there will be no warnings or errors if you drop a set; the set will
simply disappear from replication.

This goes through and drops the Slony-I schema from each node;
use this if you want to destroy replication throughout a cluster. As
its effects are necessarily rather destructive, this has the potential
to be pretty unsafe.

This is a shell script designed to rummage through a Slony-I
cluster and generate a set of slon.conf files
that slon accesses via the slon -f slon.conf
option.

With all of the configuration residing in a configuration file
for each slon, they can be invoked with minimal muss and fuss, with
no risk of forgetting the -a option and thereby
breaking a log shipping
node.

Running it requires the following environment configuration:

Firstly, the environment needs to be set up with
suitable parameters for libpq to connect to one of the databases in
the cluster. Thus, you need some suitable combination of the
following environment variables set:

PGPORT

PGDATABASE

PGHOST

PGUSER

PGSERVICE

SLONYCLUSTER - the name of the
Slony-I cluster to be "rummaged".

MKDESTINATION - a directory for
configuration to reside in; the script will create
MKDESTINATION/$SLONYCLUSTER/conf for the slon
configuration files, and
MKDESTINATION/$SLONYCLUSTER/pid for slon to
store PID files in.

LOGHOME - a directory for log files to
reside in; a directory of the form
$LOGHOME/$SLONYCLUSTER/node[number] will be created
for each node.

For any "new" nodes that it discovers, this script
will create a new slon conf file.

Warning

It is fair to say that there are several conditions to
beware of; none of these should be greatly surprising...

The DSN is pulled from the minimum value found for
each node in sl_path. You may very well need to modify
this.

Various parameters are set to default values; you may
wish to customize them by hand.

If you are running slon processes on multiple
nodes (e.g. - as when running Slony-I across a
WAN), this script will happily create fresh new config files for
slons you wanted to have run on another host.

This would usually only cause some minor inconvenience due to,
for instance, a slon running at a non-preferred site, and either
failing due to lack of network connectivity (in which no damage is
done!) or running a bit less efficiently than it might have due to
living at the wrong end of the network "pipe."

On the other hand, if you are running a log shipping node at
the remote site, accidentally introducing a slon that
isn't collecting logs could ruin your whole
week.

The file layout set up by mkslonconf.sh
was specifically set up to allow managing slons across a
multiplicity of clusters using the script in the following
section...

Note that this file is required to contain a value for log_pid_file; that is necessary to allow this script to detect whether the slon is running or not.

SLON_LOG

This file is the location where slon log files are to be stored, if need be. There is an option slon_conf_syslog for slon to use syslog to manage logging; in that case, you may prefer to set SLON_LOG to /dev/null.

Note that these environment variables may either be set, in the
script, or overridden by values passed in from the environment. The
latter usage makes it easy to use this script in conjunction with the
regression tests so that it is regularly tested.

This is a shell script which uses the configuration as set up
by mkslonconf.sh and is intended to support an
approach to running Slony-I involving regularly
(e.g. via a cron process) checking to ensure that
slon processes are running.

It uses the following environment variables:

PATH which needs to contain, preferably
at the beginning, a path to the slon binaries that should be
run.

SLHOME indicates the
"home" directory for slon configuration files; they
are expected to be arranged in subdirectories, one for each cluster,
with filenames of the form node1.conf,
node2.conf, and such

If you remove some of these files, or rename them so their
names do not conform to the find command, they
won't be found; that is an easy way to drop nodes out of this system.

LOGHOME indicates the
"home" directory for log storage.

This script does not assume the use of the Apache log rotator
to manage logs; in that PostgreSQL version 8 does its own log
rotation, it seems undesirable to retain a dependancy on specific log
rotation "technology."

CLUSTERS is a list of Slony-I clusters
under management.

In effect, you could run this every five minutes, and it would
launch any missing slon processes.

Upstart is a
recent alternative to /sbin/init to handle
automatically starting tasks and services when a system boots. It is
particularly popular on the Ubuntu Linux distribution.

share/upstart-slon.conf-sample is a
sample script for use with upstart.
Deployment will require some customization in order to indicate where
Slony-I binaries and configuration are found in a particular
environment.

If you are running a lot of replicated databases, where there
are numerous Slony-I clusters, it can get painful to track and
document this. The following tools may be of some assistance in this.

slony-cluster-analysis.sh is a shell
script intended to provide some over-time analysis of the
configuration of a Slony-I cluster. You pass in the usual
libpq environment variables
(PGHOST, PGPORT,
PGDATABASE, and such) to connect to a member of a
Slony-I cluster, and pass the name of the cluster as an argument.

The script then does the following:

Runs a series of queries against the Slony-I tables to get lists of nodes, paths, sets, and tables.

This is stowed in a temporary file in /tmp

A comparison is done between the present configuration and the configuration the last time the tool was run. If the configuration differs, an email of the difference (generated using diff) is sent to a configurable email address.

If the configuration has changed, the old configuration file is renamed to indicate when the script noticed the change.

Ultimately, the current configuration is stowed in LOGDIR in a filename like cluster.last

There is a sample "wrapper" script,
slony-cluster-analysis-mass.sh, which sets things
up to point to a whole bunch of Slony-I clusters.

This should make it easier for a group of DBAs to keep track of
two things:

This script uses a number (possibly large, if your
configuration needs to be particularly complex) of environment
variables to determine the shape of the configuration of a cluster.
It uses default values extensively, and in many cases, relatively few
environment values need to be set in order to get a viable
configuration.

Traditionally, people have used a database superuser for this,
but that is not necessary as discussed Section 5.7.2

PGPORT

default port number

PGDATABASE

default database name

TABLES

a list of fully qualified table names (e.g. - complete with
namespace, such as public.my_table)

SEQUENCES

a list of fully qualified sequence names (e.g. - complete with
namespace, such as public.my_sequence)

Defaults are provided for all of these
values, so that if you run
configure-replication.sh without setting any
environment variables, you will get a set of slonik scripts. They may
not correspond, of course, to any database you actually want to
use...

slonik config files are generated in a temp directory under
/tmp. The usage is thus:

preamble.slonik is a
"preamble" containing connection info used by the other
scripts.

Verify the info in this one closely; you may want to keep this
permanently to use with future maintenance you may want to do on the
cluster.

create_nodes.slonik

This is the first script to run; it sets up the requested nodes
as being Slony-I nodes, adding in some Slony-I-specific config
tables and such.

You can/should start slon processes any time after this step has
run.

store_paths.slonik

This is the second script to run; it indicates how the slons
should intercommunicate. It assumes that all slons can talk to all
nodes, which may not be a valid assumption in a complexly-firewalled
environment. If that assumption is untrue, you will need to modify
the script to fix the paths.

create_set.slonik

This sets up the replication set consisting of the whole bunch
of tables and sequences that make up your application's database
schema.

When you run this script, all that happens is that triggers are
added on the origin node (node #1) that start collecting updates;
replication won't start until #5...

There are two assumptions in this script that could be
invalidated by circumstances:

That all of the tables and sequences have been
included.

This becomes invalid if new tables get added to your
schema and don't get added to the TABLES
list.

That all tables have been defined with primary
keys.

Best practice is to always have and use true primary keys.
If you have tables that require choosing a candidate primary key,
you will have to modify this script by hand to accomodate
that.

subscribe_set_2.slonik

And 3, and 4, and 5, if you set the number of nodes
higher...

This is the step that "fires up"
replication.

The assumption that the script generator makes is that all
the subscriber nodes will want to subscribe directly to the origin
node. If you plan to have "sub-clusters," perhaps
where there is something of a "master" location at each
data centre, you may need to revise that.

The slon processes really ought to be running by the time you
attempt running this step. To do otherwise would be rather
foolish.

In the tools area,
duplicate-node.sh is a script that may be used to
help create a new node that duplicates one of the ones in the
cluster.

The script expects the following parameters:

Cluster name

New node number

Origin node

Node being duplicated

New node

For each of the nodes specified, the script offers flags to
specify libpq-style parameters for
PGHOST, PGPORT,
PGDATABASE, and PGUSER; it is expected
that .pgpass will be used for storage of
passwords, as is generally considered best practice. Those values may
inherit from the libpq environment variables, if
not set, which is useful when using this for testing. When
"used in anger," however, it is likely that nearly all of
the 14 available parameters should be used.

The script prepares files, normally in
/tmp, and will report the name of the directory
that it creates that contain SQL and slonik scripts to set up the
new node.

schema.sql

This is drawn from the origin node, and contains the "pristine" database schema that must be applied first.

The tool tools/slonikconfdump.sh was
created to help dump out a slonik script to duplicate the
configuration of a functioning Slony-I cluster. It should be
particularly useful when upgrading Slony-I to version 2.0; see Section 5.4.6 for more details.

It dumps out:

Cluster name

Node connection information

Note that it uses the first value it finds (e.g. - for the lowest numbered client node).

Nodes

Sets

Tables

Sequences

Subscriptions

Note that the subscriptions are ordered by set, then by
provider, then by receiver. This ordering does not necessarily
indicate the order in which subscriptions need to be
applied.

The output should be reviewed before it is applied elsewhere.
Particular attention should be paid to the ADMIN
CONNINFO, as it picks the first value that it sees for each
node; in a complex environment, where visibility of nodes may vary
from subnet to subnet, it may not pick the right value. In addition,
SUBSCRIBE SET statements do not necessarily
indicate the order in which subscriptions need to be applied.

A new script for Slony-I 1.1 is
generate_syncs.sh, which addresses the following kind of
situation.

Supposing you have some possibly-flakey server where the
slon daemon that might not run all the time, you might
return from a weekend away only to discover the following situation.

On Friday night, something went "bump" and while the
database came back up, none of the slon daemons
survived. Your online application then saw nearly three days worth of
reasonably heavy transaction load.

When you restart slon on Monday, it
hasn't done a SYNC on the master since Friday, so that the next
"SYNC set" comprises all of the updates between Friday
and Monday. Yuck.

If you run generate_syncs.sh as a cron job every
20 minutes, it will force in a periodic SYNC on the origin, which
means that between Friday and Monday, the numerous updates are split
into more than 100 syncs, which can be applied incrementally, making
the cleanup a lot less unpleasant.

Note that if SYNCs are running
regularly, this script won't bother doing anything.