View Issue Details

IDProjectCategoryView StatusLast Update
0000242Pgpool-IIBugpublic2016-09-06 17:18
Reportersupp_kAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionopen 
Platformx86 64OSCentOSOS Version7.2
Product Version3.5.3 
Target VersionFixed in Version 
Summary0000242: show pool_nodes tells both nodes are standby
DescriptionWhen 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 Informationpgpool 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 = ''
TagsNo tags attached.

Activities

t-ishii

2016-09-06 09:36

developer   ~0001049

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

supp_k

2016-09-06 15:46

reporter   ~0001051

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!

t-ishii

2016-09-06 17:17

developer   ~0001054

Ok, issue resolved.

Issue History

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