[pgpool-general: 2754] Re: Query regarding pgpool load balancing setup
Yugo Nagata
nagata at sraoss.co.jp
Fri Apr 11 18:18:17 JST 2014
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
> >
>
--
Yugo Nagata <nagata at sraoss.co.jp>
More information about the pgpool-general
mailing list