<div>Hi,</div><div><br></div><div>There is unexpected behavior in pgpool2 that seems to be a bug. Please find the details as following i.e.</div><br>Phenomenon<br><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
pgpool2 configuration option "follow_master_command" special character %H (Hostname of the new master node) is not working as expected <br>In master slave mode after failover, 2nd node become new master but got unexpected hostname (via %H). Please find attached pgpool.conf for details.</blockquote>
<div><br></div><div>Versions</div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
OS version: RHEL - 64<br>pgpool version: pgpool-II version 3.2.1 (namameboshi)</blockquote><div><div><br></div><div><b>Details/Observations</b> :- </div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
</blockquote>At Start<br><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
node_id | hostname | port | status | lb_weight | role<br>---------+----------+------+--------+-----------+---------<br> 0 | dmp02 | 5444 | 2 | 0.333333 | primary<br> 1 | dmp03 | 5444 | 2 | 0.333333 | standby<br>
2 | dmp04 | 5444 | 2 | 0.333333 | standby<br></blockquote><div> </div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
</blockquote>Failover arguments when the user stopped DMP02.</div><div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
2013îN 2åé 8ì˙ ã‡ójì˙ 12:44:12 JST</blockquote><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
FAILED_NODE_ID =0<br>FAILED_NODE_NAME=dmp02<br>FAILED_NODE_PORT=5444<br>FAILED_NODE_DATA=/opt/PostgresPlus/9.2AS/data<br>NEW_MASTER_ID =1<br>OLD_MASTER_ID =0<br>NEW_MASTER_NAME =dmp03<br>OLD_PRIMARY_ID =0<br>NEW_MASTER_DATA =/opt/PostgresPlus/9.2AS/data<br>
0<br></blockquote><div> </div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
</blockquote>Then, these arguments told user that the master node has been changed<br><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
2013îN 2åé 8ì˙ ã‡ójì˙ 12:44:13 JST<br>FAILED_NODE_ID =0<br>FAILED_NODE_NAME=dmp02<br>FAILED_NODE_PORT=5444<br>FAILED_NODE_DATA=/opt/PostgresPlus/9.2AS/data<br>NEW_MASTER_ID =1<br>OLD_MASTER_ID =0<br>NEW_MASTER_NAME =dmp03<br>
OLD_PRIMARY_ID =0<br>NEW_MASTER_DATA =/opt/PostgresPlus/9.2AS/data<br>2013îN 2åé 8ì˙ ã‡ójì˙ 12:44:17 JST<br>FAILED_NODE_ID =2<br>FAILED_NODE_NAME=dmp04<br>FAILED_NODE_PORT=5444<br>FAILED_NODE_DATA=/opt/PostgresPlus/9.2AS/data<br>
NEW_MASTER_ID =1<br>OLD_MASTER_ID =0<br>NEW_MASTER_NAME =dmp02 <b><--- *** The master name is unexpected ***</b><br>OLD_PRIMARY_ID =0<br>NEW_MASTER_DATA =/opt/PostgresPlus/9.2AS/data<br></blockquote><div> </div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
node_id | hostname | port | status | lb_weight | role<br>---------+----------+------+--------+-----------+---------<br> 0 | dmp02 | 5444 | 2 | 0.333333 | standby<br> 1 | dmp03 | 5444 | 2 | 0.333333 | primary<br>
2 | dmp04 | 5444 | 2 | 0.333333 | standby<br></blockquote><div> </div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state<br>
-------+----------+--------------+------------------+---------------+-----------------+-------------+----------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------<br>
20641 | 10 | enterprisedb | dmp02 | 172.24.217.23 | | 16948 | 08-FEB-13 17:39:44.078173 +09:00 | streaming | 14/C50000E0 | 14/C50000E0 | 14/C50000E0 | 14/C50000E0 | 0 | async<br>
(1 çs)<br></blockquote><div> </div>There is one pg_stat_replication record because dmp04 is connecting to dmp02</div><div><br><div>This phenomenon is reproduced by user. What we need to do is just make the scripts like follow_master.sh and launch pgpool process with -F [path to pcp.conf].</div>
<br>1. Before failover<br><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
-bash-3.2$ psql -h 10.1.1.187 -p 9999 -c "show pool_nodes"<br>node_id | hostname | port | status | lb_weight | role<br>---------+----------+------+--------+-----------+---------<br>0 | slave1 | 5444 | 2 | 0.333333 | primary<br>
1 | slave2 | 5444 | 2 | 0.333333 | standby<br>2 | slave3 | 5444 | 2 | 0.333333 | standby<br>(3 行)</blockquote><br>2. We stopped slave 1 node with "pg_ctl -D /data -m immediate stop".<br><br>3. After failover<br>
<blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">-bash-3.2$ psql -h 10.1.1.187 -p 9999 -c "show pool_nodes"<br>
node_id | hostname | port | status | lb_weight | role<br>---------+----------+------+--------+-----------+---------<br>0 | slave1 | 5444 | 2 | 0.333333 | standby<br>1 | slave2 | 5444 | 2 | 0.333333 | primary<br>2 | slave3 | 5444 | 2 | 0.333333 | standby<br>
(3 行)</blockquote><br>4. Then user got output like below after failover that is not expected that is present at the end of follow_master.sh.log i.e.</div><div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
NEW_MASTER_NAME =slave1</blockquote><div><br></div><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
</blockquote>[root@master1 ~]# cat /tmp/failover.sh.log<br><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
2013年 3月 13日 水曜日 11:36:28 JST<br>FAILED_NODE_ID =0<br>FAILED_NODE_NAME=slave1<br>FAILED_NODE_PORT=5444<br>FAILED_NODE_DATA=/opt/PostgresPlus/9.2AS/data<br>NEW_MASTER_ID =1<br>OLD_MASTER_ID =0<br>NEW_MASTER_NAME =slave2<br>
OLD_PRIMARY_ID =0<br>NEW_MASTER_DATA =/opt/PostgresPlus/9.2AS/data<br>0<br></blockquote>[root@master1 ~]# cat /tmp/follow_master.sh.log<br><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
2013年 3月 13日 水曜日 11:36:30 JST<br>FAILED_NODE_ID =0<br>FAILED_NODE_NAME=slave1<br>FAILED_NODE_PORT=5444<br>FAILED_NODE_DATA=/opt/PostgresPlus/9.2AS/data<br>NEW_MASTER_ID =1<br>OLD_MASTER_ID =0<br>NEW_MASTER_NAME =slave2<br>
OLD_PRIMARY_ID =0<br>NEW_MASTER_DATA =/opt/PostgresPlus/9.2AS/data<br>2013年 3月 13日 水曜日 11:36:32 JST<br>FAILED_NODE_ID =2<br>FAILED_NODE_NAME=slave3<br>FAILED_NODE_PORT=5444<br>FAILED_NODE_DATA=/opt/PostgresPlus/9.2AS/data<br>
NEW_MASTER_ID =1<br>OLD_MASTER_ID =0<br>NEW_MASTER_NAME =<b>slave1<br></b>OLD_PRIMARY_ID =0<br>NEW_MASTER_DATA =/opt/PostgresPlus/9.2AS/data</blockquote></div><div><br></div><div>Please guide. Thanks.</div><div><br></div>
<div><div>Best Regards,</div><div>Asif Naeem</div></div>