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

[Bo Peng]

Hi,

I think Pgpool-II considers the primary node0 is down, 
even if node0 is up.

Please remove pgpool_status file which you specified in "logdir" paramater
and restart Pgpool-II.


On Fri, 30 Nov 2018 10:58:06 +0100
Christian Schwaderer <schwaderer at ivocotec.de> wrote:

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


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan