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 pgpoolII-layer for failover. I've installed pgpool on the first VM and configured it with these settings:
port = 5437 backend_hostname0 = '192.168.56.177' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '192.168.56.178' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/data' backend_flag1 = 'ALLOW_TO_FAILOVER' master_slave_mode = on master_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
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?