pg-zdm: PostgreSQL Zero-Downtime Migration Tool

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.

✨ Features

• 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.

---

⚙️ Prerequisites

Database Configuration (CRITICAL)

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

PostgreSQL User

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

Database	Required Privileges	Purpose
Source DB	REPLICATION, CREATE ROLE (or superuser)	Create Publication and Replication Slot.
Target DB	CREATE	Create Subscription.

Network and Connectivity

• 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.

---

💻 Installation

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

Build for Linux/Mac

go build -o pg-zdm main.go

Cross-Compile for Windows (Recommended)

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

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

---

🚀 Usage Guide (3-Step Migration)

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

DSN Format

Use the standard PostgreSQL connection string format:

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

Step 1: Initialize Replication

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);

Step 2: Monitor Status

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-21T10:00:00Z

✅ Status: FULLY SYNCHRONIZED. Ready for 'zdm cutover' command.

Step 3: Perform Cutover

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.

---

🛠 Command Reference

zdm init

Flag	Description	Mandatory
--source	Connection string for the Source database.	Yes
--target	Connection string for the Target database.	Yes
--ignore-missing	Limit publication to tables present on Target.	No
--defer-copy	Create subscription with copy_data=false; copy later.	No

zdm status

Flag	Description	Mandatory
--source	Connection string for the Source database.	Yes
--target	Connection string for the Target database.	Yes

zdm cutover

Flag	Description	Mandatory
--source	Connection string for the Source database.	Yes
--target	Connection string for the Target database.	Yes

---

🚨 Troubleshooting

Error Message	Cause	Solution
source wal_level not logical	Source database is not configured for logical decoding.	Change wal_level = logical in postgresql.conf on the Source and restart the server.
could not connect to the publisher: ... role "barip" does not exist	The connection string inside the CREATE SUBSCRIPTION command is failing.	Ensure the --source DSN is a complete and accessible string (postgres://user:pass@host:port/db) that can be reached from the Target server.
could not connect to the publisher ... host is localhost	The --source DSN uses localhost, which resolves to the Target host when creating the subscription.	Replace localhost with the Source server’s IP/hostname in --source.
relation "schema.table" does not exist during publication	Publication creation lists tables with special/case-sensitive names unquoted.	The tool quotes identifiers automatically; if you see this, verify schema/table names exist on the Source.
relation does not exist during subscription copy	Target is missing tables included in the publication.	Create schema on Target first (e.g., pg_dump --schema-only), or run init --ignore-missing, or init --defer-copy and later REFRESH PUBLICATION after schema is ready.
no free replication slots	All slots are in use.	Increase max_replication_slots and restart the Source.
insufficient wal_senders	max_wal_senders too low.	Increase max_wal_senders and restart the Source.
insufficient replication privilege	Source user lacks REPLICATION or superuser.	Grant REPLICATION or use a superuser for --source.
cannot execute CREATE PUBLICATION in a read-only transaction	Your initial connection to the Source is read-only (e.g., connecting to a standby).	Connect the --source flag directly to the primary/master instance. The user must have superuser or the REPLICATION role.
timeout waiting for target to catch up	Target failed to apply final changes within 5 minutes.	Immediately check the Source DB logs. Run zdm status on a loop in a separate window to confirm if lag is actually stuck. If so, manual intervention or debugging of replication is required.