Postgres Migrations Using Logical Replication
Migrating a PostgreSQL database is never a trivial task – especially when you’re dealing with production systems, large datasets, and strict uptime requirements. Whether you’re upgrading PostgreSQL versions or shifting infrastructure, choosing the right migration strategy is critical.
Let’s explore the available approaches and then deep dive into logical replication, the most flexible option when downtime isn’t acceptable.
Common PostgreSQL Migration Approaches
1. Dump & Restore (pg_dump / pg_restore)
This is the most straightforward method.
- Captures schema + data + objects
- Easy to execute and widely used
- Ideal for small to medium databases (50–150 GB)
On modern systems, migrations can complete in under an hour.
Limitation: Requires downtime during restore.
2. WAL-Based Migration
Using tools like pgBackRest or WAL-G:
- Take a base backup
- Stream WAL logs to the target system
- Maintain near real-time sync
Best for large databases (TB scale)
Minimal downtime during cutover
Limitation: Not always possible in managed services like RDS.
3. Logical Replication (The Smart Alternative)
When:
- Your DB is too large for dump/restore
- WAL access is restricted
- Downtime must be minimized
Logical replication becomes the go-to solution.
How Logical Replication Works
- Publisher → Source database
- Subscriber → Target database
Process:
- Initial data copy
- Continuous replication of changes (INSERT, UPDATE, DELETE)
Step-by-Step Logical Migration
Step 1: Prepare Schema
Since schema isn’t replicated, you must migrate it manually:
pg_dump -Fc -s $SOURCE_DB_URI | pg_restore --no-owner --no-acl -d $TARGET_DB_URI
Keep schema in sync if changes continue during migration.
Step 2: Configure Publisher
Enable logical replication:
wal_level = logical
Tune key parameters:
- max_replication_slots
- max_wal_senders
- max_logical_replication_workers
- max_worker_processes
Create replication user:
CREATE ROLE migrator WITH REPLICATION LOGIN PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migrator;
Handle Tables Without Primary Keys
Logical replication needs row identification.
Check missing PKs:
-- query to find tables without primary keys
Fix options:
ALTER TABLE tablename REPLICA IDENTITY USING INDEX idx_unique;
-- OR
ALTER TABLE tablename REPLICA IDENTITY FULL;
Create Publication
CREATE PUBLICATION migration_pub FOR ALL TABLES;
Step 3: Setup Subscriber
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=... dbname=... user=... password=...'
PUBLICATION migration_pub;
This automatically:
- Creates replication slots
- Starts initial data sync
Step 4: Monitor Progress
Track replication status:
SELECT * FROM pg_stat_subscription;
Check table sync status:
SELECT * FROM pg_subscription_rel;
Status codes:
d→ copyingf→ finisheds→ synchronizedr→ ready
Step 5: Testing & Cutover
Before switching:
- Validate data consistency
- Run application tests
At cutover:
- Stop writes on source
- Redirect application to new DB
Step 6: Fix Sequences (Critical Step)
Logical replication does NOT sync sequences.
Generate fixes:
SELECT 'SELECT setval(...)';
Final Thoughts
Logical replication is one of the most powerful strategies for modern PostgreSQL migrations. While it requires more setup than traditional methods, it offers unmatched flexibility especially in environments where downtime is costly or unacceptable. The key to success lies in careful planning: ensuring schema alignment, monitoring replication health, and handling edge cases like sequences and primary keys. When executed properly, logical replication enables seamless migrations with minimal disruption to production systems.
At Learnomate Technologies, we regularly guide professionals through real-world PostgreSQL migration strategies like logical replication. Our hands-on training focuses not just on theory, but on solving practical challenges such as zero-downtime migrations, performance tuning, and production troubleshooting helping learners gain the confidence needed to handle enterprise-level database environments.
