[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 メーリングリストの案内