Failover

When the upstream database fails and a replica is promoted to primary, Readyset can be redirected to the new source without a full restart. The failover procedure depends on the database type and the replication mode in use.

MySQL

GTID replication

When Readyset is replicating with GTIDs, failover is straightforward because GTIDs are global identifiers that are consistent across all servers in a replication topology. No position adjustment is needed.

1. Stop replication:

ALTER READYSET STOP REPLICATION;

2. Point Readyset at the new primary:

ALTER READYSET CHANGE CDC TO 'mysql://<user>:<password>@new-primary:3306/db';

3. Resume replication:

ALTER READYSET START REPLICATION;

Readyset resumes from the last GTID it processed. As long as the new primary has the same GTID history, replication continues seamlessly.

Binlog file and position replication

Binlog file names and positions are local to each MySQL server. When failing over to a new primary, you must determine the correct starting position on the new server. This is the same process as failing over any MySQL replica.

1. Stop replication:

ALTER READYSET STOP REPLICATION;

2. Check the current replication position in Readyset:

SHOW READYSET STATUS;

The Minimum Replication Offset field shows the current binlog file and position (e.g., binlog.000003:154).

3. Determine the equivalent position on the new primary. This step is environment-specific and is the same procedure used when failing over any MySQL replica. Common approaches include:

   • Using SHOW BINARY LOG STATUS on the new primary if the replica was in sync
   • Consulting your replication management tool (e.g., Orchestrator, MHA)
   • Comparing GTID sets if the servers have gtid_mode=ON (consider switching to GTID replication instead)

4. Set the replication position to the new primary's binlog coordinates:

ALTER READYSET SET REPLICATION POSITION 'binlog.000001:4587';

5. Point Readyset at the new primary:

ALTER READYSET CHANGE CDC TO 'mysql://<user>:<password>@new-primary:3306/db';

6. Resume replication:

ALTER READYSET START REPLICATION;

PostgreSQL

PostgreSQL logical replication uses replication slots that are local to the primary server. When the primary fails, the slot is lost and cannot be recreated at the old position on a new server. Readyset handles this automatically by performing a full resnapshot.

1. Stop replication:

ALTER READYSET STOP REPLICATION;

2. Point Readyset at the new primary:

ALTER READYSET CHANGE CDC TO 'postgresql://<user>:<password>@new-primary:5432/db';

3. Resume replication:

ALTER READYSET START REPLICATION;

Readyset detects that the replication slot does not exist on the new primary, creates a new slot, and performs a full resnapshot of all tables. Once the snapshot completes, replication resumes from the new slot's starting position.

Monitoring failover

Use SHOW READYSET STATUS to monitor the failover process:

• Status: Shows Replication Stopped after stopping, then transitions back to Online once replication catches up.
• Minimum Replication Offset / Maximum Replication Offset: Show the current replication position. For MySQL GTID, this displays the GTID set. For binlog file and position, it displays the file name and offset.