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:
- Add new (column, table) — the application ignores the new.
- Deploy code that writes to both places.
- Migrate existing data in batches.
- Deploy code that reads only from the new.
- 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
| Method | Execution time | Downtime | Rollback | Additional resources |
|---|---|---|---|---|
| pg_upgrade | Seconds (100 GB) | Yes (up to 5 min) | No | Minimal |
| Logical replication | Hours (setup) | No | Yes | Extra server, disks |
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
| Tool | Database | Features |
|---|---|---|
| gh-ost | MySQL | Online schema migration without locks, from GitHub |
| pg-osc | PostgreSQL | Analog of gh-ost for Postgres |
| Flyway | PostgreSQL, MySQL | Versioned migrations, undo support |
| Liquibase | PostgreSQL, MySQL | Changelogs in XML/YAML/JSON |
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
Additional checks for logical replication
- Ensure wal_level = logical on the old server. - Verify the replication user has publish privileges. - Monitor lag using pg_stat_replication.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.