PostgreSQL Logical Replication for Migrations and Read Replicas

PostgreSQL's logical replication has evolved from a niche feature into a production-grade tool for zero-downtime migrations and distributed read scaling. Unlike physical replication, which ships raw WAL blocks at the storage level, logical replication decodes the WAL into row-level changes and applies them via a publish-subscribe model. This gives you the flexibility to replicate only selected tables, run different PostgreSQL versions on publisher and subscriber, and even promote a subscriber to a standalone master without rebuilding the entire cluster. For DBAs managing large-scale PostgreSQL estates, mastering logical replication is no longer optional—it's a core operational skill.

Why Logical Replication Over Physical Replication

Physical replication is simpler and lower-latency, but it locks you into identical hardware, PostgreSQL versions, and full-database copies. Logical replication breaks those constraints. You can replicate a subset of tables—critical for sharded or multi-tenant architectures—and run the subscriber on a newer minor version for phased upgrades. Logical replication also supports bidirectional setups (with careful conflict handling) and can be paused, resumed, or re-synced per table without rebuilding the entire replica. The trade-off is higher CPU overhead on the publisher for decoding WAL and potential lag under heavy write loads. Choose logical when you need granular control; choose physical when you need raw speed and simplicity.

Rule of thumb: Use physical replication for identical, full-database disaster recovery. Use logical replication for migrations, partial data distribution, or version upgrades.

Setting Up Publication and Subscription

Logical replication requires wal_level = logical on the publisher. After a restart, create a publication that defines which tables to expose:

-- On the publisher
CREATE PUBLICATION migration_pub
  FOR TABLE orders, users, products
  WITH (publish = 'insert, update, delete, truncate');

On the subscriber, create identical table schemas (indexes are optional but recommended for performance). Then create a subscription that connects to the publisher:

-- On the subscriber
CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=publisher-host dbname=mydb user=repl_user password=secret'
  PUBLICATION migration_pub
  WITH (copy_data = true, create_slot = true, enabled = true);

The copy_data = true option snapshots existing data during initial sync. For large tables, this can take hours—monitor pg_stat_subscription for progress. Always use a dedicated replication user with REPLICATION and LOGIN privileges, and ensure network connectivity over the replication port (default 5432).

Handling Schema Changes During Migration

Logical replication does not replicate DDL. If you add a column on the publisher, the subscriber must have that column added manually before replication resumes. During a migration, plan schema changes in phases:

1. Add new columns to both publisher and subscriber while replication is paused.
2. Resume replication—new rows will include the column, but existing rows on the subscriber remain NULL until updated.
3. For column drops, remove the column from the publication first, then drop it on both sides.
4. Use ALTER TABLE ... SET LOGGED if converting from unlogged tables.

To avoid replication failures, never run DDL on the publisher without first applying matching DDL on the subscriber. Use a schema migration tool like sqldef or pg_easy_replicate to automate this coordination.

Using Logical Replication for Read Replicas

Logical replication is ideal for read replicas that serve reporting queries or feed data warehouses. You can replicate only the tables needed for analytics, reducing storage and I/O on the subscriber. For high-availability read scaling, consider these patterns:

• Selective replication: Publish only high-read tables, leaving write-heavy tables on the primary.
• Multiple subscribers: One subscriber for real-time dashboards, another for nightly batch jobs.
• Version mixing: Run PostgreSQL 15 on the subscriber while the publisher is on 14, enabling early access to new features.

Be aware that logical replication does not carry sequence values or large object (blob) changes. For sequences, pre-set them on the subscriber to avoid conflicts if you later promote it.

Monitoring and Troubleshooting Lag

Lag in logical replication is measured in two ways: WAL position lag and apply lag. Use these queries on the publisher:

-- Check replication slots and lag
SELECT slot_name, database, active, 
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

On the subscriber, monitor apply progress:

-- Subscriber apply lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Common causes of lag: large transactions (logical replication applies per-row, not per-transaction), missing indexes on the subscriber, or network bandwidth. If lag spikes, check pg_stat_subscription for errors—the subscriber will retry failed transactions automatically. For persistent lag, consider increasing max_logical_replication_workers and max_sync_workers_per_subscription.

Promoting a Subscriber to Standalone Database

Promoting a logical subscriber to a standalone primary is straightforward but requires careful sequencing:

1. Stop all application writes to the publisher (or ensure the subscriber is fully caught up).
2. On the subscriber, run ALTER SUBSCRIPTION ... DISABLE to stop receiving changes.
3. Drop the subscription with DROP SUBSCRIPTION to remove the replication slot from the publisher.
4. Optionally, run SELECT pg_replication_origin_advance(...) to reset origin tracking.
5. Point applications to the subscriber's new connection string.

After promotion, the subscriber becomes a fully independent database. It will have all data that was replicated up to the point of disconnection. Any sequences or auto-increment columns must be manually adjusted if the subscriber will accept writes—use ALTER SEQUENCE ... RESTART WITH to avoid primary key conflicts.

Best Practices and Limitations

Logical replication is not a silver bullet. Key limitations include:

• No DDL replication: Schema changes must be applied manually on both sides.
• No TRUNCATE on partitioned tables: Use DELETE instead.
• No large objects (BLOBs): Use TOAST columns instead.
• No sequences or serial columns: Pre-set values on subscriber.

Best practices to follow:

• Always test with a staging environment that mirrors production data volume.
• Set wal_keep_size high enough to prevent WAL recycling during subscriber downtime.
• Use pg_rewind if you need to re-sync a subscriber that fell too far behind.
• Monitor replication slots for bloat—orphaned slots can fill your WAL disk.
• For zero-downtime migrations, combine logical replication with a blue-green deployment pattern.

The Short Version

Logical replication gives DBAs the power to migrate databases without downtime, build selective read replicas, and upgrade PostgreSQL versions incrementally. Set wal_level = logical, create publications and subscriptions with explicit table lists, and monitor lag via system catalogs. Handle schema changes manually on both sides, and when promoting a subscriber, disable the subscription and drop it cleanly. Avoid logical replication for full-database DR—physical replication is better there. For everything else, logical replication is the Swiss Army knife of PostgreSQL operational tooling.