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

Tue Jun 18 14:39:21 JST 2013

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