show pool_nodes shows status of standby "down" without any reasons

[Avahita]

Hi, everyone

I installed this version and I have two nodes of postgresql17 .
pool_version

4.3.7 (tamahomeboshi)

with these configuration in pgpool.conf:

---

backend_clustering_mode = 'streaming_replication'
listen_addresses = ''
port = 9999
pcp_listen_addresses = ''
pcp_port = 9898
backend_hostname0 = '???'
backend_port0 = ???
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/17/main'
backend_application_name0 = 'server0'
backend_hostname1 = '???'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/17/main'
backend_application_name1 = 'server1'
enable_pool_hba = off
pool_passwd = '/etc/pgpool2/pool_passwd'
num_init_children = 32
max_pool = 4
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool'
log_filename = 'pgpool.log'
load_balance_mode = on
statement_level_load_balance = on
sr_check_period = 10
sr_check_user = '???'
sr_check_password = '????'
sr_check_database = 'postgres'
delay_threshold = 5
prefer_lower_delay_standby = on
health_check_period = 10
health_check_timeout = 20
health_check_user = '????'
health_check_password = '????'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 5

---

I don't have any problem to connect to primary and secondry node through my pgpool server but it insists on showing standby as down so it doesn't send read queries to it and functions well for load balancing.

please help me with finding the issue ?

Any help will be appreciated.

[tatsuo-ishii]

Pgpool-II 4.3.7 is not only pretty old (released almost 2 years ago), but has a serious security issue.
https://pgpool.net/mediawiki/index.php/Main_Page#Pgpool-II_4.6.1.2C_4.5.7.2C_4.4.12.2C_4.3.15_and_4.2.22_officially_released_.282025.2F05.2F15.29

If you plan to use pgpool in production environment, I recommend to upgrade to 4.3.15 or newer.

Anyway, can you share out put of "show pool_nodes" or "pcp_node_info" so that I can confirm your standby PostgreSQL is actually running.

[Avahita]

Hi,
First, I'm very thankful for your answer.
you can see the result of show pool_nodes below.
I replaced my ip with ?.?.?.? but I'm sure they return my primary ip and secondary ip.

node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | ?.?.?.? | 5432 | up | up | 0.500000 | primary | primary | 2 | true | 0 | | | 2025-06-14 19:21:28
1 | ?.?.?.? | 5432 | down | up | 0.500000 | standby | standby | 0 | false | 0 | | | 2025-06-14 19:21:28
(2 rows)

It shows down for standby but I checked telnet and connectivity to replica through that user and all of them works well so I can't discovered why pgpool identifies it as down so it doesn't send read queries to replica.

[tatsuo-ishii]

Okay. The standby PostgreSQL is actually running. I think the reason why pgpool thinks the standby being down is, pgpool read pgpool_status file and found the standby was down before starting pgpool. If you are sure the standby is healthy, you can make it up by using pcp_attach_node command.

[Avahita]

Thanks a lot.
I have to confess that I wanted to try that command but Unfortunately, pcp commands doesn't work for the problem of authentication: I set username and password in pcp.conf like this format: pgpool_admin:MD5mypassword
and I checked it with this command : pg_md5 "mypassword"
and it returns the same string so in this way , I'm sure that my password is correct in MD5 but pcp commands like below one :
pcp_proc_count -h [mypgpoolip] -U pgpool_admin -p 9898 -W

raise this error :

FATAL: authentication failed for user "pgpool_admin"
DETAIL: username and/or password does not match

in the result, my resolving methods gets harder and harder

[tatsuo-ishii]

Make sure that "MD5mypassword" is exactly 32-bytes long. If it is, I suspect your password includes some shell special variables something like $#. If so, you need to quote your password using ' (single quote), not " (double quote). i.e.
pg_md5 'mypassword'

[Avahita]

Hi, thanks for your answer , my password doesn't have any special characters and I checked it with mix of alphabet and numbers and kept it simple enough to eliminate confusion.

I double checked with below commands :

pg_md5 ‘mypass’
Returns a string that I called X

when I show the below file, that string is the same :(I meant just after MD5 word , my string started and exactly it’s the same with the previous result)

cat /etc/pgpool2/pcp.conf
pgpool_admin:MD5X

do you have any other recommendation to check and solve this puzzle?

[tatsuo-ishii]

So your pcp.conf includes "MD5" before md5 encoded string produced by pg_md5? You don't need "MD5" part at all.
Here is a correct pcp.conf example. Suppose user name is "foo" and it's password is "bar".

$ pg_md5 'bar'
37b51d194a7513e45b56f6524f2d51f2
$ tail pcp.conf
foo:37b51d194a7513e45b56f6524f2d51f2

[Avahita]

So your pcp.conf includes "MD5" before md5 encoded string produced by pg_md5? You don't need "MD5" part at all. Here is a correct pcp.conf example. Suppose user name is "foo" and it's password is "bar".

$ pg_md5 'bar'
37b51d194a7513e45b56f6524f2d51f2
$ tail pcp.conf
foo:37b51d194a7513e45b56f6524f2d51f2

Hi, Thanks for your response, Due to having a comprehensive conversation for this issue to give others feedbacks, I wrote all of my changing. First, I changed it exactly based on what you said but my issue insisted then I opened pcp.conf and investigated it carefully just a space in the beginning of the line of my user was culprit. I omitted that then tried pcp_attach_node and saw this message which I was fancy seeing:
pcp_attach_node -- Command Successful

now, I have count of select_cnt for standby node.

I'm very thankful for your patience in answering me.

[tatsuo-ishii]

Thank you for the feedback. I am glad to hear that.