<p>I have been learning Kubernetes for a few weeks and now I am trying to figure out the right way to connect a web server to a <code>statefulset</code> correctly.</p>
<p>Let's say I deployed a master-slave Postgres <code>statefulset</code> and now I will connect my web server to it. By using a cluster IP service, the requests will be load balanced across the master and the slaves for both reading (<code>SELECT</code>) and writing (<code>UPDATE</code>, <code>INSERT</code>, <code>DELETE</code>) records, right? But I can't do that because writing requests should be handled by the master. However, when I point my web server to the master using the headless service that will give us a DNS entry for each pod, I won't get any load balancing to the other slave replications and all of the requests will be handled by one instance and that is the master. So how am I supposed to connect them the right way? By obtaining both load balancing to all replications along with the slave in reading records and forwarding writing records requests to the master?</p>
<p>Should I use two endpoints in the web server and configure them in writing and reading records?</p>
<p>Or maybe I am using headless services and <code>statefulsets</code> the wrong way since I am new to Kubernetes?</p>

joe1531
<p>Well, your thinking is correct - the master should be read-write and replicas should be read only. How to configure it properly? There are different possible approaches.</p>
<hr />
<p>First approach is what you thinking about, to setup two headless services - one for accessing primary instances, the second one to access to the replica instances - good example is <a href="https://www.kubegres.io/doc/getting-started.html" rel="nofollow noreferrer">Kubegres</a>:</p>
<blockquote>
<p>In this example, Kubegres created 2 Kubernetes Headless services (of default type ClusterIP) using the name defined in YAML (e.g. &quot;mypostgres&quot;):</p>
<ul>
<li>a Kubernetes service &quot;mypostgres&quot; allowing to access to the Primary PostgreSql instances</li>
<li>a Kubernetes service &quot;mypostgres-replica&quot; allowing to access to the Replica PostgreSql instances</li>
</ul>
</blockquote>
<p>Then you will have two endpoints:</p>
<blockquote>
<p>Consequently, a client app running inside a Kubernetes cluster, would use the hostname &quot;mypostgres&quot; to connect to the Primary PostgreSql for read and write requests, and optionally it can also use the hostname &quot;mypostgres-replica&quot; to connect to any of the available Replica PostgreSql for read requests.</p>
</blockquote>
<p>Check <a href="https://www.kubegres.io/doc/getting-started.html" rel="nofollow noreferrer">this starting guide for more details</a>.</p>
<p>It's worth noting that there are many database solutions which are using this approach - another example is MySQL. <a href="https://kubernetes.io/docs/tasks/run-application/run-replicated-stateful-application/" rel="nofollow noreferrer">Here is a good article in Kubernetes documentation about setting MySQL using Stateful set</a>.</p>
<p>Another approach is to use some <a href="https://devopscube.com/deploy-postgresql-statefulset/" rel="nofollow noreferrer">middleware component which will act as a gatekeeper to the cluster, for example Pg-Pool</a>:</p>
<blockquote>
<p>Pg pool is a middleware component that sits in front of the Postgres servers and acts as a gatekeeper to the cluster.<br />
It mainly serves two purposes: Load balancing &amp; Limiting the requests.</p>
</blockquote>
<blockquote>
<ol>
<li><strong>Load Balancing:</strong>  Pg pool takes connection requests and queries. It analyzes the query to decide where the query should be sent.</li>
<li>Read-only queries can be handled by read-replicas. Write operations can only be handled by the primary server. In this way, it loads balances the cluster.</li>
<li><strong>Limits the requests:</strong>  Like any other system, Postgres has a limit on no. of concurrent connections it can handle gracefully.</li>
<li>Pg-pool limits the no. of connections it takes up and queues up the remaining. Thus, gracefully handling the overload.</li>
</ol>
</blockquote>
<p>Then you will have one endpoint for all operations - the Pg-Pool service. Check <a href="https://devopscube.com/deploy-postgresql-statefulset/" rel="nofollow noreferrer">this article for more details, including the whole setup process</a>.</p>
