[pgpool-general: 2746] Re: Query regarding pgpool load balancing setup

[Yugo Nagata]

Hi Abhilash,

If you can reproduce the problem that pool_nodes doesn't show 'primary',
could you please send all logs of pgpool and backends?

On Thu, 10 Apr 2014 12:46:00 +0530
Abhilash MB <abhilash.mb at poornam.com> wrote:

> Hi Yugo,
> 
> Could you please help me in sorting out the following issues?
> 
> Thank you,
> Abhilash
> 
> 
> On 04/10/2014 12:43 PM, Yugo Nagata wrote:
> > Hi,
> >
> > On Tue, 08 Apr 2014 10:07:54 +0530
> > Abhilash MB <abhilash.mb at poornam.com> wrote:
> >
> >>
> >> Hello,
> >>
> >> We are having a few doubts regarding pgpool load balancing setup with
> >> Master/Slave streaming replication.
> >>
> >> As per pgpool expert "Tatsuo Ishii", pgpool lower versions doesn't come
> >> up with "role" feature and hence, I have installed pgpool 3.3.3 version
> >> in a pair of master-slave nodes (101 & 102) and tried 'show pool_nodes'
> >> command; But it shows both the nodes as standby. Please see the snippet
> >> below.
> >>
> >> ========
> >> postgres at pgpool:~$ psql -p 9999
> >> psql (9.2.6)
> >> Type "help" for help.
> >>
> >> postgres=#
> >> postgres=# show pool_nodes;
> >>    node_id |   hostname    | port | status | lb_weight |  role
> >> ---------+---------------+------+--------+-----------+---------
> >>    0       | 172.17.10.101 | 5432 | 2      | 0.500000  | standby
> >>    1       | 172.17.10.102 | 5432 | 2      | 0.500000  | standby
> >> (2 rows)
> >> ========
> >> root at pgpool:~# grep backend_hostname /usr/local/etc/pgpool.conf
> >> #backend_hostname0 = 'host1'
> >> #backend_hostname1 = 'host2'
> >> backend_hostname0 = '172.17.10.101'
> >> backend_hostname1 = '172.17.10.102'
> >> ========
> >>
> >> When I created a database 'test',  Node 0: 172.17.10.101 handled that
> >> request.
> >>
> >> ====
> >> 2014-04-06 07:20:54 LOG:   pid 7457: DB node id: 0 backend pid: 2303
> >> statement: create database test;
> >> ====
> >>
> >> I don't understand why its not showing 'primary' in the 'show
> >> pool_nodes' command and also how pgpool came to know that 172.17.10.101
> >> is the master server ?
> >>
> >>
> >> I checked pgpool log, postgresql log of master and slave but couldn't
> >> see the command "SELECT pg_is_in_recovery()". Please see the following
> >> snippets.
> > If you configure log_statement properly, sending the query to backends
> > would fail for some reason or others. pgpool log messages begining with
> > "find_primary_node" might be clue.
> >
> >> ====
> >> master:
> >> root at pgpool:~# grep pg_is_in_recovery
> >> /var/log/postgresql/postgresql-9.2-main.log
> >> root at pgpool:~#
> >> ~~~~
> >> slave:
> >> root at pgpool:~# grep pg_is_in_recovery
> >> /var/log/postgresql/postgresql-9.2-main.log
> >> root at pgpool:~#
> >> ====
> >>
> >> The unexpected thing happened next..;
> >>
> >> When I swapped backend_hostname_ID, pgpool took  backend_hostname1 -
> >> '172.17.10.102' as Master and send write query to that node! Please see
> >> the logs:
> >>
> >> ====
> >> root at pgpool:~# grep backend_hostname /usr/local/etc/pgpool.conf
> >> #backend_hostname0 = 'host1'
> >> #backend_hostname1 = 'host2'
> >> backend_hostname0 = '172.17.10.101'
> >> backend_hostname1 = '172.17.10.102'
> >> ====
> >> test=# create database test_master;
> >> ERROR:  cannot execute CREATE DATABASE in a read-only transaction
> >> test=#
> >> ====
> >> 2014-04-06 07:49:30 LOG:   pid 7652: pool_send_and_wait: Error or notice
> >> message from backend: : DB node id: 0 backend pid: 7669 statement:
> >> create database test_master; message: cannot execute CREATE DATABASE in
> >> a read-only transaction
> >> ====
> >>
> >>
> >> Replication sender and receiver processes are given below:
> >>
> >> ====
> >> root at pgpool:~# ps aux | grep  receiver
> >> postgres  7498  0.0  0.8 138028  4336 ?        Ss   07:13   0:00
> >> postgres: wal receiver process   streaming 0/E048C60
> >> root      7694  0.0  0.1   8068  1040 pts/1    S+   08:05   0:00 grep
> >> --color=auto receiver
> >> root at pgpool:~#
> >> root at pgpool:~# hostname -i
> >> 172.17.10.102
> >> root at pgpool:~#
> >> ~~~~~
> >> root at pgpool:~# ps aux | grep sender
> >> postgres  2302  0.0  1.6 127788  4420 ?        Ss   07:13   0:00
> >> postgres: wal sender process postgres 172.17.10.102(44919) streaming
> >> 0/E048CF8
> >> root      2517  0.0  0.3   8060   936 pts/0    S+   08:06   0:00 grep
> >> --color=auto sender
> >> root at pgpool:~# hostname -i
> >> 172.17.10.101
> >> ====
> >>
> >> Could you please shed some light on these issues ?
> >>
> >>
> >> Regards,
> >> Abhilash
> >>
> >>
> >>
> >>
> >> _______________________________________________
> >> pgpool-general mailing list
> >> pgpool-general at pgpool.net
> >> http://www.pgpool.net/mailman/listinfo/pgpool-general
> >
> 


-- 
Yugo Nagata <nagata at sraoss.co.jp>