Slony-i Data Synchronization for PostgreSQL

Original--http://www.tuicool.com/articles/mmvarf

Let's talk about the limitations of Slony:

1. DDL actions are not copied to;

2. If you want to use Slony to synchronize data, the table must be a primary key

The content of everyone to see the original , personal feeling summed up very well!

Here are the maintenance for slony; custom scripts

Includes service startup/shutdown, and Slony set delete/install.

#!/bin/sh

SLONIK=/opt/pgsql/bin/slonik

SLON=/opt/pgsql/bin/slon

CLUSTER_NAME=lottu_cluster

MASTERDBNAME=masterdb

SLAVEDBNAME=slavedb

MASTERHOST=192.168.8.121

SLAVEHOST=192.168.8.120

REPLICATIONUSER=postgres

PASSWORD=li0924

uninstall()

{

   $SLONIK << _EOF_  

       cluster name = $CLUSTER_NAME;

       

       node 1 admin conninfo = ‘dbname=$MASTERDBNAME  host=$MASTERHOST user=$REPLICATIONUSER password=$PASSWORD‘;

       node 2 admin conninfo = ‘dbname=$SLAVEDBNAME  host=$SLAVEHOST user=$REPLICATIONUSER password=$PASSWORD‘;

 

       uninstall node (id = 2);

       uninstall node (id = 1);

_EOF_

}

install()

{

   $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 eachnode 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 password=$PASSWORD‘;

   node 2 admin conninfo = ‘dbname=$SLAVEDBNAME  host=$SLAVEHOST user=$REPLICATIONUSER password=$PASSWORD‘;

   #--

   # init the first node.  Its id MUST be 1.  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 master or origin of

   # the set is node 1.

   #--

   create set (id=1, origin=1, comment=‘All lottu tables‘);

   set add table (set id=1, origin=1, id=1,

                  fully qualified name = ‘public.lottu‘,

                  comment=‘lottu table‘);

                 

   # set add sequence (set id=1, origin = 1, id = 1,

   #               fully qualified name = ‘public.t1_id_seq‘,

   #               comment = ‘t1 id sequence‘);

   #--

   # 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 password=$PASSWORD‘);

   store path (server = 2, client = 1, conninfo=‘dbname=$SLAVEDBNAME  host=$SLAVEHOST user=$REPLICATIONUSER password=$PASSWORD‘);

_EOF_

}

start()

{

   $SLON $CLUSTER_NAME "dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER password=$PASSWORD" >> /home/postgresql/master.log &

 #  $SLON $CLUSTER_NAME "$SLAVE" >> /home/postgresql/slave.log &

}

stop()

{

   # killall slon

    kill -9 `ps axu|grep ‘dbname=masterdb‘ |grep -v grep|awk ‘{print $2}‘`

}

case $1 in

   ‘install‘)

       install

       ;;

   ‘uninstall‘)

       uninstall

       ;;

   ‘start‘)

       start

       ;;

   ‘stop‘)

       stop

       ;;

   *)

       echo "usage: $0 {install|uninstall|start|stop} "

       ;;

esac

Slony-i Data Synchronization for PostgreSQL