PostgreSQL Streaming Replication vs MySQL GTID: Protocol Mechanics and Failover Comparison

Problem statement: Your replica has fallen behind or refused to reconnect after a failover, and you need to know whether the root cause is WAL retention exhaustion (PostgreSQL) or a GTID set gap (MySQL) — and which re-sync path applies.

Symptom Identification

The two protocols surface divergence differently. Before assuming the cause, pull the correct diagnostic for your engine.

PostgreSQL signals:

-- Check for inactive slots blocking WAL cleanup
SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;

-- Measure per-replica lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lag
FROM pg_stat_replication;

A replica that has stopped streaming shows state = 'disconnected' or disappears from pg_stat_replication entirely. In the replica’s PostgreSQL log you will see:

FATAL:  requested WAL segment 000000010000000A00000001 has already been removed

MySQL signals:

-- Full replica status (MySQL 8.0+)
SHOW REPLICA STATUS\G

Key fields: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source, Last_IO_Error, Retrieved_Gtid_Set, Executed_Gtid_Set.

A gap in GTID coverage produces:

Got fatal error 1236 from source when reading data from binary log:
'The source is purging binary logs'

Routing-layer signals (engine-agnostic): application 404 responses immediately after writes, ProxySQL health-check failures on the read hostgroup, or PgBouncer log lines showing server connection closed correlating with replay_lag spikes. When replication lag pushes replicas over your SLA threshold, routing queries based on data freshness requirements describes how to adjust thresholds without dropping traffic.

Set explicit alerting thresholds:

Protocol Architecture

The two engines replicate via fundamentally different mechanisms. Understanding them is essential to diagnosing failures correctly.

PostgreSQL WAL streaming ships physical change records from the primary’s WAL directly to standby nodes. The standby’s wal_receiver process writes segments to its local WAL and the startup process applies them. Position tracking is LSN-based (Log Sequence Number). The primary exposes slot state in pg_replication_slots; when a slot’s restart_lsn falls too far behind, WAL retention must cover the gap or the replica is forcibly disconnected.

MySQL GTID replication tags every committed transaction with a globally unique identifier (server_uuid:transaction_id). Replicas negotiate which GTID ranges they need via SOURCE_AUTO_POSITION = 1, eliminating binlog position bookkeeping. The source’s gtid_purged system variable marks which transactions are no longer available in binary logs — if a replica’s gtid_executed set has gaps that gtid_purged no longer covers, the replica cannot resume and requires a fresh clone.

Root Cause Analysis

PostgreSQL: WAL Retention Exhaustion and Inactive Slots

PostgreSQL tracks how much WAL a replica still needs through replication slots. A slot’s restart_lsn is the oldest WAL position the primary must retain. When a replica disconnects (network partition, crash, maintenance) and the slot remains active in name but the connection is gone, pg_wal grows without bound. Two distinct failure modes result:

1. Inactive slot, WAL not yet purged: The replica can reconnect and resume once the network recovers. Check active = false but restart_lsn still present — this is safe if the data directory has capacity.
2. Slot dropped or wal_keep_size overridden: The primary discards WAL the replica still needs. The replica cannot resume streaming and logs FATAL: requested WAL segment ... has already been removed.

The parameter interaction matters: wal_keep_size (PostgreSQL 13+, replaces wal_keep_segments) sets a minimum floor of WAL to retain regardless of slots, but slots take precedence if their restart_lsn demands more. Setting wal_keep_size too low while relying on slots alone is safe only if you monitor slot restart_lsn drift.

MySQL: Binary Log Purge and GTID Set Divergence

MySQL’s binlog_expire_logs_seconds (MySQL 8.0, replaces expire_logs_days) automatically purges binary logs once they age past the threshold. If a replica is offline for longer than that window — cross-region outage, maintenance window, cascading topology failure — the source purges GTID ranges the replica hasn’t applied. The replica’s gtid_executed set then has gaps that no longer exist in the source’s binary logs.

The key diagnostic is comparing the two GTID sets:

-- On the source
SELECT @@global.gtid_executed;    -- all committed transactions
SELECT @@global.gtid_purged;      -- transactions no longer in binary logs

-- On the replica
SHOW REPLICA STATUS\G             -- see Executed_Gtid_Set vs Retrieved_Gtid_Set

If gtid_purged on the source contains UUIDs and ranges that are absent from the replica’s gtid_executed, the replica is in an unrecoverable gap and must be re-cloned.

A secondary failure mode is topology divergence after unsanctioned writes to a replica (enforce_gtid_consistency = OFF, or SET sql_log_bin = 0 bypassing GTID recording). These produce GTID ranges in the replica’s gtid_executed that the source never generated — invisible to normal replication checks but detected by pt-table-checksum.

Step-by-Step Resolution

Step 1 — Validate Baseline Configuration

Before attempting recovery, confirm that both ends of the replication link have the expected parameters. A parameter mismatch at the source is often the root cause of recurring failures.

PostgreSQL baseline:

-- Confirm wal_level and active slots
SHOW wal_level;               -- must be 'replica' or 'logical'
SHOW max_wal_senders;         -- must be >= replica_count + 2
SHOW hot_standby;             -- must be 'on' for read replicas
SHOW wal_keep_size;           -- set to cover expected replica lag (e.g., '1GB')

SELECT slot_name, slot_type, active, restart_lsn
FROM pg_replication_slots;

MySQL baseline:

SHOW VARIABLES LIKE 'gtid_mode';               -- must be ON
SHOW VARIABLES LIKE 'enforce_gtid_consistency'; -- must be ON
SHOW VARIABLES LIKE 'binlog_format';            -- must be ROW
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- tune to cover expected outage window

• wal_level = replica and all replication slots show expected restart_lsn advancement
• gtid_mode = ON and enforce_gtid_consistency = ON on all nodes
• binlog_format = ROW on source; SOURCE_AUTO_POSITION = 1 in replica’s replication config

Step 2 — Isolate the Lagging Replica

Prevent the routing layer from serving stale reads while you recover the replica.

-- ProxySQL: soft-offline the lagging replica to drain existing connections
UPDATE mysql_servers
SET status = 'OFFLINE_SOFT'
WHERE hostgroup_id = 20 AND hostname = 'replica-host';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# PgBouncer: pause the database to drain gracefully
psql -p 6432 -c "PAUSE dbname;"

Detecting and handling replication lag in real-time covers lag-based health checks that automate this drain step.

Step 3 — Execute Fast Re-Sync

PostgreSQL — pg_rewind (preferred when the replica is within the primary’s WAL history):

# Stop the replica
pg_ctl stop -D /var/lib/postgresql/data

# Rewind to re-align with the primary's timeline
pg_rewind \
  --target-pgdata=/var/lib/postgresql/data \
  --source-server="host=primary-host port=5432 user=replicator dbname=postgres"

# Mark the data directory as a standby
touch /var/lib/postgresql/data/standby.signal

# Restart as a streaming standby
pg_ctl start -D /var/lib/postgresql/data

pg_rewind requires wal_log_hints = on or data checksums enabled at initdb time. If neither is present, fall back to pg_basebackup.

MySQL — Clone Plugin (MySQL 8.0.17+, preferred for full re-sync):

-- On the replica (one-time plugin install)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- Clone from the source; replica restarts automatically and resumes GTID replication
CLONE INSTANCE FROM 'repl_user'@'source-host':3306
  IDENTIFIED BY 'secure_password';

After the clone completes, the replica reads the source’s GTID state from the cloned data and resumes automatically. No manual CHANGE REPLICATION SOURCE TO is needed.

Step 4 — Validate Replication Alignment

Do not restore routing until lag has closed to zero.

-- PostgreSQL: lag in bytes and interval
SELECT client_addr, replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication
WHERE client_addr = 'replica-host';
-- Target: replay_lag < '1 second', lag_bytes < 1048576 (1 MB)

-- MySQL: confirm both threads running and lag at zero
SHOW REPLICA STATUS\G
-- Target: Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source: 0

• PostgreSQL replay_lag < 1s and lag_bytes < 1 MB
• MySQL Seconds_Behind_Source = 0 with both threads running
• Routing health checks show replica as ONLINE before traffic is restored

Step 5 — Restore Routing

-- ProxySQL: return replica to online state
UPDATE mysql_servers
SET status = 'ONLINE'
WHERE hostgroup_id = 20 AND hostname = 'replica-host';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# PgBouncer: resume the paused database
psql -p 6432 -c "RESUME dbname;"

Configuration Snippet

Annotated minimal configuration for both engines, tuned for a multi-region read replica setup:

PostgreSQL postgresql.conf (primary):

# Replication role — 'replica' is sufficient for physical standbys
wal_level = replica

# Reserve slots for 3 replicas + 2 extras for monitoring / failover tools
max_wal_senders = 5

# Retain 2 GB of WAL on disk regardless of slot state
# Increase if cross-region replicas can lag more than this during network events
wal_keep_size = 2GB

# Allow replicas to serve read queries while applying WAL
hot_standby = on

# Conflicts: kill hot-standby queries that block WAL apply for more than 5 s
max_standby_streaming_delay = 5s

MySQL source my.cnf:

[mysqld]
# Enable GTID-based replication — must match on all nodes
gtid_mode                 = ON
enforce_gtid_consistency  = ON

# Row-based binary logging is required for deterministic GTID application
binlog_format             = ROW

# Retain binary logs for 7 days (604800 s); increase for long maintenance windows
binlog_expire_logs_seconds = 604800

# Expose replica status to monitoring without SUPER privilege
performance_schema        = ON

MySQL replica my.cnf:

[mysqld]
gtid_mode                 = ON
enforce_gtid_consistency  = ON
binlog_format             = ROW

# Enable relay log recovery to survive replica crashes without data loss
relay_log_recovery        = ON

# Report replica errors to performance_schema for Prometheus scraping
performance_schema        = ON

Verification and Rollback

Confirm the fix is holding (run 5 minutes after re-enabling routing):

-- PostgreSQL
SELECT client_addr, state, replay_lag
FROM pg_stat_replication;
-- All replicas should show state='streaming' and replay_lag < 1s

-- MySQL
SELECT * FROM performance_schema.replication_applier_status_by_worker\G
-- No errors in LAST_ERROR_MESSAGE; APPLYING_TRANSACTION_LAST_RETRY_ERROR_NUMBER = 0

Rollback — if the re-sync made things worse:

For PostgreSQL, stop the replica and restore from the most recent pg_basebackup snapshot stored in your WAL archive. Set restore_command in postgresql.conf to point at the archive and create standby.signal:

restore_command    = 'cp /mnt/wal-archive/%f %p'
recovery_target_action = 'promote'

For MySQL, if the clone introduced errors, drop the replica’s data directory, restore the most recent Percona XtraBackup or mysqldump snapshot, then re-run CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1.

Edge Cases and Gotchas

1. Cascading replicas (replica of replica)

In a chain topology — primary → replica A → replica B — pg_rewind can only rewind against the immediate upstream node. If replica A was promoted to primary and replica B needs to follow the new primary, B must rewind against the new primary directly, not against A. On MySQL, cascading replicas using log_replica_updates = ON propagate GTIDs through the chain, so SOURCE_AUTO_POSITION works transparently as long as the intermediate node’s binary logs are intact.

2. Logical vs physical replication (PostgreSQL)

If the standby was created for logical replication (wal_level = logical) rather than physical streaming, pg_rewind does not apply — logical replication subscribers are not file-system standbys and must re-subscribe from a publication snapshot. Physical standbys require wal_level = replica.

3. Multi-region latency and synchronous commit

In multi-region topologies with cross-AZ replicas, setting synchronous_commit = remote_write for any replica adds that replica’s round-trip latency to every write commit. This is appropriate for RPO-zero DR but will increase write latency measurably. Scope synchronous_standby_names to only the DR standby, not the read replicas serving traffic. MySQL has no native synchronous commit; use semi-synchronous replication (rpl_semi_sync_source_wait_for_replica_count = 1) for a similar guarantee with a one-replica ACK requirement.

FAQ

Related

← Back to Designing Multi-Region Read Replica Topologies

• Step-by-Step Guide to Setting Up Cross-AZ Read Replicas — topology setup and AWS/GCP region configuration for physically distributed replicas
• When to Use Logical vs Physical Replication for Read Scaling — decision framework for choosing replication mode before deployment
• Detecting and Handling Replication Lag in Real-Time — Prometheus queries and alerting rules for replay_lag and Seconds_Behind_Source
• Configuring MySQL Read-Only Routing with Lag Thresholds — ProxySQL lag-based health check configuration that complements the MySQL GTID setup above