Load Balancing with Connection Manager v6.0.2

You can use an external load balancer to distribute traffic across multiple Connection Manager instances. Treat the Connection Manager ports as you would with any other Postgres port, but using the read-write ports for write traffic and the read-only ports for read traffic.

Depending on your load balancer, you have a number of options for how to configure it.

Connection Manager routing

If you want the Connection Manager to route traffic to the write leader, simply connect to the Connection Manager's read-write port. The Connection Manager will route the traffic to the write leader of the node group the node is a member of.

HAProxy example: Connection Manager routing

With HAProxy, you could use a configuration similar to the following example to load balance between three Connection Manager instances, each running on different hosts and ports. Read write traffic to haproxy port 5010 is directed to the connection manager's read-write port (default 6432) on any one of the nodes, and read-only traffic to haproxy port 5011 is directed to the connection manager's read-only port (default 6433). The latter is used for read-only traffic, which is distributed across the nodes in a round-robin fashion.

global
    maxconn 100

defaults
    log     global
    mode    tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind 127.0.0.1:7000
    stats enable
    stats uri /

listen read-write
    bind *:5010
    option pgsql-check user checkuser postgres
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 host1:6432 maxconn 100 check port 6432
    server node2 host2:6432 maxconn 100 check port 6432
    server node3 host3:6432 maxconn 100 check port 6432

listen read-only
    balance roundrobin
    bind *:5011
    option pgsql-check user checkuser postgres
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 host1:6433 maxconn 100 check port 6433
    server node2 host2:6433 maxconn 100 check port 6433
    server node3 host3:6433 maxconn 100 check port 6433

Direct routing

You may wish to connect directly to the nodes in your cluster, bypassing the Connection Manager. You can still leverage the Connection Manager by consulting its HTTP endpoints for information about the nodes in your cluster, such as which node is the write leader, and then connect directly to that node. The /node/is-read-write endpoint can be used to check if a node is the write leader, and the /node/is-read-only endpoint can be used to get information about the read-only pool.

The main reason you may want to configure the load balancer like this is if you want a layer 4 tcp proxy that looks more like a traditional Postgres connection, where TLS termination and authentication happen on the server. Connection Manager provides a layer 7 proxy which handles TLS termination and authentication at the proxy level, that is within the Connection Manager itself.

The drawback to this approach is that you will not benefit from the Connection Manager's features, especially its ability to enforce read-only connections to the read-only pool. Connections will just be directed to the non-write-leader nodes rather than the write leader.

See Monitoring the Connection Manager for more information on the available endpoints.

HAProxy example: Direct routing

Here is an example configuration where we use HAProxy to route traffic directly to the nodes based on their health status. The configuration checks the health of the nodes by querying the /node/is-read-write and /node/is-read-only endpoints, and routes traffic accordingly. As in the previous example, we are routing read-write traffic on port 5010 to the node that is the write leader and read-only traffic on port 5011 to the nodes that are read-only.

global
    maxconn 100

defaults
    log     global
    mode    tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind 127.0.0.1:7000
    stats enable
    stats uri /

listen read-write
    bind *:5010
    option httpchk GET /node/is-read-write
    http-check expect string true
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 host1:5432 maxconn 100 check port 6434
    server node2 host2:5432 maxconn 100 check port 6434
    server node3 host3:5432 maxconn 100 check port 6434

listen read-only
    balance roundrobin
    bind *:5011
    option httpchk GET /node/is-read-only
    http-check expect string true
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 host1:5432 maxconn 100 check port 6434
    server node2 host2:5432 maxconn 100 check port 6434
    server node3 host3:5432 maxconn 100 check port 6434

This configuration will check the health of the nodes by querying the /node/is-read-write and /node/is-read-only endpoints, and will only route traffic to nodes that are healthy and available for read-write or read-only operations, respectively.