Requirements
Before you can create a replication cluster, you must install Slony on every computer that will host a node. You can find Slony at the PostgreSQL's gborg web site (http://gborg.postgresql.org) or at http://slony.info. Currently, you must compile Slony yourself (at the time we are writing this, binary distributions are not available). To build Slony, you must have the following PostgreSQL components on each system:
- PostgreSQL C header files (typically found in /usr/include, you can locate these files with the command pg_config includedir)
- PostgreSQL Server C header files (typically found in /usr/include/pgsql/ server, you can locates these files with the command pg_config includedir-server)
- PostgreSQL client libraries (typically found in /usr/lib, you can locate the actual directory with the command pg_config -libdir)
- PL/pgSQL (typically found in /usr/lib/pgsql, you can locate the actual directory with the command pg_config pkglibdir)
Once you've confirmed that you have all of the components that you need, download and unpack the Slony archive. Next cd to the directory that holds the freshly unpacked files and then, dance the familiar GNU Tango: ./configure && make && make install.
If you've installed PostgreSQL in a "standard" location, configure should be able to find the PostgreSQL components that it requires. If configure complains that it can't find a PostgreSQL component, you may need to add one or more of the following options to the configure command line:
- with-pgconfigdir=directory: The given directory must contain the pg_config utility (configure uses pg_config to find most of the other PostgreSQL components)
- with-pgbindir=directory: Location of the PostgreSQL postmaster (Slony installs a few programs, such as slon and slonik, in this directory)
- with-pgincludedir=directory: Location of the PostgreSQL headers
- with-pgincludeserverdir=directory: Location of the PostgreSQL server headers
- with-pglibdir=directory: Location of the PostgreSQL libraries (Slony programs are linked against the libpq object library)
- with-pgpkglibdir=directory: Location of the PostgreSQL pkglib directory (specifically, configure searches for the PL/pgSQL shared library)
- with-pgsharedir=directory: Location of the PostgreSQL share directory (Slony installs a number of SQL scripts in this directory)
In addition to the Slony package itself, you must prepare each PostgreSQL server for replication. First, the target database (that is, the database that you are originating or subscribing to) must exist on each node. Although not required, you may find it easier to manage the cluster if you use consistent database names on each node. In our video store example, every node contains a database named movies. Within movies, each node contains one schema for each branch. (You would end up with a schema named springfield, a schema named boomtown, and a schema named snoozeville.) Finally, you must install PL/pgSQL into each node. Many of the triggers and functions that Slony uses are written in PL/pgSQL (others are written in C).
To carry out its work, Slony adds a number of tables, views, sequences, functions, and types to each node. Slony segregates most of its data into a single schema within each nodethe name of the schema is formed by adding an underscore to the start of the replication cluster name. For example, if you create a replication cluster named branches, Slony creates a schema named _branches in each node. Slony will refuse to cooperate if it finds anything in the cluster schema. (Slony prefers to create the cluster schema itself.) Although it's possible to populate the Slony schemas by hand, it's easier to use the slonik command. slonik is a scripting engineto create or manage a Slony cluster, you write a script and invoke the slonik command to execute that script.
When you execute a script, slonik connects to one or more of the databases in the cluster and executes commands (often stored procedures) in those databases. Each script must contain a preamble that tells slonik how to connect to each node. For example, a script that manages the branches cluster would begin like this:
cluster name = branches; node 1 admin conninfo = 'dbname=movies host=springfield user=slony'; node 2 admin conninfo = 'dbname=movies host=boomtown user=slony'; node 3 admin conninfo = 'dbname=movies host=snoozeville user=slony'; ...
The first command in the preamble tells slonik the name of the cluster. (slonik needs the cluster name so that it can refer to the proper schema within each database.) The cluster name directive is followed by a series of node declarations. Each node declaration assigns a unique numeric identifier to the node and tells slonik how to connect to the database. Since slonik is a client application that uses the libpq library to connect to each database, you must provide the connection information in the form of a libpq-style connection string. The conninfo string typically specifies a database name, a hostname, a username, and occasionally a port number, but you can include any of the connection properties described in Table 5.2. If you want to consolidate all of your connection properties into a single location, you might consider creating a pg_service.conf file and including the service names in your slonik script instead. For example, if you have a pg_service.conf file that contains the following entries:
[springfield-slonik] dbname=movies host=springfield user=slony [boomtown-slonik] dbname=movies host=boomtown user=slony [snoozeville-slonik] dbname=movies host=snoozeville user=slony
You could write a slonik preamble like this:
# File: preamble cluster name = branches; node 1 admin conninfo = 'service=springfield-slonik'; node 2 admin conninfo = 'service=boomtown-slonik'; node 3 admin conninfo = 'service=snoozeville-slonik'; ...
Instead of writing the same preamble in every slonik script, you can save the preamble in a separate file (say, preamble.sk) and then include<> that file in other scripts. The preamble file is a great place to define symbolic names for values that you'll use in other scripts. Listing 24.1 shows a revised preamble.sk file that we'll include<> in the rest of the scripts that you'll see in this chapter.
Listing 24.1. preamble.sk
# File: preamble.sk define CLUSTER branches; define SPRINGFIELD 1; define BOOMTOWN 2; define SNOOZEVILLE 3; define fqn fully qualified name; define SUCCESS 0; define FAILURES 1; cluster = @CLUSTER; node @SPRINGFIELD admin conninfo = 'dbname=movies host=springfield user=slony'; node @BOOMTOWN admin conninfo = 'dbname=movies host=boomtown user=slony'; node @SNOOZEVILLE admin conninfo = 'dbname=movies host=snoozeville user=slony';
Once you have the preamble in place, you can add commands to the slonik script. Most commands are composed of a one, two, or three-word verb followed by a comma-separated list of options enclosed in a pair of parentheses.