[pgpool-general: 8911] Re: pgpool not connecting to db nodes

[NINGWEI CHEN]

Hi,

> I am trying to setup a test pgpool setup, I created 2 PostgreSQL nodes 
> and set them up in a HOT Standby / Streaming Replication environment.
> 
> 
> I installed the postgresql client package and pgpool on the pgpool node
> 
> 
> I can run psql -h from the pgpool server to each of the postgreSQL nodes 
> and connect without a password
> 
> 
> I setup an empty pool_passwd file to disable the use of a password file.
> 
> 
> I configured a backend0 and backend 1 section, but when I start pgpool I 
> get errors related to connecting to the backend nodes:
> 
> 2023-08-07 15:48:44.023: main pid 3761: LOG:  find_primary_node: 
> make_persistent_db_connection_noerror failed on node 0
> 2023-08-07 15:48:44.026: main pid 3761: DEBUG:  authenticate kind = 0
> 2023-08-07 15:48:44.027: main pid 3761: LOG:  find_primary_node: 
> make_persistent_db_connection_noerror failed on node 1
> 2023-08-07 15:48:44.027: main pid 3761: DEBUG: 
>   pool_acquire_follow_primary_lock: lock was not held by anyone
> 2023-08-07 15:48:44.027: main pid 3761: DEBUG: 
>   pool_acquire_follow_primary_lock: succeeded in acquiring lock
> 2023-08-07 15:48:44.028: main pid 3761: DEBUG: 
>   verify_backend_node_status: there's no primary node
> 2023-08-07 15:48:44.028: main pid 3761: DEBUG: 
>   pool_release_follow_primary_lock called
> 2023-08-07 15:48:45.031: main pid 3761: DEBUG:  authenticate kind = 0
> 2023-08-07 15:48:45.032: main pid 3761: LOG:  find_primary_node: 
> make_persistent_db_connection_noerror failed on node 0
> 2023-08-07 15:48:45.035: main pid 3761: DEBUG:  authenticate kind = 0
> 2023-08-07 15:48:45.036: main pid 3761: LOG:  find_primary_node: 
> make_persistent_db_connection_noerror failed on node 1
> 2023-08-07 15:48:45.036: main pid 3761: DEBUG: 
>   pool_acquire_follow_primary_lock: lock was not held by anyone
> 2023-08-07 15:48:45.036: main pid 3761: DEBUG: 
>   pool_acquire_follow_primary_lock: succeeded in acquiring lock
> 2023-08-07 15:48:45.036: main pid 3761: DEBUG: 
>   verify_backend_node_status: there's no primary node
> 2023-08-07 15:48:45.036: main pid 3761: DEBUG: 
>   pool_release_follow_primary_lock called
> ^C2023-08-07 15:48:46.040: main pid 3761: DEBUG:  authenticate kind = 0
> 2023-08-07 15:48:46.041: main pid 3761: LOG:  find_primary_node: 
> make_persistent_db_connection_noerror failed on node 0
> 2023-08-07 15:48:46.044: main pid 3761: DEBUG:  authenticate kind = 0
> 2023-08-07 15:48:46.045: main pid 3761: LOG:  find_primary_node: 
> make_persistent_db_connection_noerror failed on node 1
> 2023-08-07 15:48:46.046: main pid 3761: DEBUG: 
>   pool_acquire_follow_primary_lock: lock was not held by anyone
> 2023-08-07 15:48:46.046: main pid 3761: DEBUG: 
>   pool_acquire_follow_primary_lock: succeeded in acquiring lock
> 2023-08-07 15:48:46.046: main pid 3761: DEBUG: 
>   verify_backend_node_status: there's no primary node
> 2023-08-07 15:48:46.046: main pid 3761: DEBUG: 
>   pool_release_follow_primary_lock called
> 2023-08-07 15:48:47.050: main pid 3761: DEBUG:  authenticate kind = 0
> 

Have you set up the sr_check_user? 

In the process of finding Streaming Replication's primary node, Pgpool-II will try to
connect to the backends using sr_check_user and sr_check_database settings.
And if the connection fails, 「find_primary_node: make_persistent_db_connection_noerror failed on node」will be thrown,
which matches your log. 
So if you haven't set the sr_check_user yet, that could be the cause.

src/main/pgpool_main.c
===
    /*
     * Establish connections to backend
     */
    for (i = 0; i < NUM_BACKENDS; i++)
    {
        slots[i] = NULL;

        if (!VALID_BACKEND(i))
            continue;

        bkinfo = pool_get_node_info(i);

        slots[i] = make_persistent_db_connection_noerror(i, bkinfo->backend_hostname,
                                                         bkinfo->backend_port,
                                                         pool_config->sr_check_database,
                                                         pool_config->sr_check_user,
                                                         password ? password : "", false);
        if (!slots[i])
        {
            ereport(LOG,
                    (errmsg("find_primary_node: make_persistent_db_connection_noerror failed on node %d", i)));
        }
    }
===


Please refer to this manual for further information.
https://www.pgpool.net/docs/latest/en/html/runtime-streaming-replication-check.html


Best Regards.
-- 
SRA OSS LLC
Chen Ningwei<chen at sraoss.co.jp>