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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.