[pgpool-general: 2744] Re: Query regarding pgpool load balancing setup
Yugo Nagata
nagata at sraoss.co.jp
Thu Apr 10 16:13:18 JST 2014
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