[pgpool-general-jp: 352] 不整合検知
スクゥ 陶山 泰
suyama @ skoo.co.jp
2008年 1月 11日 (金) 11:56:52 JST
陶山です。
最新版 pgpool-II(2.0.2の直前) をインストールしテストしています。
レプリケーションは、できているのですが、2つのノードの不整合を検知で
きていないのではないかと思われます。
テスト環境
bench_replication データベース
branches 表
pgpool ポート 5432(サーバA)
ノードA:PostgreSQL ポート 5435(サーバA)
ノードB:PostgreSQL ポート 5432(サーバB)
pgpool.conf の主要パラメタ
insert_lock = true
pgpool2_hostname = 'pgmain'
replication_mode = true
replication_strict = true
replication_stop_on_mismatch = true
health_check_period = 10
health_check_user = 'postgres'
load_balance_mode = false
テスト
psql -p 5432 bench_replication
INSERT INTO branches ( bid, bbalance ) VALUES ( 99, 99 );
INSERT INTO branches ( bid, bbalance ) VALUES ( 98, 98 );
INSERT INTO branches ( bid, bbalance ) VALUES ( 97, 97 );
INSERT INTO branches ( bid, bbalance ) VALUES ( 96, 96 );
しておいて、
サーバBで、ノードBに対し
psql -p 5432 bench_replication
DELETE FROM branches WHERE bid = 99;
これでノードAには元もとのサンプルデータ(1,0) と合わせ5行
ノードBには、4行のデータがあることになる
ここで、サーバAで pgpool-II 経由で 問合せを行う
psql -p 5432 bench_replication
SELECT * FROM branches;
ノードBからの問合せ結果とノードAからの問合せ結果が異なるはずだが、
bid | bbalance | filler
-----+----------+--------
1 | 0 |
99 | 99 |
98 | 98 |
97 | 97 |
96 | 96 |
(5 rows)
となり、不整合状態が表示されない。
pgpoolAdmin でも何のエラー表示もされない
ログを下記に載せますが、検知できないと思っているのは何か私の勘違い
があるのでしょうか?
それとも設定が悪いのでしょうか?
以下は、ログです
−− ここから −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
2008-01-11 11:34:47 DEBUG: pid 3114: key: load_balance_mode
2008-01-11 11:34:47 DEBUG: pid 3114: value: false kind: 1
中略
2008-01-11 11:36:24 DEBUG: pid 3146: I am 3146 accept fd 0
2008-01-11 11:36:24 DEBUG: pid 3146: Protocol Major: 3 Minor: 0 database: bench_replicatio
n user: postgres
2008-01-11 11:36:24 DEBUG: pid 3146: pool_send_auth_ok: send pid 3169 to frontend
2008-01-11 11:36:24 DEBUG: pid 3146: select_load_balancing_node: selected backend id is 1
2008-01-11 11:36:27 DEBUG: pid 3114: starting health checking
2008-01-11 11:36:27 DEBUG: pid 3114: health_check: 0 the DB node status: 2
2008-01-11 11:36:27 DEBUG: pid 3114: health_check: 1 the DB node status: 2
2008-01-11 11:36:34 DEBUG: pid 3146: read kind from frontend Q(51)
2008-01-11 11:36:34 DEBUG: pid 3146: statement2: select * from branches;
2008-01-11 11:36:34 DEBUG: pid 3146: waiting for backend 0 completing the query
2008-01-11 11:36:34 DEBUG: pid 3146: select_load_balancing_node: selected backend id is 0
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend T
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: T
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend D
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: D
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend D
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: D
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend D
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: D
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend D
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: D
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend D
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: D
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend C
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: C
2008-01-11 11:36:34 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend Z
NUM_BACKENDS: 1
2008-01-11 11:36:34 DEBUG: pid 3146: pool_process_query: kind from backend: Z
2008-01-11 11:36:34 DEBUG: pid 3146: pool_read_message_length: slot: 0 length: 5
2008-01-11 11:36:34 DEBUG: pid 3146: ReadyForQuery: message length: 5
2008-01-11 11:36:34 DEBUG: pid 3146: ReadyForQuery: transaction state: I
2008-01-11 11:36:34 DEBUG: pid 3146: end_load_balance: end load balance mode
2008-01-11 11:36:37 DEBUG: pid 3146: read kind from frontend X(58)
2008-01-11 11:36:37 DEBUG: pid 3146: statement2: RESET ALL
2008-01-11 11:36:37 DEBUG: pid 3146: waiting for backend 0 completing the query
2008-01-11 11:36:37 DEBUG: pid 3146: waiting for backend 1 completing the query
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend C
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 1 th backend C
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: pool_process_query: kind from backend: C
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend Z
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 1 th backend Z
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: pool_process_query: kind from backend: Z
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length: slot: 0 length: 5
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length: slot: 1 length: 5
2008-01-11 11:36:37 DEBUG: pid 3146: ReadyForQuery: message length: 5
2008-01-11 11:36:37 DEBUG: pid 3146: ReadyForQuery: transaction state: I
2008-01-11 11:36:37 DEBUG: pid 3146: statement2: SET SESSION AUTHORIZATION DEFAULT
2008-01-11 11:36:37 DEBUG: pid 3146: waiting for backend 0 completing the query
2008-01-11 11:36:37 DEBUG: pid 3146: waiting for backend 1 completing the query
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend S
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 1 th backend S
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: pool_process_query: kind from backend: S
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length2: master slot: 0 length: 20
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length2: master slot: 1 length: 20
2008-01-11 11:36:37 DEBUG: pid 3146: 0 th backend: name: is_superuser value: on
2008-01-11 11:36:37 DEBUG: pid 3146: 1 th backend: name: is_superuser value: on
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend S
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 1 th backend S
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: pool_process_query: kind from backend: S
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length2: master slot: 0 length: 35
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length2: master slot: 1 length: 35
2008-01-11 11:36:37 DEBUG: pid 3146: 0 th backend: name: session_authorization value: post
gres
2008-01-11 11:36:37 DEBUG: pid 3146: 1 th backend: name: session_authorization value: post
gres
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend C
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 1 th backend C
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: pool_process_query: kind from backend: C
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 0 th backend Z
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: read_kind_from_backend: read kind from 1 th backend Z
NUM_BACKENDS: 2
2008-01-11 11:36:37 DEBUG: pid 3146: pool_process_query: kind from backend: Z
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length: slot: 0 length: 5
2008-01-11 11:36:37 DEBUG: pid 3146: pool_read_message_length: slot: 1 length: 5
2008-01-11 11:36:37 DEBUG: pid 3146: ReadyForQuery: message length: 5
2008-01-11 11:36:37 DEBUG: pid 3146: ReadyForQuery: transaction state: I
2008-01-11 11:36:37 DEBUG: pid 3146: pool_connection_pool_timer: set close time 1200018997
2008-01-11 11:36:37 DEBUG: pid 3114: starting health checking
2008-01-11 11:36:37 DEBUG: pid 3114: health_check: 0 the DB node status: 2
2008-01-11 11:36:37 DEBUG: pid 3114: health_check: 1 the DB node status: 2
−− ここまで −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
pgpool-general-jp メーリングリストの案内