<div dir="ltr"><div><div><div><div><div><div><span style="font-family:monospace,monospace">Hello,<br><br>I&#39;m trying to deploy a high availability setup with PostgreSQL 9.6 and PgPool 3.6.0.<br>Everything was working fine up to the second host, I could get queries load balanced (I use streaming replication)<br><br>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.<br><br>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.<br><br>But then, since, I can&#39;t get it to leave the &quot;unused&quot; state :<br><br># show pool_nodes;<br> node_id | hostname  | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay<br>---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------<br> 0       | 10.0.0.58 | 5432 | up     | 0.333333  | primary | 145        | false             | 0<br> 1       | 10.0.0.59 | 5432 | up     | 0.333333  | standby | 11         | true              | 0<br> 2       | 10.0.0.36 | 5432 | unused | 0.333333  | standby | 0          | false             | 0<br><br></span></div><span style="font-family:monospace,monospace">Here you can see my issue in node 2.<br></span></div><span style="font-family:monospace,monospace">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 :<br><br>root@gc-lab-pgsql-1:~# netstat -anp | grep 10.0.0.57<br>tcp        0      0 <a href="http://10.0.0.58:5432">10.0.0.58:5432</a>          <a href="http://10.0.0.57:41323">10.0.0.57:41323</a>         ESTABLISHED 878/main: mathieu<br>tcp        0      0 <a href="http://10.0.0.58:5432">10.0.0.58:5432</a>          <a href="http://10.0.0.57:39822">10.0.0.57:39822</a>         ESTABLISHED 31799/main: mathieu<br>tcp        0      0 <a href="http://10.0.0.58:5432">10.0.0.58:5432</a>          <a href="http://10.0.0.57:39864">10.0.0.57:39864</a>         ESTABLISHED 31841/main: mathieu<br><br>root@gc-lab-pgsql-2:~# netstat -anp | grep 10.0.0.57<br>tcp        0      0 <a href="http://10.0.0.59:5432">10.0.0.59:5432</a>          <a href="http://10.0.0.57:42897">10.0.0.57:42897</a>         ESTABLISHED 23707/main: mathieu<br>tcp        0      0 <a href="http://10.0.0.59:5432">10.0.0.59:5432</a>          <a href="http://10.0.0.57:44398">10.0.0.57:44398</a>         ESTABLISHED 25526/main: mathieu<br>tcp        0      0 <a href="http://10.0.0.59:5432">10.0.0.59:5432</a>          <a href="http://10.0.0.57:42939">10.0.0.57:42939</a>         ESTABLISHED 23748/main: mathieu<br><br>root@gc-lab-pgsql-3:~# netstat -anp | grep 10.0.0.57<br></span></div><div><span style="font-family:monospace,monospace">&lt;Nothing&gt;<br><br></span></div><div><span style="font-family:monospace,monospace">Weird thing, I can see  the node 2 in the pool_pools<br></span><br><span style="font-family:monospace,monospace"><br># show pool_pools;</span></div><div><span style="font-family:monospace,monospace"> pool_pid |     start_time      | pool_id | backend_id | database | username |     create_time     | majorversion | minorversion | pool_counter | pool_backendpid | pool_connected<br>----------+---------------------+---------+------------+----------+----------+---------------------+--------------+--------------+--------------+-----------------+----------------<br> 5307     | 2016-11-25 10:22:28 | 0       | 0          |          |          |                     | 0            | 0            | 0            | 0               | 0<br> 5307     | 2016-11-25 10:22:28 | 0       | 1          |          |          |                     | 0            | 0            | 0            | 0               | 0<br> 5307     | 2016-11-25 10:22:28 | 0       | 2          |          |          |                     | 0            | 0            | 0            | 0               | 0<br> 5307     | 2016-11-25 10:22:28 | 1       | 0          |          |          |                     | 0            | 0            | 0            | 0               | 0<br> 5307     | 2016-11-25 10:22:28 | 1       | 1          |          |          |                     | 0            | 0            | 0            | 0               | 0<br> 5307     | 2016-11-25 10:22:28 | 1       | 2          |          |          |                     | 0            | 0            | 0            | 0               | 0<br> 5307     | 2016-11-25 10:22:28 | 2       | 0          |          |          |                     | 0            | 0            | 0            | 0               | 0<br><br><br></span></div><span style="font-family:monospace,monospace">I can&#39;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.<br></span></div><div><span style="font-family:monospace,monospace"><br></span></div><div><span style="font-family:monospace,monospace">I ran pgbench in both write and read on the pgpool, and connections to node 2 are never used.<br></span></div><div><span style="font-family:monospace,monospace"><br></span></div><span style="font-family:monospace,monospace">How can I troubleshoot that ? I could not find anything interesting in the logs.<br><br></span></div><span style="font-family:monospace,monospace">Thank you.<br></span></div><span style="font-family:monospace,monospace">Mathieu<br></span><div><div><div><div><div><div><div><span style="font-family:monospace,monospace"><br></span></div></div></div></div></div></div></div></div>