[pgpool-general: 2567] Re: Why "show pool_nodes" indicating both upstream nodes are in "standby role"?

[Tatsuo Ishii]

> Hi, Tatsuo,
> 
> Thanks for the answer. I think it is the case in our setting. We did not
> set sr_check_user account correctly.
> 
> However, it still puzzles me somewhat.  Is there any logic which pgpool
> uses to decide which node is the primary even if  the sr_check_user is not
> set correctly. Otherwise, how come the pgpool still seems to be able to
> correctly dispatch the data-changing SQL request to the primary server in
> our setup?

In this case pgpool-II sends DMLs, DCLs etc. to the node which is
physically appears firstly in pgpool.conf.

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

> Regards,
> 
> Weikai (Victor)  Xie
> 
> 
> On Wed, Feb 12, 2014 at 12:12 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> Have you set sr_check_user and sr_check_password correctly?
>>
>> sr_check_user = 'nobody'
>>                                    # Streaming replication check user
>>                                    # This is neccessary even if you
>> disable streaming
>>                                    # replication delay check by
>> sr_check_period = 0
>> sr_check_password = ''
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > Hi,
>> >
>> > Our pgpool2 configuration uses load_balancing with two upstream nodes.
>> The
>> > two nodes are configured to using streaming replication. All seems work
>> > well. I also tried to insert some data in my application which is using
>> the
>> > pgpool and it works just fine.
>> >
>> > However, when I use psql to connect to pgpool2 and issue "show
>> pool_nodes",
>> > it tells me both nodes are standby.
>> >
>> > ubuntu=>show pool_nodes;
>> >  node_id |  hostname  | port | status | lb_weight |  role
>> > ---------+------------+------+--------+-----------+---------
>> >  0       | 10.0.2.46  | 5432 | 2      | 0.500000  | standby
>> >  1       | 10.0.1.248 | 5432 | 2      | 0.500000  | standby
>> > (2 rows)
>> >
>> > It puzzled me a lot. I'm expecting one should be the master (or
>> primary?).
>> >
>> > The key fields of my pgpool.conf are shown below
>> > ...
>> > master_slave_mode = on
>> > master_slave_sub_mode = 'stream'
>> > load_balance_mode = on
>> > parallel_mode = off
>> > replication_mode = off
>> > ...
>> >
>> >
>> > I also run select pg_is_in_recovery() on both upperstream nodes. They
>> > correctly return t on the master node and f on the slave node.
>> >
>> > On slave node:
>> >
>> > caboodl at ip-10-0-1-248:~$ psql
>> > psql (9.1.10)
>> > Type "help" for help.
>> >
>> > ubuntu=> select pg_is_in_recovery();
>> >  pg_is_in_recovery
>> > -------------------
>> >  t
>> > (1 row)
>> >
>> >
>> > On master node:
>> >
>> > ubuntu at ip-10-0-2-46:~$ psql
>> > psql (9.1.10)
>> > Type "help" for help.
>> > caboodl=> select pg_is_in_recovery();
>> >  pg_is_in_recovery
>> > -------------------
>> >  f
>> > (1 row)
>> >
>> >
>> > BTW. Is slave equals to standby and master equals to primary in the
>> > terminology of pgpool2?
>> >
>> >
>> >
>> > Regards,
>> >
>> > Weikai (Victor)  Xie
>>