[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