View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000242 | Pgpool-II | Bug | public | 2016-09-05 23:12 | 2016-09-06 17:18 |
| Reporter | supp_k | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Platform | x86 64 | OS | CentOS | OS Version | 7.2 |
| Product Version | 3.5.3 | ||||
| Summary | 0000242: show pool_nodes tells both nodes are standby | ||||
| Description | When we execute "show pool_nodes" statement we see two backends are considered like standby -bash-4.2$ psql --port=5432 -c "show pool_nodes" --host=127.0.0.1 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+-----------+-------+--------+-----------+---------+------------+-------------------+------------------- 0 | a.db.node | 15432 | up | 0.500000 | standby | 1267717 | true | 0 1 | b.db.node | 15432 | up | 0.500000 | standby | 1065887 | false | 0 What is the reason for such a behaviour? ps_is_in_recovery() procedure correctly says F and T when executed on bith instances of pgpool. | ||||
| Additional Information | pgpool configuratio file: # pgpool configuration file listen_addresses = '*' port = 5432 socket_dir = '/var/run/pgpool' listen_backlog_multiplier = 2 serialize_accept = on pcp_listen_addresses = '*' pcp_port = 9898 pcp_socket_dir = '/var/run/pgpool' backend_hostname0 = 'a.db.node' backend_port0 = 15432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/9.5/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'b.db.node' backend_port1 = 15432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/9.5/data' backend_flag1 = 'ALLOW_TO_FAILOVER' enable_pool_hba = off pool_passwd = 'pool_passwd' authentication_timeout = 60 ssl = off num_init_children = 240 max_pool = 1 child_life_time = 0 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 log_destination = 'syslog' log_line_prefix = '' log_connections = off log_hostname = off log_statement = off log_per_node_statement = off log_standby_delay = 'none' syslog_facility = 'LOCAL0' syslog_ident = 'pgpool' debug_level = 0 log_error_verbosity = default client_min_messages = error log_min_messages = error pid_file_name = '/var/run/pgpool/pgpool.pid' logdir = '/var/log/pgpool' connection_cache = on reset_query_list = 'ABORT; DISCARD ALL' replication_mode = off replicate_select = off insert_lock = off lobj_lock_table = '' replication_stop_in_mismatch = off failover_if_affected_tuples_mismatch = off load_balance_mode = on ignore_leading_white_space = on white_function_list = '' black_function_list = 'nextval,setval,lastval,currval' database_redirect_preference_list = '' app_name_redirect_preference_list = '' allow_sql_comments = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 0 sh_check_user = 'replication' sr_check_password = '' sr_check_database = 'postgres' delay_threshold = 0 follow_master_command = '' health_check_period = 10 health_check_timeout = 5 health_check_user = 'postgres' health_check_password = 'postgres' health_check_database = 'postgres' health_check_max_retries = 0 health_check_retry_delay = 1 connect_timeout = 10000 failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R' 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 = on trusted_servers = '' ping_path = '/bin' wd_hostname = 'a.db.node' wd_port = 9000 wd_priority = 1 wd_authkey = '' wd_ipc_socket_dir = '/var/run/pgpool' delegate_IP = '10.28.64.253' if_cmd_path = '/sbin' if_up_cmd = 'ip addr add $_IP_$/19 dev eth1' if_down_cmd = 'ip addr del $_IP_$/19 dev eth1' arping_path = '/usr/sbin' arping_cmd = 'arping -U $_IP_$ -w 1 -I eth1' clear_memqcache_on_escalation = on wd_escalation_command = '' wd_de_escalation_command = '' wd_monitoring_interfaces_list = '' wd_lifecheck_method = 'heartbeat' wd_interval = 1 wd_heartbeat_port = 9694 wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30 heartbeat_destination0 = 'b.db.node' heartbeat_destination_port0 = 9694 heartbeat_device0 = 'eth1' wd_life_point = 3 wd_lifecheck_query = 'SELECT 1' wd_lifecheck_dbname = 'template1' wd_lifecheck_user = 'postgres' wd_lifecheck_password = 'postgres' other_pgpool_hostname0 = 'b.db.node' other_pgpool_port0 = 5432 other_wd_port0 = 9000 relcache_expire = 0 relcache_size = 256 check_temp_table = off check_unlogged_table = off memory_cache_enabled = off memqcache_method = 'shmem' memqcache_memcached_host = 'localhost' memqcache_memcached_port = 11211 memqcache_total_size = 5000000 memqcache_max_num_cache = 1000000 memqcache_expire = 1 memqcache_auto_cache_invalidation = on memqcache_maxcache = 409600 memqcache_cache_block_size = 1048576 memqcache_oiddir = '/var/log/pgpool/oiddir' white_memqcache_table_list = '' lack_memqcache_table_list = '' | ||||
| Tags | No tags attached. | ||||
|
|
Pgpool-II uses pg_is_in_recovery to find the primary node. http://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_find_the_primary_node.3F If all the node returns true, then they are standby. Do you see following in the pgpool log when you start Pgpool-II? 2016-09-06 09:35:16: pid 8971: LOG: find_primary_node: checking backend no 0 2016-09-06 09:35:16: pid 8971: LOG: find_primary_node: primary node id is 0 |
|
|
Oh, now thanks to your response I see I see the error in the mentioned configuration file: sh_check_user = 'replication' Now it works as it should work: -bash-4.2$ psql --port=5432 -c "show pool_nodes" --host=127.0.0.1 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+-----------+-------+--------+-----------+---------+------------+-------------------+------------------- 0 | a.db.node | 15432 | up | 0.500000 | primary | 4 | true | 0 1 | b.db.node | 15432 | up | 0.500000 | standby | 2 | false | 0 (2 rows) Thank you! |
|
|
Ok, issue resolved. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2016-09-05 23:12 | supp_k | New Issue | |
| 2016-09-06 09:17 | t-ishii | Assigned To | => t-ishii |
| 2016-09-06 09:17 | t-ishii | Status | new => assigned |
| 2016-09-06 09:36 | t-ishii | Note Added: 0001049 | |
| 2016-09-06 09:36 | t-ishii | Status | assigned => feedback |
| 2016-09-06 15:46 | supp_k | Note Added: 0001051 | |
| 2016-09-06 15:46 | supp_k | Status | feedback => assigned |
| 2016-09-06 17:17 | t-ishii | Note Added: 0001054 | |
| 2016-09-06 17:18 | t-ishii | Status | assigned => resolved |
