<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class='hmmessage'><div style="text-align: left;">Hello All -<br><br>(this cross-posted to pgcluster-general and geoserver-users lists)<br><br>For the last few days I am trying my best to implement a PGCluster solution. The client of this cluster is Java applications (mainly geoserver) that use postgis as well. This setup works fine with a single Postgresql but when I use the cluster everything goes wrong. Many database connections gets hung. Sometime I see lot of "idle in transaction" threads. I have two physical servers, each running a replicator, load balancer and cluster.<br><br>I am not doing any upate at this time but when I do "select * from pg_locks;" I see 160 rows:<br><font size="2"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19058 | | | | | | | 654004 | 12119 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19020 | | | | | | | 653938 | 12001 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18588 | | | | | | | 653958 | 12096 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19020 | | | | | | | 653994 | 12113 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18654 | | | | | | | 653980 | 12106 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19020 | | | | | | | 654191 | 12481 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19058 | | | | | | | 654180 | 12504 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19020 | | | | | | | 654375 | 12900 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18588 | | | | | | | 654191 | 12481 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18668 | | | | | | | 654540 | 13330 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18588 | | | | | | | 654375 | 12900 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18588 | | | | | | | 653938 | 12001 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> transactionid | | | | | 654367 | | | | 654367 | 12885 | ExclusiveLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18588 | | | | | | | 653994 | 12113 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> transactionid | | | | | 654375 | | | | 654375 | 12900 | ExclusiveLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19020 | | | | | | | 653958 | 12096 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19059 | | | | | | | 653980 | 12106 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19062 | | | | | | | 654540 | 13330 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> transactionid | | | | | 654362 | | | | 654362 | 12877 | ExclusiveLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> transactionid | | | | | 654189 | | | | 654189 | 12477 | ExclusiveLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19059 | | | | | | | 654543 | 13298 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 18588 | | | | | | | 654190 | 12479 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19058 | | | | | | | 654002 | 12118 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> transactionid | | | | | 653976 | | | | 653976 | 12104 | ExclusiveLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> transactionid | | | | | 654004 | | | | 654004 | 12119 | ExclusiveLock | t</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> relation | 18577 | 19020 | | | | | | | 653982 | 12107 | AccessShareLock | t</span><br style="font-family: Courier New,Courier,Monospace;"></font><br><br>Does this look like deadlock to you? I am also copying my conf files:<br><span style="font-family: Courier New,Courier,Monospace;">#------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># file: cluster.conf (cluster_2)</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># set Replication Server information</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Replicate_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Host_Name> rep_1 </Host_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Port> 8001 </Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Recovery_Port> 8101 </Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Replicate_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Replicate_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Host_Name> rep_2 </Host_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Port> 8001 </Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Recovery_Port> 8101 </Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Replicate_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># set Cluster DB Server information</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Recovery_Port> 7001 </Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Rsync_Path> /usr/bin/rsync </Rsync_Path></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Rsync_Option>ssh</Rsync_Option></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Rsync_Compress> yes </Rsync_Compress></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Pg_Dump_Path> /usr/local/pgsql/bin/pg_dump </Pg_Dump_Path></span><br style="font-family: Courier New,Courier,Monospace;"><br><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># file: pglb.conf</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># set cluster DB server information</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#--------------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Host_Name> cluster_1 </Host_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Port> 5433 </Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Max_Connect>100</Max_Connect> </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Host_Name> cluster_2 </Host_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Port> 5433 </Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Max_Connect>100</Max_Connect> </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># set Load Balance server information </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#------------------------------------------------------------- </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Backend_Socket_Dir> /tmp </Backend_Socket_Dir> </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Receive_Port> 5432 </Receive_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Recovery_Port> 6101 </Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Max_Cluster_Num> 128 </Max_Cluster_Num></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Use_Connection_Pooling> no </Use_Connection_Pooling></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><LifeCheck_Timeout> 3s </LifeCheck_Timeout></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><LifeCheck_Interval> 15s </LifeCheck_Interval></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># A setup of a log files</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Log_File_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <File_Name> /var/log/postgresql/pglb.log </File_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <File_Size> 1M </File_Size></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Rotate> 3 </Rotate></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Log_File_Info></span><br style="font-family: Courier New,Courier,Monospace;"><br style="font-family: Courier New,Courier,Monospace;"><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># file: pgreplicate.conf</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># A setup of Cluster DB(s)</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Host_Name> cluster_1 </Host_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Port> 5433 </Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Recovery_Port> 7001 </Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Host_Name> cluster_2 </Host_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Port> 5433 </Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Recovery_Port> 7001 </Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Cluster_Server_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># A setup of a replication server</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#------------------------------------------------------------- </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Replication_Port>8001</Replication_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Recovery_Port>8101</Recovery_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><RLOG_Port>8301</RLOG_Port></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Response_Mode>normal</Response_Mode></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Use_Replication_Log>no</Use_Replication_Log></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Replication_Timeout>1min</Replication_Timeout></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><LifeCheck_Timeout>3s</LifeCheck_Timeout></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><LifeCheck_Interval>15s</LifeCheck_Interval></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Status_Log_File> /tmp/pgreplicate.sts </Status_Log_File></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Error_Log_File> /tmp/pgreplicate.log </Error_Log_File></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"># A setup of a log files </span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;">#-------------------------------------------------------------</span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"><Log_File_Info></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <File_Name> /var/log/postgresql/pgreplicate.log </File_Name></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <File_Size> 1M </File_Size></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"> <Rotate> 3 </Rotate></span><br style="font-family: Courier New,Courier,Monospace;"><span style="font-family: Courier New,Courier,Monospace;"></Log_File_Info></span><br style="font-family: Courier New,Courier,Monospace;"><br>I also tried the following:<br>1. removing the Load balancer and use the cluster directly<br>2. starting lb and replication servers in Debug mode.<br><br>In Debug mode both LB and Replication servers are spitting out a lot of messages but I saw nothing that is alarming....<br><br>I am very new to Pgcluster and hence any help or pointers are greatly appreciated.<br><br>-- Hari Gangadharan<br><br></div><br /><hr />Share life as it happens with the new Windows Live. <a href='http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_122007' target='_new'>Share now!</a></body>
</html>