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

[Abhilash MB]

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
>