[pgcluster: 329] Re: クラスタサーバ上でのクエリについて
KIKAWADA Mitsukuni/黄川田光国
mitsukuni ＠ unixmagic.net
2004年 6月 3日 (木) 22:40:10 JST
きかわだです。
On Wed, 02 Jun 2004 12:07:11 +0900
mitani <mitani ＠ sraw.co.jp> wrote:
> 三谷＠広島です．
>
> > testdb=# INSERT INTO testdb (field2) values('test val5');
> > WARNING: This query is not permitted when all replication servers fell down
> レプリケーションサーバが繋がっていないというエラーですね．
> cluster.confとか，pg_hba.confとか，pgreplicate.confなどを確認してみてく
> ださい．
pgreplicate.confを調整したら動きました。
マルチホームなホスト環境なので、おそらく出口を間違えてクラスタサーバ
(もしくはレプリケータ)が見つからなかったのだと思います。
うまく動いた場合のpgreplicate.confを下記します。
bash-2.05$ cat data/pgreplicate.conf
#=============================================================
# PGReplicate configuration file
# for PGCluster-1.0.6cv14
#-------------------------------------------------------------
# file: pgreplicate.conf
#-------------------------------------------------------------
# This file controls:
# o which hosts & port are cluster server
# o which port use for replication request from cluster server
#=============================================================
#
#-------------------------------------------------------------
# A setup of Cluster DB(s)
#
# o Host_Name : The host name of Cluster DB.
# -- please write a host name by FQDN.
# -- do not write IP address.
# o Port : The connection port with postmaster.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> wt001112 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> wt001113 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> wt001114 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
#
#-------------------------------------------------------------
# A setup of Load Balance Server
#
# o Host_Name : The host name of a load balance server.
# -- please write a host name by FQDN.
# -- do not write IP address.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#-------------------------------------------------------------
<LoadBalance_Server_Info>
<Host_Name> wt001110 </Host_Name>
<Recovery_Port> 7780 </Recovery_Port>
</LoadBalance_Server_Info>
#
#------------------------------------------------------------
# A setup of the upper replication server for cascade connection.
#
# o Host_Name : The host name of Cluster DB.
# -- please write a host name by FQDN.
# -- do not write IP address.
# o Port : The connection port with postmaster.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#------------------------------------------------------------
<Replicate_Server_Info>
<Host_Name> wt001117 </Host_Name>
<Port> 8777 </Port>
<Recovery_Port> 7778 </Recovery_Port>
</Replicate_Server_Info>
#
#-------------------------------------------------------------
# A setup of a replication server
#
# o Replicate_Port : connection for reprication
# o Recovery_Port : connection for recovery
# o Response_mode : timing which returns a response
# normal -- return result of DB which received the query
# reliable -- return result after waiting for response of
# all Cluster DBs.
# o Use_Replication_Log : A replicating query is recorded
# on the remote replication server
# which is connected in the cascade.
# yes -- use replication log
# no -- not use replication log
#-------------------------------------------------------------
<Replication_Port> 8777 </Replication_Port>
<Recovery_Port> 7778 </Recovery_Port>
<Response_Mode> normal </Response_Mode>
<Use_Replication_Log> no </Use_Replication_Log>
うまく動いた場合のログは次のようになっていました。
DEBUG(PGRsem_unlock): replicate main: selected
DEBUG(replicate_loop): replicate_loop selected
DEBUG(PGRread_packet): query size=50
DEBUG(PGRread_packet): read[50] query[INSERT INTO test (field2 ) values('hogehogehoge6')]
DEBUG(PGRread_packet): query :: INSERT INTO test (field2 ) values('hogehogehoge6')
DEBUG(PGRreplicate_packet_send): cmdSts=Q
DEBUG(PGRreplicate_packet_send): cmdType=I
DEBUG(PGRreplicate_packet_send): port=5432
DEBUG(PGRreplicate_packet_send): pid=16433
DEBUG(PGRreplicate_packet_send): except_host=wt001112
DEBUG(PGRreplicate_packet_send): from_host=wt001112
DEBUG(PGRreplicate_packet_send): dbName=testdb
DEBUG(PGRreplicate_packet_send): userName=postgres
DEBUG(PGRreplicate_packet_send): recieve sec=1086267518
DEBUG(PGRreplicate_packet_send): recieve usec=785431
DEBUG(PGRreplicate_packet_send): query_size=50
DEBUG(PGRreplicate_packet_send): query=INSERT INTO test (field2 ) values('hogehogehoge6')
DEBUG(PGRreplicate_packet_send): useFlag[2]
DEBUG(PGRis_same_host): 5432 @ wt001112 return trigger
DEBUG(is_need_sync_time): sem_lock[1]
DEBUG(PGRreturn_result): PGRreturn_result[3,1086267518,785431]
DEBUG(PGRreturn_result): 128 send
DEBUG(PGRreturn_result): wait for answer
DEBUG(replicate_loop): wait replicate
DEBUG(PGRread_packet): query size=25
DEBUG(PGRread_packet): read[25] query[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG(PGRread_packet): answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG(PGRread_packet): QUERY DONE
DEBUG(PGRread_packet): status of PGRreturn_result[0]
DEBUG(PGRread_packet): sem_lock[2]
DEBUG(PGRsem_lock): sem_unlock[1]
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ wt001112 host:5432 ＠ wt001113
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:wt001113
DEBUG(PGRsend_replicate_packet_to_server): host(wt001113) : port(5432)
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(pgr_createConn): PQsetdbLogin host[wt001113] port[5432] db[testdb] user[postgres]
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:testdb port:5432 user:postgres host:wt001113 query:INSERT INTO test (field2 ) values('hogehogehoge6')
DEBUG(insertTransactionTbl): sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1086267518,785431) )
DEBUG(insertTransactionTbl): PQexec send :INSERT INTO test (field2 ) values('hogehogehoge6')
DEBUG(insertTransactionTbl): PQexec end
DEBUG(insertTransactionTbl): sem_lock[3]
DEBUG(PGRsem_lock): sem_unlock[2]
DEBUG(PGRreplicate_packet_send_each_server): except:5432 ＠ wt001112 host:5432 ＠ wt001114
DEBUG(PGRreplicate_packet_send_each_server): send replicate to:wt001114
DEBUG(PGRsend_replicate_packet_to_server): host(wt001114) : port(5432)
DEBUG(getTransactionTbl): not found in transaction tbl host wt001112 db:testdb pid:16433
DEBUG(getTransactionTbl): not found in getTransactionTbl
DEBUG(getTransactionTbl): not found in transaction tbl host wt001112 db:testdb pid:16433
DEBUG(pgr_createConn): PQsetdbLogin host[wt001114] port[5432] db[testdb] user[postgres]
DEBUG(pgr_createConn): PQsetdbLogin ok!!
DEBUG(insertTransactionTbl): db:testdb port:5432 user:postgres host:wt001114 query:INSERT INTO test (field2 ) values('hogehogehoge6')
DEBUG(insertTransactionTbl): sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1086267518,785431) )
DEBUG(insertTransactionTbl): PQexec send :INSERT INTO test (field2 ) values('hogehogehoge6')
DEBUG(insertTransactionTbl): PQexec end
DEBUG(insertTransactionTbl): sem_lock[4]
DEBUG(PGRsem_lock): sem_unlock[3]
DEBUG(delete_template): sem_unlock[4]
DEBUG(PGRsem_unlock): PGRreplicate_packet_send end
うまく動かなかった場合のpgreplicate.confは次のようになっています。
bash-2.05$ cat data/pgreplicate.conf
#=============================================================
# PGReplicate configuration file
# for PGCluster-1.0.6cv14
#-------------------------------------------------------------
# file: pgreplicate.conf
#-------------------------------------------------------------
# This file controls:
# o which hosts & port are cluster server
# o which port use for replication request from cluster server
#=============================================================
#
#-------------------------------------------------------------
# A setup of Cluster DB(s)
#
# o Host_Name : The host name of Cluster DB.
# -- please write a host name by FQDN.
# -- do not write IP address.
# o Port : The connection port with postmaster.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> wt001112-local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> wt001113-local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> wt001114-local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7779 </Recovery_Port>
</Cluster_Server_Info>
#
#-------------------------------------------------------------
# A setup of Load Balance Server
#
# o Host_Name : The host name of a load balance server.
# -- please write a host name by FQDN.
# -- do not write IP address.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#-------------------------------------------------------------
<LoadBalance_Server_Info>
<Host_Name> wt001110 </Host_Name>
<Recovery_Port> 7780 </Recovery_Port>
</LoadBalance_Server_Info>
#
#------------------------------------------------------------
# A setup of the upper replication server for cascade connection.
#
# o Host_Name : The host name of Cluster DB.
# -- please write a host name by FQDN.
# -- do not write IP address.
# o Port : The connection port with postmaster.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#------------------------------------------------------------
<Replicate_Server_Info>
<Host_Name> wt001117 </Host_Name>
<Port> 8777 </Port>
<Recovery_Port> 7778 </Recovery_Port>
</Replicate_Server_Info>
#
#-------------------------------------------------------------
# A setup of a replication server
#
# o Replicate_Port : connection for reprication
# o Recovery_Port : connection for recovery
# o Response_mode : timing which returns a response
# normal -- return result of DB which received the query
# reliable -- return result after waiting for response of
# all Cluster DBs.
# o Use_Replication_Log : A replicating query is recorded
# on the remote replication server
# which is connected in the cascade.
# yes -- use replication log
# no -- not use replication log
#-------------------------------------------------------------
<Replication_Port> 8777 </Replication_Port>
<Recovery_Port> 7778 </Recovery_Port>
<Response_Mode> normal </Response_Mode>
<Use_Replication_Log> no </Use_Replication_Log>
ホスト構成を下記します。
192.168.1.110 load balancer
192.168.1.112-114 cluster server
192.168.1.117 replicater
+----+
| LB |
+-+--+
wt001110 192.168.1.110
(マルチホーム側IF wt001110-local 172.16.1.110)
|
+--+-------------------------------+-----------------+
| | |
+-----------+-----------------+ +--------------+--------------+ |
| wt001112 192.168.1.112 | | wt001113 192.168.1.113 | |
|(wt001112-local 172.16.1.112)| |(wt001113-local 172.16.1.113)| |
+-------------+---------------+ +--+--------------------------+ |
| | |
| | |
| | +------------------------+----+
| | | wt001114 192.168.1.114 |
| | |(wt001114-local 172.16.1.114)|
| | +---------------+-------------+
| | |
+--------------------+--------------------+
|
+----------------+------------+
| wt001114 192.168.1.114 |
|(wt001114-local 172.16.1.114)|
+-----------------------------+
192.168.1.117のルーティング
bash-2.05$ netstat -rn
Routing Table: IPv4
Destination Gateway Flags Ref Use Interface
-------------------- -------------------- ----- ----- ------ ---------
192.168.1.0 192.168.1.110 U 1 28 hme0
172.16.1.0 172.16.1.110 U 1 367 hme1
172.16.0.0 172.16.1.1 UG 1 15
224.0.0.0 192.168.1.110 U 1 0 hme0
default 192.168.1.1 UG 1 2
127.0.0.1 127.0.0.1 UH 1 4470 lo0
192.168.1.110のルーティング
bash-2.05$ netstat -rn
Routing Table: IPv4
Destination Gateway Flags Ref Use Interface
-------------------- -------------------- ----- ----- ------ ---------
192.168.1.0 192.168.1.117 U 1 500 hme0
172.16.1.0 172.16.1.117 U 1 509 hme1
172.16.0.0 172.16.1.1 UG 1 0
224.0.0.0 192.168.1.117 U 1 0 hme0
default 192.168.1.1 UG 1 4
127.0.0.1 127.0.0.1 UH 1 90 lo0
クラスタサーバ(192.168.1.112-114)のルーティング
bash-2.05$ netstat -rn
Routing Table: IPv4
Destination Gateway Flags Ref Use Interface
-------------------- -------------------- ----- ----- ------ ---------
192.168.1.0 192.168.1.112 U 1 317 hme0
172.16.1.0 172.16.1.112 U 1 351 hme1
172.16.0.0 172.16.1.1 UG 1 6
224.0.0.0 192.168.1.112 U 1 0 hme0
default 192.168.1.1 UG 1 0
127.0.0.1 127.0.0.1 UH 2 1453 lo0
どうやらレプリケータはデフォルトルーティング側からパケットを投げていて、
コンフィグの内容によってはうまく通信が出来ないということだと考えられます。
コンフィグでマルチホーム側のインターフェースを指定しても動くようだと
なお良いと思います。
以上、ご参考になりますでしょうか。
--
黄川田光国 / KIKAWADA Mitsukuni <mitsukuni ＠ unixmagic.net>
pgcluster メーリングリストの案内