[pgpool-general: 5150] Re: My 3rd backend stay in unused state

[Tatsuo Ishii]

It seems there's something wrong in you pgpool.conf (especially
in the backend.. section). Can you show us pgpool.conf?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hello,
> 
> I'm trying to deploy a high availability setup with PostgreSQL 9.6 and
> PgPool 3.6.0.
> Everything was working fine up to the second host, I could get queries load
> balanced (I use streaming replication)
> 
> But then I wanted to add a 3rd backend to I can have more read load
> balancing (1 master streaming to 2 replicas), so I just took a snapshot of
> the first replica and created another instance of it as second replica, it
> can connect without problem to the master and start replicating.
> 
> I duplicated the configuration I have in pgpool from the first replica to
> the second one, also added it to the list of the backends in
> trusted_servers.
> 
> But then, since, I can't get it to leave the "unused" state :
> 
> # show pool_nodes;
>  node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay
> ---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------
>  0       | 10.0.0.58 | 5432 | up     | 0.333333  | primary | 145        |
> false             | 0
>  1       | 10.0.0.59 | 5432 | up     | 0.333333  | standby | 11         |
> true              | 0
>  2       | 10.0.0.36 | 5432 | unused | 0.333333  | standby | 0          |
> false             | 0
> 
> Here you can see my issue in node 2.
> When doing tcpdump or netstats, I can also see that there is not
> connections from the pgpool to the postgresql on port 5432, but I can see a
> ping coming from the pgpool to this server, the others servers have active
> connections :
> 
> root at gc-lab-pgsql-1:~# netstat -anp | grep 10.0.0.57
> tcp        0      0 10.0.0.58:5432          10.0.0.57:41323
> ESTABLISHED 878/main: mathieu
> tcp        0      0 10.0.0.58:5432          10.0.0.57:39822
> ESTABLISHED 31799/main: mathieu
> tcp        0      0 10.0.0.58:5432          10.0.0.57:39864
> ESTABLISHED 31841/main: mathieu
> 
> root at gc-lab-pgsql-2:~# netstat -anp | grep 10.0.0.57
> tcp        0      0 10.0.0.59:5432          10.0.0.57:42897
> ESTABLISHED 23707/main: mathieu
> tcp        0      0 10.0.0.59:5432          10.0.0.57:44398
> ESTABLISHED 25526/main: mathieu
> tcp        0      0 10.0.0.59:5432          10.0.0.57:42939
> ESTABLISHED 23748/main: mathieu
> 
> root at gc-lab-pgsql-3:~# netstat -anp | grep 10.0.0.57
> <Nothing>
> 
> Weird thing, I can see  the node 2 in the pool_pools
> 
> 
> # show pool_pools;
>  pool_pid |     start_time      | pool_id | backend_id | database |
> username |     create_time     | majorversion | minorversion | pool_counter
> | pool_backendpid | pool_connected
> ----------+---------------------+---------+------------+----------+----------+---------------------+--------------+--------------+--------------+-----------------+----------------
>  5307     | 2016-11-25 10:22:28 | 0       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  5307     | 2016-11-25 10:22:28 | 0       | 1          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  5307     | 2016-11-25 10:22:28 | 0       | 2          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  5307     | 2016-11-25 10:22:28 | 1       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  5307     | 2016-11-25 10:22:28 | 1       | 1          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  5307     | 2016-11-25 10:22:28 | 1       | 2          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
>  5307     | 2016-11-25 10:22:28 | 2       | 0          |
> |          |                     | 0            | 0            |
> 0            | 0               | 0
> 
> 
> I can't find why, node 1 and 2 have exactly the same configuration, and in
> everything is the same in my pgpool configuration about those 2 nodes.
> 
> I ran pgbench in both write and read on the pgpool, and connections to node
> 2 are never used.
> 
> How can I troubleshoot that ? I could not find anything interesting in the
> logs.
> 
> Thank you.
> Mathieu