Online recovery failed, failed to connect to postmaster on hostname

[liuhqsir]

distribution: UBUNTU 22.04
version: 4.6.2

When i set up PostgreSQL standby server by using Pgpool-II online recovery functionality,the password in the pool_passwd file is the same as the password found in the database, but the log shows that the password does not match.

I would like to know what password pgpool uses for online recovery and why does the logs show a mismatch?
😄

[tatsuo-ishii]

The password format should not be in md5 format in case of online recovery. Try to use AES256 or TEXT type password in pool_passwd for postgres user (you don't need to change pg_hba.conf and password in pg_authid).

[liuhqsir]

I use grep to match the two files to show that the password in pgpass is the same as the password in the pool_passwd, I use plaintext, and the psql tool uses the password in the pgpass file to log in to indicate that the password is correct.
However, it still gives an error showing the wrong password.Is it related to the @ character in my password?

And I found that I had to do it twice to execute it properly, on the first execution, the following error message is reported: pgpool_remote_start failed

[tatsuo-ishii]

What do you have in recovery_password of pgpool.conf? Is it '' (empty string) or something else?

[tatsuo-ishii]

Also I need full pgpool log starting from "LOG: starting recovering node ..."

[liuhqsir]

It is empty in in recovery_password of pgpool.conf. The log is too long, I only take screenshots.

[tatsuo-ishii]

Can you share PostgreSQL log? Now that pool_passwd is fixed, the reason why postmaster does respond may be changing.

[liuhqsir]

I'd like to track down which file 10.10.20.127 used to get the password, but I'm not familiar with the strace tool and don't see pool_passwd file it uses.

PostgreSQL log:

[tatsuo-ishii]

I'd like to track down which file 10.10.20.127 used to get the password, but I'm not familiar with the strace tool and don't see pool_passwd file it uses.

You need to attach strace to the pcp_child process of pcp_main process. Suppose pcp_main process's pid to be xyz, then you can do something like:

strace -p xyz -f

strace will automatically attach to pcp child process which will load pool_passwd.

BTW, the PostgreSQL log says pcp process on 10.10.20.127 cannot connect to PostgreSQL's standby server because the password does not match. Since the recovery treatment succeeded on PostgreSQL primary server (is that 10.10.20.149?) using pool_passwd, I assume the password in pool_passwd is correctly set. But failed to connect to standby serverd due to password unmatch. Can you run psql on 10.10.20.127 and connect to PostgreSQL standby server with user = postgres using the same password set in pool_passwd?

[liuhqsir]

PostgreSQL primary server: 10.10.20.127
PostgreSQL standby server: 10.10.20.149
I can to access the standby server on the primary server.

[liuhqsir]

After I changed the authentication of the postgres user to trust, it was successful. But the main backend node can't get up.

[tatsuo-ishii]

find_primary_node reported that there's no primary node although the primary is actually up and running. I think pgpool main process failed to connect to PostgreSQL primary node . It uses sr_check_user and sr_check_password. Can you share those settings?

[tatsuo-ishii]

What if set clear text password or AES256 encrypted password to sr_check_password, health_check_password and recovery_password?

[liuhqsir]

Content of sr_check_user and sr_check_password:

[liuhqsir]

health_check_password
These parameters are empty in the config file, how do I clear them, I can only set them to trust in the pg_hba file.

[tatsuo-ishii]

What if set clear text password or AES256 encrypted password to sr_check_password, health_check_password and recovery_password?

Have you tried this?