PostgreSQL的 Slony-I 資料同步

標籤：

原文--http://www.tuicool.com/articles/mMvARf

先談談slony的局限性：

1. DDL動作是不會被複製到；

2. 如果想使用slony來同步資料，表必須是帶有主鍵的

內容大家看原文；個人感覺總結的很好！

下面是為slony維護；定製的指令碼

包括了服務啟動/關閉；以及slony set刪除/安裝。

#!/bin/shSLONIK=/opt/pgsql/bin/slonikSLON=/opt/pgsql/bin/slonCLUSTER_NAME=lottu_clusterMASTERDBNAME=masterdbSLAVEDBNAME=slavedbMASTERHOST=192.168.8.121SLAVEHOST=192.168.8.120REPLICATIONUSER=postgresPASSWORD=li0924uninstall(){    $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

 

PostgreSQL的 Slony-I 資料同步