Near-Zero-Downtime PostgreSQL and MySQL Migration via Replication

The problem

A large production database has to move to another server or region, for example to meet data residency requirements or to leave a provider with expensive egress. The usual first idea is a dump and restore. On a database of several hundred gigabytes this means hours of copying while the application keeps writing. The restored copy is already stale when it finishes, and the dump adds read load and I/O pressure to the live primary during the copy.

A second failure pattern shows up with replication-based moves. The new server is set up as a replica but never catches up. Replication lag stays at minutes, so there is never a safe moment to switch. A third pattern is a cutover that loses writes, or leaves two writable copies of the same database.

Why it happens

A dump is a snapshot, and every write after the snapshot is missing from the copy. The only way to close that gap without long downtime is to ship the changes continuously until the target is nearly identical, then freeze writes briefly and switch.

Replicas fall behind for a few predictable reasons:

• Slow destination storage. Replaying changes means random writes. If the target volume cannot sustain the primary's write rate, lag grows without bound.
• Single-stream WAN transfer. The change stream is one TCP connection. Long round-trip times, small socket buffers or loss-sensitive congestion control cap its throughput.
• Removed history. If the primary discards WAL or binary logs before the replica reads them, the replica has to be rebuilt from scratch. PostgreSQL replication slots prevent this, at the price of retained WAL on the primary.
• Unsafe cutover order. If writes continue on the old primary after promotion, the two servers diverge.

Some widely copied recipes also contain errors. They set gtid_mode = ON in one step on a running MySQL server, which is rejected. They run sysctl -p on a drop-in file, which does not load it. They tune congestion control on the receiving side only. They describe password_encryption as protecting WAN traffic. None of these steps does what the recipe claims, and the corrections are in the sections below.

Source of truth

The procedure relies on the vendors' documentation:

• PostgreSQL 17: pg_basebackup confirms that -R (--write-recovery-conf) creates standby.signal and appends connection settings to postgresql.auto.conf. It also states that the tool works with servers of the same or an older major version, and that WAL streaming mode (-X stream) needs server 9.3 or later. A physical replica therefore cannot be used to change major versions.
• PostgreSQL 17: Log-Shipping Standby Servers confirms that a standby leaves standby mode and becomes writable when pg_ctl promote is run or pg_promote() is called.
• MySQL 8.4: Enabling GTID Transactions Online confirms the required order on a running server. Set enforce_gtid_consistency to WARN, then ON. Then step gtid_mode through OFF_PERMISSIVE and ON_PERMISSIVE to ON. A direct jump to ON is not the documented path.
• MySQL 8.4: Setting Up Replication Using GTIDs confirms that each server needs gtid_mode=ON and enforce_gtid_consistency enabled for GTID-based replication.

Ways to fix it

Why this fix

Streaming replication followed by a controlled promotion is chosen because it shortens the outage to the cutover step itself. It also keeps the old primary intact until the new one is verified. The work is split into four parts:

1. Validate the target's storage before anything else.
2. Build the replica with a replication slot, over TLS.
3. Wait for lag to reach zero.
4. Freeze writes, confirm the replica has every change, promote, and repoint the application.

The method is called zero-downtime only loosely. Writes are blocked for the duration of the final catch-up and switch, which is usually short. If even that is unacceptable, an application-level approach is needed. A logical replica is the choice when the PostgreSQL major version must change at the same time.

Fixing it step by step

The procedure below is given as commands and configuration to apply. Output is not reproduced, because it depends on the environment. Paths follow Debian conventions. On RHEL-family systems the data directory is typically under /var/lib/pgsql/17/data. Host names and the address 192.0.2.15 are placeholders.

1. Check destination storage

Run a random-write test on the volume that will hold the data directory, using a block size close to PostgreSQL's 8 kB page. Do this before building anything. Remove the test file afterwards.

fio --name=randwrite --filename=/var/lib/postgresql/fio.test --size=4G \
    --rw=randwrite --bs=8k --iodepth=32 --numjobs=4 --ioengine=libaio \
    --direct=1 --runtime=60 --time_based --group_reporting
rm /var/lib/postgresql/fio.test

Compare the sustained write IOPS with the primary's peak write rate. If the target is not clearly faster, choose different storage.

2. PostgreSQL: prepare the source

Create a dedicated role that has the REPLICATION attribute:

sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';"

Since PostgreSQL 10, wal_level defaults to replica and max_wal_senders to 10, so those lines are only needed if they were changed. Setting password_encryption = scram-sha-256 only controls how newly set passwords are hashed, so set the password again after enabling it. It does not encrypt the replication stream. TLS does that, and requires ssl = on on the source. Cap the WAL a stalled replica can pin on the source:

# postgresql.conf on the source
ssl = on
password_encryption = scram-sha-256
max_replication_slots = 10
max_slot_wal_keep_size = 50GB

Allow the replica's address only, and require TLS:

# pg_hba.conf on the source
hostssl  replication  replicator  192.0.2.15/32  scram-sha-256

sudo systemctl reload postgresql

Settings that require a restart, such as ssl or max_replication_slots, need systemctl restart postgresql instead. Plan that restart for a quiet moment.

3. PostgreSQL: build the replica

On the target, install the same major version, stop the service and empty the data directory. pg_basebackup requires an empty target. Put the password in ~postgres/.pgpass so that it can be read without a prompt, because -R records the connection settings in postgresql.auto.conf:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/17/main/*
sudo -u postgres sh -c 'echo "source-db.example.com:5432:replication:replicator:change-me" > ~/.pgpass && chmod 600 ~/.pgpass'
sudo -u postgres env PGSSLMODE=require pg_basebackup \
    -h source-db.example.com -U replicator \
    -D /var/lib/postgresql/17/main/ \
    -X stream -C -S migration_slot -R -P -v
sudo systemctl start postgresql

-C -S migration_slot creates the slot on the source, so WAL is kept until the replica has consumed it. -R writes standby.signal and the connection settings. Check what was written:

sudo grep -E 'primary_conninfo|primary_slot_name' /var/lib/postgresql/17/main/postgresql.auto.conf

4. Tune the WAN path

Congestion control applies to the sending side, so for the WAL stream the setting belongs on the source (the sender). It matters less on the replica. Buffers must cover the bandwidth-delay product: for example, 1 Gbit/s at 25 ms is roughly 3 MB. BBR needs kernel 4.9 or later and the tcp_bbr module.

# /etc/sysctl.d/99-db-migration.conf
net.core.default_qdisc = fq
net.ipv4.tcp_congestion_control = bbr
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

sudo modprobe tcp_bbr
sudo sysctl --system
sysctl net.ipv4.tcp_congestion_control

Plain sysctl -p reads only /etc/sysctl.conf. Use sysctl --system, or pass the file name explicitly.

5. Watch lag, then cut over

On the source, the replica should be streaming with a replay lag near zero:

sudo -u postgres psql -c "SELECT application_name, state, sent_lsn, replay_lsn, replay_lag FROM pg_stat_replication;"

For the cutover, stop the application first. Setting default_transaction_read_only is not a reliable guard, because sessions can override it. Then confirm that the replica has replayed the source's last position. Compare these two values until they are equal:

sudo -u postgres psql -c "SELECT pg_current_wal_lsn();"          # on the source
sudo -u postgres psql -c "SELECT pg_last_wal_replay_lsn();"       # on the replica

Stop the old primary. A clean shutdown lets the WAL senders flush all remaining WAL to the replica. Then promote the replica and keep the old primary from restarting as a writable server:

sudo systemctl stop postgresql          # on the source
sudo systemctl disable postgresql       # on the source
sudo -u postgres psql -c "SELECT pg_promote();"   # on the replica
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

The last query should return f. Point the application's connection string or DNS record at the new host and start the application. pg_ctl promote -D /var/lib/postgresql/17/main is an equivalent way to promote.

6. MySQL 8.4 variant

On the source, enable GTIDs in the documented order, if they are not on yet. Before moving to ON, check the error log for GTID-unsafe statements. Also wait until Ongoing_anonymous_transaction_count is zero:

SET GLOBAL enforce_gtid_consistency = WARN;
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
SET GLOBAL gtid_mode = ON;

Also write the same settings into my.cnf so that they survive a restart. Create a replication account that requires TLS:

CREATE USER 'replicator'@'192.0.2.15' IDENTIFIED BY 'change-me' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.0.2.15';

The replica needs an initial copy before replication can start. A recipe that goes straight to CHANGE REPLICATION SOURCE TO leaves the replica empty. For a large InnoDB-only dataset, the clone plugin (8.0.17 and later) copies a running server. Both sides need the same MySQL version, and the clone user needs the BACKUP_ADMIN privilege on the donor. A physical backup tool or mysqldump --single-transaction are alternatives, and the dump embeds the GTID set. Once the copy is in place, run this on the replica:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='source-db.example.com',
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='change-me',
    SOURCE_AUTO_POSITION=1,
    SOURCE_SSL=1;
START REPLICA;
SHOW REPLICA STATUS\G

For the cutover, stop the application and make the source read-only. Read its executed GTID set, and wait for the replica to apply it. WAIT_FOR_EXECUTED_GTID_SET returns 0 when the set has been applied:

-- on the source
SET GLOBAL super_read_only = ON;
SELECT @@GLOBAL.gtid_executed;

-- on the replica
SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid_set_from_source>', 60);
STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

Then repoint the application at the new host.

Limits and alternatives

• Write freeze remains. The method has a short write freeze, not literally zero downtime. Test the full procedure on a copy, and time the freeze, before the real move.
• Same major version only. A physical PostgreSQL replica cannot change major version or CPU architecture. Use logical replication or dump and restore for those cases, and handle sequences and schema changes explicitly.
• Slots can fill the disk. A replication slot that nobody reads retains WAL indefinitely unless max_slot_wal_keep_size is set. If the migration is abandoned, drop the slot with pg_drop_replication_slot('migration_slot').
• No automatic failback. After promotion, the old primary is no longer in sync. If rollback might be needed, set up reverse replication before accepting writes on the new server.
• MariaDB differs. MariaDB has its own GTID implementation and its own replication syntax, so the MySQL commands above do not apply to it.
• Latency after the move. Moving the database but not the application servers changes every query's round-trip time. Sequential queries add up, so move the application tier together with, or before, the database.