[pgpool-general: 2729] Query regarding pgpool load balancing setup

Abhilash MB abhilash.mb at poornam.com
Tue Apr 8 13:37:54 JST 2014



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.

====
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






More information about the pgpool-general mailing list