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

[Tatsuo Ishii]

> That explains.
> 
> I have one more related question. As far as I know, the convention of
> postgres is that if you connect to postgresql, the default database to open
> is the one with the same name as the name of the account you are connecting
> with. Does that mean if I set sr_check_user=foo, I should also make sure
> there is a database named foo in all the nodes?

No you don't need to. Pgpool-II connects to "postgres" database in
this case.

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 4:24 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > 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
>> >>
>>