[pgpool-general: 1849] unexpected EOF on client connection error makes Pgpool to failover slave node

[Özhan Karaman]

Hi;
We have 3 node streaming replication setup with memcached enabled on PgPool 3.2.4. On the backend servers we are using Postgresql 9.1.9. From Pgpool server we get the error below and Postgresql starts to failover one of the slave node.

Jun 18 01:24:28 abc pgpool[4434]: pool_read: read failed (Connection reset by peer)
Jun 18 01:24:28 abc pgpool[4434]: degenerate_backend_set: 2 fail over request from pid 4434
Jun 18 01:24:28 abc pgpool[4434]: pool_flush_it: write failed to backend (2). reason: Broken pipe offset: 0 wlen: 5

I checked the degenerated slave nodes Postgresql nodes and the error is below:

FATAL:  terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
LOG:  unexpected EOF on client connection

There is no problem on network layer or firewall layer. This error happens once a day on different times. After this error i could make pcp_attach_node to attach node again to Pgpool successfully. Our application server sends queries to Pgpool using PHP. 

Our Pgpool setup is below, this connection EOF error is temporary so is it possible to tell pgpool to retry connection or ignore this temporary error and do not change the node distribution?

Regards
Özhan

Our Pgpool Setup:
listen_addresses = '192.168.0.1'
port = 5437
socket_dir = '/tmp'
# - pgpool Communication Manager Connection Settings -
pcp_port = 9898
pcp_socket_dir = '/tmp'

backend_hostname0 = '192.168.1.1'
backend_port0 = 5432
backend_weight0 = 2 
backend_data_directory0 = '/u02/pgdata91'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.2'
backend_port1 = 5432
backend_weight1 = 30
backend_data_directory1 = '/u02/pgdata91'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '192.168.1.3'
backend_port2 = 5432
backend_weight2 = 30
backend_data_directory2 = '/u02/pgdata91'
backend_flag2 = 'ALLOW_TO_FAILOVER'

# - Authentication -
enable_pool_hba = off
pool_passwd = 'pool_passwd'
authentication_timeout = 60
# - SSL Connections -
ssl = off
#ssl_key = './server.key'
#ssl_cert = './server.cert'
#ssl_ca_cert = ''
#ssl_ca_cert_dir = ''

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Pool size -
num_init_children = 512
max_pool = 10
child_life_time = 300
child_max_connections = 1000
connection_life_time = 600
client_idle_limit = 0


#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------

# - Where to log -

log_destination = 'syslog'
print_timestamp = on
log_connections = off
log_hostname = off
log_statement = off
log_per_node_statement = on
log_standby_delay = 'none'
# - Syslog specific -
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'

# - Debug -
debug_level = 0
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/var/run/pgpool.pid'
logdir = '/tmp'

#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
# - Degenerate handling -
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off


#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------

load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'

#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
master_slave_sub_mode = 'stream'
# - Streaming -
sr_check_period = 0
sr_check_user = 'postgres'
sr_check_password = ''
delay_threshold = 0
# - Special commands -
follow_master_command = ''


#------------------------------------------------------------------------------
# PARALLEL MODE
#------------------------------------------------------------------------------
parallel_mode = off
pgpool2_hostname = ''

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------

health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgozi91'
health_check_password = ''
health_check_max_retries = 0
health_check_retry_delay = 1

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/usr/local/bin/failover.sh %d %H'
failback_command = ''
fail_over_on_backend_error = on

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0

#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = off 
trusted_servers = ''
delegate_IP = ''
wd_hostname = ''
wd_port = 9000
wd_interval = 10
ping_path = '/bin'
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 command path
arping_cmd = 'arping -U $_IP_$ -w 1'
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'


#------------------------------------------------------------------------------
# OTHERS
#------------------------------------------------------------------------------
relcache_expire = 0
relcache_size = 256
check_temp_table = off

#------------------------------------------------------------------------------
# ON MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = on
memqcache_method = 'memcached'
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 = ''