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

[Christian Schwaderer]

Thanks!

I've tried that. However, pgpool keeps writing my node0 as "down" to its 
log file - no matter what I do. However, node0  is up! I can access it 
from withing psql and also from my app with node-postgres driver.

What can I do to find out why pgpool doesn't find my node0?

On 01.12.18 03:50, Bo Peng wrote:
> 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
>>
>
-- 
Christian Schwaderer, Software-Entwickler/software developer
------------------------------------------------------------
ivocoTec GmbH
Entwicklungsabteilung/IT department

Postplatz 3
D-16761 Hennigsdorf

https://ivocotec.de
Telefon/phone +49 (0)3302 20 63 230