Setting Up Database Replication (Master-Slave/Multi-Master): Ensuring High Data Availability

As applications grow, the database becomes a critical point of failure. A downtime or data loss incident can cause serious damage. Database Replication allows real-time data copying between servers, ensuring High Availability (HA) and fast recovery. This article provides a detailed guide on implementing MySQL Group Replication and PostgreSQL Streaming Replication on VPS, from traditional Master-Slave to modern Multi-Master setups in 2026.

1. Why Do You Need Database Replication?

Replication is not just backup — it brings many important benefits:

• High Availability: When the Master goes down, a Slave can take over immediately.
• Load Balancing: Offload Read queries to Slave servers.
• Disaster Recovery: Data is continuously replicated.
• Zero Data Loss: With synchronous replication configuration.

// Interface simulating Database Replication Status
interface ReplicationStatus {
  serverRole: "Master" | "Slave" | "Multi-Master";
  lagSeconds: number;
  isHealthy: boolean;
  lastSync: Date;
  replicationMode: "Async" | "Semi-Sync" | "Group";
}

function checkReplicationHealth(status: ReplicationStatus): boolean {
  if (status.lagSeconds > 5) {
    console.warn(`[REPLICATION ALERT] High lag: ${status.lagSeconds} seconds on ${status.serverRole}`);
    return false;
  }
  console.log(`[REPLICATION] ${status.serverRole} is healthy - Lag: ${status.lagSeconds}s`);
  return true;
}
 

2. MySQL vs PostgreSQL Replication Comparison

Criteria	MySQL Group Replication	PostgreSQL Streaming Replication
Architecture	Multi-Master (True Active-Active)	Master-Slave (Cascading possible)
Consistency	Multi-Master with Conflict Detection	Strong Consistency
Write Performance	Good for write scaling	Excellent for Read Replicas
Complexity	Medium - High	Low - Medium

3. VPS Requirements for Database Replication

It is recommended to use at least 3 VPS (1 Master + 2 Slaves) to achieve quorum and High Availability.

4. Setting Up MySQL Group Replication (Multi-Master)

// my.cnf configuration for Group Replication
const mysqlGroupConfig = `
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_checksum = NONE
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = ON
group_replication_local_address = "192.168.1.10:33061"
group_replication_group_seeds = "192.168.1.10:33061,192.168.1.11:33061,192.168.1.12:33061"
group_replication_bootstrap_group = OFF
`;

console.log("[MySQL] Group Replication configuration applied");
 

5. Setting Up PostgreSQL Streaming Replication

// postgresql.conf on Master
const pgMasterConfig = `
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on
`;

// recovery.conf / postgresql.conf on Slave
const pgSlaveConfig = `
primary_conninfo = 'host=master-ip port=5432 user=repl_user password=strongpass'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
`;

console.log("[PostgreSQL] Streaming Replication has been configured Master → Slave");
 

6. Monitoring and Failover

• Use Orchestrator or Patroni for PostgreSQL automatic failover.
• Use ProxySQL / HAProxy for MySQL traffic routing.
• Monitor replication lag with Prometheus + Grafana.

// Monitoring Replication Lag
function monitorReplicationLag() {
  const lag = 2.3; // seconds
  if (lag > 10) {
    console.error(`[FAILOVER ALERT] Replication lag is too high: ${lag}s - Triggering alert`);
  }
}

monitorReplicationLag();
 

7. Best Practices for Replication Deployment

• Use dedicated VPS for databases.
• Enable Semi-Synchronous or Synchronous Replication for critical data.
• Perform regular backups + Point-in-Time Recovery.
• Test failover regularly (chaos engineering).
• Monitor disk I/O and network bandwidth.
• Avoid Multi-Master unless truly necessary.

8. Conclusion: Database Replication Deployment Checklist

Before going into production, verify the following:

1. Have you chosen the right model (Master-Slave or Multi-Master)?
2. Is replication stable with low lag?
3. Do you have an automatic or fast manual failover mechanism?
4. Have you set up replication lag monitoring?
5. Are backups and Point-in-Time Recovery ready?
6. Have you tested server failure scenarios?

Implementing Database Replication is a crucial step to achieve High Availability for your system. Whether using MySQL or PostgreSQL, replication gives you peace of mind to develop applications without worrying about data loss or prolonged downtime.

Hope this detailed guide helps you successfully set up a robust and stable Database Replication system on your VPS!