[pgcluster: 1012] Re: スレーブからの更新がマスタに反映しない

[中野浩久＠三協]

自己レスです

pgreplicateのポートを10000に変更し、さらに
両端末の設定ファイル（cluster.conf,pgreplicate.conf)の
<Host_Name>をFQDNに書き直したところ
一応反映されるようになりました

が、test2のpsqlからデータの変更をすると
行ったきりになり戻ってこない状態になります
仕方なく強制終了し、データの確認をしたところ
test1にとりあえず複製はされていました

この状態ではスレーブ側から実質更新が行えない状態ですが
何故このような状況になるのかわかりません

その際のpgreplicateのログを添付させていただきますので
よろしくお願いします

2007-11-09 17:50:48 [2022] DEBUG:PGRread_packet():PG_read_query returns 
insert into test1 values ('1','from test2').
2007-11-09 17:50:48 [2022] DEBUG:PGRreturn_result():PGRreturn_result[1]
2007-11-09 17:50:48 [2022] DEBUG:PGRreturn_result():128 send
2007-11-09 17:50:48 [2022] DEBUG:PGRread_packet():PG_read_query returns 
insert into test1 values ('1','from test2').
2007-11-09 17:50:48 [2022] DEBUG:PGRdo_replicate():query :: insert into 
test1 values ('1','from test2')
2007-11-09 17:50:48 [2022] DEBUG:cmdSts=Q
2007-11-09 17:50:48 [2022] DEBUG:cmdType=I
2007-11-09 17:50:48 [2022] DEBUG:rlog=0
2007-11-09 17:50:48 [2022] DEBUG:port=5432
2007-11-09 17:50:48 [2022] DEBUG:pid=1443
2007-11-09 17:50:48 [2022] DEBUG:from_host=127.0.0.1
2007-11-09 17:50:48 [2022] DEBUG:dbName=testdb
2007-11-09 17:50:48 [2022] DEBUG:userName=postgres
2007-11-09 17:50:48 [2022] DEBUG:recieve sec=1194598248
2007-11-09 17:50:48 [2022] DEBUG:recieve usec=458214
2007-11-09 17:50:48 [2022] DEBUG:query_size=43
2007-11-09 17:50:48 [2022] DEBUG:request_id=1
2007-11-09 17:50:48 [2022] DEBUG:replicate_id=0
2007-11-09 17:50:48 [2022] DEBUG:recovery_status=0
2007-11-09 17:50:48 [2022] DEBUG:query=insert into test1 values ('1','from 
test2')
2007-11-09 17:50:48 [2022] DEBUG:sem_lock [1] req
2007-11-09 17:50:48 [2022] DEBUG:sem_lock [1] got it
2007-11-09 17:50:48 [2022] DEBUG:pgr_createConn():PQsetdbLogin 
host[192.168.0.51] port[5432] db[testdb] user[postgres]
2007-11-09 17:50:48 [2022] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2007-11-09 17:50:48 [2022] DEBUG:start thread_send_cluster()
2007-11-09 17:50:48 [2022] 
DEBUG:send_replicate_packet_to_server():sync_command(SELECT 
PGR_SYSTEM_COMMAND_FUNCTION(3,1194598248,458214,15,1,3) )
2007-11-09 17:50:48 [2022] DEBUG:pgr_createConn():PQsetdbLogin 
host[192.168.0.52] port[5432] db[testdb] user[postgres]
2007-11-09 17:50:48 [2022] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2007-11-09 17:50:48 [2022] DEBUG:start thread_send_cluster()
2007-11-09 17:50:48 [2022] 
DEBUG:send_replicate_packet_to_server():sync_command(SELECT 
PGR_SYSTEM_COMMAND_FUNCTION(3,1194598248,458214,15,1,3) )
2007-11-09 17:50:48 [2022] 
DEBUG:send_replicate_packet_to_server():sync_command(SELECT 
PGR_SYSTEM_COMMAND_FUNCTION(8,3,0,1) )
2007-11-09 17:50:48 [2022] 
DEBUG:send_replicate_packet_to_server():sync_command(SELECT 
PGR_SYSTEM_COMMAND_FUNCTION(8,3,0,1) )
2007-11-09 17:50:48 [2022] 
DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2007-11-09 17:50:48 [2022] 
DEBUG:send_replicate_packet_to_server():sync_command returns SYSTEM_COMMAND
2007-11-09 17:50:48 [2022] DEBUG:send_replicate_packet_to_server():PQexec 
send :insert into test1 values ('1','from test2')
2007-11-09 17:50:48 [2022] DEBUG:send_replicate_packet_to_server():PQexec 
returns :INSERT 199100 1
2007-11-09 17:50:48 [2022] DEBUG:thread_send_cluster():return value from 
send_replicate_packet_to_server() is 0
2007-11-09 17:50:48 [2022] DEBUG:thread_send_cluster():pthread_exit[1]
2007-11-09 17:50:48 [2022] DEBUG:send_replicate_packet_to_server():PQexec 
send :insert into test1 values ('1','from test2')
2007-11-09 17:50:48 [2022] DEBUG:send_replicate_packet_to_server():PQexec 
returns :INSERT 117198 1
2007-11-09 17:50:48 [2022] DEBUG:thread_send_cluster():return value from 
send_replicate_packet_to_server() is 0
2007-11-09 17:50:48 [2022] DEBUG:thread_send_cluster():pthread_exit[0]
2007-11-09 17:50:48 [2022] DEBUG:sem_unlock[1]

ここでtest2のpsqlを強制終了しました

2007-11-09 17:54:37 [2020] DEBUG:PGRread_packet():PG_read_query returns .
2007-11-09 17:54:37 [2020] DEBUG:replicate_loop():session closed
2007-11-09 17:54:37 [2020] DEBUG:PGRdo_replicate():query :: 
PGR_CLOSE_CONNECTION
2007-11-09 17:54:37 [2020] DEBUG:cmdSts=O
2007-11-09 17:54:37 [2020] DEBUG:cmdType=x
2007-11-09 17:54:37 [2020] DEBUG:rlog=0
2007-11-09 17:54:37 [2020] DEBUG:port=5432
2007-11-09 17:54:37 [2020] DEBUG:pid=1950
2007-11-09 17:54:37 [2020] DEBUG:from_host=192.168.0.51
2007-11-09 17:54:37 [2020] DEBUG:dbName=testdb
2007-11-09 17:54:37 [2020] DEBUG:userName=postgres
2007-11-09 17:54:37 [2020] DEBUG:recieve sec=1194598477
2007-11-09 17:54:37 [2020] DEBUG:recieve usec=959653
2007-11-09 17:54:37 [2020] DEBUG:query_size=21
2007-11-09 17:54:37 [2020] DEBUG:request_id=1
2007-11-09 17:54:37 [2020] DEBUG:replicate_id=0
2007-11-09 17:54:37 [2020] DEBUG:recovery_status=0
2007-11-09 17:54:37 [2020] DEBUG:query=PGR_CLOSE_CONNECTION
2007-11-09 17:54:37 [2020] DEBUG:sem_lock [1] req
2007-11-09 17:54:37 [2020] DEBUG:sem_lock [1] got it
2007-11-09 17:54:37 [2020] DEBUG:same host
2007-11-09 17:54:37 [2020] DEBUG:pgr_createConn():PQsetdbLogin 
host[test2.kk-sankyo.local] port[5432] db[testdb] user[postgres]
2007-11-09 17:54:37 [2020] DEBUG:pgr_createConn():PQsetdbLogin ok!!
2007-11-09 17:54:37 [2020] DEBUG:replicate_packet_send_internal():connect 
db:testdb port:5432 user:postgres host:test2.kk-sankyo.local 
query:PGR_CLOSE_CONNECTION
2007-11-09 17:54:37 [2020] DEBUG:start thread_send_cluster()
2007-11-09 17:54:37 [2020] DEBUG:PGR_Notice_Abort():sync_command(SELECT 
PGR_SYSTEM_COMMAND_FUNCTION(7))
2007-11-09 17:54:37 [2020] DEBUG:deleteTransactionTbl():
2007-11-09 17:54:37 [2020] DEBUG:sem_unlock[1]
2007-11-09 17:54:37 [2020] DEBUG:replicate_loop():replicate loop exit
2007-11-09 17:54:54 [1908] DEBUG:quick_exit:signo = 2
2007-11-09 17:54:54 [2022] DEBUG:quick_exit:signo = 2

>
>
>> こんにちは。
>>
>> test2にクエリーを投げた時の
>> レプリケーションサーバのデバッグログを見れば
>> 何か判るかもしれません。
>> レプリケーションサーバを-vnオプションをつけて起動してみてください。
>>
>> 三谷
>>
>