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

Tue Jun 18 15:50:56 JST 2013

Hi Tatsuo;
You are right FAQ#1.17 points out like our problem, so i will add "hot_standby_feedback = on" to standby configs but what i like to ask is normally if slave server receives conflict recovery Pgpool continues to work, but if it receives EOF it degenerates slave server. 

Our application server's has 90 seconds default timeout limit. So i did not change max_standby_archive_delay and max_standby_streaming_delay default values.

Below is my postgresql config on server, do you recommend any more settings to get rid of this EOF error? I will also recheck network/firewall layer.

listen_addresses = '*'
port = 5432
max_connections = 3000
shared_buffers = 6096MB
hot_standby = on
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
restart_after_crash = off
replication_timeout = 5000
synchronous_commit = on
wal_receiver_status_interval = 2
archive_mode = on
archive_command = 'cp -i %p /PGArch/%f'

Regards
Özhan

On Jun 18, 2013, at 8:39 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Sounds like FAQ #1.17:
> http://www.pgpool.net/mediawiki/index.php/FAQ#I_see_standby_servers_go_down_status_in_steaming_replication_mode_and_see_PostgreSQL_messages_.22terminating_connection_due_to_conflict.22_Why.3F
> 
> Have you tried "hot_standby_feedback = on" in your standby server's postgresql.conf?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> 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 = ''
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> 