[pgpool-general: 2755] Re: Query regarding pgpool load balancing setup
Abhilash MB
abhilash.mb at poornam.com
Fri Apr 11 18:45:24 JST 2014
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
>
More information about the pgpool-general
mailing list