How to Migrate a PostgreSQL Database with Zero Downtime

Taking your database offline for a migration is a relic of the past. Production databases serve real users around the clock, and even a 5-minute outage during peak hours can cost revenue and erode trust. This tutorial walks through the techniques professionals use to migrate PostgreSQL databases with zero downtime — including logical replication, dual-write patterns, and safe cutover strategies.

The Core Challenge

A naive migration looks like this: stop writes → dump → restore → update connection string → resume. The problem is the "stop writes" step. For any app with real traffic, that's not acceptable. Zero-downtime migration requires keeping both databases live simultaneously and moving traffic gradually.

Strategy Overview

1Provision the target database on your new platform
2Baseline restore — copy the current state without stopping writes
3Enable logical replication — stream changes from source to target
4Verify sync — confirm target has caught up
5Cutover — switch connection strings with minimal write pause
6Decommission the old database

Step 1: Provision Your Target PostgreSQL Database

On PandaStack, create a new PostgreSQL database from [dashboard.pandastack.io](https://dashboard.pandastack.io) → Databases. Note the connection string — you'll use it throughout this tutorial.

# Set variables for convenience
export SOURCE_DB="postgresql://user:pass@old-host:5432/mydb"
export TARGET_DB="postgresql://user:pass@pandastack-host:5432/mydb"

# Confirm connectivity to both
psql "$SOURCE_DB" -c "SELECT version();"
psql "$TARGET_DB" -c "SELECT version();"

Step 2: Take a Baseline Snapshot

Use pg_dump with --no-acl and --no-owner for maximum compatibility. Critically, record the WAL LSN (log sequence number) at dump time — you'll use this to start replication from the right point.

# Get the current LSN before dumping
psql "$SOURCE_DB" -c "SELECT pg_current_wal_lsn();"

# Dump schema and data
pg_dump   --format=custom   --no-acl   --no-owner   --compress=9   --verbose   "$SOURCE_DB" > baseline.dump

# Restore to target
pg_restore   --no-acl   --no-owner   --verbose   --exit-on-error   -d "$TARGET_DB"   baseline.dump

Step 3: Enable Logical Replication on the Source

Logical replication streams row-level changes (INSERT, UPDATE, DELETE) from source to target in real time.

# On the source database — set wal_level to logical
psql "$SOURCE_DB" -c "ALTER SYSTEM SET wal_level = logical;"
psql "$SOURCE_DB" -c "SELECT pg_reload_conf();"

# Verify
psql "$SOURCE_DB" -c "SHOW wal_level;"

# Create a publication for all tables
psql "$SOURCE_DB" -c "CREATE PUBLICATION migration_pub FOR ALL TABLES;"

# Create a replication slot to track progress
psql "$SOURCE_DB" -c "SELECT pg_create_logical_replication_slot('migration_slot', 'pgoutput');"

Step 4: Create a Subscription on the Target

# On the target database, create a subscription
psql "$TARGET_DB" -c "
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=old-host port=5432 dbname=mydb user=replication_user password=yourpassword'
PUBLICATION migration_pub
WITH (copy_data = false, slot_name = 'migration_slot');
"

# Monitor replication lag
psql "$SOURCE_DB" -c "
SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_name = 'migration_slot';
"

Watch the lag column. When it reads 0 bytes, the target has caught up and you're ready for cutover.

Step 5: Verify Data Integrity

Before cutting over, verify row counts match between source and target:

# Compare row counts on key tables
psql "$SOURCE_DB" -c "SELECT 'source' AS db, count(*) FROM orders;"
psql "$TARGET_DB" -c "SELECT 'target' AS db, count(*) FROM orders;"

# Check last N records match
psql "$SOURCE_DB" -c "SELECT id, updated_at FROM orders ORDER BY updated_at DESC LIMIT 10;"
psql "$TARGET_DB" -c "SELECT id, updated_at FROM orders ORDER BY updated_at DESC LIMIT 10;"

Step 6: The Cutover

This is the only moment where you pause writes — keep it under 30 seconds.

# 1. Put application in maintenance mode or block write traffic
# 2. Wait for replication lag to reach zero
psql "$SOURCE_DB" -c "
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots WHERE slot_name = 'migration_slot';
"

# 3. Update DATABASE_URL in PandaStack dashboard to TARGET_DB
panda env set DATABASE_URL="$TARGET_DB"

# 4. Trigger a redeploy
panda deploy

# 5. Verify app connects to new database
curl https://your-app.pandastack.io/api/health

Step 7: Cleanup

# Drop the subscription on target
psql "$TARGET_DB" -c "DROP SUBSCRIPTION migration_sub;"

# Drop the replication slot and publication on source
psql "$SOURCE_DB" -c "SELECT pg_drop_replication_slot('migration_slot');"
psql "$SOURCE_DB" -c "DROP PUBLICATION migration_pub;"

Keep the old database running in read-only mode for 48 hours as a backup, then decommission it. Logical replication is the gold standard for zero-downtime database migrations — master it and you'll never need a maintenance window again. See [docs.pandastack.io](https://docs.pandastack.io) for PandaStack's managed database documentation.

How to Migrate a PostgreSQL Database with Zero Downtime