<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Exchange Server">
<!-- converted from rtf -->
<style><!-- .EmailQuote { margin-left: 1pt; padding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<font face="Calibri" size="2"><span style="font-size:11pt;">
<div>Hi,</div>
<div>I am new to pgpool. I have just installed pgpool and configured Master/Slave mode with “stream” sub_mode. I have enabled load balance as that is the main feature we want test out. I have enabled Health Check. The load balance appears to work as I can
see connections to both Master and Slave database. </div>
<div>pgpool is installed on a separate server. (linux Redhat 6)</div>
<div>ie: server1 = pgpool</div>
<div>server2 = Master</div>
<div>server3= Slave</div>
<div>(wal replication)</div>
<div> </div>
<div>I tested two scenarios and it failing:</div>
<div> </div>
<ol style="margin:0;padding-left:36pt;">
<li>When I stop/start the slave database, pgpool stops load balancing. It looks like it doesn’t know that it available again after the slave comes backup online. Even after I restart pgpool it doesn’t load balance. It just sends all queries to the Master.</li></ol>
<div> </div>
<ol start="2" style="margin:0;padding-left:36pt;">
<li>I tried shutting down slave db and then shut down master db (no failover) and restart master db and restart slave, pgpool failed to reconnect to the master or the slave. I get database unavailable errors. However, if I restart pgpool for this scenario,
its all fine.</li></ol>
<div> </div>
<div>It appears that pgpool is not doing the healthcheck correctly?? Or have I missed something here??</div>
<div> </div>
<div>Here are my main pgpool.conf changes:</div>
<div> </div>
<div>#------------------------------------------------------------------------------</div>
<div># CONNECTIONS</div>
<div>#------------------------------------------------------------------------------</div>
<div> </div>
<div># - pgpool Connection Settings -</div>
<div> </div>
<div>listen_addresses = '*'</div>
<div> # Host name or IP address to listen on:</div>
<div> # '*' for all, '' for no TCP/IP connections</div>
<div> # (change requires restart)</div>
<div>#port = 9999</div>
<div>port = 5432</div>
<div> # Port number</div>
<div> # (change requires restart)</div>
<div>socket_dir = '/var/run/postgresql'</div>
<div> # Unix domain socket path</div>
<div> # The Debian package defaults to</div>
<div> # /var/run/postgresql</div>
<div> # (change requires restart)</div>
<div>listen_backlog_multiplier = 2</div>
<div> # Set the backlog parameter of listen(2) to</div>
<div> # num_init_children * listen_backlog_multiplier.</div>
<div> # (change requires restart)</div>
<div>serialize_accept = off</div>
<div> # whether to serialize accept() call to avoid thundering herd problem</div>
<div> # (change requires restart)</div>
<div> </div>
<div># - pgpool Communication Manager Connection Settings -</div>
<div> </div>
<div>pcp_listen_addresses = '*'</div>
<div> # Host name or IP address for pcp process to listen on:</div>
<div> # '*' for all, '' for no TCP/IP connections</div>
<div> # (change requires restart)</div>
<div>pcp_port = 9898</div>
<div> # Port number for pcp</div>
<div> # (change requires restart)</div>
<div>pcp_socket_dir = '/var/run/postgresql'</div>
<div> # Unix domain socket path for pcp</div>
<div> # The Debian package defaults to</div>
<div> # /var/run/postgresql</div>
<div> # (change requires restart)</div>
<div> </div>
<div># - Backend Connection Settings -</div>
<div> </div>
<div>backend_hostname0 = '10.69.20.11'</div>
<div> # Host name or IP address to connect to for backend 0</div>
<div>backend_port0 = 5432</div>
<div> # Port number for backend 0</div>
<div>backend_weight0 = 1</div>
<div> # Weight for backend 0 (only in load balancing mode)</div>
<div>backend_data_directory0 = '/var/lib/pgsql/9.5/data'</div>
<div> # Data directory for backend 0</div>
<div>backend_flag0 = 'ALLOW_TO_FAILOVER'</div>
<div> # Controls various backend behavior</div>
<div> # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER</div>
<div>backend_hostname1 = '10.68.20.11'</div>
<div>backend_port1 = 5432</div>
<div>backend_weight1 = 1</div>
<div>backend_data_directory1 = '/var/lib/pgsql/9.5/data'</div>
<div>backend_flag1 = 'ALLOW_TO_FAILOVER'</div>
<div> </div>
<div> </div>
<div>#------------------------------------------------------------------------------</div>
<div># POOLS</div>
<div>#------------------------------------------------------------------------------</div>
<div> </div>
<div># - Concurrent session and pool size -</div>
<div> </div>
<div>num_init_children = 32</div>
<div> # Number of concurrent sessions allowed</div>
<div> # (change requires restart)</div>
<div>max_pool = 4</div>
<div> # Number of connection pool caches per connection</div>
<div> # (change requires restart)</div>
<div> </div>
<div># - Life time -</div>
<div> </div>
<div>child_life_time = 300</div>
<div> # Pool exits after being idle for this many seconds</div>
<div>child_max_connections = 0</div>
<div> # Pool exits after receiving that many connections</div>
<div> # 0 means no exit</div>
<div>connection_life_time = 0</div>
<div> # Connection to backend closes after being idle for this many seconds</div>
<div> # 0 means no close</div>
<div>client_idle_limit = 0</div>
<div> # Client is disconnected after being idle for that many seconds</div>
<div> # (even inside an explicit transactions!)</div>
<div> # 0 means no disconnection</div>
<div> </div>
<div>#------------------------------------------------------------------------------</div>
<div># CONNECTION POOLING</div>
<div>#------------------------------------------------------------------------------</div>
<div> </div>
<div>connection_cache = on</div>
<div> # Activate connection pools</div>
<div> # (change requires restart)</div>
<div> </div>
<div> # Semicolon separated list of queries</div>
<div> # to be issued at the end of a session</div>
<div> # The default is for 8.3 and later</div>
<div>reset_query_list = 'ABORT; DISCARD ALL'</div>
<div> # The following one is for 8.2 and before</div>
<div>#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'</div>
<div> </div>
<div> </div>
<div>#------------------------------------------------------------------------------</div>
<div># LOAD BALANCING MODE</div>
<div>#------------------------------------------------------------------------------</div>
<div> </div>
<div>load_balance_mode = on</div>
<div> # Activate load balancing mode</div>
<div> # (change requires restart)</div>
<div>ignore_leading_white_space = on</div>
<div> # Ignore leading white spaces of each query</div>
<div>white_function_list = ''</div>
<div> # Comma separated list of function names</div>
<div> # that don't write to database</div>
<div> # Regexp are accepted</div>
<div>#black_function_list = 'nextval,setval,nextval,setval'</div>
<div>black_function_list = 'currval,lastval,nextval,setval'</div>
<div> # Comma separated list of function names</div>
<div> # that write to database</div>
<div> # Regexp are accepted</div>
<div> </div>
<div>database_redirect_preference_list = ''</div>
<div> # comma separated list of pairs of database and node id.</div>
<div> # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'</div>
<div> # valid for streaming replicaton mode only.</div>
<div> </div>
<div>app_name_redirect_preference_list = ''</div>
<div> # comma separated list of pairs of app name and node id.</div>
<div> # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'</div>
<div> # valid for streaming replicaton mode only.</div>
<div>allow_sql_comments = off</div>
<div> # if on, ignore SQL comments when judging if load balance or</div>
<div> # query cache is possible.</div>
<div> # If off, SQL comments effectively prevent the judgment</div>
<div> # (pre 3.4 behavior).</div>
<div> </div>
<div>#------------------------------------------------------------------------------</div>
<div># MASTER/SLAVE MODE</div>
<div>#------------------------------------------------------------------------------</div>
<div> </div>
<div>master_slave_mode = on</div>
<div> # Activate master/slave mode</div>
<div> # (change requires restart)</div>
<div>master_slave_sub_mode = 'stream'</div>
<div> # Master/slave sub mode</div>
<div> # Valid values are combinations slony or</div>
<div> # stream. Default is slony.</div>
<div> # (change requires restart)</div>
<div> </div>
<div># - Streaming -</div>
<div> </div>
<div>sr_check_period = 5</div>
<div> # Streaming replication check period</div>
<div> # Disabled (0) by default</div>
<div>sr_check_user = 'postgres'</div>
<div> # Streaming replication check user</div>
<div> # This is necessary even if you disable</div>
<div> # streaming replication delay check with</div>
<div> # sr_check_period = 0</div>
<div>sr_check_password = 'xxxxxxxxxxxxxxxx'</div>
<div> # Password for streaming replication check user</div>
<div>sr_check_database = 'postgres'</div>
<div> # Database name for streaming replication check</div>
<div>delay_threshold = 10000000</div>
<div> # Threshold before not dispatching query to standby node</div>
<div> # Unit is in bytes</div>
<div> # Disabled (0) by default</div>
<div> </div>
<div># - Special commands -</div>
<div> </div>
<div>follow_master_command = ''</div>
<div> # Executes this command after master failover</div>
<div> # Special values:</div>
<div> # %d = node id</div>
<div> # %h = host name</div>
<div> # %p = port number</div>
<div> # %D = database cluster path</div>
<div> # %m = new master node id</div>
<div> # %H = hostname of the new master node</div>
<div> # %M = old master node id</div>
<div> # %P = old primary node id</div>
<div> # %r = new master port number</div>
<div> # %R = new master database cluster path</div>
<div> # %% = '%' character</div>
<div> </div>
<div>#------------------------------------------------------------------------------</div>
<div># HEALTH CHECK</div>
<div>#------------------------------------------------------------------------------</div>
<div> </div>
<div>health_check_period = 40</div>
<div> # Health check period</div>
<div> # Disabled (0) by default</div>
<div>health_check_timeout = 10</div>
<div> # Health check timeout</div>
<div> # 0 means no timeout</div>
<div>health_check_user = 'postgres'</div>
<div> # Health check user</div>
<div>health_check_password = 'xxxxxxxxxxxxxxxxxx'</div>
<div> # Password for health check user</div>
<div>health_check_database = ''</div>
<div> # Database name for health check. If '', tries 'postgres' frist, then 'template1'</div>
<div> </div>
<div>health_check_max_retries = 3</div>
<div> # Maximum number of times to retry a failed health check before giving up.</div>
<div>health_check_retry_delay = 1</div>
<div> # Amount of time to wait (in seconds) between retries.</div>
<div>connect_timeout = 10000</div>
<div> # Timeout value in milliseconds before giving up to connect to backend.</div>
<div> # Default is 10000 ms (10 second). Flaky network user may want to increase</div>
<div> # the value. 0 means no timeout.</div>
<div> # Note that this value is not only used for health check,</div>
<div> # but also for ordinary conection to backend.</div>
<div> </div>
<div> </div>
<div>Thanks.</div>
<div>Dylan</div>
<div> </div>
</span></font>
</body>
</html>