<div>Hi,</div><div><br></div><div>We are facing issue with pgpool health check failsafe mechanism in production environment. I have previously posted this issue on <a href="http://www.pgpool.net/mantisbt/view.php?id=50">http://www.pgpool.net/mantisbt/view.php?id=50</a>. I have observed 2 issue with gpool-II version 3.2.3 (built with latest source code) i.e.</div>
<div><br></div>Used versions i.e.<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">
pgpool-II version 3.2.3<br>postgresql 9.2.3 (Master + Slave)</blockquote><div><br><div>1. In master slave configuration, if health check and failover is enabled i.e.</div><div><br></div><div>pgpool.conf <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">
backend_flag0 = 'ALLOW_TO_FAILOVER'<br>backend_flag1 = 'ALLOW_TO_FAILOVER'<br></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">
health_check_period = 5<br>health_check_timeout = 1<br>health_check_max_retries = 2<br>health_check_retry_delay = 10</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">
load_balance_mode = off</blockquote><div><br></div>On Linux64, When master server is running fine and without load balancing and when suddenly if network interruption happen or any other reason (I mimic the situation via forcefully shutdown dbserver via immediate mode etc) and pgpool is not able to make connection to slave server. After that first connection attempt to pgpool return without error/warning message and pgpool do fail over and kill all child processes. Does that make sense that when there is no load balancing and master dbserver is serving the queries well and disconnection of slave server trigger failover ?.</div>
<div><br></div><div><div>pgpool.log</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">
....<br>2013-04-02 17:24:36 DEBUG: pid 65431: I am 65431 accept fd 6<br>2013-04-02 17:24:36 DEBUG: pid 65431: read_startup_packet: application_name: psql<br>2013-04-02 17:24:36 DEBUG: pid 65431: Protocol Major: 3 Minor: 0 database: postgres user: asif<br>
2013-04-02 17:24:36 DEBUG: pid 65431: new_connection: connecting 0 backend<br>2013-04-02 17:24:36 DEBUG: pid 65431: new_connection: connecting 1 backend<br>2013-04-02 17:24:36 ERROR: pid 65431: connect_inet_domain_socket: getsockopt() detected error: Connection refused<br>
2013-04-02 17:24:36 ERROR: pid 65431: connection to localhost(7445) failed<br>2013-04-02 17:24:36 ERROR: pid 65431: new_connection: create_cp() failed<br>2013-04-02 17:24:36 LOG: pid 65431: degenerate_backend_set: 1 fail over request from pid 65431<br>
2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler called<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: starting to select new master node<br>2013-04-02 17:24:36 LOG: pid 65417: starting degeneration. shutdown host localhost(7445)<br>
2013-04-02 17:24:36 LOG: pid 65417: Restart all children<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65418<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65419<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65420<br>
2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65421<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65422<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65423<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65424<br>
2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65425<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65426<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65427<br>2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65428<br>
2013-04-02 17:24:36 DEBUG: pid 65417: failover_handler: kill 65429<br>...<br>...</blockquote><div> </div></div>2. In the same previous configuration, If I disable failover i.e.<br><br><div>pgpool.conf <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">
backend_flag0 = 'DISALLOW_TO_FAILOVER'<br>backend_flag1 = 'DISALLOW_TO_FAILOVER'<br></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">
health_check_period = 5<br>health_check_timeout = 1<br>health_check_max_retries = 2<br>health_check_retry_delay = 10</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">
load_balance_mode = off</blockquote><div><br></div>On Linux64, When master server is running fine and there is no load balancing and no failover and suddenly slave server appear to be disconnected because of network interruption happen or any other reason (I mimic it by forcefully shutdown dbserver via immediate mode etc). After that no connection attempt got successful to pgpool until health check complete and master database server log shows the following messages i.e.</div>
<div><br></div><div>dbserver.log</div><div> ...<br> ...<br> LOG: incomplete startup packet <br> LOG: incomplete startup packet<br> LOG: incomplete startup packet<br> LOG: incomplete startup packet<br> LOG: incomplete startup packet<br>
...</div><div><br></div><div>3. While testing this scenario on my MacOSX machine (gcc), it seems that health check is not getting complete and endless with pgpool configuration settings as issue #2 above and it completely refrain me from to to connect pgpool any more i.e.</div>
<div><br></div><div>pgpool.log</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">
...<br>...<br>2013-04-03 11:29:29 DEBUG: pid 44263: retrying <b>679</b> th health checking<br>2013-04-03 11:29:29 DEBUG: pid 44263: health_check: 0 th DB node status: 2<br>2013-04-03 11:29:29 DEBUG: pid 44263: pool_ssl: SSL requested but SSL support is not available<br>
2013-04-03 11:29:29 DEBUG: pid 44263: s_do_auth: auth kind: 0<br>2013-04-03 11:29:29 DEBUG: pid 44263: s_do_auth: backend key data received<br>2013-04-03 11:29:29 DEBUG: pid 44263: s_do_auth: transaction state: I<br>2013-04-03 11:29:29 DEBUG: pid 44263: health_check: 1 th DB node status: 2<br>
2013-04-03 11:29:29 ERROR: pid 44263: connect_inet_domain_socket: getsockopt() detected error: Connection refused<br>2013-04-03 11:29:29 ERROR: pid 44263: make_persistent_db_connection: connection to localhost(7445) failed<br>
2013-04-03 11:29:29 ERROR: pid 44263: health check failed. 1 th host localhost at port 7445 is down<br>2013-04-03 11:29:29 LOG: pid 44263: health_check: 1 failover is canceld because failover is disallowed<br>2013-04-03 11:29:34 DEBUG: pid 44263: retrying <b>680</b> th health checking<br>
2013-04-03 11:29:34 DEBUG: pid 44263: health_check: 0 th DB node status: 2<br>2013-04-03 11:29:34 DEBUG: pid 44263: pool_ssl: SSL requested but SSL support is not available<br>2013-04-03 11:29:34 DEBUG: pid 44263: s_do_auth: auth kind: 0<br>
2013-04-03 11:29:34 DEBUG: pid 44263: s_do_auth: backend key data received<br>2013-04-03 11:29:34 DEBUG: pid 44263: s_do_auth: transaction state: I<br>2013-04-03 11:29:34 DEBUG: pid 44263: health_check: 1 th DB node status: 2<br>
2013-04-03 11:29:34 ERROR: pid 44263: connect_inet_domain_socket: getsockopt() detected error: Connection refused<br>2013-04-03 11:29:34 ERROR: pid 44263: make_persistent_db_connection: connection to localhost(7445) failed<br>
2013-04-03 11:29:34 ERROR: pid 44263: health check failed. 1 th host localhost at port 7445 is down<br>2013-04-03 11:29:34 LOG: pid 44263: health_check: 1 failover is canceld because failover is disallowed<br>...<br>...</blockquote>
<div><br></div><div>I will try it on Linux64 machine too. Thanks.</div><div><br></div><div><div>Best Regards,</div><div>Asif Naeem</div><div><br></div></div></div>