PostgreSQL replication with Slony-I

In recent versions of PostgreSQL there are replication capabilities built-in, but for older versions I’ve been using Slony-I. I’m going to describe how I’ve replicated a database running on PostgreSQL 8.4 with Slony 1.2. For more info, you can read the official documentation.

  • Create a superuser role in both servers for replication:

CREATE ROLE slony WITH SUPERUSER LOGIN PASSWORD 'mipassword';

  • Example values:

CLUSTERNAME=db_cluster
MASTERDBNAME=mydb
SLAVEDBNAME=mydb
MASTERHOST=psql01.example.com
SLAVEHOST=psql02.example.com
REPLICATIONUSER=slony
DBUSER=user
export CLUSTERNAME MASTERDBNAME SLAVEDBNAME MASTERHOST SLAVEHOST REPLICATIONUSER DBUSER

  • Create destination database with the same encoding as source.

CREATE DATABASE web ENCODING 'LATIN1';

  • The database needs PL/pgSQL installed, to check it:

c mydb
SELECT COUNT(*) FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';

If it’s not installed:
CREATE LANGUAGE plpgsql;

  • All the tables need a primary key. In case someone doesn’t have PK, we can see errors like this:

PGRES_FATAL_ERROR select "_db_cluster".determineIdxnameUnique('public.my_table', NULL);  - ERROR:  Slony-I: table "public"."my_table" has no primary key

  • One option for tables without PK, is to add a column with a serial value:
begin;
  alter my_table add column id serial;
  update my_table set id = nextval('my_table_id_seq');
  alter table my_table add primary key(id);
commit;
  • Create roles in the destination database:

CREATE ROLE user WITH LOGIN PASSWORD 'whatever';

  • Copy the schema. We also can use “pg_dump -c” if the tables already exist in the destination.

pg_dump -s -U $REPLICATIONUSER -h $MASTERHOST $MASTERDBNAME | psql -U $REPLICATIONUSER -h $SLAVEHOST -d $SLAVEDBNAME

  • Create the slon configuration in /etc/slon.conf. It needs the connection info for the local database.
cluster_name='db_cluster'
conn_info='host=psql01.example.com dbname=mydb user=slony password=mypassword'
pid_file='/var/run/slon/slon.pid'
log_level=1
  • Create slon init script, configure the variables SLON_BUILD, SLON_CONF, SLON_LOG and SLON_USER
#!/bin/sh
# slon    This is the init script for starting up the Slony-I
#
# chkconfig: - 64 36
# description: Starts and stops the Slon daemon that handles
#              Slony-I replication.
# processname: slon
# pidfile:      /var/run/slon.pid
# $Id: start_slon.sh,v 1.3 2009/06/11 19:03:45 cbbrowne Exp $

# The following lines are ones you may wish to customize;
# alternatively, you may set SLON_BUILD and SLON_CONF in your
# environment to override the values in this script.
SLON_BUILD=/usr/bin
SLON_CONF=/etc/slon.conf
SLON_LOG=/var/log/slon.log
SLON_USER=postgres

SLON_BIN_PATH=${SLON_BUILD:-"/home/chris/dbs/postgresql-8.3.3/bin"}
SLON_CONF=${SLON_CONF:-"${HOME}/test/slon-conf.1"}
SLON_LOG=${SLON_LOG:-"${HOME}/test/slon.1.log"}    # If you use syslog, then this may use /dev/null

# shouldn't need to edit anything below this

test -x "$SLON_BIN_PATH/slon" || (echo "missing slon - ${SLON_BIN_PATH}/slon"; exit 1)
test -r "$SLON_CONF" || (echo "No slon conf file - $SLON_CONF"; exit 1)

PID_LINE=`grep pid_file $SLON_CONF | cut -d "#" -f 1 | grep "^[:space:]*pid_file='.*'"`
PID_FILE=`echo $PID_LINE | cut -d "=" -f 2 | cut -d "'" -f 2`
if [ "x$PID_FILE" == "x" ]; then
echo "pid_file not found in slon conf file - $SLON_CONF"
exit 1
else
if [ -f $PID_FILE ]; then
PID=`cat $PID_FILE`
FINDPID=`ps -p ${PID} | awk '{print $1}' | grep "^$PID$"`
fi
fi

case "$1" in
start)
if [ ! -z "$FINDPID" ]; then
echo "**** slon already running - PID $PID ****"
exit 1
fi
touch $SLON_LOG
test -w "$SLON_LOG" || (echo "**** SLON_LOG not writable - $SLON_LOG ****"; exit 1)
echo "Starting slon: $SLON_BIN_PATH/slon -p ${PID_FILE} -f ${SLON_CONF} 1>> ${SLON_LOG} 2>&1 &"
su $SLON_USER -c "$SLON_BIN_PATH/slon -p ${PID_FILE} -f ${SLON_CONF} 1>> ${SLON_LOG} 2>&1 &"
;;
stop)
echo "Stopping slon"
if [ ! -z "$FINDPID" ]; then
kill -15 ${PID}
echo "Killed slon at PID ${PID}"
else
echo "**** slon with PID ${PID} not found ****"
fi
;;
status)
echo "SLON_CONF:${SLON_CONF}"
echo "SLON_BIN_PATH:${SLON_BIN_PATH}"
if [ -f $PID_FILE ]; then
if [ ! -z "$FINDPID" ]; then
echo "**** Slon running as PID:$PID ****"
else
echo "**** Slon not running - PID:$PID - ${FINDPID} ****"
fi
else
echo "**** Slon not running - no PID file ${PID_FILE} ****"
fi
;;
*)
echo "Usage: $0 [start|stop|status]"
;;
esac
  • Initialize the slon configuration with the slonik tool. You can create a script like this:
#!/bin/sh
CLUSTERNAME=db_cluster
MASTERDBNAME=mydb
SLAVEDBNAME=mydb
MASTERHOST=psql01.example.com
SLAVEHOST=psql02.example.com
REPLICATIONUSER=slony
PASSWORD=mypassword

slonik <<_EOF_
cluster name = $CLUSTERNAME;

node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER password=$PASSWORD';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER password=$PASSWORD';

init cluster ( id=1, comment = 'Master Node');

create set (id=1, origin=1, comment='All the tables');
set add table (set id=1, origin=1, id=1, fully qualified name = 'public.my_table1', comment = 'Table 1');
set add table (set id=1, origin=1, id=2, fully qualified name = 'public.my_table2', comment = 'Table 2');
set add table (set id=1, origin=1, id=3, fully qualified name = 'public.my_table3', comment = 'Table 3');

store node (id=2, comment = 'Slave node', event node=1);
store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER password=$PASSWORD');
store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER password=$PASSWORD');
_EOF_
  • Start the replication, again with a slonik script:
#!/bin/sh
CLUSTERNAME=db_cluster
MASTERDBNAME=mydb
SLAVEDBNAME=mydb
MASTERHOST=psql01.example.com
SLAVEHOST=psql02.example.com
REPLICATIONUSER=slony
PASSWORD=mypassword

slonik <<_EOF_
cluster name = $CLUSTERNAME;

node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER password=$PASSWORD';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER password=$PASSWORD';

subscribe set ( id = 1, provider = 1, receiver = 2, forward = yes);
_EOF_
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s