<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style>
<!--
@font-face
{font-family:Calibri}
@font-face
{font-family:Tahoma}
@font-face
{font-family:Consolas}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif"}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif"}
span.BalloonTextChar
{font-family:"Tahoma","sans-serif"}
span.EmailStyle19
{font-family:"Calibri","sans-serif";
color:windowtext}
.MsoChpDefault
{font-size:10.0pt}
@page WordSection1
{margin:72.0pt 72.0pt 72.0pt 72.0pt}
div.WordSection1
{}
-->
</style>
</head>
<body lang="EN-AU" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">I’m experiencing an issue with pgpool-II v3.2.0 and testing raw failover capability with Postgres v8.4.1 on Centos 5. I’m not sure if it’s a configuration issue on my part or a bug.
</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I’m running pgpool on a tomcat application server and wish it to failover to a secondary database server if my primary server becomes unavailable. I will setup log shipping and warm standby later but right now I just want to confirm the
failover is working. I have defined the two backend nodes, disabled all “other” modes and enabled health checking.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">The problem is that while the master node (backend_hostname0) is up then everything is fine, however, if it gets taken offline then pgpool is performing the expected actions of a failover but setting the master node id back to ‘0’ and my
database connections fail because its unavailable. The health checking is detecting when nodes are up or down so it should be shifting connections to the slave node id 1 (backend_hostname1) but the master node id is always set to node 0.
</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Thanks in advance for your thoughts/insight. </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">“show pool_nodes” when both nodes are up<span style="font-size:9.0pt; font-family:Consolas">.
</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">node_id | hostname | port | status | lb_weight | role</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">---------+-----------------+------+--------+-----------+--------</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">0 | node01 | 5432 | 2 | 0.500000 | master</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">1 | node02 | 5432 | 1 | 0.500000 | slave</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal">If I stop the node01 database, I get errors like below after I shut down the database and then try to connect with a psql session. It seems to me that it’s not picking the correct node id when it fails over.
</p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 27473: postmaster on DB node 0 was shutdown by administrative command</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 27473: degenerate_backend_set: 0 fail over request from pid 27473</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 25992: starting degeneration. shutdown host node01(5432)</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 25992: Restart all children</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 25992: execute command: /bin/true</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 25992: failover: set new primary node: -1</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 25992: failover: set new master node: 0 <-- this doesn’t seem right to me.</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 25992: failover done. shutdown host node01(5432)</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:37 LOG: pid 27481: worker process received restart request</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:38 LOG: pid 25992: worker child 27481 exits with status 256</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:38 LOG: pid 27482: pcp child process received restart request</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:38 LOG: pid 25992: fork a new worker child pid 27903</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:38 LOG: pid 25992: PCP child 27482 exits with status 256</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:38 LOG: pid 25992: fork a new PCP child pid 27904</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 27902: connection received: host=X.X.X.X port=57167</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 ERROR: pid 27902: connect_inet_domain_socket: connect() failed: Connection refused</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 ERROR: pid 27902: connection to node01(5432) failed</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 ERROR: pid 27902: new_connection: create_cp() failed</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 27902: degenerate_backend_set: 0 fail over request from pid 27902</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 25992: starting degeneration. shutdown host node01(5432)</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 25992: Restart all children</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 25992: execute command: /bin/true</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 25992: failover: set new primary node: -1</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 25992: failover: set new master node: 0 <-- this doesn’t seem right to me.
</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 25992: failover done. shutdown host node01(5432)</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:52 LOG: pid 27903: worker process received restart request</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:53 LOG: pid 25992: worker child 27903 exits with status 256</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:53 LOG: pid 27904: pcp child process received restart request</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:53 LOG: pid 25992: fork a new worker child pid 27914</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:53 LOG: pid 25992: PCP child 27904 exits with status 256</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">2012-09-25 14:26:53 LOG: pid 25992: fork a new PCP child pid 27915</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal">My pgpool.conf has all modes set to off….</p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">replication_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">load_balance_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">master_slave_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">parallel_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal">… and pgpool.conf in full.</p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas"> </span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">listen_addresses = '*'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">port = 9999</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">socket_dir = '/tmp'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">pcp_port = 9898</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">pcp_socket_dir = '/tmp'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_hostname0 = 'node01'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_port0 = 5432</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_weight0 = 1</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_data_directory0 = '/data'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_flag0 = 'ALLOW_TO_FAILOVER'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_hostname1 = 'node02'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_port1 = 5432</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_weight1 = 1</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_data_directory1 = '/data'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">backend_flag1 = 'ALLOW_TO_FAILOVER'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">enable_pool_hba = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">authentication_timeout = 60</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">ssl = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">num_init_children = 8</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">max_pool = 4</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">child_life_time = 300</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">child_max_connections = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">connection_life_time = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">client_idle_limit = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">log_destination = 'stderr'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">print_timestamp = on</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">log_connections = on</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">log_hostname = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">log_statement = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">log_per_node_statement = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">log_standby_delay = 'none'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">syslog_facility = 'LOCAL0'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">syslog_ident = 'pgpool'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">debug_level = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">pid_file_name = '/var/run/pgpool/pgpool.pid'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">logdir = '/tmp'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">connection_cache = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">reset_query_list = 'ABORT; DISCARD ALL'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">replication_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">replicate_select = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">insert_lock = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">lobj_lock_table = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">replication_stop_on_mismatch = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">failover_if_affected_tuples_mismatch = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">load_balance_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">ignore_leading_white_space = on</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">white_function_list = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">black_function_list = 'currval,lastval,nextval,setval'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">master_slave_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">master_slave_sub_mode = 'slony'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">sr_check_period = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">sr_check_user = 'nobody'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">sr_check_password = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">delay_threshold = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">follow_master_command = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">parallel_mode = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">pgpool2_hostname = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">health_check_period = 5</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">health_check_timeout = 20</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">health_check_user = 'postgres'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">health_check_password = 'XXsecretXX'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">health_check_max_retries = 3</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">health_check_retry_delay = 1</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">failover_command = '/bin/true'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">failback_command = '/bin/true'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">fail_over_on_backend_error = on</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">recovery_user = 'nobody'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">recovery_password = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">recovery_1st_stage_command = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">recovery_2nd_stage_command = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">recovery_timeout = 90</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">client_idle_limit_in_recovery = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">use_watchdog = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">trusted_servers = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">delegate_IP = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">wd_hostname = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">wd_port = 9000</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">wd_interval = 10</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">ping_path = '/bin'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">ifconfig_path = '/sbin'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">if_down_cmd = 'ifconfig eth0:0 down'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">arping_cmd = 'arping -U $_IP_$ -w 1'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">wd_life_point = 3</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">wd_lifecheck_query = 'SELECT 1'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">relcache_expire = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">relcache_size = 256</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">check_temp_table = on</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memory_cache_enabled = off</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_method = 'shmem'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_memcached_host = 'localhost'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_memcached_port = 11211</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_total_size = 67108864</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_max_num_cache = 1000000</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_expire = 0</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_auto_cache_invalidation = on</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_maxcache = 409600</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_cache_block_size = 1048576</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">memqcache_oiddir = '/var/log/pgpool/oiddir'</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">white_memqcache_table_list = ''</span></p>
<p class="MsoNormal"><span style="font-size:9.0pt; font-family:Consolas">black_memqcache_table_list = ''</span></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Thanks again,</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Quentin</p>
</div>
<hr>
<p style="font:7pt/7pt Verdana,serif; color:Grey">This communication is confidential and may contain privileged information.<br>
If you are not the named recipient, please erase this communication and contact the sender immediately.<br>
You must not copy, use or disclose this communication, or any attachments or information contained within, without prior consent.<br>
<br>
<span style="font:18pt/7pt Webdings,serif; color:Green">P</span> <span style="font:7pt/7p; color:green">Please consider the environment before printing this email</p>
</span>
</body>
</html>