雑なメモ(無保証)

Slony-I


●Slony-Iインストール手順
(参考:http://www.interdb.jp/techinfo/web+db48/slony.html)


※rootで実行
mkdir /usr/local/slony1
chown postgres:postgres /usr/local/slony1
cd /usr/local/slony1/
wget http://www.slony.info/downloads/1.2/source/slony1-1.2.21.tar.bz2
chown postgres:postgres slony1-1.2.21.tar.bz2

mkdir /var/log/slony1
chown postgres:postgres /var/log/slony1


※postgresで実行
su - postgres
cd /usr/local/slony1
tar xvfj slony1-1.2.21.tar.bz2
cd slony1-1.2.21
./configure --prefix=/usr/local/slony1 --with-perltools=/usr/local/slony1/bin --with-pgconfigdir=/usr/local/pgsql/bin

 ./configure --prefix=/usr/local/slony1 --with-perltools=/usr/local/slony1/bin --with-pgconfigdir=/var/pgsql/bin --with-pglibdir=/var/pgsql/lib

make && make install


○テスト用DB作成(マスタ側のみ)
su - postgres
createdb slonydb
createlang plpgsql slonydb

psql slonydb -q
 CREATE TABLE tbl_pkey (id int primary key, data int);
 CREATE TABLE tbl_uniq (id int NOT NULL, data int, UNIQUE(id));
 CREATE TABLE tbl (id int, data int);


 CREATE TABLE tbl_pkey_1 (id int primary key, data int);
 CREATE TABLE tbl_uniq_1 (id int NOT NULL, data int, UNIQUE(id));
 CREATE TABLE tbl_1 (id int, data int);
 \q


 CREATE TABLE tbl_pkey_11 (id int primary key, data int);
 CREATE TABLE tbl_uniq_11 (id int NOT NULL, data int, UNIQUE(id));
 CREATE TABLE tbl_11 (id int, data int);
 


※データベーススキーマをスレーブ側にコピー
 *スレーブ側でbashrcにpostgresql関連のパス等が設定されている必要がある

pg_dump -s -C slonydb | ssh 192.168.10.102 psql

 pg_dump -s -C slonydb | ssh 192.168.11.193 psql
 pg_dump -p5433 -s -C slonydb_11 | ssh 192.168.11.193 psql -p5433

※データを1行挿入して、マスタとスレーブを非同期状態にしておく
psql slonydb -q
 INSERT INTO tbl VALUES (1,1);
 SELECT * FROM tbl;
  id | data
 ----+------
   1 |    1
 (1 row)

 \q

 

●Slony-I設定
Slony-Iはレプリケーションを行う全てのテーブルにユニークなキーが必要。
それらがない場合には、Slony-Iが自動的にシリアルナンバーを割り振る。

・slon_tools.conf

cd /usr/local/slony1/etc
cp slon_tools.conf-sample slon_tools.conf
※クラスタ名を設定
 $CLUSTER_NAME = 'slony_test';
※マスターサーバの番号
 $MASTERNODE = 1;
※ノード1のサーバ情報を設定
 add_node(node     => 1,
          host     => 'postgres01',
          dbname   => 'slonydb',
          port     => 5432,
          user     => 'postgres',
          password => 'postgres');
※ノード2のサーバ情報を設定
 add_node(node     => 2,
          host     => 'postgres02',
          dbname   => 'slonydb',
          port     => 5432,
          user     => 'postgres',
          password => '');
※使用しないものはコメントアウト
 #    add_node(node     => 3,
 #            host     => 'server3',
 #            dbname   => 'database',
 #            port     => 5432,
 #            user     => 'postgres',
 #            password => '');


 #    add_node(node     => 4,
 #            parent   => 3,
 #            host     => 'server4',
 #            dbname   => 'database',
 #            port     => 5432,
 #            user     => 'postgres',
 #            password => '');
 #

 

# The $SLONY_SETS variable contains information about all of the sets
# in your cluster.

$SLONY_SETS = {

    # A unique name for the set
    "set1" => {

        # The set_id, also unique
        "set_id" => 1,

        # Uncomment the following line to change the origin
        # (a.k.a. master) for the set.  The default is $MASTERNODE.
        #
        # "origin" => 1,

        # If this is set to 1, table and sequence names will be folded to lower-case
        # to match the way that PostgreSQL handles unquoted names.
        # For example, CREATE TABLE ACCOUNT(...) actually turns into CREATE TABLE account(...);
        # unless you put quotes around the table name
        # Slony always quotes object names, so you may get a mis-match between the table-name
        # as PostgreSQL understands it, and as Slony represents it.
        # default value is 0
        #
        # foldCase => 0,

        # The first ID to use for tables and sequences that are added
        # to the replication cluster.  This must be unique across the
        # cluster.
        #
        # TODO: This should be determined automatically, which can be
        # done fairly easily in most cases using psql.  create_set
        # should derive it, and give an option to override it with a
        # specific value.
        "table_id"    => 1,
        "sequence_id" => 1,

        # This array contains a list of tables that already have
        # primary keys.
※レプリケーションを行うテーブルのうち、プライマリーキーをもつテーブルを列挙
        "pkeyedtables" => [
                           'tbl_pkey',
                           ],

        # For tables that have unique not null keys, but no primary
        # key, enter their names and indexes here.
※レプリケーションを行うテーブルのうち、ある列がNOT NULL且つユニークキーとなるテーブルがある場合、テーブル名とユニークキーを列挙。
        "keyedtables" => {
            'tbl_uniq' => 'tbl_uniq_id_key',
        },

        # If a table does not have a suitable key or set of keys that
        # can act as a primary key, Slony can add one.
        #
        # Note: The Slony development team does not recomment this
        # approach -- you should create your own primary keys instead.
※レプリケーションを行うテーブルのうち、プライマリーキーもユニークキーも持たないテーブルを列挙。
        "serialtables" => ["tbl"],

        # Sequences that need to be replicated should be entered here.
※使わないものは空にする
        "sequences" => ['',
                        '',
                        ],
    },

    "set2" => {
        "set_id"       => 2,
        "table_id"     => 6,
        "sequence_id"  => 3,
        "pkeyedtables" => ["table6"],
        "keyedtables"  => {},
        "serialtables" => [],
        "sequences"    => [],
    },

};


・作成したslon_tools.confファイルはスレーブ側にも保存
cd /usr/local/slony1/etc/
scp slon_tools.conf 192.168.10.102:/usr/local/slony1/etc/
 scp slon_tools_1.conf 192.168.11.193:/usr/local/slony1/etc/
 scp slon_tools_11.conf 192.168.11.193:/usr/local/slony1/etc/


●クラスタの初期化
cd /usr/local/slony1/bin
./slonik_init_cluster | ./slonik
 ./slonik_init_cluster --config /usr/local/slony1/etc/slon_tools_1.conf | ./slonik
 <stdin>:10: Set up replication nodes
 <stdin>:13: Next: configure paths for each node/origin
 <stdin>:16: Replication nodes prepared
 <stdin>:17: Please start a slon replication daemon for each node

 

●セットset1の初期化
cd /usr/local/slony1/bin
./slonik_create_set set1 | ./slonik
 ./slonik_create_set set1 --config /usr/local/slony1/etc/slon_tools_1.conf | ./slonik
 <stdin>:6:   Adding unique key to table public.tbl...
 <stdin>:21: Subscription set 1 created
 <stdin>:22: Adding tables to the subscription set
 <stdin>:26: Add unkeyed table public.tbl
 <stdin>:30: Add primary keyed table public.tbl_pkey
 <stdin>:34: Add candidate primary keyed table public.tbl_uniq
 <stdin>:37: Adding sequences to the subscription set
 <stdin>:38: All tables added


●サブスクライバの設定
cd /usr/local/slony1/bin
./slonik_subscribe_set set1 2 | ./slonik
 ./slonik_subscribe_set --config /usr/local/slony1/etc/slon_tools_1.conf set1 2 | ./slonik
 <stdin>:10: Subscribed nodes to set 1

 

●slonの起動
cd /usr/local/slony1/bin
./slon_start 1  ./slon_start --config /usr/local/slony1/etc/slon_tools_1.conf 1
 Invoke slon for node 1 - /usr/local/slony1/bin/slon -s 1000 -d2 slony_test 'host=postgres01 dbname=slonydb user=postgres port=5432 password=postgres' 2>&1 > /var/log/slony1/slony1/node1/slonydb-2010-06-30_12:56:56.log &
 Slon successfully started for cluster slony_test, node node1
 PID [8429]
 Start the watchdog process as well...

・スレーブ側も起動
ssh 192.168.10.102
cd /usr/local/slony1/bin
./slon_start 2  ./slon_start --config /usr/local/slony1/etc/slon_tools_1.conf 1
 Invoke slon for node 2 - /usr/local/slony1/bin/slon -s 1000 -d2 slony_test 'host=postgres02 dbname=slonydb user=postgres port=5432' 2>&1 > /var/log/slony1/slony1/node2/slonydb-2010-06-30_12:59:00.log &
 Slon successfully started for cluster slony_test, node node2
 PID [7570]
 Start the watchdog process as well...
 [postgres@postgres02 bin]$ NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "tbl__Slony-I_slony_test_rowID_key" for table "tbl"
 CONTEXT:  SQL statement "alter table only "public"."tbl" add unique ("_Slony-I_slony_test_rowID");"
 PL/pgSQL function "determineattkindserial" line 54 at EXECUTE statement
 NOTICE:  truncate of "public"."tbl" succeeded
 NOTICE:  truncate of "public"."tbl_pkey" succeeded
 NOTICE:  truncate of "public"."tbl_uniq" succeeded


●動作確認
・マスター側
psql slonydb -q
INSERT INTO tbl VALUES (2,2);
SELECT * from tbl;

・スレーブ側
psql slonydb -q
SELECT * FROM tbl;
  id | data | _Slony-I_slony_test_rowID
 ----+------+---------------------------
   1 |    1 |          1000000000000001   ←起動時レプリケーションされたもの
   2 |    2 |          1000000000000002   ←起動後に追加されたレコードがレプリケーションされた
 (2 rows)      ↓   
        ・ユニークキーを定義しなかったので、 Slony-Iが自動的に"_Slony-I_slony_test_rowID"というユニークキーを設定している

UPDATE tbl SET data = 100 WHERE id = 1;
ERROR:  Slony-I: Table tbl is replicated and cannot be modified on a subscriber node
※更新系SQL(INSERT、UPDATE、DELETE)はエラーとなって実行できない

 

------------------------------------------------------------------------
操作
●スイッチオーバー
cd /usr/local/slony1/bin
./slonik_move_set set1 1 2 | ./slonik   ※set1のマスタを1から2へ
 <stdin>:4: Locking down set 1 on node 1
 <stdin>:6: Locked down - moving it
 <stdin>:8: Replication set 1 moved from node 1 to 2.  Remember to
 <stdin>:9: update your configuration file, if necessary, to note the new location
 <stdin>:10: for the set.

※ノード1(元マスタ)で更新できなくなる
psql slonydb -q
UPDATE tbl SET data = 100 WHERE id = 1;
ERROR:  Slony-I: Table tbl is replicated and cannot be modified on a subscriber node

※ノード2(新マスタ)で更新できる
psql slonydb -q
UPDATE tbl SET data = 100 WHERE id = 1;
SELECT * from tbl;
 id | data | _Slony-I_slony_test_rowID
----+------+---------------------------
  2 |    2 |          1000000000000002
  1 |  100 |          1000000000000001
(2 rows)


・もとに戻す
cd /usr/local/slony1/bin
./slonik_move_set set1 2 1 | ./slonik   ※set1のマスタを2から1へ

 

 


------------------------------------------------------------------------
●フェールオーバー
※マスタ側をダウンさせる
pg_ctl -D /usr/local/pgsql/data -m immediate stop

※スレーブ側で操作
cd /usr/local/slony1/bin
./slonik_failover 1 2 | ./slonik   ※引数:ウンしたノード、代わりにマスターになるノード
 INFO: calling failedNode(1,2) on node 1
 <stdin>:4: NOTICE:  failedNode: set 1 has no other direct receivers - move now
 INFO: Waiting for slon engines to restart
 INFO: Node with highest sync for set 1 is 2
 <stdin>:10: Replication sets originating on 1 failed over to 2

※マスター側のデーモンプロセスslonが起動してるなら、停止させる
cd /usr/local/slony1/bin
./slon_kill 1
 slon_kill.pl...   Killing all slon and slon_watchdog instances for the cluster slony_test
 1.  Kill slon watchdogs
 slon_watchdog for cluster slony_test killed - PID [8443]
 
 2. Kill slon processes
 slon_watchdog for cluster slony_test killed - PID [8428]
 slon_watchdog for cluster slony_test killed - PID [9428]

これでノード2だけの縮退運転モード

 

 

------------------------------------------------------------------------
●スレーブのオンラインリカバリ
※スレーブ側(node2)のPostgreSQLサーバとslonデーモンを停止
※スレーブ側で操作
pg_ctl -D /usr/local/pgsql/data -m immediate stop
cd /usr/local/slony1/bin
slon_kill 2
 slon_kill.pl...   Killing all slon and slon_watchdog instances for the cluster slony_test
 1.  Kill slon watchdogs
 slon_watchdog for cluster slony_test killed - PID [7583]
 
 2. Kill slon processes
 slon_watchdog for cluster slony_test killed - PID [7569]
 slon_watchdog for cluster slony_test killed - PID [8514]


※マスター側でノード2を削除
※マスタ側で操作
cd /usr/local/slony1/bin
./slonik_drop_node 2 | ./slonik
 <stdin>:10: dropped node 2 cluster


※スレーブ側の再構築
※スレーブ側で操作
※テーブルスキーマもすべて再作成
pg_ctl -D /usr/local/pgsql/data start
dropdb slonydb
createdb slonydb
createlang plpgsql slonydb
psql slonydb -q
CREATE TABLE tbl_pkey (id int primary key, data int);
CREATE TABLE tbl_uniq (id int NOT NULL, data int, UNIQUE(id));
CREATE TABLE tbl (id int, data int);

※マスター側の作業
※マスタ側で操作
※ノード2を再設定
cd /usr/local/slony1/bin
./slonik_store_node 2 | ./slonik
 <stdin>:7: Set up replication nodes
 <stdin>:10: Next: configure paths for each node/origin
 <stdin>:13: Replication nodes prepared
 <stdin>:14: Please start a slon replication daemon for each node

※サブスクライバも再設定
※マスタ側で操作
cd /usr/local/slony1/bin
./slonik_subscribe_set set1 2 | ./slonik
<stdin>:10: Subscribed nodes to set 1


※スレーブ側slonの再起動
※スレーブ側で操作
cd /usr/local/slony1/bin
./slon_start 2
 Invoke slon for node 2 - /usr/local/slony1/bin/slon -s 1000 -d2 slony_test 'host=postgres02 dbname=slonydb user=postgres port=5432' 2>&1 > /var/log/slony1/slony1/node2/slonydb-2010-06-30_13:55:55.log &
 Slon successfully started for cluster slony_test, node node2
 PID [8805]
 Start the watchdog process as well...
 NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "tbl__Slony-I_slony_test_rowID_key" for table "tbl"
 CONTEXT:  SQL statement "alter table only "public"."tbl" add unique ("_Slony-I_slony_test_rowID");"
         PL/pgSQL function "determineattkindserial" line 54 at EXECUTE statement
 NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "tbl__Slony-I_slony_test_rowID_key" for table "tbl"
 CONTEXT:  SQL statement "alter table only "public"."tbl" add unique ("_Slony-I_slony_test_rowID");"
 PL/pgSQL function "determineattkindserial" line 54 at EXECUTE statement
 NOTICE:  truncate of "public"."tbl" succeeded
 NOTICE:  truncate of "public"."tbl" succeeded
 NOTICE:  truncate of "public"."tbl_pkey" succeeded
 NOTICE:  truncate of "public"."tbl_pkey" succeeded
 NOTICE:  truncate of "public"."tbl_uniq" succeeded
 NOTICE:  truncate of "public"."tbl_uniq" succeeded

 

※Slonyには切り戻しという発想がないらしく、切り替わったら切り替わりっぱなしらしい。

※Slonyはスキーマ変更はレプリケートしない。

設定をやり直したり消したりするときは以下で全部削除。
slonik_uninstall_nodes | slonik

セットだけ削除するときはこれ。
slonik_drop_set 1 | slonik

 

 

 


最終更新 2011/05/05 20:04:22 - llinfo_arp
(2011/05/05 15:46:43 作成)


Amazon