[pgsql-jp: 34352] Re: pgpool 2.3.1 で縮退運転に失敗します

[Ishikawa Tomoyuki]

石川です。

> 1) PostgreSQLのバージョンはいくつですか？

pgpool     2.3.1（パッチ適用）
PostgreSQL 7.4.3


> 2) -n -d オプションを付けて詳しいデバッグログを見せてください．

前回のテスト内容に合わせてログを記述しました。

０．pgpool とMaster, Secondary のpostmaster を起動。

１．Master 側を停止した場合

１．１．pgpool 経由でデータベースにアクセスする
psql -p 9999
---
DEBUG: pid 550: I am 550 accept fd 6
DEBUG: pid 550: Protocol Major: 3 Minor: 0 database: postgres user: postgres
DEBUG: pid 550: name: client_encoding value: SQL_ASCII
DEBUG: pid 550: name: DateStyle value: ISO, MDY
DEBUG: pid 550: name: is_superuser value: on
DEBUG: pid 550: name: server_version value: 7.4.3
DEBUG: pid 550: name: session_authorization value: postgres
DEBUG: pid 550: read kind from backend pending data Z len: 5 po: 160
DEBUG: pid 550: ReadyForQuery: message length: 5
DEBUG: pid 550: ReadyForQuery: transaction state: I
---

１．２．Kill -QUIT でMaster 側のpostmaster を停止
---
DEBUG: pid 550: read kind from backend N
DEBUG: pid 550: pool_process_query: waiting for secondary for data ready
ERROR: pid 550: pool_check_fd: data is not ready tp->tv_sec 5 tp->tp_usec 0
ERROR: pid 550: pool_process_query: secondary data is not ready at
synchronous p
oint. abort this session
ERROR: pid 550: pool_process_query: kind does not match between backends
master(
N) secondary()
---

１．３．データベースへアクセスするが、反応が返ってこない
---
postgres=# select * from test;

（このコマンドを実行して30分間待ったが応答なし）
（デバッグモードにも応答なし）


２．Secondary 側を停止した場合

２．１．pgpool 経由でデータベースにアクセスする
psql -p 9999
---
DEBUG: pid 349: I am 349 accept fd 6
DEBUG: pid 349: Protocol Major: 3 Minor: 0 database: postgres user: postgres
DEBUG: pid 349: name: client_encoding value: SQL_ASCII
DEBUG: pid 349: name: DateStyle value: ISO, MDY
DEBUG: pid 349: name: is_superuser value: on
DEBUG: pid 349: name: server_version value: 7.4.3
DEBUG: pid 349: name: session_authorization value: postgres
DEBUG: pid 349: read kind from backend pending data Z len: 5 po: 160
DEBUG: pid 349: ReadyForQuery: message length: 5
DEBUG: pid 349: ReadyForQuery: transaction state: I
---

２．２．Kill -QUIT でSecondary 側のpostmaster を停止

２．３．データベースへアクセスする
・１回目：失敗
---
postgres=# select * from test;
ERROR:  kind mismatch between backends
HINT:  check data consistency between master and secondary
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
---
DEBUG: pid 349: read kind from frontend Q(51)
DEBUG: pid 349: Query: select * from test;
DEBUG: pid 349: read kind from backend T
DEBUG: pid 349: pool_process_query: waiting for secondary for data ready
ERROR: pid 349: pool_process_query: kind does not match between backends
master(
T) secondary(N)
DEBUG: pid 349: len:386
LOG: pid 349: do_child: exits with status 1 due to error
DEBUG: pid 348: I am 348 accept fd 6
DEBUG: pid 348: Protocol Major: 3 Minor: 0 database: postgres user: postgres
DEBUG: pid 317: child 349 exits with status 256 by signal 0
ERROR: pid 348: connect_inet_domain_socket: connect() failed: Connection
refused
LOG: pid 348: notice_backend_error: master: 0 fail over request from pid 348
DEBUG: pid 353: I am 353
DEBUG: pid 317: failover_handler called
LOG: pid 317: starting degeneration. shutdown secondary host
Secondary_Host(5432)
---

・２回目：失敗
---
ishikawa$ psql -p 9999
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
---
DEBUG: pid 353: I am 353 accept fd 6
DEBUG: pid 353: Protocol Major: 3 Minor: 0 database: postgres user: postgres
ERROR: pid 353: connect_inet_domain_socket: connect() failed: Connection
refused
LOG: pid 353: notice_backend_error: master: 0 fail over request from pid 353
User Signal 2

（デバッグモードが解除される）
---


> 3) pgpool.confの内容を見せてください．

初期設定を修正したのは下記の箇所です。

listen_address = '*'
backend_host_name = 'Master_Host'
secondary_backend_host_name = 'Secondary_Host'
secondary_backend_port = 5432
replication_mode = true
logdir = '/var/log/pgpool'


以上です。
よろしくお願いします。