View Issue Details

IDProjectCategoryView StatusLast Update
0000446Pgpool-IIBugpublic2018-11-23 01:36
Reporterilya_sAssigned Tot-ishii 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionopen 
PlatformOSCentOSOS Version7.0.0
Product Version4.0.1 
Target Version4.0.2Fixed in Version 
Summary0000446: show pool_nodes; reports both nodes as standby
DescriptionHave 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.

TagsNo tags attached.

Activities

ilya_s

2018-11-14 18:13

reporter  

pgpool.conf (43,546 bytes)

martinrw

2018-11-15 00:41

reporter   ~0002251

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.

ilya_s

2018-11-15 00:54

reporter   ~0002252

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.

martinrw

2018-11-15 01:05

reporter   ~0002253

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.

t-ishii

2018-11-15 09:17

developer   ~0002254

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

t-ishii

2018-11-15 11:02

developer   ~0002256

Last edited: 2018-11-15 11:02

View 2 revisions

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.

martinrw

2018-11-15 18:13

reporter   ~0002258

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?

ilya_s

2018-11-15 18:19

reporter   ~0002259

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.

t-ishii

2018-11-15 18:28

developer   ~0002261

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.

auth_fix.diff (908 bytes)
diff --git a/src/auth/pool_auth.c b/src/auth/pool_auth.c
index 99758222..6b2b79b7 100644
--- a/src/auth/pool_auth.c
+++ b/src/auth/pool_auth.c
@@ -153,7 +153,6 @@ connection_do_auth(POOL_CONNECTION_POOL_SLOT * cp, char *password)
 					 errdetail("backend replied with invalid kind")));
 
 		cp->con->auth_kind = AUTH_REQ_OK;
-		return;
 	}
 	else if (auth_kind == AUTH_REQ_CRYPT)	/* crypt password? */
 	{
@@ -177,7 +176,6 @@ connection_do_auth(POOL_CONNECTION_POOL_SLOT * cp, char *password)
 					 errdetail("backend replied with invalid kind")));
 
 		cp->con->auth_kind = AUTH_REQ_OK;
-		return;
 	}
 	else if (auth_kind == AUTH_REQ_MD5) /* md5 password? */
 	{
@@ -204,7 +202,6 @@ connection_do_auth(POOL_CONNECTION_POOL_SLOT * cp, char *password)
 					 errdetail("backend replied with invalid kind")));
 
 		cp->con->auth_kind = AUTH_REQ_OK;
-		return;
 	}
 	else if (auth_kind == AUTH_REQ_SASL)
 	{
auth_fix.diff (908 bytes)

ilya_s

2018-11-15 18:33

reporter   ~0002262

t-ishii, great news! Thanks a lot for your assistance and quick follow-up!
Really appreciate it

I suppose the ticket can be closed.

t-ishii

2018-11-15 18:35

developer   ~0002263

You are welcome. Is it possible for you to confirm that the patch fix your issue before closing the ticket?

ilya_s

2018-11-15 19:01

reporter   ~0002264

Sure, need some time to transfer sources to server, will keep you posted with the result of testing.

ilya_s

2018-11-16 00:50

reporter   ~0002265

t-ishii, I've just tested the patch and it fixed the issue.
Hope we'll see it in next release soon :)

t-ishii

2018-11-16 07:32

developer   ~0002266

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.

Issue History

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 View Revisions
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 View Revisions
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