<p>I have the following architecture for the PostgreSQL cluster:</p>
<p><a href="https://i.stack.imgur.com/TlP1l.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TlP1l.png" alt="enter image description here" /></a></p>
<p>Here, there are multiple clients that interacts with PostgreSQL pods via pgpool, the issue is, when the pod (could be <code>pgpool</code> or <code>PostgreSQL</code> pod) terminates (for multiple reasons) the client is getting impacts and have to recreate the connection. For example in this diagram, if <code>postgresql-1</code> pod terminates then <code>client-0</code> will have to recreate the connection with the cluster.</p>
<p>Is there a way in kubernetes to handle it so that connections to <code>pgpool k8s service</code> are load balanced/ recreated to other pods so that the clients do not see the switch over <strong>and are not impacted</strong>?</p>
<p>Please note these are TCP connections and not HTTP connections (which are stateless). Also, all the PostgreSQL pods are <a href="https://www.postgresql.org/docs/10/runtime-config-wal.html#SYNCHRONOUS-COMMIT-MATRIX" rel="nofollow noreferrer">always in sync with remote_apply</a>.</p>

<blockquote>
<p>Is there a way in kubernetes to handle it so that connections to pgpool k8s service are load balanced/ recreated to other pods...</p>
</blockquote>
<p>Connections to pgpool k8s service is load balance by kube-proxy. Endpoints (pgpool pods) that back the service will automatically be update whenever there's a change (eg. scaling) in pods population.</p>
<blockquote>
<p>...so that the clients do not see the switch over and are not impacted?</p>
</blockquote>
<p>Should the pgpool pod that the client connected gets terminated, the client tcp state become invalid (eg. void remote IP). There's no need to keep such connection alive but re-connect to the pgpool <strong>service</strong> where kube-proxy will route you to next available pgpool pod. The actual connection to backend database is managed by pgpool including database failover. With pgpool as the proxy, you do not need to worry about database switching.</p>
