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

Abhilash MB abhilash.mb at poornam.com
Thu Apr 17 23:35:27 JST 2014


Hi Yugo,

May I ask you one more doubt..

What is your opinion about integrating "repmgr" and "pgpool" ? Is it 
possible ?
If yes, can you please let me know how it is gonna work together, I mean 
the architecture, query flow and replication ?

Thank you,
Abhilash



On 04/11/2014 03:15 PM, Abhilash MB wrote:
> Hi Yugo,
>
> Thanks a lot for your help. The issue has been fixed :)
>
> =======
> root at 102:/var/run# pgpool -n &
> [1] 1171
> root at 102:/var/run# 2014-04-11 05:39:18 LOG:   pid 1171: pgpool-II 
> successfully started. version 3.3.3 (tokakiboshi)
> 2014-04-11 05:39:18 LOG:   pid 1171: find_primary_node: primary node 
> id is 0
> =======
> postgres=# show pool_nodes;
>  node_id |   hostname    | port | status | lb_weight |  role
> ---------+---------------+------+--------+-----------+---------
>  0       | 172.17.10.101 | 5432 | 2      | 0.500000  | primary
>  1       | 172.17.10.102 | 5432 | 2      | 0.500000  | standby
> =======
>
> Regards,
> Abhilash
>
>
>
> On 04/11/2014 02:48 PM, Yugo Nagata wrote:
>> Hi Abhilash,
>>
>> I found error messages 'find_primary_node: make_persistent_connection 
>> failed'
>> and 'role "nobody" does not exist'. This means pgpool failed to find
>> the primary node because user executing pg_is_in_recovery() doesn't 
>> exist
>> in backends. Please consider the user specified by sr_check_user exists.
>>
>> On Fri, 11 Apr 2014 14:02:02 +0530
>> Abhilash MB <abhilash.mb at poornam.com> wrote:
>>
>>> Hi Yugo,
>>>
>>> Thanks a lot for the response.
>>>
>>> I have setup a load balancing master-slave cluster in pgpool with
>>> streaming replication. Then I created a database "mermidons" via pgpool
>>> console. I could see that "show pool_nodes" is showing all the back-end
>>> nodes as standby.
>>>
>>> But when I created a database, pgpool took backend_hostname0 as master
>>> and redirected that write query to hostname0.
>>>
>>> As you said, I have collected the pgpool and postgresql logs; 
>>> However, I
>>> couldn't see the command
>>>
>>> "SELECT pg_is_in_recovery()" in the logs.
>>>
>>>
>>> ========
>>> root at 102:~# grep backend_hostname /usr/local/etc/pgpool.conf
>>> backend_hostname0 = '172.17.10.101'
>>> backend_hostname1 = '172.17.10.102'
>>> ========
>>> postgres=# create database mermidons;
>>> CREATE DATABASE
>>> postgres=#
>>> 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)
>>> ========
>>>
>>>
>>> I have pasted the logs for your reference :
>>>
>>> =====
>>> pgpool log :
>>>
>>> ---------+---------------+------+--------+-----------+---------
>>> oot at 102:/var/run# 2014-04-11 03:59:22 LOG:   pid 953: pgpool-II
>>> successfully started. version 3.3.3 (tokakiboshi)
>>> 2014-04-11 03:59:22 ERROR: pid 953: pool_read: EOF encountered with 
>>> backend
>>> 2014-04-11 03:59:22 ERROR: pid 953: s_do_auth: error while reading
>>> message kind
>>> 2014-04-11 03:59:22 ERROR: pid 953: make_persistent_db_connection:
>>> s_do_auth failed
>>> 2014-04-11 03:59:22 ERROR: pid 953: find_primary_node:
>>> make_persistent_connection failed
>>> 2014-04-11 03:59:22 ERROR: pid 953: pool_read: EOF encountered with 
>>> backend
>>> 2014-04-11 03:59:22 ERROR: pid 953: s_do_auth: error while reading
>>> message kind
>>> 2014-04-11 03:59:22 ERROR: pid 953: make_persistent_db_connection:
>>> s_do_auth failed
>>> 2014-04-11 03:59:22 ERROR: pid 953: find_primary_node:
>>> make_persistent_connection failed
>>> 2014-04-11 03:59:29 LOG:   pid 980: connection received: host=[local]
>>> 2014-04-11 04:00:28 LOG:   pid 980: statement: create database 
>>> mermidons;
>>> 2014-04-11 04:00:28 LOG:   pid 980: DB node id: 0 backend pid: 8813
>>> statement: create database mermidons;
>>> 2014-04-11 04:02:44 LOG:   pid 980: statement: show pool_nodes;
>>> ---------+---------------+------+--------+-----------+---------
>>>
>>> postgresql Master log:
>>>
>>> ---------+---------------+------+--------+-----------+---------
>>> 2014-04-11 03:53:35 EDT LOG:  autovacuum launcher started
>>> 2014-04-11 03:53:35 EDT LOG:  database system is ready to accept 
>>> connections
>>> 2014-04-11 03:53:35 EDT LOG:  incomplete startup packet
>>> 2014-04-11 03:56:00 EDT FATAL:  role "nobody" does not exist
>>> 2014-04-11 03:57:58 EDT LOG:  received fast shutdown request
>>> 2014-04-11 03:57:58 EDT LOG:  aborting any active transactions
>>> 2014-04-11 03:57:58 EDT LOG:  autovacuum launcher shutting down
>>> 2014-04-11 03:57:58 EDT LOG:  shutting down
>>> 2014-04-11 03:57:58 EDT LOG:  database system is shut down
>>> 2014-04-11 03:57:59 EDT LOG:  database system was shut down at
>>> 2014-04-11 03:57:58 EDT
>>> 2014-04-11 03:57:59 EDT LOG:  autovacuum launcher started
>>> 2014-04-11 03:57:59 EDT LOG:  database system is ready to accept 
>>> connections
>>> 2014-04-11 03:58:00 EDT LOG:  incomplete startup packet
>>> 2014-04-11 03:58:13 EDT FATAL:  role "nobody" does not exist
>>> 2014-04-11 03:59:22 EDT FATAL:  role "nobody" does not exist
>>> ---------+---------------+------+--------+-----------+---------
>>>
>>>
>>>
>>> postgresql Slave log:
>>>
>>> ---------+---------------+------+--------+-----------+---------
>>> 2014-04-11 03:59:11 EDT LOG:  shutting down
>>> 2014-04-11 03:59:11 EDT LOG:  database system is shut down
>>> 2014-04-11 03:59:12 EDT LOG:  database system was shut down in recovery
>>> at 2014-04-11 03:59:11 EDT
>>> 2014-04-11 03:59:12 EDT LOG:  entering standby mode
>>> 2014-04-11 03:59:12 EDT LOG:  consistent recovery state reached at 
>>> 0/303F540
>>> 2014-04-11 03:59:12 EDT LOG:  record with zero length at 0/303F540
>>> 2014-04-11 03:59:12 EDT LOG:  database system is ready to accept read
>>> only connections
>>> 2014-04-11 03:59:12 EDT LOG:  streaming replication successfully
>>> connected to primary
>>> 2014-04-11 03:59:12 EDT LOG:  incomplete startup packet
>>> 2014-04-11 03:59:22 EDT FATAL:  role "nobody" does not exist
>>> 2014-04-11 04:00:29 EDT LOG:  redo starts at 0/303F540
>>> (END)
>>> ---------+---------------+------+--------+-----------+---------
>>> =====
>>>
>>>
>>> Could you please help to sort this out ?
>>>
>>>
>>> Thank you,
>>> Abhilash
>>>
>>>
>>>
>>>
>>>
>>>
>>> On 04/10/2014 12:53 PM, Yugo Nagata wrote:
>>>> 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
>>
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
> DISCLAIMER  :  This email and any files transmitted with it are 
> property of Poornam Info Vision Pvt. Ltd. This email contains 
> confidential information intended solely for the use of the individual 
> or entity to whom they are addressed. If you have received this email 
> in error please notify the sender immediately and delete this e-mail 
> from your system. If you are not the intended recipient you are 
> notified that disclosing, copying, distributing or taking any action 
> in reliance on the contents of this information is strictly prohibited.
>
> Warning: Although the company has taken reasonable precautions to 
> ensure no viruses are present in this email, the company cannot accept 
> responsibility for any loss or damage arising from the use of this 
> email or attachments.
>



More information about the pgpool-general mailing list