Comprehensive Guide: Zero-Downtime Migration of PostgreSQL and MySQL

Imagine this: your PostgreSQL 13 database is running under 10,000 RPS, and you need to upgrade to PostgreSQL 15 without stopping the application. A typical dump and restore means hours of downtime—clients are lost, revenue drains. Each hour of downtime can cost up to $250,000. We solve this with zero-downtime migration using logical replication, batched data transfer, and automatic failover. With over 50 successful projects, we guarantee 99.9% data integrity and provide a custom plan for your infrastructure.

Over the past three years, we have handled migrations for projects with loads from 1,000 to 50,000 RPS. This article covers all aspects of database upgrade—both pg_upgrade and logical replication—and shows how to update your database painlessly while maintaining availability. Contact us for a free consultation, and we will prepare an individual migration plan within one day. Pricing starts at $5,000 for a basic PostgreSQL upgrade.

Principles of zero-downtime migrations

Any schema change follows backward-compatible stages:

1. Add new (column, table) — the application ignores the new.
2. Deploy code that writes to both places.
3. Migrate existing data in batches.
4. Deploy code that reads only from the new.
5. Remove the old.

No DROP COLUMN or RENAME COLUMN in production in a single step.

How to perform a zero-downtime PostgreSQL migration?

Method 1: pg_upgrade with a replica

pg_upgrade is about 10x faster than logical replication in terms of execution time, but requires a short downtime window for preparation and does not allow rollback. "pg_upgrade uses hard links to avoid copying data, making it extremely fast."

# 1. Set up the new PostgreSQL version side by side apt install postgresql-15 # 2. Stop writes (short downtime for preparation) pg_ctl -D /var/lib/postgresql/14/main stop # 3. pg_upgrade with --link (no file copying) /usr/lib/postgresql/15/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/14/main \ --new-datadir=/var/lib/postgresql/15/main \ --old-bindir=/usr/lib/postgresql/14/bin \ --new-bindir=/usr/lib/postgresql/15/bin \ --link \ --check # 4. Execute upgrade /usr/lib/postgresql/15/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/14/main \ --new-datadir=/var/lib/postgresql/15/main \ --old-bindir=/usr/lib/postgresql/14/bin \ --new-bindir=/usr/lib/postgresql/15/bin \ --link 

The --link mode uses hard links instead of copying—for a 100 GB database, it takes seconds instead of hours. Drawback: you cannot start the old version afterward.

Method 2: Logical Replication (true zero-downtime)

-- On the old server (PG 13) CREATE PUBLICATION migration_pub FOR ALL TABLES; -- On the new server (PG 15) — create the same schema pg_dump -s -U postgres myapp | psql -U postgres -h new-server myapp -- Subscribe to replication CREATE SUBSCRIPTION migration_sub CONNECTION 'host=old-server dbname=myapp user=replication password=pass' PUBLICATION migration_pub; -- Monitor initial sync progress SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription; 

After synchronization:

-- Check lag (should be near zero) SELECT now() - last_msg_receipt_time AS subscription_lag FROM pg_stat_subscription; -- Cutover: stop writes to old DB, wait for lag = 0 -- Update application connection string -- Drop subscription DROP SUBSCRIPTION migration_sub; 

Comparison of migration methods

Why schema migrations require multiple steps?

Adding a NOT NULL column

You cannot do it in one step — ALTER TABLE will lock the table for the entire DEFAULT computation. Correct approach:

-- Step 1: add column with DEFAULT (PostgreSQL 11+ — instant) ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL; -- Step 2: fill data in batches DO $$ DECLARE batch_size INT := 1000; offset_val INT := 0; BEGIN LOOP UPDATE users SET phone = '' WHERE id IN ( SELECT id FROM users WHERE phone IS NULL ORDER BY id LIMIT batch_size ); EXIT WHEN NOT FOUND; PERFORM pg_sleep(0.01); END LOOP; END $$; -- Step 3: add NOT NULL constraint (fast if no NULLs) ALTER TABLE users ALTER COLUMN phone SET NOT NULL; 

Renaming a column

-- Step 1: add new column ALTER TABLE orders ADD COLUMN customer_id BIGINT; -- Step 2: fill data (+ trigger for new writes) CREATE OR REPLACE FUNCTION sync_customer_id() RETURNS TRIGGER AS $$ BEGIN NEW.customer_id := NEW.user_id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER sync_customer_id_trigger BEFORE INSERT OR UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION sync_customer_id(); -- Batch fill existing records UPDATE orders SET customer_id = user_id WHERE customer_id IS NULL; -- Step 3: deploy code reading customer_id -- Step 4: remove old column and trigger ALTER TABLE orders DROP COLUMN user_id; DROP TRIGGER sync_customer_id_trigger ON orders; 

Tools for online migrations

Example of using gh-ost:

gh-ost \ --host=db-master \ --database=myapp \ --table=users \ --alter="ADD INDEX idx_email (email)" \ --execute 

Testing the migration plan

# Restore production dump to staging pg_restore -U postgres -d myapp_staging production.dump # Verify migration plan psql -U postgres myapp_staging < migration_plan.sql # Measure execution time \timing on \i migration_plan.sql 

Monitoring and rollback readiness

After initiating logical replication, it is critical to monitor replication lag and the health of both servers. We configure Prometheus + Grafana with a dashboard for pg_stat_replication: lag exceeding 100 MB signals to slow down batch migration or increase resources. Simultaneously, we keep a rollback plan ready: in case of any anomaly, we can return traffic to the old server within 30 seconds. Typical metrics for monitoring: received_lsn vs sent_lsn (lag in bytes), write_lag, flush_lag, and replay_lag from pg_stat_replication. For MySQL — Seconds_Behind_Source from SHOW SLAVE STATUS. Zero lag before cutover is achieved by stopping writes on the source for 2–5 seconds—this is the only moment of 'risk'. For large databases (>=500 GB), pre-synchronization via rsync or pg_basebackup speeds up initial subscription preparation by 3-5 times compared to pure replication. We document every step in a runbook and train the client's team to use it.

What's included in the work

• Audit of current database schema and DBMS versions.
• Development of a backward-compatible migration plan.
• Setup of logical replication or pg_upgrade.
• Batch data transfer with integrity verification.
• Lag monitoring and automatic failover.
• Documentation and team training.
• Rollback plan guarantee for 30 days.

Timelines and cost

Zero-downtime PostgreSQL upgrade with logical replication: 2–3 days. Complex schema migration with multiple steps: 1–2 weeks (including staging tests). Cost: starting from $5,000 for a basic PostgreSQL upgrade. Contact us for a free evaluation of your project.