<div dir="ltr"><div>Hello Tatsuo,<br><br></div><div>After reviewing one more time, I've seen that my third node missed the parallel_mode=1,<br><br></div><div>Now I can see it up and as secondary, but I can see that not all my nodes have load_balance_node to true, I don't know if this is normal ?<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 | 5433 | up | 0.333333 | primary | 0 | false | 0<br> 1 | 10.0.0.59 | 5433 | up | 0.333333 | standby | 0 | false | 0<br> 2 | 10.0.0.36 | 5433 | up | 0.333333 | standby | 0 | true | 0<br><br></div><div>Thank you.<br></div><div>Best regards,<br></div><div>Mathieu<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Dec 7, 2016 at 6:43 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">It seems there's something wrong in you pgpool.conf (especially<br>
in the backend.. section). Can you show us pgpool.conf?<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
<div class="HOEnZb"><div class="h5"><br>
> Hello,<br>
><br>
> I'm trying to deploy a high availability setup with PostgreSQL 9.6 and<br>
> PgPool 3.6.0.<br>
> Everything was working fine up to the second host, I could get queries load<br>
> balanced (I use streaming replication)<br>
><br>
> But then I wanted to add a 3rd backend to I can have more read load<br>
> balancing (1 master streaming to 2 replicas), so I just took a snapshot of<br>
> the first replica and created another instance of it as second replica, it<br>
> 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<br>
> the second one, also added it to the list of the backends in<br>
> trusted_servers.<br>
><br>
> But then, since, I can't get it to leave the "unused" state :<br>
><br>
> # show pool_nodes;<br>
> node_id | hostname | port | status | lb_weight | role | select_cnt |<br>
> load_balance_node | replication_delay<br>
> ---------+-----------+------+-<wbr>-------+-----------+---------+<wbr>------------+-----------------<wbr>--+-------------------<br>
> 0 | 10.0.0.58 | 5432 | up | 0.333333 | primary | 145 |<br>
> false | 0<br>
> 1 | 10.0.0.59 | 5432 | up | 0.333333 | standby | 11 |<br>
> true | 0<br>
> 2 | 10.0.0.36 | 5432 | unused | 0.333333 | standby | 0 |<br>
> false | 0<br>
><br>
> Here you can see my issue in node 2.<br>
> When doing tcpdump or netstats, I can also see that there is not<br>
> connections from the pgpool to the postgresql on port 5432, but I can see a<br>
> ping coming from the pgpool to this server, the others servers have active<br>
> 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" rel="noreferrer" target="_blank">10.0.0.58:5432</a> <a href="http://10.0.0.57:41323" rel="noreferrer" target="_blank">10.0.0.57:41323</a><br>
> ESTABLISHED 878/main: mathieu<br>
> tcp 0 0 <a href="http://10.0.0.58:5432" rel="noreferrer" target="_blank">10.0.0.58:5432</a> <a href="http://10.0.0.57:39822" rel="noreferrer" target="_blank">10.0.0.57:39822</a><br>
> ESTABLISHED 31799/main: mathieu<br>
> tcp 0 0 <a href="http://10.0.0.58:5432" rel="noreferrer" target="_blank">10.0.0.58:5432</a> <a href="http://10.0.0.57:39864" rel="noreferrer" target="_blank">10.0.0.57:39864</a><br>
> 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" rel="noreferrer" target="_blank">10.0.0.59:5432</a> <a href="http://10.0.0.57:42897" rel="noreferrer" target="_blank">10.0.0.57:42897</a><br>
> ESTABLISHED 23707/main: mathieu<br>
> tcp 0 0 <a href="http://10.0.0.59:5432" rel="noreferrer" target="_blank">10.0.0.59:5432</a> <a href="http://10.0.0.57:44398" rel="noreferrer" target="_blank">10.0.0.57:44398</a><br>
> ESTABLISHED 25526/main: mathieu<br>
> tcp 0 0 <a href="http://10.0.0.59:5432" rel="noreferrer" target="_blank">10.0.0.59:5432</a> <a href="http://10.0.0.57:42939" rel="noreferrer" target="_blank">10.0.0.57:42939</a><br>
> ESTABLISHED 23748/main: mathieu<br>
><br>
> root@gc-lab-pgsql-3:~# netstat -anp | grep 10.0.0.57<br>
> <Nothing><br>
><br>
> Weird thing, I can see the node 2 in the pool_pools<br>
><br>
><br>
> # show pool_pools;<br>
> pool_pid | start_time | pool_id | backend_id | database |<br>
> username | create_time | majorversion | minorversion | pool_counter<br>
> | pool_backendpid | pool_connected<br>
> ----------+-------------------<wbr>--+---------+------------+----<wbr>------+----------+------------<wbr>---------+--------------+-----<wbr>---------+--------------+-----<wbr>------------+----------------<br>
> 5307 | 2016-11-25 10:22:28 | 0 | 0 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
> 5307 | 2016-11-25 10:22:28 | 0 | 1 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
> 5307 | 2016-11-25 10:22:28 | 0 | 2 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
> 5307 | 2016-11-25 10:22:28 | 1 | 0 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
> 5307 | 2016-11-25 10:22:28 | 1 | 1 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
> 5307 | 2016-11-25 10:22:28 | 1 | 2 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
> 5307 | 2016-11-25 10:22:28 | 2 | 0 |<br>
> | | | 0 | 0 |<br>
> 0 | 0 | 0<br>
><br>
><br>
> I can't find why, node 1 and 2 have exactly the same configuration, and in<br>
> everything is the same in my pgpool configuration about those 2 nodes.<br>
><br>
> I ran pgbench in both write and read on the pgpool, and connections to node<br>
> 2 are never used.<br>
><br>
> How can I troubleshoot that ? I could not find anything interesting in the<br>
> logs.<br>
><br>
> Thank you.<br>
> Mathieu<br>
</div></div></blockquote></div><br></div>