DEV Community

SoftwareDevs mvpfactory.io
SoftwareDevs mvpfactory.io

Posted on Originally published at mvpfactory.io

PostgreSQL Logical Replication for Zero-Downtime Database Splits

#webdev #programming 
---
title: "PostgreSQL Logical Replication: Zero-Downtime Database Splits"
published: true
description: "Split a monolithic PostgreSQL database into microservices using logical replication — covering slot management, WAL pressure, lag monitoring, and the cutover window that doesn't lose writes."
tags: postgresql, architecture, microservices, api
canonical_url: https://mvpfactory.co/blog/postgresql-logical-replication-zero-downtime-db-splits
---
Enter fullscreen mode Exit fullscreen mode

What We Will Build

By the end of this tutorial you will know how to split a monolithic PostgreSQL database into service-owned databases using logical replication — with a cutover window measured in seconds, not hours. Let me show you the pattern I use in every large-scale migration, including the two constraints that silently wreck most teams before they ever reach cutover.

Prerequisites

  • PostgreSQL 10+ on source and target
  • A replication user with REPLICATION privilege on the source
  • wal_level = logical set on the source (SHOW wal_level; to verify)
  • Network access between source and target databases

Step 1: Create the Replication Slot First — Before Anything Else

Here is the minimal setup to get this working. The slot is your foundation. Create it on the source database before schema migrations, before application changes, before anything.

-- On the source (monolith) database
SELECT pg_create_logical_replication_slot('svc_orders_slot', 'pgoutput');

-- Verify it exists and note the confirmed_flush_lsn
SELECT slot_name, plugin, confirmed_flush_lsn, active
FROM pg_replication_slots
WHERE slot_name = 'svc_orders_slot';
Enter fullscreen mode Exit fullscreen mode

Then set a hard WAL retention limit immediately:

ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

An inactive slot causes PostgreSQL to retain every WAL segment from the slot's creation point. On a write-heavy system your disk fills up while you are still in setup. Set max_slot_wal_keep_size on day one — not the night before cutover.

Step 2: Publish Only the Tables the Target Service Owns

Don't publish the entire database. Scope the publication to the exact tables crossing service boundaries.

-- On the source
CREATE PUBLICATION orders_pub
FOR TABLE orders, order_items, order_status_history
WITH (publish = 'insert, update, delete');
Enter fullscreen mode Exit fullscreen mode

Then on the target database:

CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=monolith-db port=5432 dbname=monolith user=replicator password=...'
PUBLICATION orders_pub
WITH (slot_name = 'svc_orders_slot', copy_data = true);
Enter fullscreen mode Exit fullscreen mode

copy_data = true backfills existing rows before streaming begins. For large tables this takes time — plan for it.

Step 3: Monitor Lag on a 10-Second Interval, Not Just Before Cutover

The docs do not mention this, but a single lag check before your cutover window is not enough. Run this on the source for at least 24 hours to understand your baseline lag under real write load:

SELECT
  slot_name,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag_size,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_name = 'svc_orders_slot';
Enter fullscreen mode Exit fullscreen mode

Alert at 50 MB. Sustained growth means your subscriber cannot keep up with write volume — that is a problem to fix before you schedule a maintenance window, not during one.

Step 4: Execute the Cutover Window

You have a 10–30 second window where both databases must be consistent. Execute in this order:

  1. Route new writes to target via feature flag or DNS switch
  2. Drain in-flight transactions on the source (pg_stat_activity)
  3. Confirm lag reaches zero with the query above
  4. Drop the subscription on the target — it is now authoritative
  5. Drop the publication and slot on the source

The feature flag must be atomic. A partial rollout during this window means writes land on the source after you drop the subscription — those writes never replicate forward. That is data divergence, not a race condition you can retry your way out of.

Gotchas

Sequences are not replicated. Here is the gotcha that will save you hours. If orders_id_seq is at 9,400,000 on the source at cutover, your target sequence might be at 1. Set it explicitly after the subscription is established but before cutover:

-- On the target database
SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders) + 10000);
Enter fullscreen mode Exit fullscreen mode

DDL is not replicated either. Any ALTER TABLE on a published table breaks the replication stream entirely. Lock your schema on published tables for the full migration window and apply DDL to both databases manually and in sync.

The comparison you need to make:

Approach Downtime Window Rollback Path
pg_dump / restore Hours Manual restore
Logical replication < 30 seconds Re-route traffic

Conclusion

Create the slot on day one and set max_slot_wal_keep_size immediately. Run lag monitoring on a 10-second interval for 24 hours before your cutover window. Fix your sequences and audit every DDL change before you begin — both take 30 minutes upfront and hours to recover from if you skip them.

Logical replication is the right tool for this migration. Used carefully, it gives you a live data bridge with a cutover window that does not cost your users a maintenance page.

Top comments (0)