Friday, October 5, 2007

PostgreSQL Replication with Slony-I

In an earlier blog post, we looked at synchronous, master-master replication in PostgreSQL using PGCluster. PGCluster's load balanacing and replication features provide PostgreSQL with high availability features that are not included in the core distribution. While PGCluster does provide an adequate solution for many environments, it is not the only replication mechanism available for PostgreSQL. In addition, its drawbacks may be too great to be deployed in many circumstances.

In this post, we look at another replication mechanism available for PostgreSQL, Slony-I. Slony-I is an asynchronous, master-slave replication system. With PGCluster, we had the ability to load balance connections to the PostgreSQL database, knowing that data that is modified in one server will be replicated across to the other server. Additionally, its synchronous nature gave us confidence that, in the event of a failure, all completed transactions will be accounted for. With Slony-I, we run into a very different type of replication. Slony-I is a master-slave system, designed to utilize one master database, and one or more slaves. Data is replicated in a one-way fashion, from the master to the slave(s). It does not include a built-in load balancing feature like PGCluster, and there is no automatic failover from a failed master to one of the slaves. Failover must be manually configured using a third-party utility, such as heartbeat. Slony-I's asynchronous nature means that, in the event of a database failure, there may be uncommitted transactions that have not been replicated across. Slony-I performs batching of transaction replication in order to improve performance. However, if the master fails prior to a batch of transactions being replicated, those transactions are lost.

On the surface, it may appear as though Slony-I is a less-than-ideal choice for database replication. However, upon further investigation, it becomes clear that Slony-I may be an acceptable choice, depending on the needs of your organization. In our example, we have one master and one slave. Our master is on a host called pgmaster, while the slave is on a host called pgslave. The database that we wish to replicate is called repl_test_db. The database consists of a single, basic table, repl_test_tbl.

Before we can begin replicating our data, we need to examine a few of the core concepts involved in Slony-I replication. The first is the notion of a 'cluster'. A cluster is simply a collection of database nodes that are connected together. These are the databases that will eventually be replicated. A 'replication set' describes the actual data (tables, sequences, etc.) that will be replicated. So, while a cluster describes the underlying database that will support replication, a replication set describes the underlying database objects that will be replicated.

To make our replication simpler, we will first declare some environment variables that will be used throughout the commands that we will issue. We will store these environment variables in a shell script called env.sh. The contents are as follows:

#!/bin/sh
REPLICATIONUSER=postgres
CLUSTERNAME=cluster_a
MASTERDB=repltestdb
SLAVEDB=${MASTERDB}
MASTERHOST=pgmaster
SLAVEHOST=pgslave
PSQL=/usr/bin/psql

CREATEDB=/usr/bin/createdb

CREATELANG=/usr/bin/createlang

CREATEUSER=/usr/bin/createuser

PGDUMP=/usr/bin/pg_dump


export REPLICATIONUSER CLUSTERNAME MASTERDBNAME SLAVEDBNAME MASTERHOST SLAVEHOST
export PSQL CREATEDB CREATELANG CREATEUSER PGDUMP


Now that our environment variables have been established, we are ready to begin setting up the necessary databases. Once again, I recommend creating a shell script that will encapsulate all the commands needed to perform our setup steps. This script will first create the repltestdb database on our master host, and then ensure that the plpgsql language is installed. This step is vital, as Slony-I requires that plpgsql be installed in order to run. Once the master database has been setup, our table will be created and populated with some simple test data. From here, we begin to setup the slave host. The slave database is created, and then a pg_dump is performed to copy the initial data from our master database into the slave database. NOTE: It is important that our master and slave databases are able to communicate with each other. This will involve modifying the pg_hba.conf file to ensure that the database permissions are set properly. For more information on how to accomplish this, see the PostgreSQL 8.2 referrence manual, or the post on PGCluster replication.

For our tests, we will use a very simple table consisting of 2 columns. In our next article, we will look at the performance differences between PGCluster and Slony-I when using larger and more complex data sets. For this example, however, our simple setup will be sufficient. The SQL script looks like this:


DROP TABLE IF EXISTS repl_test_tbl;

CREATE SEQUENCE repl_test_tbl_id_seq;
CREATE TABLE repl_test_tbl (
id INTEGER DEFAULT nextval('repl_test_tbl_id_seq') NOT NULL,
my_str VARCHAR(32),
PRIMARY KEY (id)
);

INSERT INTO repl_test_tbl (my_str) VALUES ('This is my #1 string');
INSERT INTO repl_test_tbl (my_str) VALUES ('This is my #2 string');


After our databases have been created and setup, we are ready to start initializing the replication sets. The slonik utility is used to issue commands to the Slony-I replication engine. The easiest way to enter these commands is using a shell script like the one in this example. Our replication script looks like this:

#!/bin/sh

echo Setting PostgreSQL environment variables...
. ./env.sh

SQL_FILE=repl_test.sql

echo Initializing master database...
${CREATEDB} -O ${REPLICATIONUSER} -h ${MASTERHOST} ${MASTERDB}
${CREATELANG} -U ${REPLICATIONUSER} plpgsql -h ${MASTERHOST} ${MASTERDB}

echo Initializing master database data...
${PSQL} -U ${REPLICATIONUSER} ${MASTERDB} < ${SQL_FILE}

echo Initializing slave database...
${CREATEDB} -O ${REPLICATIONUSER} -h ${SLAVEHOST} ${SLAVEDB}
${CREATELANG} -U ${REPLICATIONUSER} plpgsql -h ${SLAVEHOST} ${SLAVEDB}
${PGDUMP} -s -U ${REPLICATIONUSER} -h ${MASTERHOST} ${MASTERDB} | ${PSQL} -U ${REPLICATIONUSER} -h ${SLAVEHOST} ${SLAVEDB}

echo Initializing the Slony-I cluster...

slonik <<_eof_
cluster name = ${CLUSTERNAME};
node 1 admin conninfo = 'dbname=${MASTERDB} host=${MASTERHOST} user=${REPLICATIONUSER}';
node 2 admin conninfo = 'dbname=${SLAVEDB} host=${SLAVEHOST} user=${REPLICATIONUSER}';
init cluster (id = 1, comment = 'Master Node');
create set (id = 1, origin = 1, comment = 'Test repl. table');
set add table (set id = 1, origin = 1, id = 1, full qualified name = 'public.repl_test_tbl', comment = 'Test repl. table');
set add sequence (set id = 1, origin = 1, id = 2, full qualified name = 'public.repl_test_tbl_id_seq', comment = 'Test repl. table PK');

store node (id = 2, comment = 'Slave Node');
store path (server = 1, client = 2, conninfo = 'dbname=${MASTERDB} host=${MASTERHOST} user=${REPLICATIONUSER}');
store path (server = 2, client = 1, conninfo = 'dbname=${SLAVEDB} host=${SLAVEHOST} user=${REPLICATIONUSER}');
store listen (origin = 1, provider = 1, receiver = 2);
store listen (origin = 2, provider = 2, receiver = 1);


It is important to run this script as the postgres user, so make sure you su to postgres before running it. In this script, you will notice how we add our repl_test_tbl table to the replication set. It is also important to note that the repl_test_tbl_id_seq sequence must be added to the replication set as well. Slony-I requires sequences to be explicity added to the replication set, and it also requires that every table has a primary key.

Now that our cluster and replication sets have been defined, it is time to start the replication engine and subscribe to the replication set. Subscribing to a replication set tells the database to start replicating to the defined slaves. The slon command is used to start and stop the replication engine. Once again, it is best to make use of our environment variable shell script to make things easier on us. Run the first command as postgres at a command prompt on the master host, and the second set of commands on the slave host:

Master Host:
. ./env.sh
slon $CLUSTERNAME "dbname=$MASTERDB user=$REPLICATIONUSER" &


Slave Host:
. ./env.sh
slon $CLUSTERNAME "dbname=$SLAVEDB user=$REPLICATIONUSER" &


If all was successful, we are ready to subscribe to the replication sets. The shell script for performing the subscription follows:

#!/bin/sh

. ./env.sh
echo Subscribing to replication set...

slonik <<_eof_
cluster name = ${CLUSTERNAME};
node 1 admin conninfo = 'dbname=${MASTERDB} host=${MASTERHOST} user=${REPLICATIONUSER}';
node 2 admin conninfo = 'dbname=${SLAVEDB} host=${SLAVEHOST} user=${REPLICATIONUSER}';
subscribe set (id = 1, provider = 1, receiver = 2, forward = yes);


Once again, run this script as the postgres user. We are now ready to test our data replication. First, connect to the PostgreSQL master and do a SELECT to see the data in our table prior to replication. If you perform the same SELECT on the PostgreSQL slave, you should see the same data. This is the result of the pg_dump that we performed earlier. Now it is time to see if our replication is successful. Once again, connect to the master database, and, this time, INSERT data into our replicated table. If we have configured our replication properly, you should be able to connect to the slave database and see that the newly INSERTed data has been successfully replicated to the slave.

If you followed the steps listed above, you can see our replication works as expected. But, how does this compare to PGCluster for data replication? As evidenced by the number of commands we were required to issue above to get the replication working, it is obvious that Slony-I requires more administration and has a steeper learning curve. Slony-I also requires us to manually define the replication sets and the data to be replicated. With PGCluster, our databases are replicated automatically. This is due to the fact that PGCluster uses rsync as its underlying mechanism to replicate the data. However, this reliance on rsync may prove to reduce performance, particularly with large datasets. Slony-I uses a trigger-based mechanism for replicating the data. In addition, its asynchronous nature may win out in environments that do not need the high availablity that a synchronous, multi-master solution can provide.

So, it is obvious that PGCluster wins out in terms of ease of administration. But what about performance? You'll have to wait until my next article to see how each of these replication mechanisms handle larger datasets, including PGBench.