Slony-I
Rev.2を表示中。最新版はこちら。
●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には切り戻しという発想がないらしく、切り替わったら切り替わりっぱなしらしい。
