[pgpool-general: 6316] pgpool connection is read-only (can SELECT, but not UPDATE or INSERT)

[Christian Schwaderer]

Dear all,

On two Ubuntu 18.04 Virtual Machines, I've setup PostgreSQL 11 on each, 
one being master, the other one hot-standy. The replication works as I 
expect.

Now I want to add a pgpool-layer for failover. I've installed pgpool 
4.0.2 on the first VM and configured it with these settings:

|port =5437backend_hostname0 ='192.168.56.177'backend_port0 
=5432backend_weight0 =1backend_data_directory0 
='/var/lib/pgsql/data'backend_flag0 
='ALLOW_TO_FAILOVER'backend_hostname1 ='192.168.56.178'backend_port1 
=5432backend_weight1 =1backend_data_directory1 
='/var/lib/pgsql/data'backend_flag1 
='ALLOW_TO_FAILOVER'master_slave_mode =onmaster_slave_sub_mode ='stream'|

(Hoping these are the most important things. I think I didn't change the 
rest.)

Now if I connect with a Postgres-client (I've tested psql and 
node-postgres) to 192.168.56.177:5437, I can do |SELECT| statements, but 
no |UPDATE|/|INSERT|. I'll always get |cannot execute UPDATE/INSERT in a 
read-only transaction|.

Is this somehow connected to misconfigured load-balancing? I'm quite new 
to this, but as far as I understand, pgpool can be configured to 
distribute read statements among slaves, while all write statements 
should go to the master. However, this is not what I want to have. So, 
what I'm a doing wrong?


Best,

Christian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20181130/303780ab/attachment.html>