<div dir="ltr">Ok, looks like the connections I see on pgpool2 are just healthchecks and standard db SELECT calls:<div>
<p class=""><span class="">SELECT 1;</span></p><div><p class=""><span class="">SELECT </span>class.relname, blocking_activity.client_addr, blocking_activity.query FROM pg_catalog.pg_locks blocked JOIN pg_catalog.pg_stat_activity a ON a.pid=blocked.pid JOIN pg_catalog.pg_locks locks ON locks.locktype = blocked.locktype AND locks.DATABASE IS NOT DISTINCT FROM blocked.DATABASE AND locks.relation IS NOT DISTINCT FROM blocked.relation AND locks.page IS NOT DISTINCT FROM blocked.page AND locks.tuple IS NOT DISTINCT FROM blocked.tuple AND locks.virtualxid IS NOT DISTINCT FROM blocked.virtualxid AND locks.transactionid IS NOT DISTINCT FROM blocked.transactionid AND locks.classid IS NOT DISTINCT FROM blocked.classid AND locks.objid IS NOT DISTINCT FROM blocked.objid AND locks.objsubid IS NOT DISTINCT FROM blocked.objsubid AND locks.pid != blocked.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid=locks.pid JOIN pg_catalog.pg_class class ON class.oid=locks.relation WHERE NOT blocked.GRANTED;</p><p class="">I think we can consider this solved. Thank you, guys, for helping with this.</p><p class="">Regards,</p><p class="">Vlad</p></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Aug 22, 2016 at 3:41 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="">> Hello,<br>
><br>
> I attach node only when I start the failover node (PostgreSQL) after<br>
> primary is up and running. E.g. I start PostgreSQL on pgpool1, then I start<br>
> popool-II and it detects the backend.<br>
<br>
</span>Probably at this point a fail over happens because PostgreSQL pgpool2<br>
is not running. So far so good.<br>
<span class=""><br>
> Then, when I start pgpool2, I need to<br>
> manually attach it, so pgpool-II would know that the backend is online,<br>
> right?<br>
<br>
</span>Correct.<br>
<span class=""><br>
> Now nodes status. At this moment I see following nodes status:<br>
> node_id hostname port status lb_weight role select_cnt<br>
> 0 pgpool1 5432 2 0.500000 primary 66133198<br>
> 1 pgpool2 5432 2 0.500000 standby 0<br>
<br>
</span>I don't know which node you executed "show pool_nodes" but as long as<br>
the result of the command are same, it is ok.<br>
<br>
So you sometimes see write queries are sent to PostgreSQL on pgpool2?<br>
<span class=""><br>
> I have a sneaky feeling that lb_weight has something to do about what is<br>
> going on. However, if you take a look at the pgpool.conf attached to the<br>
> initial message, you'll find the backends configured like this:<br>
> backend_hostname0 = 'pgpool1'<br>
> backend_port0 = 5432<br>
> backend_weight0 = 1<br>
> backend_data_directory0 = '/var/lib/pgsql/9.4/data'<br>
> backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
><br>
> backend_hostname1 = 'pgpool2'<br>
> backend_port1 = 5432<br>
> backend_weight1 = 1<br>
> backend_data_directory1 = '/var/lib/pgsql/9.4/data'<br>
> backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
><br>
> In particular, backend_weight is set to 1 on both nodes.<br>
<br>
</span>Don't worry. The load balance weight is always "normalized" so that<br>
the tatal weight is 1.0.<br>
<span class=""><br>
> Also, as far as I<br>
> understand, this setting should not matter when load_balance_mode=off.<br>
<br>
</span>Correct.<br>
<div class="HOEnZb"><div class="h5"><br>
> Regards,<br>
> Vlad<br>
><br>
><br>
> On Sat, Aug 20, 2016 at 4:37 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
><br>
>> > Hi Lucas,<br>
>> ><br>
>> > I checked the log and found no failover entries. Here's how that<br>
>> 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>
>> Why do you need attach the backends? Pgpool-II automatically attach<br>
>> all backends valid in pgpool.conf.<br>
>><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>
>> 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>
>><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>
>> ><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<br>
>> = 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<br>
>> establishing<br>
>> >>> connections to hot standby server. As a result client applications<br>
>> start<br>
>> >>> failing as they cannot write to the database they're connected to. So<br>
>> far<br>
>> >>> the only solution for me is to keep the hot standby detached (which is<br>
>> 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<br>
>> 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<br>
>> all<br>
>> >>> clients connect to master only when both backends are attached? From<br>
>> 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>
>><br>
</div></div></blockquote></div><br></div>