PostgreSQL Logical Replication

Logical Replication for Zero-Downtime Migration

PostgreSQL logical replication sends changes at the row level (INSERT, UPDATE, DELETE) rather than the WAL byte level. This enables replication between different PostgreSQL versions, selective table replication, and zero-downtime major version upgrades.

Use Cases

• Major version upgrades without downtime
• Replicating specific tables to a reporting database
• Multi-master replication (with conflict resolution)
• Cross-cloud database migration

Setup: Publisher (Source)

# postgresql.conf on publisher
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- Create publication
CREATE PUBLICATION my_pub FOR ALL TABLES;

Setup: Subscriber (Target)

-- Create subscription (schema must already exist on subscriber)
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=source-db port=5432 dbname=mydb user=replicator password=secret'
  PUBLICATION my_pub;

Zero-Downtime Migration Steps

1. Set up the new PostgreSQL version with the same schema
2. Create a publication on the old server and subscription on the new
3. Wait for initial data sync to complete
4. Monitor replication lag until it reaches zero
5. Switch application connections to the new server
6. Drop the subscription and decommission the old server

Eazy SaaS Tip: Logical replication is our preferred method for major version upgrades on databases that cannot afford downtime. The entire process can be done during business hours with careful monitoring.