<div dir="ltr">Thanks the advices, I have modified the config file generation!<div><br></div><div>Earlier I have asked several times about recommendations on the backend list, but somehow I must not have been clean enough, never got an answer.</div>
<div>Now it's OK, "trusted servers" are different than the pool members, and the backend list is the same for all.</div><div><br></div><div>will be back soon with the results!</div><div><br></div><div>Thanks again!</div>
<div><br></div><div>Regards,</div><div>Attila</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-04-23 5:10 GMT+02:00 Yugo Nagata <span dir="ltr"><<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<div class=""><br>
On Tue, 22 Apr 2014 15:24:42 +0200<br>
Attila Heidrich <<a href="mailto:attila.heidrich@gmail.com">attila.heidrich@gmail.com</a>> wrote:<br>
<br>
> Hi!<br>
><br>
> The summary is generated by a script called "pool" :<br>
> <a href="https://gist.github.com/dansimau/1582492" target="_blank">https://gist.github.com/dansimau/1582492</a><br>
> Unfortunately there are no logs at all, logging was temporary disabled.<br>
<br>
</div>Without logs, I cannot analyze how and why the problem occured,<br>
though, in general, it can occur when heartbeat connection is broken.<br>
<div class=""><br>
> > > This isn't the first time, usually happens in a high load situation.<br>
<br>
</div>The high load situation means that pgpool is receiving a lot of queries<br>
from clients?<br>
<br>
BTW, I find trusted_servers as the each other server and this is not<br>
recommended. When one server goes down, the other server also goes down<br>
since there is no alive trusted_servers.<br>
<br>
In addition, configurations about backend_hostname are different between<br>
pgpool.confs. These should be same.<br>
<div class="HOEnZb"><div class="h5"><br>
><br>
> The current (normal) status is:<br>
><br>
> root@control-1:~# salt postg\* cmd.run "pool status"<br>
> postgres-1:<br>
> Node: 0<br>
> Host: postgres-1<br>
> Port: 5433<br>
> Weight: 0.500000<br>
> Status: Up, in pool (1)<br>
> Role: Master<br>
><br>
> Node: 1<br>
> Host: postgres-2<br>
> Port: 5433<br>
> Weight: 0.500000<br>
> Status: Up, in pool (1)<br>
> Role: Master<br>
> postgres-2:<br>
> Node: 0<br>
> Host: postgres-2<br>
> Port: 5433<br>
> Weight: 0.500000<br>
> Status: Up, in pool and connected (2)<br>
> Role: Master<br>
><br>
> Node: 1<br>
> Host: postgres-1<br>
> Port: 5433<br>
> Weight: 0.500000<br>
> Status: Up, in pool and connected (2)<br>
> Role: Master<br>
><br>
> IP address status<br>
><br>
> postgres-2:<br>
> 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP<br>
> qlen 1000<br>
> link/ether 00:50:56:8f:7e:7b brd ff:ff:ff:ff:ff:ff<br>
> inet <a href="http://10.6.14.11/24" target="_blank">10.6.14.11/24</a> brd <a href="tel:10.6.14.255" value="+3610614255">10.6.14.255</a> scope global eth0<br>
> valid_lft forever preferred_lft forever<br>
> inet <a href="http://10.6.14.15/24" target="_blank">10.6.14.15/24</a> scope global secondary eth0<br>
> valid_lft forever preferred_lft forever<br>
> inet6 fe80::250:56ff:fe8f:7e7b/64 scope link<br>
> valid_lft forever preferred_lft forever<br>
> postgres-1:<br>
> 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP<br>
> qlen 1000<br>
> link/ether 00:50:56:8f:58:ab brd ff:ff:ff:ff:ff:ff<br>
> inet <a href="http://10.6.14.10/24" target="_blank">10.6.14.10/24</a> brd <a href="tel:10.6.14.255" value="+3610614255">10.6.14.255</a> scope global eth0<br>
> valid_lft forever preferred_lft forever<br>
> inet6 fe80::250:56ff:fe8f:58ab/64 scope link<br>
> valid_lft forever preferred_lft forever<br>
><br>
> Config files with modified passwords:<br>
><br>
> postgres-2:<br>
> listen_addresses = '*'<br>
> port = 5432<br>
> socket_dir = '/var/run/postgresql'<br>
> pcp_port = 9898<br>
> pcp_socket_dir = '/var/run/postgresql'<br>
> backend_hostname0 = 'postgres-2'<br>
> backend_port0 = 5433<br>
> backend_weight0 = 1<br>
> backend_data_directory0 = '/var/lib/postgresql/9.3'<br>
> backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
> backend_hostname1 = 'postgres-1'<br>
> backend_port1 = 5433<br>
> backend_weight1 = 1<br>
> backend_data_directory1 = '/var/lib/postgresql/9.3'<br>
> backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
> enable_pool_hba = on<br>
> pool_passwd = ''<br>
> authentication_timeout = 60<br>
> ssl = off<br>
> num_init_children = 32<br>
> max_pool = 4<br>
> child_life_time = 300<br>
> child_max_connections = 0<br>
> connection_life_time = 0<br>
> client_idle_limit = 0<br>
> log_destination = 'syslog'<br>
> print_timestamp = on<br>
> log_connections = off<br>
> log_hostname = on<br>
> log_statement = off<br>
> log_per_node_statement = on<br>
> log_standby_delay = 'none'<br>
> syslog_facility = 'LOCAL0'<br>
> syslog_ident = 'pgpool'<br>
> debug_level = 0<br>
> pid_file_name = '/var/run/postgresql/pgpool.pid'<br>
> logdir = '/var/log/postgresql'<br>
> connection_cache = on<br>
> reset_query_list = 'ABORT; DISCARD ALL'<br>
> replication_mode = on<br>
> replicate_select = off<br>
> insert_lock = on<br>
> lobj_lock_table = ''<br>
> replication_stop_on_mismatch = off<br>
> failover_if_affected_tuples_mismatch = off<br>
> health_check_period = 15<br>
> health_check_timeout = 5<br>
> health_check_user = 'pg_admin'<br>
> health_check_password = 'P4ssw0rd'<br>
> health_check_max_retries = 2<br>
> health_check_retry_delay = 1<br>
> failover_command = 'echo $(date): host:%h, new master id:%m, old master<br>
> id:%M >> /var/lib/pgsql/failover.log'<br>
> failback_command = 'echo $(date): host:%h, new master id:%m, old master<br>
> id:%M >> /var/lib/pgsql/failback.log'<br>
> fail_over_on_backend_error = on<br>
> recovery_user = 'pg_admin'<br>
> recovery_password = 'P4ssw0rd'<br>
> recovery_1st_stage_command = 'copy_base_backup'<br>
> recovery_2nd_stage_command = 'switch_xlog'<br>
> recovery_timeout = 90<br>
> client_idle_limit_in_recovery = 0<br>
> use_watchdog = on<br>
> trusted_servers = 'postgres-1'<br>
> delegate_IP = '10.6.14.15'<br>
> wd_hostname = 'postgres-2'<br>
> wd_port = 9000<br>
> wd_interval = 5<br>
> ping_path = '/bin'<br>
> ifconfig_path = '/var/lib/postgresql'<br>
> if_up_cmd = 'ip add add <a href="http://10.6.14.15/24" target="_blank">10.6.14.15/24</a> dev eth0'<br>
> if_down_cmd = 'ip add del <a href="http://10.6.14.15/24" target="_blank">10.6.14.15/24</a> dev eth0'<br>
> arping_path = '/var/lib/postgresql'<br>
> arping_cmd = 'arping -U 10.6.14.15 -w 1'<br>
> wd_life_point = 3<br>
> wd_lifecheck_query = 'SELECT 1'<br>
> wd_escalation_command = ''<br>
> wd_lifecheck_method = 'heartbeat'<br>
> wd_interval = 15<br>
> wd_heartbeat_port = 9694<br>
> wd_heartbeat_keepalive = 5<br>
> wd_heartbeat_deadtime = 30<br>
> heartbeat_destination0 = 'postgres-1'<br>
> heartbeat_destination_port0 = 9694<br>
> heartbeat_device0 = ''<br>
> other_pgpool_hostname0 = 'postgres-1'<br>
> other_pgpool_port0 = 5432<br>
> other_wd_port0 = 9000<br>
> relcache_expire = 0<br>
> relcache_size = 256<br>
> check_temp_table = on<br>
> memory_cache_enabled = off<br>
> memqcache_method = 'shmem'<br>
> memqcache_memcached_host = 'localhost'<br>
> memqcache_memcached_port = 11211<br>
> memqcache_total_size = 67108864<br>
> memqcache_max_num_cache = 1000000<br>
> memqcache_expire = 0<br>
> memqcache_auto_cache_invalidation = on<br>
> memqcache_maxcache = 409600<br>
> memqcache_cache_block_size = 1048576<br>
> memqcache_oiddir = '/var/log/pgpool/oiddir'<br>
> white_memqcache_table_list = ''<br>
> black_memqcache_table_list = ''<br>
> postgres-1:<br>
> listen_addresses = '*'<br>
> port = 5432<br>
> socket_dir = '/var/run/postgresql'<br>
> pcp_port = 9898<br>
> pcp_socket_dir = '/var/run/postgresql'<br>
> backend_hostname0 = 'postgres-1'<br>
> backend_port0 = 5433<br>
> backend_weight0 = 1<br>
> backend_data_directory0 = '/var/lib/postgresql/9.3'<br>
> backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
> backend_hostname1 = 'postgres-2'<br>
> backend_port1 = 5433<br>
> backend_weight1 = 1<br>
> backend_data_directory1 = '/var/lib/postgresql/9.3'<br>
> backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
> enable_pool_hba = on<br>
> pool_passwd = ''<br>
> authentication_timeout = 60<br>
> ssl = off<br>
> num_init_children = 32<br>
> max_pool = 4<br>
> child_life_time = 300<br>
> child_max_connections = 0<br>
> connection_life_time = 0<br>
> client_idle_limit = 0<br>
> log_destination = 'syslog'<br>
> print_timestamp = on<br>
> log_connections = off<br>
> log_hostname = on<br>
> log_statement = off<br>
> log_per_node_statement = on<br>
> log_standby_delay = 'none'<br>
> syslog_facility = 'LOCAL0'<br>
> syslog_ident = 'pgpool'<br>
> debug_level = 0<br>
> pid_file_name = '/var/run/postgresql/pgpool.pid'<br>
> logdir = '/var/log/postgresql'<br>
> connection_cache = on<br>
> reset_query_list = 'ABORT; DISCARD ALL'<br>
> replication_mode = on<br>
> replicate_select = off<br>
> insert_lock = on<br>
> lobj_lock_table = ''<br>
> replication_stop_on_mismatch = off<br>
> failover_if_affected_tuples_mismatch = off<br>
> health_check_period = 15<br>
> health_check_timeout = 5<br>
> health_check_user = 'pg_admin'<br>
> health_check_password = 'P4ssw0rd'<br>
> health_check_max_retries = 2<br>
> health_check_retry_delay = 1<br>
> failover_command = 'echo $(date): host:%h, new master id:%m, old master<br>
> id:%M >> /var/lib/pgsql/failover.log'<br>
> failback_command = 'echo $(date): host:%h, new master id:%m, old master<br>
> id:%M >> /var/lib/pgsql/failback.log'<br>
> fail_over_on_backend_error = on<br>
> recovery_user = 'pg_admin'<br>
> recovery_password = 'P4ssw0rd'<br>
> recovery_1st_stage_command = 'copy_base_backup'<br>
> recovery_2nd_stage_command = 'switch_xlog'<br>
> recovery_timeout = 90<br>
> client_idle_limit_in_recovery = 0<br>
> use_watchdog = on<br>
> trusted_servers = 'postgres-2'<br>
> delegate_IP = '10.6.14.15'<br>
> wd_hostname = 'postgres-1'<br>
> wd_port = 9000<br>
> wd_interval = 5<br>
> ping_path = '/bin'<br>
> ifconfig_path = '/var/lib/postgresql'<br>
> if_up_cmd = 'ip add add <a href="http://10.6.14.15/24" target="_blank">10.6.14.15/24</a> dev eth0'<br>
> if_down_cmd = 'ip add del <a href="http://10.6.14.15/24" target="_blank">10.6.14.15/24</a> dev eth0'<br>
> arping_path = '/var/lib/postgresql'<br>
> arping_cmd = 'arping -U 10.6.14.15 -w 1'<br>
> wd_life_point = 3<br>
> wd_lifecheck_query = 'SELECT 1'<br>
> wd_escalation_command = ''<br>
> wd_lifecheck_method = 'heartbeat'<br>
> wd_interval = 15<br>
> wd_heartbeat_port = 9694<br>
> wd_heartbeat_keepalive = 5<br>
> wd_heartbeat_deadtime = 30<br>
> heartbeat_destination0 = 'postgres-2'<br>
> heartbeat_destination_port0 = 9694<br>
> heartbeat_device0 = ''<br>
> other_pgpool_hostname0 = 'postgres-2'<br>
> other_pgpool_port0 = 5432<br>
> other_wd_port0 = 9000<br>
> relcache_expire = 0<br>
> relcache_size = 256<br>
> check_temp_table = on<br>
> memory_cache_enabled = off<br>
> memqcache_method = 'shmem'<br>
> memqcache_memcached_host = 'localhost'<br>
> memqcache_memcached_port = 11211<br>
> memqcache_total_size = 67108864<br>
> memqcache_max_num_cache = 1000000<br>
> memqcache_expire = 0<br>
> memqcache_auto_cache_invalidation = on<br>
> memqcache_maxcache = 409600<br>
> memqcache_cache_block_size = 1048576<br>
> memqcache_oiddir = '/var/log/pgpool/oiddir'<br>
> white_memqcache_table_list = ''<br>
> black_memqcache_table_list = ''<br>
><br>
><br>
><br>
> 2014-04-18 10:36 GMT+02:00 Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>>:<br>
><br>
> > Hi,<br>
> ><br>
> > On Tue, 15 Apr 2014 12:24:51 +0200<br>
> > Attila Heidrich <<a href="mailto:attila.heidrich@gmail.com">attila.heidrich@gmail.com</a>> wrote:<br>
> ><br>
> > > Dear guys!<br>
> > ><br>
> > > Where to find the problem in the situation above?<br>
> > ><br>
> > > No logs at all, for some reason pgpool stopped logging (usually it uses<br>
> > > syslog).<br>
> ><br>
> > The followings seem a summarised results of some command (pcp command or<br>
> > show pool_nodes?). I want to konw the actual command results. Could you<br>
> > please send me these, log messages, and pgpool.conf?<br>
> ><br>
> > ><br>
> > > root@postgres-1:/etc/pgpool2# pool status<br>
> > > Node: 0<br>
> > > Host: postgres-1<br>
> > > Port: 5433<br>
> > > Weight: 0.500000<br>
> > > Status: Up, in pool (1)<br>
> > > Role: Master<br>
> > ><br>
> > > Node: 1<br>
> > > Host: postgres-2<br>
> > > Port: 5433<br>
> > > Weight: 0.500000<br>
> > > Status: Up, in pool (1)<br>
> > > Role: Master<br>
> > ><br>
> > > root@postgres-2:/etc/pgpool2# pool status<br>
> > > Node: 0<br>
> > > Host: postgres-2<br>
> > > Port: 5433<br>
> > > Weight: 0.500000<br>
> > > Status: Up, in pool and connected (2)<br>
> > > Role: Master<br>
> > ><br>
> > > Node: 1<br>
> > > Host: postgres-1<br>
> > > Port: 5433<br>
> > > Weight: 0.500000<br>
> > > Status: Up, in pool and connected (2)<br>
> > > Role: Master<br>
> > ><br>
> > > This isn't the first time, usually happens in a high load situation.<br>
> > ><br>
> > > Attila<br>
> ><br>
> ><br>
> > --<br>
> > Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
> ><br>
<br>
<br>
</div></div><span class="HOEnZb"><font color="#888888">--<br>
Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp">nagata@sraoss.co.jp</a>><br>
</font></span></blockquote></div><br></div>