View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000446 | Pgpool-II | Bug | public | 2018-11-14 18:13 | 2018-11-23 01:36 |
| Reporter | ilya_s | Assigned To | t-ishii | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| OS | CentOS | OS Version | 7.0.0 | ||
| Product Version | 4.0.1 | ||||
| Target Version | 4.0.2 | ||||
| Summary | 0000446: show pool_nodes; reports both nodes as standby | ||||
| Description | Have a pgpool installed on ******01 along with postgresql 11 which is master node streaming replicating to ******02 (slave node). Pgpool is in streaming replication mode with load balancing (master_slave_mode = on, master_slave_sub_mode = 'stream') Pgpool unable to find primary nodes. Situation is the same for both 4.0.0 and 4.0.1. This is what I see in debug mode: ********* 15:25:07: pid 25417: LOG: find_primary_node_repeatedly: waiting for finding a primary node ********* 15:25:07: pid 25476: DEBUG: initializing backend status ********* 15:25:07: pid 25477: DEBUG: initializing backend status ********* 15:25:07: pid 25417: DEBUG: authenticate kind = 5 ********* 15:25:07: pid 25417: DEBUG: authenticate kind = 5 ********* 15:25:07: pid 25417: DEBUG: do_query: extended:0 query:"SELECT pg_is_in_recovery()" ********* 15:25:07: pid 25417: LOG: get_query_result: no rows returned ********* 15:25:07: pid 25417: DETAIL: node id (0) ********* 15:25:07: pid 25417: DEBUG: do_query: extended:0 query:"SELECT pg_is_in_recovery()" ********* 15:25:07: pid 25417: LOG: get_query_result: no rows returned ********* 15:25:07: pid 25417: DETAIL: node id (1) ********* 15:25:07: pid 25417: DEBUG: verify_backend_node_status: there's no primary node The result of show pool_nodes: psql --port=9999 -c "show pool_nodes" -U replica postgres node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | ******01 | 5432 | up | 0.666667 | standby | 0 | true | 0 |********** 09:15:44 1 | ******02 | 5432 | up | 0.333333 | standby | 0 | false | 0 |********** 09:15:44 (2 rows) [postgres@******01]$ psql -U replica postgres psql (11.1) Type "help" for help. postgres=> \conninfo You are connected to database "postgres" as user "replica" via socket in "/var/run/postgresql" at port "5432". postgres=> SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) [postgres@******02]$ psql -U replica postgres psql (11.1) Type "help" for help. postgres=> \conninfo You are connected to database "postgres" as user "replica" via socket in "/var/run/postgresql" at port "5432". postgres=> SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) So the results of pg_is_in_recovery() are correct and it definetely returns rows. Already digged in different config parameters but yet didn't find the root cause. Kindly help with determining it. | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
I am having this exact same issue since upgrading from 3.75 to 4.0. Not sure if there is any more that I can add here but if there are some troubleshooting / diagnostic steps that I can run please let me know and I'll do my best. |
|
|
I've just tested pg_pool 3.4 (yeah, very old, but that was the only available at the moment, security rules...) and postgresql 10.6 with exact same config for pgpool and postgres. No issue presented, primary node is found correctly. I'll be able to test 10.6 + 4.0 tomorrow if it will be required martinrw, it would be nice if you mention your postgres version just to determine if the issue is between pgpool 4.0 and PG11, or it's just in pgpool 4.0. |
|
|
I'm running postgres 10.6 So far my tests have been: pgpool 3.7.5 with postgres 10.6 = everything works pgpool 4.0.1 with postgres 10.6 = the issue you described above What makes this even more frustrating is that it appears that the 3.x rpms have been removed from the repos (i'm using the yum.postgresql.org repo) which means I can't downgrade nicely now. |
|
|
ilya_s, My guess is you have problem with sr_check_password parameter in your pgpool.conf. (I don't know what you actually set for the parameter because you mask it with '*'). Have you read Pgpool-II 4.0 manual and follow the instruction? http://www.pgpool.net/docs/latest/en/html/runtime-streaming-replication-check.html |
|
|
Wait. Do you set the auth method for "replica" in pg_hba.conf to md5? Then it is possible that you hit a bug with Pgpool-II 4.0. Workaround is, change the auth method in pg_hba.conf from md5 to scram-sha-256. In this case you need to set the password in PostgreSQL from md5 to scram-sha-256 by using alter user command. You need to make sure setting password_encryption = scram-sha-25 before it. |
|
|
In my pgpool.conf I have the password as plan text for the sr_check_password setting. The documents you just pointed to say " To specify the unencrypted clear text password, prefix the password string with TEXT. For example if you want to set mypass as a password, you should specify TEXTmypass in the password field. In the absence of a valid prefix, Pgpool-II will considered the string as a plain text password. " I have tried it both with and without the TEXT prefix so there is no reason for this not to work right? My pg_hba.conf is set to md5. It doesn't sound like an ideal solution to change the password encryption type in the database especially as it used to work in 3.75. Is this literally the only workaround? You mentioned that this is a bug in pgpool 4.0... are there any plans to fix it? |
|
|
t-ishii, Sure I did, reporting to bug tracker is the last resort after all other options were used. Password in sr_check_password is correct. Yeap, you're right. Performed some tests. Just added separate line for sr_check_user (replica in my case). Setting autentification to 'trust' for it resolved the issue. Setting to either password or md5 - brought it back. Didn't tested with scram-sha-256, too complicated. One more thing, just FYI. When the issue was presented, I still could observe such messages in postgres log: LOG: connection received: host=**** port=47888 LOG: connection authorized: user=replica database=postgres LOG: statement: SELECT pg_is_in_recovery() So pgpool was able to login as replica to db and ran the query, yet for some reason it didn't receive any output. Could you please provide some more details on the bug you've mentioned? Thanks in advance. |
|
|
I have just posted a fix for this today along with the explanation of cause of the problem .[pgpool-hackers: 3122] https://www.pgpool.net/pipermail/pgpool-hackers/2018-November/003122.html I attach the same patch here. |
|
|
t-ishii, great news! Thanks a lot for your assistance and quick follow-up! Really appreciate it I suppose the ticket can be closed. |
|
|
You are welcome. Is it possible for you to confirm that the patch fix your issue before closing the ticket? |
|
|
Sure, need some time to transfer sources to server, will keep you posted with the result of testing. |
|
|
t-ishii, I've just tested the patch and it fixed the issue. Hope we'll see it in next release soon :) |
|
|
Usama has committed the fix. The issue marked resolved. Yeah, I would like to see the next release soon as well. Probably Peng, the release manager, starts to think the release schedule. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-11-14 18:13 | ilya_s | New Issue | |
| 2018-11-14 18:13 | ilya_s | File Added: pgpool.conf | |
| 2018-11-15 00:41 | martinrw | Note Added: 0002251 | |
| 2018-11-15 00:54 | ilya_s | Note Added: 0002252 | |
| 2018-11-15 01:05 | martinrw | Note Added: 0002253 | |
| 2018-11-15 09:17 | t-ishii | Note Added: 0002254 | |
| 2018-11-15 11:02 | t-ishii | Note Added: 0002256 | |
| 2018-11-15 11:02 | t-ishii | Note Edited: 0002256 | |
| 2018-11-15 18:13 | martinrw | Note Added: 0002258 | |
| 2018-11-15 18:19 | ilya_s | Note Added: 0002259 | |
| 2018-11-15 18:28 | t-ishii | File Added: auth_fix.diff | |
| 2018-11-15 18:28 | t-ishii | Note Added: 0002261 | |
| 2018-11-15 18:33 | ilya_s | Note Added: 0002262 | |
| 2018-11-15 18:35 | t-ishii | Note Added: 0002263 | |
| 2018-11-15 18:36 | t-ishii | Assigned To | => t-ishii |
| 2018-11-15 18:36 | t-ishii | Status | new => assigned |
| 2018-11-15 18:36 | t-ishii | Description Updated | |
| 2018-11-15 19:01 | ilya_s | Note Added: 0002264 | |
| 2018-11-16 00:50 | ilya_s | Note Added: 0002265 | |
| 2018-11-16 07:32 | t-ishii | Note Added: 0002266 | |
| 2018-11-16 07:33 | t-ishii | Status | assigned => resolved |
| 2018-11-16 07:33 | t-ishii | Target Version | => 4.0.2 |
