Help with Integrating Replication Manager for Automatic Failover in MariaDB Replication on Kubernetes

[achar007]

---

Hello everyone,

I'm seeking some help to integrate Replication Manager (Repmgr) for automatic primary failover in my MariaDB replication setup.

Topology Overview:

• I have a 3-pod setup in Kubernetes configured as a StatefulSet.
• Pod-0 is the primary for Pod-1 and Pod-2, while Pod-1 is the primary for Pod-0.
• Pod-0 and Pod-1 are configured as both primary and replica to each other (multi-master setup).
• Pod-1 and Pod-2 have the read_only variable set to ON.

So, effectively, it's a multi-master and replica topology. We want automatic failover to occur when Pod-0 fails, and the system should promote the latest replica, preferably Pod-1, as the new primary.

Questions regarding Repmgr as an automatic failover solution:

1. Does Repmgr support this topology?
   According to the documentation, Repmgr supports only a 2-node multi-master topology. My case involves 3 pods with 2 multi-masters. Will Repmgr work for this scenario?

2. Does Repmgr work independently without load balancers like MaxScale/HAProxy/ProxySQL?
   If a load balancer is mandatory, what would a sample HAProxy configuration look like for this MariaDB topology?

3. How many Repmgr pods should I run?
   Should I run 3 Repmgr pods, one alongside each MariaDB pod, or is 1 Repmgr pod enough to monitor all 3 MariaDB pods?

   • If just 1 pod is required, would that be a single point of failure?
   • If 3 Repmgr pods are needed, how do they communicate with each other? Can you provide any documentation on this?
   • Also, what would a sample configuration file (config.toml) look like in this case?

4. Can Repmgr be used as a viable solution for this setup?
   I'm curious to know if Repmgr is the best tool for this scenario or if there are other recommended alternatives.

---

Topology Diagram:

graph TD;
    A[Pod-0 Master & Replica] --> B[Pod-1 Master & Replica read_only=ON];
    A --> C[Pod-2 Replica read_only=ON];
    B --> A;
    B --> C;

Loading

Any insights or suggestions would be highly appreciated. Thanks in advance!

---

[caffeinated92]

Why Pod-1 Master but read only ON?

[caffeinated92]

We only need one replication-manager to monitor them

[svaroqui]

For question 2 you will need also need additional pod for proxysql or haproxy or maxscale

[achar007]

Why Pod-1 Master but read only ON?

That's our current configuration, but I'm fine with adjusting to a setup where Repmgr works with one master and two replicas.

[achar007]

We only need one replication-manager to monitor them

Here’s a rewritten version of your question:

---

Wouldn't having only one replication-manager create a single point of failure? For example, if I have a topology with 2 MariaDB pods in Datacenter A and 1 pod in Datacenter B, and my replication-manager is running in Datacenter A:

• What happens if Datacenter A goes down for an extended period, like 2 days?
• The replication-manager would be down, and there would be no polling or monitoring of the MariaDB pod in Datacenter B.

How can I ensure high availability across datacenters and avoid this single point of failure?

[achar007]

For question 2 you will need also need additional pod for proxysql or haproxy or maxscale

I’d prefer to go with HAProxy if a load balancer is mandatory. Could you provide a sample HAProxy configuration for this MariaDB topology?

Also, just a quick question: If MaxScale can handle automatic failover on its own, why would we need Replication Manager in addition? What are the specific benefits that Replication Manager offers over MaxScale?

[svaroqui]

• repman can generate the haproxy config for you
• repman can genrate db config for you
• repman can monitor databases and serve as grafana datasource
• repman can backup
• repman can reseed backups
• repman can collect all logs of databases
• repman is fully open source on support is commercial
• repman can connect to you k8s and stop start rolling restart you node
• repman manage many routes to your db
• repman can alert via email, pushover, slack, mattermost
  Not exhaustive list

[svaroqui]

here is a sample of stuff we do generate

# Generated by Signal18 replication-manager v2.3.47 on 2024-09-18T08:41:51+02:00 

global

 daemon
 maxconn 4096

 stats socket /run/haproxy.sock mode 666 level admin
 stats socket [email protected]:1999 level admin expose-fd listeners


 log /dev/log local0 debug
 external-check
 
defaults
   log global
   mode http
   option dontlognull
   option redispatch
   option clitcpka
   option srvtcpka
   retries 3
   maxconn 500000
   timeout http-request 5s
   timeout connect 5000ms
   timeout client 50000s
   timeout server 50000s

listen stats
   bind 0.0.0.0:1988
   mode http
   stats enable
   stats uri /
   stats refresh 2s
   stats realm Haproxy\ Stats
   stats show-legends


resolvers dns
 parse-resolv-conf
 resolve_retries       3
 timeout resolve       1s
 timeout retry         1s
 hold other           30s
 hold refused         30s
 hold nx              30s
 hold timeout         30s
 hold valid           10s
 hold obsolete        30s


frontend my_write_frontend
    bind 0.0.0.0:3306
    option tcplog 
    mode tcp
    default_backend service_write

frontend my_read_frontend
    bind 0.0.0.0:3307
    option tcplog 
    mode tcp
    default_backend service_read

backend service_write
    mode tcp
    balance leastconn 
 
   
    server leader db1.mycluster.svc.myk8s:3306 init-addr last,libc,none resolvers dns  weight 100 maxconn 2000 check inter 1000

backend service_read
    mode tcp
    balance leastconn 
     
    
    server db9355774201571780572 db1.mycluster.svc.myk8s:3306 init-addr last,libc,none resolvers dns weight 100 maxconn 2000 check inter 1000
    server db14842905546860910127 db2.mycluster.svc.myk8s:3306 init-addr last,libc,none resolvers dns weight 100 maxconn 2000 check inter 1000
    server db17733702361281493374 db3.mycluster.svc.myk8s:3306 init-addr last,libc,none resolvers dns weight 100 maxconn 2000 check inter 1000

Please not that if you need k8s api it available for the docker image name replication-manager-pro that i would advice you to use

You will need to explicitly set in repman conf specify

--prov-orchestrator string                             onpremise|opensvc|kube|slapos|local (default "opensvc")

ok if onpremise this is the regular way to specify hostname in repman names, but kube api will no be usable for provision services and running jobs
when choosing kuke the it's connect but with some rules for the DNS name of the services (pods)

just decale db-servers-hosts="db1,db2,db3"
and prov-orchestrator-cluster = "cloud18" will be the kubernetes cluster name may be that's local in my souvenir
then you need to have a match between the cluster name and the kubenetes namespace
prov-net-cni = true
prov-net-cni-cluster = "cluster.local"
kube-config = "/Users/apple/.kube/config"
http-bootstrap-button = true

here is my last test of kube deployment of a cluster

[cluster1]
title = "cluster1"
db-servers-hosts = "db1,db2"
db-servers-prefered-master = "db1"
db-servers-credential = "root:mariadb"
db-servers-connect-timeout = 1

replication-credential = "root:mariadb"
prov-db-agents = "s18-control-plane-vrplg,s18-md-0-4kzvt"
prov-db-service-type = "docker"
prov-db-docker-img = "mariadb:10.11"
prov-db-tags = "semisync,innodb,noquerycache,threadpool,logslow,smallredolog"


[Default]
prov-orchestrator = "kube"
prov-net-cni  = true
prov-net-cni-cluster = "cluster.local"
kube-config = "/Users/apple/.kube/config"
http-bootstrap-button   = true

[svaroqui]

Giving you this for an inspiration of integration as we know our kube deployment lack behind opensvc that we use heavely for docker , oci auto cluster deployment a lot , we don't use secrets for the init containers for example and no api call via ssl

[svaroqui]

1 Does Repmgr support this topology?
According to the documentation, Repmgr supports only a 2-node multi-master topology. My case involves 3 pods with 2 multi-masters. Will Repmgr work for this scenario?

Can you explain more are they 2 master-master different clusters , we support multi clusters in same repman and can combine it with multi source. We have production deployments using 2 master slave clusters linked on the masters each cluster being on different domain id and we could combine it in both direction we don't really like multi master that writes on all nodes as it lost consistency and bring data divergence DC1(maste-slave) <-> DC2 (maste-slave) <-> DC3 (maste-slave) take care of having row based ,heartbeat on each dc and idempotent a more nice consistent scenario is DC1(maste-slave) DB1 -> DC2 (maste-slave) DB1 & DB2