View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000530 | Pgpool-II | Bug | public | 2019-07-28 16:44 | 2019-10-07 12:35 |
| Reporter | kunfong | Assigned To | pengbo | ||
| Priority | immediate | Severity | major | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Product Version | 3.6.6 | ||||
| Summary | 0000530: pgpool can't connect to standby replica | ||||
| Description | Hi, I try to use pgpool to do "read/write split" test, and I success to connect master but can't connect slave. here is the result pgpool=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | 35.225.136.237 | 5432 | up | 0.500000 | primary | 1 | true | 0 1 | 35.188.43.194 | 5432 | unused | 0.500000 | standby | 0 | false | 0 configuration file ---------------------------- # pgPool-II configuration file # ---------------------------- # # This file consists of lines of the form: # # name = value # # Whitespace may be used. Comments are introduced with "#" anywhere on a line. # The complete list of parameter names and allowed values can be found in the # pgPool-II documentation. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use "pgpool reload". Some # parameters, which are marked below, require a server shutdown and restart to # take effect. # #------------------------------------------------------------------------------ # CONNECTIONS #------------------------------------------------------------------------------ # - pgpool Connection Settings - listen_addresses = 'localhost' # Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) port = 9999 # Port number # (change requires restart) socket_dir = '/tmp' # Unix domain socket path # The Debian package defaults to # /var/run/postgresql # (change requires restart) listen_backlog_multiplier = 2 # Set the backlog parameter of listen(2) to # num_init_children * listen_backlog_multiplier. # (change requires restart) serialize_accept = off # whether to serialize accept() call to avoid thundering herd problem # (change requires restart) | ||||
| Tags | No tags attached. | ||||
|
|
listen_addresses = 'localhost' port = 9999 socket_dir = '/tmp' listen_backlog_multiplier = 2 serialize_accept = off pcp_listen_addresses = '*' pcp_port = 9898 pcp_socket_dir = '/tmp' backend_hostname0 = '35.225.136.*' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/data' backend_flag0 = 'DISALLOW_TO_FAILOVER' backend_hostname1 = '35.188.43.*' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/data1' backend_flag1 = 'DISALLOW_TO_FAILOVER' enable_pool_hba = on pool_passwd = 'pool_passwd' authentication_timeout = 60 ssl = off #ssl_key = './server.key' #ssl_cert = './server.cert' #ssl_ca_cert = '' #ssl_ca_cert_dir = '' num_init_children = 32 max_pool = 4 child_life_time = 300 child_max_connections = 0 connection_life_time = 0 client_idle_limit = 0 log_destination = 'stderr' log_connections = off log_hostname = off log_statement = off log_per_node_statement = off log_standby_delay = 'if_over_threshold' syslog_facility = 'LOCAL0' syslog_ident = 'pgpool' pid_file_name = '/var/run/pgpool/pgpool.pid' logdir = '/tmp' connection_cache = on reset_query_list = 'ABORT; DISCARD ALL' replication_mode = off replicate_select = off insert_lock = off lobj_lock_table = '' replication_stop_on_mismatch = off failover_if_affected_tuples_mismatch = off load_balance_mode = on ignore_leading_white_space = on white_function_list = '' black_function_list = 'currval,lastval,nextval,setval' database_redirect_preference_list = '' app_name_redirect_preference_list = '' allow_sql_comments = off master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 10 sr_check_user = 'yunjin' sr_check_password = 'postgres' sr_check_database = 'pgpool' delay_threshold = 10000000 follow_master_command = '' health_check_period = 0 health_check_timeout = 20 health_check_user = 'nobody' health_check_password = '' health_check_database = '' health_check_max_retries = 0 health_check_retry_delay = 1 connect_timeout = 10000 failover_command = '' failback_command = '' fail_over_on_backend_error = on search_primary_node_timeout = 300 recovery_user = 'nobody' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 client_idle_limit_in_recovery = 0 use_watchdog = off trusted_servers = '' ping_path = '/bin' wd_hostname = '' wd_port = 9000 wd_priority = 1 wd_authkey = '' wd_ipc_socket_dir = '/tmp' delegate_IP = '' if_cmd_path = '/sbin' if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' arping_path = '/usr/sbin' arping_cmd = 'arping -U $_IP_$ -w 1' clear_memqcache_on_escalation = on wd_escalation_command = '' wd_de_escalation_command = '' wd_lifecheck_method = 'heartbeat' wd_interval = 10 wd_heartbeat_port = 9694 wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30 heartbeat_destination0 = 'host0_ip1' heartbeat_destination_port0 = 9694 heartbeat_device0 = '' wd_life_point = 3 wd_lifecheck_query = 'SELECT 1' wd_lifecheck_dbname = 'template1' wd_lifecheck_user = 'nobody' wd_lifecheck_password = '' relcache_expire = 0 relcache_size = 256 check_temp_table = on check_unlogged_table = on memory_cache_enabled = off memqcache_method = 'shmem' 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 = '' |
|
|
Can you connect standby using port 5432? Could you share the log of pgpool? |
|
|
Hi My postgres is build on GCP cloudsql, I can connect it through GCP console but seem like I can't connect it from pgpool and here is the log [root@luke-pos pgpool]# cat pgpool.log 2019-07-29 05:28:24: pid 5153: DEBUG: initializing pool configuration 2019-07-29 05:28:24: pid 5153: DETAIL: num_backends: 2 total_weight: 2.000000 2019-07-29 05:28:24: pid 5153: DEBUG: initializing pool configuration 2019-07-29 05:28:24: pid 5153: DETAIL: backend 0 weight: 1073741823.500000 flag: 0001 2019-07-29 05:28:24: pid 5153: DEBUG: initializing pool configuration 2019-07-29 05:28:24: pid 5153: DETAIL: backend 1 weight: 1073741823.500000 flag: 0001 2019-07-29 05:28:24: pid 5153: FATAL: pid file found. is another pgpool(4912) is running? 2019-07-29 05:28:33: pid 4946: DEBUG: pool_flush_it: flush size: 37 2019-07-29 05:28:33: pid 4946: DEBUG: pool_read: read 13 bytes from backend 0 2019-07-29 05:28:33: pid 4946: DEBUG: authenticate kind = 5 2019-07-29 05:28:33: pid 4946: DEBUG: pool_write: to backend: 0 kind:p 2019-07-29 05:28:33: pid 4946: DEBUG: pool_flush_it: flush size: 41 2019-07-29 05:28:33: pid 4946: DEBUG: pool_flush_it: flush size: 0 2019-07-29 05:28:33: pid 4946: DEBUG: pool_read: read 318 bytes from backend 0 2019-07-29 05:28:33: pid 4946: DEBUG: authenticate kind = 0 2019-07-29 05:28:33: pid 4946: DEBUG: authenticate backend: key data received 2019-07-29 05:28:33: pid 4946: DEBUG: authenticate backend: transaction state: I 2019-07-29 05:28:33: pid 4946: DEBUG: pool_write: to backend: 0 kind:X 2019-07-29 05:28:33: pid 4946: DEBUG: pool_flush_it: flush size: 5 |
|
|
2019-07-30 08:24:02: pid 4346: DEBUG: initializing pool configuration 2019-07-30 08:24:02: pid 4346: DETAIL: num_backends: 2 total_weight: 2.000000 2019-07-30 08:24:02: pid 4346: DEBUG: initializing pool configuration 2019-07-30 08:24:02: pid 4346: DETAIL: backend 0 weight: 1073741823.500000 flag: 0001 2019-07-30 08:24:02: pid 4346: DEBUG: initializing pool configuration 2019-07-30 08:24:02: pid 4346: DETAIL: backend 1 weight: 1073741823.500000 flag: 0001 2019-07-30 08:24:02: pid 4346: DEBUG: loading hba configuration 2019-07-30 08:24:02: pid 4346: DETAIL: loading file :"/etc/pgpool-II/pool_hba.conf" for client authentication configuration file 2019-07-30 08:24:02: pid 4346: DEBUG: pool_coninfo_size: num_init_children (32) * max_pool (4) * MAX_NUM_BACKENDS (128) * sizeof(ConnectionInfo) (136) = 2228224 bytes requested for shared memory 2019-07-30 08:24:02: pid 4346: DEBUG: ProcessInfo: num_init_children (32) * sizeof(ProcessInfo) (32) = 1024 bytes requested for shared memory 2019-07-30 08:24:02: pid 4346: DEBUG: Request info are: sizeof(POOL_REQUEST_INFO) 5304 bytes requested for shared memory 2019-07-30 08:24:02: pid 4346: DEBUG: Recovery management area: sizeof(int) 4 bytes requested for shared memory 2019-07-30 08:24:02: pid 4346: LOG: Setting up socket for ::1:9999 2019-07-30 08:24:02: pid 4346: LOG: Setting up socket for 127.0.0.1:9999 2019-07-30 08:24:02: pid 4346: LOG: find_primary_node_repeatedly: waiting for finding a primary node 2019-07-30 08:24:02: pid 4346: LOG: find_primary_node: checking backend no 0 2019-07-30 08:24:02: pid 4347: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4348: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4349: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4350: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4351: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4376: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4378: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4360: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4346: DEBUG: pool_flush_it: flush size: 37 2019-07-30 08:24:02: pid 4362: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4363: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4365: DEBUG: initializing backend status 2019-07-30 08:24:02: pid 4368: DEBUG: initializing backend status |
|
|
Hi all log is attached |
|
|
Sorry for late response. Pgpool-II stores backend status into "pgpool_status" file. While starting Pgpool-II, Pgpool-II will read backend status from "pgpool_status" file first and restore the previous status. To discard the status file, start Pgpool-II using "-D" option. http://www.pgpool.net/docs/latest/en/html/pgpool.html If you installed Pgpool-II using RPM, add "-D" to "/etc/sysconfig/pgpool". ----- OPTS=" -D -n" ----- |
|
|
No response more than one month. May I close this issue? |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2019-07-28 16:44 | kunfong | New Issue | |
| 2019-07-28 16:49 | kunfong | Note Added: 0002737 | |
| 2019-07-29 09:10 | pengbo | Assigned To | => pengbo |
| 2019-07-29 09:10 | pengbo | Status | new => assigned |
| 2019-07-29 09:14 | pengbo | Note Added: 0002738 | |
| 2019-07-29 14:31 | kunfong | Note Added: 0002739 | |
| 2019-07-30 17:25 | kunfong | Note Added: 0002740 | |
| 2019-07-30 17:28 | kunfong | File Added: log.rtf | |
| 2019-07-30 17:28 | kunfong | Note Added: 0002741 | |
| 2019-08-16 11:35 | pengbo | Note Added: 0002789 | |
| 2019-08-16 11:36 | pengbo | Status | assigned => feedback |
| 2019-09-30 09:41 | pengbo | Note Added: 0002895 | |
| 2019-10-07 12:35 | pengbo | Status | feedback => closed |
