<div dir="ltr">Hello,<div><br></div><div>I attach node only when I start the failover node (PostgreSQL) after primary is up and running. E.g. I start PostgreSQL on pgpool1, then I start popool-II and it detects the backend. Then, when I start pgpool2, I need to manually attach it, so pgpool-II would know that the backend is online, right?</div><div><br></div><div>Now nodes status. At this moment I see following nodes status:</div><div><font face="monospace, monospace">node_id hostname port status lb_weight role select_cnt</font></div><div><div><font face="monospace, monospace">0 pgpool1 5432 2 0.500000 primary 66133198</font></div><div><font face="monospace, monospace">1 pgpool2 5432 2 0.500000 standby </font>0</div></div><div><br></div><div>I have a sneaky feeling that lb_weight has something to do about what is going on. However, if you take a look at the pgpool.conf attached to the initial message, you'll find the backends configured like this:</div><div><div><font face="monospace, monospace">backend_hostname0 = 'pgpool1'</font></div><div><font face="monospace, monospace">backend_port0 = 5432</font></div><div><font face="monospace, monospace">backend_weight0 = 1</font></div><div><font face="monospace, monospace">backend_data_directory0 = '/var/lib/pgsql/9.4/data'</font></div><div><font face="monospace, monospace">backend_flag0 = 'ALLOW_TO_FAILOVER'</font></div><div><font face="monospace, monospace"><br></font></div><div><font face="monospace, monospace">backend_hostname1 = 'pgpool2'</font></div><div><font face="monospace, monospace">backend_port1 = 5432</font></div><div><font face="monospace, monospace">backend_weight1 = 1</font></div><div><font face="monospace, monospace">backend_data_directory1 = '/var/lib/pgsql/9.4/data'</font></div><div><font face="monospace, monospace">backend_flag1 = 'ALLOW_TO_FAILOVER'</font></div></div><div><br></div><div>In particular, backend_weight is set to 1 on both nodes. Also, as far as I understand, this setting should not matter when load_balance_mode=off.</div><div><br></div><div>Regards,</div><div>Vlad</div><div><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Sat, Aug 20, 2016 at 4:37 PM, 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"><span class="">> Hi Lucas,<br>
><br>
> I checked the log and found no failover entries. Here's how that happens. I<br>
> start two postgres backends (master-slave streaming replication) and<br>
> pgpool-II instance. Then I attach both of the nodes and initially all the<br>
> clients get connected to master only.<br>
<br>
</span>Why do you need attach the backends? Pgpool-II automatically attach<br>
all backends valid in pgpool.conf.<br>
<span class=""><br>
> I see that with ps ax | grep<br>
> postgres. After some time new clients start getting connected to the hot<br>
> standby node while older clients are still connected to master. Again, I<br>
> see that with ps ax | grep postgres. In that case both master and hot<br>
> standby have pgpool-II connected. That's what concerns me the most. If<br>
> there was a failover event, master would've been detached and there would<br>
> be no pgpool-II connections there.<br>
<br>
</span>Can you connect to pgool using psql then issue "show pool_nodes" when<br>
pgpool starts to behaves like this? This should show which is the<br>
primary node (role) and which node should be the node the query routes<br>
to (load_balance_node).<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>
> Vlad<br>
><br>
> On Sat, Aug 20, 2016 at 1:25 AM, Lucas Luengas <<a href="mailto:lucasluengas@gmail.com">lucasluengas@gmail.com</a>><br>
> wrote:<br>
><br>
>> Hello.<br>
>><br>
>> Have you checked pgpool log file? Maybe a failover happened?<br>
>><br>
>> On Fri, Aug 19, 2016 at 10:48 PM, Vlad Novikov <<a href="mailto:xou.slackware@gmail.com">xou.slackware@gmail.com</a>><br>
>> wrote:<br>
>><br>
>>> Hello,<br>
>>><br>
>>> I'm have setup pgpool-II with 2 backends in streaming mode (see<br>
>>> configuration file attached). In particular, I have load_balance_mode = off<br>
>>> to make sure that in this pool all connections will be established to<br>
>>> streaming master only. However, over some time I see pgpool-II establishing<br>
>>> connections to hot standby server. As a result client applications start<br>
>>> failing as they cannot write to the database they're connected to. So far<br>
>>> the only solution for me is to keep the hot standby detached (which is not<br>
>>> a good idea in terms of automated failover).<br>
>>> Pgpool-II starts behaving like this at about 100 clients connected.<br>
>>> PostgreSQL max_connections is set to 900 and with hot standby detached<br>
>>> there are no connection issues reported (all clients can connect to the<br>
>>> backend with no issues).<br>
>>> I use pgpool-II and PostgreSQL provided by PostgreSQL official repository.<br>
>>> PostgreSQL 9.4.9<br>
>>> pgpool-II 3.5.3<br>
>>> OS: CentOS 7.2<br>
>>><br>
>>> Is there anything I need to change in the configuration file to make all<br>
>>> clients connect to master only when both backends are attached? From what I<br>
>>> understand that is expected with load_balance_mode = off.<br>
>>><br>
>>> Regards,<br>
>>> Vlad<br>
>>><br>
>>> ______________________________<wbr>_________________<br>
>>> pgpool-general mailing list<br>
>>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/<wbr>listinfo/pgpool-general</a><br>
>>><br>
>>><br>
>><br>
</div></div></blockquote></div><br></div></div>