[pgpool-general: 2753] Re: Query regarding pgpool load balancing setup
Abhilash MB
abhilash.mb at poornam.com
Fri Apr 11 17:32:02 JST 2014
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