| Slony-I 2.2.10 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Fast Forward | Next | |
Chapter 2. Tutorial
- Table of Contents
- 2.1. Replicating Your First Database
- 2.2. Starting & Stopping Replication
2.1. Replicating Your First Database
In this example, we will be replicating a brand new pgbench database. The mechanics of replicating an existing database are covered here, however we recommend that you learn how Slony-I functions by using a fresh new non-production database.
Note that pgbench is a "benchmark" tool that is in the PostgreSQL set of contrib tools. If you build PostgreSQL from source, you can readily head to contrib/pgbench and do a make install to build and install it; you may discover that included in packaged binary PostgreSQL installations.
Note: pgbench creates tables with a slightly different name in PostgreSQL 8.3 than it does in later versions. If you are using this tutorial with PostgreSQL 8.3 you should remove the 'pgbench_' prefix from all of the table names listed below
The Slony-I replication engine is trigger-based, allowing us to replicate databases (or portions thereof) running under the same postmaster.
This example will show how to replicate the pgbench database running on localhost (master) to the pgbench slave database also running on localhost (slave). We make a couple of assumptions about your PostgreSQL configuration:
You have enabled access in your cluster(s) via pg_hba.conf
The REPLICATIONUSER is commonly set up to be a PostgreSQL superuser, perhaps an existing one such as postgres, pgsql, or one created for this purpose such as slony or replication. Traditionally, people have used a database superuser for this, but that is not necessary as discussed Section 5.7.2. If you set up a non-superuser user for this, there is more of a configuration burden in granting the specifically-required permissions.
You should also set the following shell variables:
CLUSTERNAME=slony_example
MASTERDBNAME=pgbench
SLAVEDBNAME=pgbenchslave
MASTERHOST=localhost
SLAVEHOST=localhost
REPLICATIONUSER=pgsql
PGBENCHUSER=pgbench
Here are a couple of examples for setting variables in common shells:
bash, sh, ksh export CLUSTERNAME=slony_example
(t)csh: setenv CLUSTERNAME slony_example
| Warning |
If you're changing these variables to use different hosts for MASTERHOST and SLAVEHOST, be sure not to use localhost for either of them. This will result in an error similar to the following: ERROR remoteListenThread_1: db_getLocalNodeId() returned 2 - wrong database? |
2.1.2. Preparing the Databases
createdb -O $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME createdb -O $PGBENCHUSER -h $SLAVEHOST $SLAVEDBNAME pgbench -i -s 1 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
One of the tables created by pgbench, pgbench_history, does not have a primary key. Slony-I requires that there is a suitable candidate primary key.
The following SQL requests will establish a proper primary key on this table:
psql -U $PGBENCHUSER -h $MASTERHOST -d $MASTERDBNAME -c "begin; alter table
pgbench_history add column id serial; update history set id =
nextval('pgbench_history_id_seq'); alter table pgbench_history add primary key(id);
commit;"Because Slony-I depends on the databases having the pl/pgSQL procedural language installed, we better install it now. It is possible that you have installed pl/pgSQL into the template1 database in which case you can skip this step because it's already installed into the $MASTERDBNAME.
createlang -h $MASTERHOST plpgsql $MASTERDBNAME
Slony-I does not automatically copy table definitions from a master when a slave subscribes to it, so we need to import this data. We do this with pg_dump.
pg_dump -s -U $REPLICATIONUSER -h $MASTERHOST $MASTERDBNAME | psql -U $REPLICATIONUSER -h $SLAVEHOST $SLAVEDBNAME
To illustrate how Slony-I allows for on the fly replication subscription, let's start up pgbench. If you run the pgbench application in the foreground of a separate terminal window, you can stop and restart it with different parameters at any time. You'll need to re-export the variables again so they are available in this session as well.
The typical command to run pgbench would look like:
pgbench -s 1 -c 5 -t 1000 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
This will run pgbench with 5 concurrent clients each processing 1000 transactions against the pgbench database running on localhost as the pgbench user.
2.1.3. Configuring the Database For Replication.
Creating the configuration tables, stored procedures, triggers and configuration is all done through the slonik tool. It is a specialized scripting aid that mostly calls stored procedures in the master/slave (node) databases.
The example that follows uses slonik directly (or embedded directly into scripts). This is not necessarily the most pleasant way to get started; there exist tools for building slonik scripts under the tools directory, including:
Section 6.1.1 - a set of Perl scripts that build slonik scripts based on a single slon_tools.conf file.
Section 6.1.2 - a shell script (e.g. - works with Bash) which, based either on self-contained configuration or on shell environment variables, generates a set of slonik scripts to configure a whole cluster.
2.1.3.1. Using slonik Command Directly
The traditional approach to administering slony is to craft slonik commands directly. An example of this given here.
The script to create the initial configuration for the simple master-slave setup of our pgbench database looks like this:
#!/bin/sh slonik <<_EOF_ #-- # define the namespace the replication system uses in our example it is # slony_example #-- cluster name = $CLUSTERNAME; #-- # admin conninfo's are used by slonik to connect to the nodes one for each # node on each side of the cluster, the syntax is that of PQconnectdb in # the C-API # -- node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'; node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'; #-- # init the first node. This creates the schema # _$CLUSTERNAME containing all replication system specific database # objects. #-- init cluster ( id=1, comment = 'Master Node'); #-- # Slony-I organizes tables into sets. The smallest unit a node can # subscribe is a set. The following commands create one set containing # all 4 pgbench tables. The master or origin of the set is node 1. #-- create set (id=1, origin=1, comment='All pgbench tables'); set add table (set id=1, origin=1, id=1, fully qualified name = 'public.pgbench_accounts', comment='accounts table'); set add table (set id=1, origin=1, id=2, fully qualified name = 'public.pgbench_branches', comment='branches table'); set add table (set id=1, origin=1, id=3, fully qualified name = 'public.pgbench_tellers', comment='tellers table'); set add table (set id=1, origin=1, id=4, fully qualified name = 'public.pgbench_history', comment='history table'); #-- # Create the second node (the slave) tell the 2 nodes how to connect to # each other and how they should listen for events. #-- store node (id=2, comment = 'Slave node', event node=1); store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'); store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'); _EOF_
Is the pgbench still running? If not, then start it again.
At this point we have 2 databases that are fully prepared. One is the master database in which pgbench is busy accessing and changing rows. It's now time to start the replication daemons.
On $MASTERHOST the command to start the replication engine is
slon $CLUSTERNAME "dbname=$MASTERDBNAME user=$REPLICATIONUSER host=$MASTERHOST"
Likewise we start the replication system on node 2 (the slave)
slon $CLUSTERNAME "dbname=$SLAVEDBNAME user=$REPLICATIONUSER host=$SLAVEHOST"
Even though we have the slon running on both the master and slave, and they are both spitting out diagnostics and other messages, we aren't replicating any data yet. The notices you are seeing is the synchronization of cluster configurations between the 2 slon processes.
To start replicating the 4 pgbench tables (set 1) from the master (node id 1) the the slave (node id 2), execute the following script.
#!/bin/sh slonik <<_EOF_ # ---- # This defines which namespace the replication system uses # ---- cluster name = $CLUSTERNAME; # ---- # Admin conninfo's are used by the slonik program to connect # to the node databases. So these are the PQconnectdb arguments # that connect from the administrators workstation (where # slonik is executed). # ---- node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'; node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'; # ---- # Node 2 subscribes set 1 # ---- subscribe set ( id = 1, provider = 1, receiver = 2, forward = no); _EOF_
Any second now, the replication daemon on $SLAVEHOST will start to copy the current content of all 4 replicated tables. While doing so, of course, the pgbench application will continue to modify the database. When the copy process is finished, the replication daemon on $SLAVEHOST will start to catch up by applying the accumulated replication log. It will do this in little steps, initially doing about 10 seconds worth of application work at a time. Depending on the performance of the two systems involved, the sizing of the two databases, the actual transaction load and how well the two databases are tuned and maintained, this catchup process may be a matter of minutes, hours, or eons.
If you encounter problems getting this working, check over the logs for the slon processes, as error messages are likely to be suggestive of the nature of the problem. The tool Section 5.1.1 is also useful for diagnosing problems with nearly-functioning replication clusters.
You have now successfully set up your first basic master/slave replication system, and the 2 databases should, once the slave has caught up, contain identical data. That's the theory, at least. In practice, it's good to build confidence by verifying that the datasets are in fact the same.
The following script will create ordered dumps of the 2 databases and compare them. Make sure that pgbench has completed, so that there are no new updates hitting the origin node, and that your slon sessions have caught up.
#!/bin/sh echo -n "**** comparing sample1 ... " psql -U $REPLICATIONUSER -h $MASTERHOST $MASTERDBNAME >dump.tmp.1.$$ <<_EOF_ select 'accounts:'::text, aid, bid, abalance, filler from pgbench_accounts order by aid; select 'branches:'::text, bid, bbalance, filler from pgbench_branches order by bid; select 'tellers:'::text, tid, bid, tbalance, filler from pgbench_tellers order by tid; select 'history:'::text, tid, bid, aid, delta, mtime, filler, id from pgbench_history order by id; _EOF_ psql -U $REPLICATIONUSER -h $SLAVEHOST $SLAVEDBNAME >dump.tmp.2.$$ <<_EOF_ select 'accounts:'::text, aid, bid, abalance, filler from pgbench_accounts order by aid; select 'branches:'::text, bid, bbalance, filler from pgbench_branches order by bid; select 'tellers:'::text, tid, bid, tbalance, filler from pgbench_tellers order by tid; select 'history:'::text, tid, bid, aid, delta, mtime, filler, id from pgbench_history order by id; _EOF_ if diff dump.tmp.1.$$ dump.tmp.2.$$ >$CLUSTERNAME.diff ; then echo "success - databases are equal." rm dump.tmp.?.$$ rm $CLUSTERNAME.diff else echo "FAILED - see $CLUSTERNAME.diff for database differences" fi
Note that there is somewhat more sophisticated documentation of the process in the Slony-I source code tree in a file called slony-I-basic-mstr-slv.txt.
If this script returns FAILED please contact the developers at http://slony.info/. Be sure to be prepared with useful diagnostic information including the logs generated by slon processes and the output of Section 5.1.1.
2.1.3.2. Using the altperl Scripts
Using the Section 6.1.1 scripts is an alternative way to get started; it allows you to avoid writing slonik scripts, at least for some of the simple ways of configuring Slony-I. The slonik_build_env script will generate output providing details you need to build a slon_tools.conf, which is required by these scripts. An example slon_tools.conf is provided in the distribution to get you started. The altperl scripts all reference this central configuration file centralize cluster configuration information. Once slon_tools.conf has been created, you can proceed as follows:
# Initialize cluster: $ slonik_init_cluster | slonik # Start slon (here 1 and 2 are node numbers) $ slon_start 1 $ slon_start 2 # Create Sets (here 1 is a set number) $ slonik_create_set 1 | slonik # subscribe set to second node (1= set ID, 2= node ID) $ slonik_subscribe_set 1 2 | slonik
You have now replicated your first database.