With VMware Data Services Manager, you can configure High Availability (HA) clusters of MySQL databases or PostgreSQL databases. As a Provider Administrator, Organization Administrator, or Organization User, you can create Replica databases of a Primary Database to support redundancy. One or more Replica databases and a Primary database can form an HA cluster of MySQL databases. However, to form an HA cluster of PostgreSQL databases, you need one or more Replica databases and a Primary database along with a Monitor database.
If the Primary database in an HA cluster stops its services, there is automatic promotion of one of the configured Replica databases as the Primary database. To support the changing resource requirements of a business, you can promote a Replica database to a Primary database manually.
WAL and Binary Logs and High Availability Clusters
Write-Ahead Log (WAL) files or binary log files log all the updates of the Primary database of a HA cluster.
If the Replica database of an HA cluster goes down for few hours or few days, the WAL files or binary log files are used by the Replica database to sync up with Primary database after the Replica database is online. However, an exponential increase in the size of the WAL files or binary log files can lead to connectivity issues for the Primary database or to stopped read-write activities on the database. Therefore, VMware Data Services Manager prevents the binary log files of MySQL databases to grow beyond 10% of the actual disk size and prevents WAL files of PostgreSQL databases to grow beyond 15% of the actual disk size.
For PostgreSQL databases versions 12 and later, VMware Data Services Manager limits the total size of WAL files to a maximum of 15% of the actual data disk size. If this limit is exceeded and automated backups are enabled, the oldest WAL files are deleted from the disk. If automated backups are enabled, this deletion happens only after the WAL files have been sent to S3 storage. If a Replica database has been offline for some time and needs the deleted WAL files for replication, replication continues by retrieving WAL files from the S3 storage.
For a Standalone PostgreSQL database, WAL files are deleted from the data disk after it is archived. For a PostgreSQL database cluster, if a Replica database is lagging or offline, the WAL files are retained in the data disk of the Primary database. Therefore, to avoid exhaustion of data disk space, WAL files are removed if they occupy more than 15% of the actual disk size. However, for database clusters, if the actual data disk size is smaller than 80GB, the total size of WAL files may exceed 15% of the actual disk size.
For MySQL databases, VMware Data Services Manager limits the total size of binary log files to a maximum of 10% of the actual data disk size. If this limit is exceeded and automated backups are enabled, the oldest binary log files are deleted from the disk only after the binary logs have been uploaded to S3 storage. If a Replica database has been offline for some time and needs the deleted binary log files for replication, the Replica database performs a full data synchronisation with the Primary database by cloning the data of the Primary database.
Limitations for Configuring High Availability Clusters
The following limitations are applicable when configuring an HA cluster of databases:
- You cannot configure an HA cluster on Microsoft SQL Server databases.
- You can configure a maximum of five nodes in an HA cluster that can include a Primary database, Replica databases, and a Monitor database.
- If you don't configure the maintenance policy of an HA cluster considering the number of nodes, automatic updates for all the nodes is not simultaneous during the maintenance window. You can configure approximately 15 minutes to 20 minutes of maintenance time per node. For example, if an HA cluster has five nodes, you can configure the Duration of the maintenance policy of the Primary database as 100 minutes or more. For more information, see Updating Database Maintenance Policy.
- You cannot create an HA cluster from the databases upgraded from releases earlier than 1.3.0. If you want to create a cluster from the upgraded databases, you must clone or restore the database, and then create an HA cluster using the cloned or restored database as the Primary database of the cluster.
- You can manually promote a Replica database to a Primary database only if its status is Online.
- For promotion of a MySQL replica database, status of all the databases in the cluster must be Online. So, if any database in the cluster is powered off, you must power it on (if possible) or remove it from the cluster.
- If the PostgreSQL Monitor database is down, the HA cluster is not functional (auto promotion does not work), but operations like backup, cloning, and restore can be performed on the databases of the HA cluster.
- In a High Availability cluster of PostgreSQL databases, you cannot configure the max_connections of a Replica database lower than that of the Primary database.
- When you configure the first Replica database of a MySQL HA cluster, you must specify the Cluster IP as a static and valid IP address that is in the subnet of the Primary databases's application network. You cannot provide the Cluster IP for the subsequent Replica databases that you create for that cluster.
- All nodes of an HA cluster must have the same Engine and OS version for smooth functioning of cluster. In case updates are available, update all the nodes before performing any operations on the cluster or any node.
- Scale VM memory or CPU resources on all the Replica databases before you perform the operation on the Primary database of the cluster. If you do not follow this sequence, there may be multiple failovers and that can lead to more downtime.
- Change database options on all the Replica databases before you change the options on the Primary database of the cluster. If tyou do not follow this sequence, there may be multiple failovers and that can lead to more downtime.
- In case of remote read replicas, ensure the remote agents have the same version of template for the smooth functioning of the HA cluster.
Creating High Availability Clusters
You can create HA clusters of MySQL databases or PostgreSQL databases in a sync mode to support immediate sync between the Primary database and Replica databases in an HA cluster. In an HA cluster of PostgreSQL databases, a PostgreSQL Monitor database is created automatically when you create the first Replica database of the cluster.
Perform the following steps to create an HA cluster of MySQL databases or PostgreSQL databases:
Click a database from the list of databases in the Databases view of the Databases pane.
The Details tab of the database appears.
Click the Cluster Settings tab and then click + CREATE in the Replication section of this tab.
In the Create Read Replica form, specify the following properties, and then click CREATE.
Property Description Replica VM Name Enter the Replica VM name for the Replica database. Namespace Select the Namespace for the Replica database. Cluster IP Enter a static valid IP address that is in the subnet of the Primary databases's application network. You cannot provide the Cluster IP for the subsequent Replica databases you create for that cluster. This property is not available for PostgreSQL replica database VMs. Candidate Priority Specify a value between 0 to 100. Higher the Candidate Priority, greater the chance of the Replica database to be auto promoted as the Primary database.
A Read Replica database is created for a MySQL HA cluster and a Monitor database is created along with the first Read Replica database for a PostgreSQL HA cluster.
Creating a High Availability Cluster During a Database Creation
You have the option to create an HA cluster of databases when you create a database. The database that you create is added to the cluster as the Primary database and you can add upto four Replica databases during the creation of the Primary database. If you have added less than four replicas during cluster creation, you can add more Replica databases to the cluster after the cluster formation.
You need not add a Monitor node to a cluster. It is automatically added when you create an HA cluster of a PostgreSQL Primary database. The Monitor node is listed Cluster Settings tab of any database that belongs to the HA cluster.
When you create an HA cluster of databases during the creation of a database, first the Primary database is created and then the Replica databases are created.
HA Status of High Availability Clusters
Create at least two replicas in an HA cluster for efficient functioning of automatic promotion in the cluster. Based on the number of Replica databases created in an HA cluster, the status of the cluster varies as follows:
- The status is Unconfigured for HA clusters that have no Replica database.
- The status is Incomplete for HA clusters that have at least one Replica database with Replication Status as Active.
- The status is Complete for HA clusters that have more than one Replica database with Replication Status as Active.
Editing Candidate Priority of a Cluster Node
Candidate priority is one of the parameters that decides which node in an HA cluster will be automatically promoted as the Primary node of the cluster during a failover. After creating an HA cluster of databases, you may need to alter the Candidate Priority of the database nodes in a cluster to increase or decrease the chance of a database node for automatic promotion. You can edit the Candidate Priority of Primary nodes and Secondary nodes of MySQL and PostgreSQL HA clusters.
For example, if one of the Replica nodes in an HA cluster is showing a lag in syncing up with the Primary node, you may want to set the candidate priority of that Replica node to zero or a lower value than the other Replica nodes in the cluster.
Perform the following steps to edit the Candidate Priority of a cluster node:
Click a database that is part of an HA cluster in the Databases view of the Databases pane.
The Details tab of the database appears.
Click the Cluster Settings tab.
In the Replication section of the Cluster Settings tab, click the three vertical dots in Actions column of the Replica database, and then select Edit Replica Configuration from the pop-up menu.
The Edit Replica Configuration dialog box appears.
In the Edit Replica Configuration dialog box, enter the Candidate Priority, and then click UPDATE.
Manual Promotion in High Availability Clusters
You can use VMware Data Services Manager to manually promote a Replica database that is in Online status to a Primary database.
Perform the following steps to manually promote a Replica database to a Primary database:
Click the Replica database in the Databases view of the Databases pane.
The Details tab of the Replica database appears.
Click the Cluster Settings tab.
In the Replication section of the Cluster Settings tab, click the three vertical dots in Actions column of the Replica database, and then select Promote Replica from the pop-up menu.
In the Promote Read Replica dialog box, click CONFIRM.
The Replica database is promoted as the Primary database, and the existing Primary database becomes a Replica database.
Deleting High availability Clusters
VMware Data Services Manager enables you to delete a HA cluster of databases at once. When you delete a MySQL database cluster, the replica databases are deleted first followed by the Primary database. When you delete a PostgreSQL database cluster, the replica databases are deleted first, followed by the Primary database, and finally the Monitor database is deleted.
Perform the following steps to delete a HA cluster of databases:
Click a Primary database of the HA clucter from the list of databases in the Databases view of the Databases pane.
The Details tab of the database appears.
Click the Cluster Settings tab.
In the Cluster Information section of the Cluster Settings tab, click ACTIONS, and then select Delete Cluster.
The Delete Cluster dialog box appears.
Note: The Delete Cluster option is not available for Replica databases of all HA clusters and Monitor databases of a PostgreSQL HA cluster.Click CONFIRM in the Delete Cluster dialog box.
In the Databases view, the Status of all the nodes of the cluster changes to Deleting.
Limitations for Deleting High availability Cluster
The following limitations are applicable when deleting an HA cluster of databases:
If a node in an HA cluster is part of a degraded environment, then the deletion of the node is stopped and the status of that node changes to PowerOff.
If the deletion of any node in an HA cluster stops, the process of deleting the remaining nodes of the cluster stops too. The status of the remaining nodes of the cluster changes to what it was before the deletion process started.
Until a node in an HA cluster is deleted, you cannot add another node in that HA cluster with the same name.
Deleting Databases in High availability Clusters
VMware Data Services Manager enables you to delete databases in an HA cluster. For a MySQL HA cluster, you must delete the Replica databases before you delete the Primary database of the cluster. For a PostgreSQL HA cluster, you must delete the Replica databases, followed by the Primary database, and then the Monitor database of the cluster.
Perform the following steps to delete a database in an HA cluster:
Click the Replica database or Primary database in the Databases view of the Databases pane.
The Details tab of the Replica database or Primary database appears.
Click the Cluster Settings tab.
In the Replication section of the Cluster Settings tab, click the three vertical dots in Actions column of the Replica database, and then select Delete from the pop-up menu.
In the Delete Instance dialog box, click CONFIRM.
