<div dir="ltr">Hi,
<div><br></div><div><div>We have two PostgreSQL instances (master/hot standby) with streaming replication and are trying to use pgpoolII in load balance mode. A few minutes after starting the application pgpool log this error message and the application crash:</div><div><br></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DEBUG: pool_read: read 16 bytes from backend 0</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DEBUG: reading backend data packet kind</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DETAIL: backend:0 of 2 kind = '2'</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DEBUG: pool_read: read 21 bytes from backend 1</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DEBUG: reading backend data packet kind</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DETAIL: backend:1 of 2 kind = '3'</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DEBUG: read_kind_from_backend max_count:1.000000 num_executed_nodes:2</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: WARNING: packet kind of backend 1 ['3'] does not match with master/majority nodes packet kind ['2']</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: FATAL: failed to read kind from backend</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: DETAIL: kind mismatch among backends. Possible last query was: "BEGIN" kind details are: 0[2] 1[3]</font></div><div><font color="#0000ff">2016-07-06 14:17:58: pid 5224: HINT: check data consistency among db nod</font>es</div><div><br></div><div>We do not know how to solve this problem, since the integrity of standby appears to be ok. Already we rebuilt the standby to make sure that there was no inconsistency. We have also noticed that the error occurs with either synchronously or asynchronous replication.</div><div><br></div><div>We did not find clues about what to do. <span style="font-size:12.8px">Can anyone help me solve this? </span></div><div><span style="font-size:12.8px"><br></span></div><div><span style="font-size:12.8px">Thanks in advance,</span></div><div><br></div><div>Alvaro</div><div><br></div><div><b>More details:</b></div><div><span style="color:rgb(0,0,255);font-size:x-small">postgres=# show pool_nodes;</span><br></div><div><div><font size="1" color="#0000ff"> node_id | hostname | port | status | lb_weight | role | select_cnt </font></div><div><font size="1" color="#0000ff">---------+----------+------+--------+-----------+---------+------------</font></div><div><font size="1" color="#0000ff"> 0 | mozart | 5432 | 2 | 0.090909 | primary | 999</font></div><div><font size="1" color="#0000ff"> 1 | velvet | 5432 | 2 | 0.909091 | standby | 3015</font></div></div><div><br></div><div><b>OS version:</b> CentOS Linux release 7.2.1511 (Core)</div><div><b>Kernel version:</b> 3.10.0-327.22.2.el7.x86_64</div><div><b>Pgpool version:</b> 3.5.3 (ekieboshi)</div><div><b>PostgreSQL version:</b> PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit</div><div><br></div><div><b>pgpool.conf:</b></div><div><font size="1" color="#0000ff">listen_addresses = '*'</font></div><div><font size="1" color="#0000ff">port = 6432</font></div><div><font size="1" color="#0000ff">socket_dir = '/tmp'</font></div><div><font size="1" color="#0000ff">pcp_listen_addresses = '*'</font></div><div><font size="1" color="#0000ff">pcp_port = 9898</font></div><div><font size="1" color="#0000ff">pcp_socket_dir = '/tmp'</font></div><div><font size="1" color="#0000ff">listen_backlog_multiplier = 2</font></div><div><font size="1" color="#0000ff">backend_hostname0 = 'mozart'</font></div><div><font size="1" color="#0000ff">backend_port0 = 5432</font></div><div><font size="1" color="#0000ff">backend_weight0 = 0.1</font></div><div><font size="1" color="#0000ff">backend_data_directory0 = '/u00/app/postgres/pgmza02/data'</font></div><div><font size="1" color="#0000ff">backend_flag0 = 'ALLOW_TO_FAILOVER'</font></div><div><font size="1" color="#0000ff">backend_hostname1 = 'velvet'</font></div><div><font size="1" color="#0000ff">backend_port1 = 5432</font></div><div><font size="1" color="#0000ff">backend_weight1 = 1</font></div><div><font size="1" color="#0000ff">backend_data_directory1 = '/u00/app/postgres/pgvlv02/data'</font></div><div><font size="1" color="#0000ff">backend_flag1 = 'DISALLOW_TO_FAILOVER'</font></div><div><font size="1" color="#0000ff">enable_pool_hba = on</font></div><div><font size="1" color="#0000ff">pool_passwd = 'pool_passwd'</font></div><div><font size="1" color="#0000ff">authentication_timeout = 60</font></div><div><font size="1" color="#0000ff">ssl = off</font></div><div><font size="1" color="#0000ff">num_init_children = 2000</font></div><div><font size="1" color="#0000ff">max_pool = 10</font></div><div><font size="1" color="#0000ff">child_life_time = 300</font></div><div><font size="1" color="#0000ff">child_max_connections = 0</font></div><div><font size="1" color="#0000ff">connection_life_time = 0</font></div><div><font size="1" color="#0000ff">client_idle_limit = 0</font></div><div><font size="1" color="#0000ff">log_destination = 'stderr'</font></div><div><font size="1" color="#0000ff">log_line_prefix = '%t: pid %p: '</font></div><div><font size="1" color="#0000ff">log_connections = off</font></div><div><font size="1" color="#0000ff">log_hostname = off</font></div><div><font size="1" color="#0000ff">log_statement = off</font></div><div><font size="1" color="#0000ff">log_per_node_statement = off</font></div><div><font size="1" color="#0000ff">log_standby_delay = 'if_over_threshold'</font></div><div><font size="1" color="#0000ff">syslog_facility = 'LOCAL0'</font></div><div><font size="1" color="#0000ff">syslog_ident = 'pgpool'</font></div><div><font size="1" color="#0000ff">debug_level = 0</font></div><div><font size="1" color="#0000ff">pid_file_name = '/var/run/pgpool/pgpool.pid'</font></div><div><font size="1" color="#0000ff">logdir = '/tmp'</font></div><div><font size="1" color="#0000ff">connection_cache = on</font></div><div><font size="1" color="#0000ff">reset_query_list = 'ABORT; DISCARD ALL'</font></div><div><font size="1" color="#0000ff">replication_mode = off</font></div><div><font size="1" color="#0000ff">replicate_select = off</font></div><div><font size="1" color="#0000ff">insert_lock = off</font></div><div><font size="1" color="#0000ff">lobj_lock_table = ''</font></div><div><font size="1" color="#0000ff">replication_stop_on_mismatch = off</font></div><div><font size="1" color="#0000ff">failover_if_affected_tuples_mismatch = off</font></div><div><font size="1" color="#0000ff">load_balance_mode = on</font></div><div><font size="1" color="#0000ff">ignore_leading_white_space = on</font></div><div><font size="1" color="#0000ff">white_function_list = ''</font></div><div><font size="1" color="#0000ff">black_function_list = 'currval,lastval,nextval,setval'</font></div><div><font size="1" color="#0000ff">database_redirect_preference_list = ''</font></div><div><font size="1" color="#0000ff">app_name_redirect_preference_list = ''</font></div><div><font size="1" color="#0000ff">allow_sql_comments = off</font></div><div><font size="1" color="#0000ff">master_slave_mode = on</font></div><div><font size="1" color="#0000ff">master_slave_sub_mode = 'stream'</font></div><div><font size="1" color="#0000ff">sr_check_period = 10</font></div><div><font size="1" color="#0000ff">sr_check_user = 'postgres'</font></div><div><font size="1" color="#0000ff">sr_check_password = 'abc123'</font></div><div><font size="1" color="#0000ff">delay_threshold = 10000000</font></div><div><font size="1" color="#0000ff">follow_master_command = ''</font></div><div><font size="1" color="#0000ff">health_check_period = 0</font></div><div><font size="1" color="#0000ff">health_check_timeout = 20</font></div><div><font size="1" color="#0000ff">health_check_user = 'nobody'</font></div><div><font size="1" color="#0000ff">health_check_password = ''</font></div><div><font size="1" color="#0000ff">health_check_max_retries = 0</font></div><div><font size="1" color="#0000ff">health_check_retry_delay = 1</font></div><div><font size="1" color="#0000ff">connect_timeout = 10000</font></div><div><font size="1" color="#0000ff">failover_command = ''</font></div><div><font size="1" color="#0000ff">failback_command = ''</font></div><div><font size="1" color="#0000ff">fail_over_on_backend_error = on</font></div><div><font size="1" color="#0000ff">search_primary_node_timeout = 10</font></div><div><font size="1" color="#0000ff">recovery_user = 'nobody'</font></div><div><font size="1" color="#0000ff">recovery_password = ''</font></div><div><font size="1" color="#0000ff">recovery_1st_stage_command = ''</font></div><div><font size="1" color="#0000ff">recovery_2nd_stage_command = ''</font></div><div><font size="1" color="#0000ff">recovery_timeout = 90</font></div><div><font size="1" color="#0000ff">client_idle_limit_in_recovery = 0</font></div><div><font size="1" color="#0000ff">use_watchdog = off</font></div><div><font size="1" color="#0000ff">trusted_servers = ''</font></div><div><font size="1" color="#0000ff">ping_path = '/bin'</font></div><div><font size="1" color="#0000ff">wd_hostname = ''</font></div><div><font size="1" color="#0000ff">wd_port = 9000</font></div><div><font size="1" color="#0000ff">wd_authkey = ''</font></div><div><font size="1" color="#0000ff">delegate_IP = ''</font></div><div><font size="1" color="#0000ff">ifconfig_path = '/sbin'</font></div><div><font size="1" color="#0000ff">if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'</font></div><div><font size="1" color="#0000ff">if_down_cmd = 'ifconfig eth0:0 down'</font></div><div><font size="1" color="#0000ff">arping_path = '/usr/sbin'</font></div><div><font size="1" color="#0000ff">arping_cmd = 'arping -U $_IP_$ -w 1'</font></div><div><font size="1" color="#0000ff">clear_memqcache_on_escalation = on</font></div><div><font size="1" color="#0000ff">wd_escalation_command = ''</font></div><div><font size="1" color="#0000ff">wd_lifecheck_method = 'heartbeat'</font></div><div><font size="1" color="#0000ff">wd_interval = 10</font></div><div><font size="1" color="#0000ff">wd_heartbeat_port = 9694</font></div><div><font size="1" color="#0000ff">wd_heartbeat_keepalive = 2</font></div><div><font size="1" color="#0000ff">wd_heartbeat_deadtime = 30</font></div><div><font size="1" color="#0000ff">heartbeat_destination0 = 'host0_ip1'</font></div><div><font size="1" color="#0000ff">heartbeat_destination_port0 = 9694 </font></div><div><font size="1" color="#0000ff">heartbeat_device0 = ''</font></div><div><font size="1" color="#0000ff">wd_life_point = 3</font></div><div><font size="1" color="#0000ff">wd_lifecheck_query = 'SELECT 1'</font></div><div><font size="1" color="#0000ff">wd_lifecheck_dbname = 'template1'</font></div><div><font size="1" color="#0000ff">wd_lifecheck_user = 'nobody'</font></div><div><font size="1" color="#0000ff">wd_lifecheck_password = ''</font></div><div><font size="1" color="#0000ff">relcache_expire = 0</font></div><div><font size="1" color="#0000ff">relcache_size = 256</font></div><div><font size="1" color="#0000ff">check_temp_table = on</font></div><div><font size="1" color="#0000ff">check_unlogged_table = on</font></div><div><font size="1" color="#0000ff">memory_cache_enabled = off</font></div><div><font size="1" color="#0000ff">memqcache_method = 'shmem'</font></div><div><font size="1" color="#0000ff">memqcache_memcached_host = 'localhost'</font></div><div><font size="1" color="#0000ff">memqcache_memcached_port = 11211</font></div><div><font size="1" color="#0000ff">memqcache_total_size = 67108864</font></div><div><font size="1" color="#0000ff">memqcache_max_num_cache = 1000000</font></div><div><font size="1" color="#0000ff">memqcache_expire = 0</font></div><div><font size="1" color="#0000ff">memqcache_auto_cache_invalidation = on</font></div><div><font size="1" color="#0000ff">memqcache_maxcache = 409600</font></div><div><font size="1" color="#0000ff">memqcache_cache_block_size = 1048576</font></div><div><font size="1" color="#0000ff">memqcache_oiddir = '/var/log/pgpool/oiddir'</font></div><div><font size="1" color="#0000ff">white_memqcache_table_list = ''</font></div><div><font size="1" color="#0000ff">black_memqcache_table_list = ''</font></div><div><font size="1" color="#0000ff"><br></font></div><div><b><font color="#000000">Master postgresql.conf:</font></b></div><div><font size="1" color="#0000ff">listen_addresses = '*'</font></div><div><font size="1" color="#0000ff">max_connections = 2000</font></div><div><font size="1" color="#0000ff">max_prepared_transactions = 2000</font></div><div><font size="1" color="#0000ff">superuser_reserved_connections = 20</font></div><div><font size="1" color="#0000ff">temp_buffers = 8MB</font></div><div><font size="1" color="#0000ff">work_mem = 16MB</font></div><div><font size="1" color="#0000ff">effective_io_concurrency = 32</font></div><div><font size="1" color="#0000ff">wal_sync_method = open_sync</font></div><div><font size="1" color="#0000ff">checkpoint_timeout = 10min</font></div><div><font size="1" color="#0000ff">archive_mode = on</font></div><div><font size="1" color="#0000ff">max_wal_senders = 4</font></div><div><font size="1" color="#0000ff">random_page_cost = 2.0</font></div><div><font size="1" color="#0000ff">log_destination = 'stderr'</font></div><div><font size="1" color="#0000ff">logging_collector = on</font></div><div><font size="1" color="#0000ff">log_directory = 'pg_log'</font></div><div><font size="1" color="#0000ff">log_filename = 'postgresql-%a.log'</font></div><div><font size="1" color="#0000ff">log_truncate_on_rotation = on</font></div><div><font size="1" color="#0000ff">log_rotation_age = 1d</font></div><div><font size="1" color="#0000ff">log_rotation_size = 0</font></div><div><font size="1" color="#0000ff">log_line_prefix = '%t:%r:%u@%d:[%p]: '</font></div><div><font size="1" color="#0000ff">log_timezone = 'Brazil/East'</font></div><div><font size="1" color="#0000ff">search_path = '"$user",public,jt,acl,client,core,eg'</font></div><div><font size="1" color="#0000ff">bytea_output = 'escape'</font></div><div><font size="1" color="#0000ff">datestyle = 'iso, dmy'</font></div><div><font size="1" color="#0000ff">timezone = 'Brazil/East'</font></div><div><font size="1" color="#0000ff">lc_messages = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">lc_monetary = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">lc_numeric = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">lc_time = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">default_text_search_config = 'pg_catalog.portuguese'</font></div><div><font size="1" color="#0000ff">wal_level = hot_standby</font></div><div><font size="1" color="#0000ff">port = 5432</font></div><div><font size="1" color="#0000ff">archive_command = 'cp %p /u00/app/postgres/pgmza02/archive/%f'</font></div><div><font size="1" color="#0000ff">shared_buffers = 4GB</font></div><div><font size="1" color="#0000ff">maintenance_work_mem = 512MB</font></div><div><font size="1" color="#0000ff">checkpoint_segments = 20</font></div><div><font size="1" color="#0000ff">wal_keep_segments = 20</font></div><div><font size="1" color="#0000ff">effective_cache_size = 8GB</font></div><div><font size="1" color="#0000ff">autovacuum_max_workers = 4</font></div><div><font size="1" color="#0000ff">autovacuum = on</font></div><div><font size="1" color="#0000ff">hot_standby = off</font></div><div><font size="1" color="#0000ff">synchronous_commit = remote_write</font></div><div><font size="1" color="#0000ff">synchronous_standby_names = '*'</font></div><div><br></div><div><b>Standby postgresql.conf:</b></div><div><font size="1" color="#0000ff">listen_addresses = '*'</font></div><div><font size="1" color="#0000ff">max_connections = 2000</font></div><div><font size="1" color="#0000ff">max_prepared_transactions = 2000</font></div><div><font size="1" color="#0000ff">superuser_reserved_connections = 20</font></div><div><font size="1" color="#0000ff">temp_buffers = 8MB</font></div><div><font size="1" color="#0000ff">work_mem = 16MB</font></div><div><font size="1" color="#0000ff">effective_io_concurrency = 32</font></div><div><font size="1" color="#0000ff">wal_sync_method = open_sync</font></div><div><font size="1" color="#0000ff">checkpoint_timeout = 10min</font></div><div><font size="1" color="#0000ff">archive_mode = on</font></div><div><font size="1" color="#0000ff">max_wal_senders = 4</font></div><div><font size="1" color="#0000ff">random_page_cost = 2.0</font></div><div><font size="1" color="#0000ff">log_destination = 'stderr'</font></div><div><font size="1" color="#0000ff">logging_collector = on</font></div><div><font size="1" color="#0000ff">log_directory = 'pg_log'</font></div><div><font size="1" color="#0000ff">log_filename = 'postgresql-%a.log'</font></div><div><font size="1" color="#0000ff">log_truncate_on_rotation = on</font></div><div><font size="1" color="#0000ff">log_rotation_age = 1d</font></div><div><font size="1" color="#0000ff">log_rotation_size = 0</font></div><div><font size="1" color="#0000ff">log_line_prefix = '%t:%r:%u@%d:[%p]: '</font></div><div><font size="1" color="#0000ff">log_timezone = 'Brazil/East'</font></div><div><font size="1" color="#0000ff">search_path = '"$user",public,jt,acl,client,core,eg'</font></div><div><font size="1" color="#0000ff">bytea_output = 'escape'</font></div><div><font size="1" color="#0000ff">datestyle = 'iso, dmy'</font></div><div><font size="1" color="#0000ff">timezone = 'Brazil/East'</font></div><div><font size="1" color="#0000ff">lc_messages = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">lc_monetary = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">lc_numeric = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">lc_time = 'pt_BR.iso88591'</font></div><div><font size="1" color="#0000ff">default_text_search_config = 'pg_catalog.portuguese'</font></div><div><font size="1" color="#0000ff">wal_level = hot_standby</font></div><div><font size="1" color="#0000ff">port = 5432</font></div><div><font size="1" color="#0000ff">archive_command = 'cp %p /u00/app/postgres/pgvlv02/archive/%f'</font></div><div><font size="1" color="#0000ff">shared_buffers = 4GB</font></div><div><font size="1" color="#0000ff">maintenance_work_mem = 512MB</font></div><div><font size="1" color="#0000ff">checkpoint_segments = 20</font></div><div><font size="1" color="#0000ff">wal_keep_segments = 20</font></div><div><font size="1" color="#0000ff">effective_cache_size = 8GB</font></div><div><font size="1" color="#0000ff">autovacuum_max_workers = 4</font></div><div><font size="1" color="#0000ff">autovacuum = on</font></div><div><font size="1" color="#0000ff">hot_standby = on</font></div><div><font size="1" color="#0000ff">synchronous_commit = remote_write</font></div><div><font size="1" color="#0000ff">synchronous_standby_names = '*'</font></div></div></div>