[pgpool-general: 4769] Kind mismatch among backends

Alvaro Cavalli Gastal alvaro.gastal at trt12.jus.br
Thu Jul 7 04:04:01 JST 2016


Hi,

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:

2016-07-06 14:17:58: pid 5224: DEBUG:  pool_read: read 16 bytes from
backend 0
2016-07-06 14:17:58: pid 5224: DEBUG:  reading backend data packet kind
2016-07-06 14:17:58: pid 5224: DETAIL:  backend:0 of 2 kind = '2'
2016-07-06 14:17:58: pid 5224: DEBUG:  pool_read: read 21 bytes from
backend 1
2016-07-06 14:17:58: pid 5224: DEBUG:  reading backend data packet kind
2016-07-06 14:17:58: pid 5224: DETAIL:  backend:1 of 2 kind = '3'
2016-07-06 14:17:58: pid 5224: DEBUG:  read_kind_from_backend
max_count:1.000000 num_executed_nodes:2
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']
2016-07-06 14:17:58: pid 5224: FATAL:  failed to read kind from backend
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]
2016-07-06 14:17:58: pid 5224: HINT:  check data consistency among db nodes

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.

We did not find clues about what to do. Can anyone help me solve this?

Thanks in advance,

Alvaro

*More details:*
postgres=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt
---------+----------+------+--------+-----------+---------+------------
 0       | mozart   | 5432 | 2      | 0.090909  | primary | 999
 1       | velvet   | 5432 | 2      | 0.909091  | standby | 3015

*OS version:* CentOS Linux release 7.2.1511 (Core)
*Kernel version:* 3.10.0-327.22.2.el7.x86_64
*Pgpool version:* 3.5.3 (ekieboshi)
*PostgreSQL version:* 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

*pgpool.conf:*
listen_addresses = '*'
port = 6432
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
backend_hostname0 = 'mozart'
backend_port0 = 5432
backend_weight0 = 0.1
backend_data_directory0 = '/u00/app/postgres/pgmza02/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'velvet'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/u00/app/postgres/pgvlv02/data'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
num_init_children = 2000
max_pool = 10
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'stderr'
log_line_prefix = '%t: pid %p: '
log_connections = off
log_hostname = off
log_statement = off
log_per_node_statement = off
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = off
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'abc123'
delay_threshold = 10000000
follow_master_command = ''
health_check_period = 0
health_check_timeout = 20
health_check_user = 'nobody'
health_check_password = ''
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = ''
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 10
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
wd_hostname = ''
wd_port = 9000
wd_authkey = ''
delegate_IP = ''
ifconfig_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host0_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''
relcache_expire = 0
relcache_size = 256
check_temp_table = on
check_unlogged_table = on
memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''

*Master postgresql.conf:*
listen_addresses = '*'
max_connections = 2000
max_prepared_transactions = 2000
superuser_reserved_connections = 20
temp_buffers = 8MB
work_mem = 16MB
effective_io_concurrency = 32
wal_sync_method = open_sync
checkpoint_timeout = 10min
archive_mode = on
max_wal_senders = 4
random_page_cost = 2.0
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%t:%r:%u@%d:[%p]: '
log_timezone = 'Brazil/East'
search_path = '"$user",public,jt,acl,client,core,eg'
bytea_output = 'escape'
datestyle = 'iso, dmy'
timezone = 'Brazil/East'
lc_messages = 'pt_BR.iso88591'
lc_monetary = 'pt_BR.iso88591'
lc_numeric = 'pt_BR.iso88591'
lc_time = 'pt_BR.iso88591'
default_text_search_config = 'pg_catalog.portuguese'
wal_level = hot_standby
port = 5432
archive_command = 'cp %p /u00/app/postgres/pgmza02/archive/%f'
shared_buffers = 4GB
maintenance_work_mem = 512MB
checkpoint_segments = 20
wal_keep_segments = 20
effective_cache_size = 8GB
autovacuum_max_workers = 4
autovacuum = on
hot_standby = off
synchronous_commit = remote_write
synchronous_standby_names = '*'

*Standby postgresql.conf:*
listen_addresses = '*'
max_connections = 2000
max_prepared_transactions = 2000
superuser_reserved_connections = 20
temp_buffers = 8MB
work_mem = 16MB
effective_io_concurrency = 32
wal_sync_method = open_sync
checkpoint_timeout = 10min
archive_mode = on
max_wal_senders = 4
random_page_cost = 2.0
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%t:%r:%u@%d:[%p]: '
log_timezone = 'Brazil/East'
search_path = '"$user",public,jt,acl,client,core,eg'
bytea_output = 'escape'
datestyle = 'iso, dmy'
timezone = 'Brazil/East'
lc_messages = 'pt_BR.iso88591'
lc_monetary = 'pt_BR.iso88591'
lc_numeric = 'pt_BR.iso88591'
lc_time = 'pt_BR.iso88591'
default_text_search_config = 'pg_catalog.portuguese'
wal_level = hot_standby
port = 5432
archive_command = 'cp %p /u00/app/postgres/pgvlv02/archive/%f'
shared_buffers = 4GB
maintenance_work_mem = 512MB
checkpoint_segments = 20
wal_keep_segments = 20
effective_cache_size = 8GB
autovacuum_max_workers = 4
autovacuum = on
hot_standby = on
synchronous_commit = remote_write
synchronous_standby_names = '*'
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160706/363d6e87/attachment-0001.html>


More information about the pgpool-general mailing list