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

[Yugo Nagata]

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>