<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="post-text" itemprop="text">
<p>Dear all,<br>
</p>
<p>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.</p>
<p>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:</p>
<pre class="lang-sql prettyprint prettyprinted" style=""><code><span class="pln">port </span><span class="pun">=</span><span class="pln"> </span><span class="lit">5437</span><span class="pln">
backend_hostname0 </span><span class="pun">=</span><span class="pln"> </span><span class="str">'192.168.56.177'</span><span class="pln">
backend_port0 </span><span class="pun">=</span><span class="pln"> </span><span class="lit">5432</span><span class="pln">
backend_weight0 </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
backend_data_directory0 </span><span class="pun">=</span><span class="pln"> </span><span class="str">'/var/lib/pgsql/data'</span><span class="pln">
backend_flag0 </span><span class="pun">=</span><span class="pln"> </span><span class="str">'ALLOW_TO_FAILOVER'</span><span class="pln">
backend_hostname1 </span><span class="pun">=</span><span class="pln"> </span><span class="str">'192.168.56.178'</span><span class="pln">
backend_port1 </span><span class="pun">=</span><span class="pln"> </span><span class="lit">5432</span><span class="pln">
backend_weight1 </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
backend_data_directory1 </span><span class="pun">=</span><span class="pln"> </span><span class="str">'/var/lib/pgsql/data'</span><span class="pln">
backend_flag1 </span><span class="pun">=</span><span class="pln"> </span><span class="str">'ALLOW_TO_FAILOVER'</span><span class="pln">
master_slave_mode </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">on</span><span class="pln">
master_slave_sub_mode </span><span class="pun">=</span><span class="pln"> </span><span class="str">'stream'</span></code></pre>
<p>(Hoping these are the most important things. I think I didn't
change the rest.)</p>
<p>Now if I connect with a Postgres-client (I've tested psql and
node-postgres) to 192.168.56.177:5437, I can do <code>SELECT</code>
statements, but no <code>UPDATE</code>/<code>INSERT</code>.
I'll always get <code>cannot execute UPDATE/INSERT in a
read-only transaction</code>.</p>
<p>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?</p>
<p><br>
</p>
<p>Best,</p>
<p>Christian</p>
</div>
</body>
</html>