View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000538Pgpool-IIGeneralpublic2019-08-13 04:562019-08-16 12:38
Reporterdeven Assigned Tot-ishii  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionopen 
Product Version4.0.5 
Summary0000538: Unable to load balance selects on primary and standby
DescriptionHi
 We are trying out pgpool-II and for some reason selects sql are only running on primary. I am unable to see load balancing with out put from SHOW POOL_NODES command.

We are running non primary on hot standby mode.

Am i missing something over here?

pgpooltest=> SHOW POOL_NODES;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+----------------------------------------+------+--------+-----------+--------+------------+-------------------+-------------------+---------------------
 0 | p_pg_alpha | 5432 | up | 0.500000 | master | 28216 | true | 0 | 2019-08-12 12:25:17
 1 | 10.170.30.30 | 5432 | up | 0.500000 | slave | 0 | false | 0 | 2019-08-12 12:25:17
(2 rows)

pgpool.conf

listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'

backend_hostname0 = 'p_pg_alpha'
backend_port0 = 5432
backend_weight0 = 0.5
backend_data_directory0 = '/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.170.30.30'
backend_port1 = 5432
backend_weight1 = 0.5
backend_data_directory1 = '/pgdata'

num_init_children = 2
max_pool = 4


pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'

helth_check_period = 5
health_check_timeout = 0
helth_check_user = 'nobody'
health_check_password = ''

enable_pool_hba = on
pool_passwd = 'pool_passwd'


Regards
Deven
TagsNo tags attached.

Activities

deven

2019-08-13 04:58

reporter   ~0002772

One thing i observered is stanby db show as slave mode in `SHOW POOL_NODES;`
Does that matter?

deven

2019-08-13 05:34

reporter   ~0002773

Also non -primary is in hot standby mode.

postgres=# show hot_standby;
 hot_standby
-------------
 on
(1 row)

t-ishii

2019-08-13 16:52

developer   ~0002776

> One thing i observered is stanby db show as slave mode in `SHOW POOL_NODES;`
> Does that matter?
Probably you forget to set: master_slave_sub_mode = 'stream'.
Not sure this is related to the issue you are having.

deven

2019-08-14 02:13

reporter   ~0002777

Thanks @t-ishii for your quick reply

Yes i missed setting master_slave_sub_mode = 'stream'.

Now it is discovering both as standby.

This is how pgpool.conf looks now.

listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'

backend_hostname0 = 'p_pg_alpha'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.170.30.30'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'

#num_init_children = 2
#max_pool = 4
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'

#helth_check_period = 5
#health_check_timeout = 0
#helth_check_user = 'pgpooltest'
#health_check_password = ''

enable_pool_hba = on
pool_passwd = 'pool_passwd'
replication_mode = off
master_slave_mode = on
master_slave_sub_mode = 'stream'
load_balance_mode = on
sr_check_user = 'pgpooltest'
wd_lifecheck_user = 'pgpooltest'

bash-4.2$ cat pool_passwd --> md5 password is from select * from pg_shadow; from primary
pgpooltest:md5e2334abce93d14d9b5fae07be042ca5b

bash-4.2$ cat pool_hba.conf --> Tried with md5 no luck
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 trust
local all all trust

I am able to connect pgpooltest with password to primary and standby.

 psql -h `hostname` -d pgpooltest -U pgpooltest -p 9999
pgpooltest=> SHOW POOL_NODES;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+----------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0 | p_pg_alpha| 5432 | up | 0.500000 | standby | 0 | true | 0 | 2019-08-13 09:50:05
 1 | 10.170.30.30 | 5432 | up | 0.500000 | standby | 0 | false | 0 | 2019-08-13 09:50:05

Debug log:

2019-08-13 10:00:43: pid 12570: DEBUG: initializing pool configuration
2019-08-13 10:00:43: pid 12570: DETAIL: num_backends: 2 total_weight: 2.000000
2019-08-13 10:00:43: pid 12570: DEBUG: initializing pool configuration
2019-08-13 10:00:43: pid 12570: DETAIL: backend 0 weight: 1073741823.500000 flag: 0000
2019-08-13 10:00:43: pid 12570: DEBUG: initializing pool configuration
2019-08-13 10:00:43: pid 12570: DETAIL: backend 1 weight: 1073741823.500000 flag: 0000
2019-08-13 10:00:43: pid 12570: 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-08-13 10:00:43: pid 12570: DEBUG: ProcessInfo: num_init_children (32) * sizeof(ProcessInfo) (32) = 1024 bytes requested for shared memory
2019-08-13 10:00:43: pid 12570: DEBUG: Request info are: sizeof(POOL_REQUEST_INFO) 5264 bytes requested for shared memory
2019-08-13 10:00:43: pid 12570: DEBUG: Recovery management area: sizeof(int) 4 bytes requested for shared memory
2019-08-13 10:00:43: pid 12570: LOG: Setting up socket for 0.0.0.0:9999
2019-08-13 10:00:43: pid 12570: LOG: Setting up socket for :::9999
2019-08-13 10:00:43: pid 12571: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12572: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12573: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12574: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12575: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12576: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12577: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12578: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12579: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12580: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12581: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12582: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12583: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12584: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12585: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12586: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12587: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12588: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12589: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12590: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12591: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12592: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12593: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12594: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12595: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12596: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12597: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12598: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12599: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12600: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12601: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12570: LOG: find_primary_node_repeatedly: waiting for finding a primary node
2019-08-13 10:00:43: pid 12602: DEBUG: initializing backend status
2019-08-13 10:00:43: pid 12570: DEBUG: authenticate kind = 5
2019-08-13 10:00:43: pid 12570: ERROR: authentication failed
2019-08-13 10:00:43: pid 12570: DETAIL: password authentication failed for user "pgpooltest"
2019-08-13 10:00:43: pid 12570: LOG: find_primary_node: make_persistent_db_connection_noerror failed on node 0
2019-08-13 10:00:43: pid 12570: DEBUG: authenticate kind = 5
2019-08-13 10:00:43: pid 12570: ERROR: authentication failed
2019-08-13 10:00:43: pid 12570: DETAIL: password authentication failed for user "pgpooltest"
2019-08-13 10:00:43: pid 12570: LOG: find_primary_node: make_persistent_db_connection_noerror failed on node 1

What do you think am in missing over here?

Regards
Deven

deven

2019-08-14 03:59

reporter   ~0002778

Ran a pgbench load test and it was as expected, but for some reason role is still 'standby' for both cluster.
Also column load_balance_node should be true for primary from article http://www.pgpool.net/docs/latest/en/html/tutorial-testing-load-balance.html.
Also select are getting load balanced .

pgpooltest=> SHOW POOL_NODES;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+----------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0 | p_pg_alpha | 5432 | up | 0.500000 | standby | 1092660 | false | 0 | 2019-08-13 11:38:39
 1 | 10.170.30.30 | 5432 | up | 0.500000 | standby | 434486 | true | 0 | 2019-08-13 11:38:39
(2 rows)

t-ishii

2019-08-14 11:31

developer   ~0002779

sr_check_user fails to connect to PostgreSQL server. Suppose pool_passwd and pg_hba.conf is correct, I suspect there's something wrong with sr_check_password and/or sr_check_database. Can you share those files?

deven

2019-08-15 01:12

reporter   ~0002780

Thanks t-ishii

 After setting up sr_check_password and sr_check_database parameter in pgpool.conf. We are getting right results from pool_nodes and not more warning in log files.

Few questions before we close this ticket.

1) sr_check_password is clear text password can we use something like sr_check_password = 'pool_passwd'. I mean user and password which is in file 'pool_passwd'.
2) As few user have mentioned with load balancing type of setup every user application connects to DB has to be in 'pool_passwd'. Is there is way around other then using 'trust' in both pool_hba.conf and pg_hba.conf?

Regard
Deven

pgpooltest=> show pool_nodes;
 node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+----------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
 0 | p_pg_alpha| 5432 | up | 0.500000 | primary | 0 | false | 0 | 2019-08-14 09:00:17
 1 | 10.170.30.30 | 5432 | up | 0.500000 | standby | 0 | true | 0 | 2019-08-14 09:00:17
(2 rows)

t-ishii

2019-08-15 07:40

developer   ~0002781

> 1) sr_check_password is clear text password can we use something like sr_check_password = 'pool_passwd'. I mean user and password which is in file 'pool_passwd'.

You can set sr_check_password = '' (empty string). Then Pgpool-II consults pool_passwd to obtain the password from it.

> 2) As few user have mentioned with load balancing type of setup every user application connects to DB has to be in 'pool_passwd'. Is there is way around other then using 'trust' in both pool_hba.conf and pg_hba.conf?

I am not sure I understand your question... but if you want to avoid to store each user's password in pool_passwd, you can set allow_clear_text_frontend_auth = on. If the user's password is not in pool_passwd, Pgpool-II will ask password for the user.

deven

2019-08-16 01:39

reporter   ~0002786

Thanks we can close the ticket now.

t-ishii

2019-08-16 12:37

developer   ~0002790

Ok, issue closed.

Issue History

Date Modified Username Field Change
2019-08-13 04:56 deven New Issue
2019-08-13 04:58 deven Note Added: 0002772
2019-08-13 05:34 deven Note Added: 0002773
2019-08-13 16:52 t-ishii Note Added: 0002776
2019-08-14 02:13 deven Note Added: 0002777
2019-08-14 03:59 deven Note Added: 0002778
2019-08-14 11:27 t-ishii Assigned To => t-ishii
2019-08-14 11:27 t-ishii Status new => assigned
2019-08-14 11:31 t-ishii Note Added: 0002779
2019-08-14 11:31 t-ishii Status assigned => feedback
2019-08-15 01:12 deven Note Added: 0002780
2019-08-15 01:12 deven Status feedback => assigned
2019-08-15 07:40 t-ishii Note Added: 0002781
2019-08-15 09:07 t-ishii Status assigned => feedback
2019-08-16 01:39 deven Note Added: 0002786
2019-08-16 01:39 deven Status feedback => assigned
2019-08-16 12:37 t-ishii Note Added: 0002790
2019-08-16 12:38 t-ishii Status assigned => closed