View Revisions: Issue #446

Summary 0000446: show pool_nodes; reports both nodes as standby
Revision 2018-11-15 18:36 by t-ishii
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.

Revision 2018-11-14 18:13 by ilya_s
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.