Replication Failover

• Create a table on the primary and insert a row.
• Read from the replica and observe that the row may appear after a short delay (replication lag).
• Simulate a primary failure and promote the replica to primary.
• Confirm that destructive operations performed on the original primary (for example, deletes) are applied on the replica.

​

High-level overview

1. Configure the primary to accept replication connections and create a replication user (engine-specific).
2. Take a base backup of the primary and restore it on the replica host.
3. Configure the replica to connect to the primary (host/port and replication credentials).
4. Start replication on the replica and confirm it is streaming or receiving changes.
5. Run test writes on the primary and observe how (and when) they appear on the replica.
6. Simulate a primary failure, promote the replica, and validate read/write behavior on the promoted node.

​

1) Prepare the primary and replica (high level)

• Configure listener settings and networking so the primary accepts replication connections.
• Create a dedicated replication user with minimal privileges.
• Produce a consistent base backup from the primary and restore it to the replica.
• Configure replica connection settings (primary host/port and replication credentials).
• Start replication on the replica and verify it is receiving changes (look for streaming/replication state using engine-specific diagnostics).

• PostgreSQL: pg_stat_replication, pg_wal_lsn_diff(), pg_basebackup (engine-specific).
• MySQL: SHOW SLAVE STATUS\G / SHOW REPLICA STATUS\G, mysqldump or binary-log based restore.
• MongoDB: rs.status() and rs.initiate() for replica sets.

​

Replication modes and tradeoffs

​

2) Create a test table and insert a row on the primary

-- primary
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('alice');

​

3) Read from the replica and observe replication lag

-- replica (read-only replica)
SELECT id, name FROM users;

• If replication is near real-time (synchronous or low-latency streaming), the row should appear almost immediately.
• With asynchronous replication, you may see a short delay before the row is present.

​

4) Perform a delete on the primary (replication of destructive operations)

-- primary
DELETE FROM users WHERE name = 'alice';

-- replica
SELECT id, name FROM users;

​

5) Simulate a failure and promote the replica

1. Simulate primary failure by stopping the service or container:
   • Systemd-managed: sudo systemctl stop postgresql (PostgreSQL) or sudo systemctl stop mysql (MySQL).
   • Containers: stop or remove the primary container.
   • Managed services: use your cloud provider’s tools to simulate downtime.
2. Promote the replica to primary using engine-specific commands:
   • PostgreSQL (streaming): pg_ctl promote or use orchestrators like repmgr or Patroni.
   • MySQL Group Replication / InnoDB Cluster: use cluster tooling to elect or promote a new primary.
   • MongoDB: use replica set election or rs.stepDown() to trigger leadership change.
3. After promotion, enable writes on the promoted node and validate both writes and reads:

-- promoted replica (now primary)
INSERT INTO users (name) VALUES ('bob');

SELECT id, name FROM users;

​

6) Expected behaviors and best practices

• Replication propagates data-change operations (inserts, updates, deletes) from primary to replica; replicas generally remain read-only until promoted.
• Asynchronous replication can show lag; the replica may be behind the primary.
• When a replica is promoted after primary failure, it becomes writable and accepts new changes.
• Destructive operations performed on the primary are reproduced on the replica; replicas do not selectively ignore deletes or other changes.
• For production systems, automate monitoring, use proper backup strategies, test failover procedures regularly, and prefer synchronous or semi-synchronous configurations if application-level durability is required.

​

Quick checklist before performing failover tests

• Confirm replication is healthy and caught up (use engine-specific status checks).
• Ensure backups and snapshots are available.
• Validate your monitoring and alerting.
• Test promotion on staging first, and document the promotion and rollback steps.

​

Links and references

• PostgreSQL replication and failover: https://www.postgresql.org/docs/current/high-availability.html
• MySQL replication: https://dev.mysql.com/doc/refman/en/replication.html
• MongoDB replica sets: https://www.mongodb.com/docs/manual/replication/
• General database high-availability concepts: https://en.wikipedia.org/wiki/High_availability