pg-zdm is a command-line utility built in Go that orchestrates a Zero-Downtime Migration (ZDM) between two PostgreSQL servers using native Logical Replication.

It automates the complex steps required to safely switch an application from a Source (Old) database to a Target (New) database with minimal or zero application downtime.

• Initialization (init): Creates the Publication on the Source and the Subscription on the Target.
  • Supports --ignore-missing to limit publication to tables that already exist on Target.
  • Supports --defer-copy to create the subscription with copy_data=false and copy later.
• Status Check (status): Provides real-time replication lag metrics from both the Source (WAL position) and Target (Apply lag).
• Safe Cutover (cutover): Executes a controlled switchover sequence:
  • Locks the Source database globally to read-only mode.
  • Waits for the Target to fully synchronize the final transaction (0 bytes lag).
  • Synchronizes all database sequences (auto-increment IDs) on the Target with a large safety buffer to prevent ID collisions.
  • Promotes the Target database, completing the migration.
• Rollback Safety: Automatically unlocks the Source database if the cutover sequence fails during the lock/wait phase.

The Source PostgreSQL instance must be configured for Logical Replication. This requires two mandatory settings in its postgresql.conf file, followed by a server restart:

1. wal_level = logical
2. max_replication_slots and max_wal_senders must be set high enough (e.g., 10 or 20).

Example postgresql.conf changes:

# postgresql.conf on the SOURCE server
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

The user specified in both DSNs must have the following privileges:

• The --source DSN must be reachable from the Target host. Avoid localhost in the source DSN when running init on the Target; the Target will resolve localhost to itself and fail to connect to the Source.
• Use the Source server’s IP or hostname in --source.

The tool is written in Go and can be built easily from source.

go build -o pg-zdm main.go

This command produces a native Windows executable (zdm.exe).

GOOS=windows GOARCH=amd64 go build -o zdm.exe main.go

All commands require the full DSNs for both the Source (Old) and Target (New) databases.

Use the standard PostgreSQL connection string format:

postgres://user:password@host:port/dbname?sslmode=disable

This sets up the Publication (zdm_pub) on the Source and the Subscription (zdm_sub) on the Target.

./pg-zdm init \
 --source "postgres://repl_user:pass@192.168.1.10:5432/db_old" \
 --target "postgres://app_user:pass@192.168.1.20:5432/db_new"

Optional flags:

• Limit publication to tables present on Target, ignoring missing ones:

./pg-zdm init \
 --ignore-missing \
 --source "postgres://repl_user:pass@192.168.1.10:5432/db_old" \
 --target "postgres://app_user:pass@192.168.1.20:5432/db_new"

• Defer initial data copy (create subscription with copy_data=false) and copy later after preparing Target schema:

./pg-zdm init \
 --defer-copy \
 --source "postgres://repl_user:pass@192.168.1.10:5432/db_old" \
 --target "postgres://app_user:pass@192.168.1.20:5432/db_new"

To start copy later:

ALTER SUBSCRIPTION zdm_sub REFRESH PUBLICATION WITH (copy_data = true);

Wait until the initial data copy is complete and the lag is zero. Run this command repeatedly until the check passes.

./pg-zdm status \
 --source "..." \
 --target "..."

Successful Output:

🔎 Fetching Replication Status...
STATUS | METRIC | LAG
------ | ------ | ---
Source Slot | Lag Holding Data | 0 bytes
Target Apply| Lag Waiting to Apply | 0 bytes
Target Commit| Latest Applied Transaction | 2025年11月21日T10:00:00Z
✅ Status: FULLY SYNCHRONIZED. Ready for 'zdm cutover' command.

This is the final, atomic step. The process will pause for up to 5 minutes while waiting for final synchronization.

./pg-zdm cutover \
 --source "..." \
 --target "..."

Successful Output:

⚡ Starting Zero Downtime Cutover Sequence...
🔒 Locking Source Database (Setting Read-Only Globally)...
🛑 Source Stopped and LSN captured at: 0/FFFFFFFF (4294967295)
⏳ Waiting for Target to catch up...
 Target LSN: 0/FFFFFFF0 | Lag: 15 bytes
 Target LSN: 0/FFFFFFFF | Lag: 0 bytes
✅ Target is fully synchronized!
🔄 Starting Sequence Synchronization...
 Found 25 sequences to sync.
 Synced "public"."users_id_seq": 10564 -> 15564
 ...
✅ All sequences synced (with +5000 buffer).
🎉 Migration Complete!
Update your application connection strings to point to the Target DB now.