[pgpool-general: 4936] Re: Load balancing issue

[Tatsuo Ishii]

> Hi Lucas,
> 
> I checked the log and found no failover entries. Here's how that happens. I
> start two postgres backends (master-slave streaming replication) and
> pgpool-II instance. Then I attach both of the nodes and initially all the
> clients get connected to master only.

Why do you need attach the backends? Pgpool-II automatically attach
all backends valid in pgpool.conf.

> I see that with ps ax | grep
> postgres. After some time new clients start getting connected to the hot
> standby node while older clients are still connected to master. Again, I
> see that with ps ax | grep postgres. In that case both master and hot
> standby have pgpool-II connected. That's what concerns me the most. If
> there was a failover event, master would've been detached and there would
> be no pgpool-II connections there.

Can you connect to pgool using psql then issue "show pool_nodes" when
pgpool starts to behaves like this? This should show which is the
primary node (role) and which node should be the node the query routes
to (load_balance_node).

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

> Vlad
> 
> On Sat, Aug 20, 2016 at 1:25 AM, Lucas Luengas <lucasluengas at gmail.com>
> wrote:
> 
>> Hello.
>>
>> Have you checked pgpool log file?  Maybe a failover happened?
>>
>> On Fri, Aug 19, 2016 at 10:48 PM, Vlad Novikov <xou.slackware at gmail.com>
>> wrote:
>>
>>> Hello,
>>>
>>> I'm have setup pgpool-II with 2 backends in streaming mode (see
>>> configuration file attached). In particular, I have load_balance_mode = off
>>> to make sure that in this pool all connections will be established to
>>> streaming master only. However, over some time I see pgpool-II establishing
>>> connections to hot standby server. As a result client applications start
>>> failing as they cannot write to the database they're connected to. So far
>>> the only solution for me is to keep the hot standby detached (which is not
>>> a good idea in terms of automated failover).
>>> Pgpool-II starts behaving like this at about 100 clients connected.
>>> PostgreSQL max_connections is set to 900 and with hot standby detached
>>> there are no connection issues reported (all clients can connect to the
>>> backend with no issues).
>>> I use pgpool-II and PostgreSQL provided by PostgreSQL official repository.
>>> PostgreSQL 9.4.9
>>> pgpool-II 3.5.3
>>> OS: CentOS 7.2
>>>
>>> Is there anything I need to change in the configuration file to make all
>>> clients connect to master only when both backends are attached? From what I
>>> understand that is expected with load_balance_mode = off.
>>>
>>> Regards,
>>> Vlad
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>>
>>