<div dir="ltr"><div class="gmail_quote"><div dir="ltr">Hello,<div><br></div><div>let's try this again:</div><div>I'm running a pgpool docker container that attaches to two nodes:</div><div><br></div><div>```</div><div><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">docker exec -it pgpool /bin/bash -c 'pgpool version'<br style="box-sizing:inherit">pgpool-II version 4.1.1 (karasukiboshi),<br style="box-sizing:inherit"> A generic connection pool/replication/load balance server for PostgreSQL<span style="box-sizing:inherit;display:block;height:unset"></span>Usage:</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">```</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">With the following configuration:</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">```</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">listen_addresses = '127.0.0.1'<br>port = 5431<br>socket_dir = '/var/run/postgresql'<br>pcp_port = 9898<br>pcp_socket_dir = '/var/run/postgresql'<br> backend_data_directory0 = '/var/lib/postgresql/9.6/main'<br> backend_flag0 = 'DISALLOW_TO_FAILOVER'<br> backend_hostname0 = '<a href="http://some.db.com/" target="_blank">some.db.com</a>'<br> backend_port0 = 5432<br> backend_weight0 = 0<br> backend_data_directory0 = '/var/lib/postgresql/9.2/main'<br> backend_data_directory1 = '/var/lib/postgresql/9.6/main'<br> backend_flag1 = 'ALLOW_TO_FAILOVER'<br> backend_hostname1 = '10.32.1.38'<br> backend_port1 = 5432<br> backend_weight1 = 1<br> backend_data_directory1 = '/var/lib/postgresql/9.2/main'<br>enable_pool_hba = on<br>authentication_timeout = 60<br>ssl = off<br>num_init_children = 329<br>max_pool = 1<br>child_life_time = 300<br>child_max_connections = 0<br>connection_life_time = 0<br>client_idle_limit = 150<br>log_destination = 'stderr'<br>print_timestamp = on<br>log_connections = off<br>log_hostname = off<br>log_statement = off<br>log_per_node_statement = off<br>log_standby_delay = 'always'<br>syslog_facility = 'LOCAL0'<br>syslog_ident = 'pgpool'<br>debug_level = 0<br>pid_file_name = '/var/run/postgresql/pgpool.pid'<br>logdir = '/var/log/postgresql'<br>connection_cache = off<br>reset_query_list = 'ABORT; DISCARD ALL'<br>replication_mode = off<br>replicate_select = off<br>insert_lock = on<br>lobj_lock_table = ''<br>replication_stop_on_mismatch = off<br>failover_if_affected_tuples_mismatch = off<br>load_balance_mode = on<br>ignore_leading_white_space = on<br>white_function_list = ''<br>black_function_list = 'nextval,setval'<br>master_slave_mode = on<br>master_slave_sub_mode = 'stream'<br>sr_check_period = 5<br>sr_check_user = 'srcheck'<br>follow_master_command = ''<br>parallel_mode = off<br>pgpool2_hostname = ''<br>system_db_hostname = 'localhost'<br>system_db_port = 5432<br>system_db_dbname = 'pgpool'<br>system_db_schema = 'pgpool_catalog'<br>system_db_user = 'pgpool'<br>health_check_period = 5<br>health_check_timeout = 10<br>health_check_user = 'healthcheck'<br>health_check_max_retries = 0<br>health_check_retry_delay = 1<br>failover_command = ''<br>failback_command = ''<br>failover_on_backend_error = off<br>search_primary_node_timeout = 10<br>recovery_user = 'nobody'<br>recovery_1st_stage_command = ''<br>recovery_2nd_stage_command = ''<br>recovery_timeout = 90<br>client_idle_limit_in_recovery = 0<br>use_watchdog = off<br>trusted_servers = ''<br>ping_path = '/bin'<br>wd_hostname = ''<br>wd_port = 9000<br>wd_authkey = ''<br>delegate_IP = ''<br>ifconfig_path = '/sbin'<br>if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'<br>if_down_cmd = 'ifconfig eth0:0 down'<br>arping_cmd = 'arping -U $_IP_$ -w 1'<br>clear_memqcache_on_escalation = on<br>wd_escalation_command = ''<br>wd_lifecheck_method = 'heartbeat'<br>wd_interval = 10<br>wd_heartbeat_port = 9694<br>wd_heartbeat_keepalive = 2<br>wd_heartbeat_deadtime = 30<br>heartbeat_destination0 = 'host0_ip1'<br>heartbeat_destination_port0 = 9694<br>heartbeat_device0 = ''<br>wd_life_point = 3<br>wd_lifecheck_query = 'SELECT 1'<br>wd_lifecheck_dbname = 'postgres'<br>wd_lifecheck_user = 'postgres'<br>relcache_query_target = master<br>relcache_expire = 0<br>relcache_size = 256<br>check_temp_table = on<br>memory_cache_enabled = off<br>memqcache_method = 'shmem'<br>memqcache_memcached_host = 'localhost'<br>memqcache_memcached_port = 11211<br>memqcache_total_size = 67108864<br>memqcache_max_num_cache = 1000000<br>memqcache_expire = 0<br>memqcache_auto_cache_invalidation = on<br>memqcache_maxcache = 409600<br>memqcache_cache_block_size = 1048576<br>memqcache_oiddir = '/var/log/pgpool/oiddir'<br>white_memqcache_table_list = ''<br>black_memqcache_table_list = ''<br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">```</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">So we end up with the following `pool_nodes` result:</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">```</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"> node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change<br>---------+------------------------------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br> 0 | <a href="http://some.db.com/" target="_blank">some.db.com</a> | 5432 | up | 0.000000 | primary | 0 | false | 0 | | | 2020-03-31 22:04:22<br> 1 | 10.32.1.38 | 5432 | up | 1.000000 | standby | 0 | true | 3283947928401 | | | 2020-03-31 22:04:22<br>(2 rows)<br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">```</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">At times, the "standby" node might go down for whatever reason. When I issue pcp_attach_node command to start sending queries to it - the whole pgpool interface seems to be disrupted:</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">- clients see disruption in queries</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">- no new queries are going through</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">- new connections to pgpool fail</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">Normal functionality is restored after about 2-5 seconds.</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">Is this an expected behavior or is there a more graceful way to reattach a node in pgpool?</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><br></pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">Thank you,</pre><pre style="color:rgb(209,210,211);white-space:pre-wrap;box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;line-height:1.50001;font-variant-ligatures:none;word-break:normal;border-radius:4px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">Artem.</pre></div></div>
</div></div>