PostgreSQL logical replication: zero-downtime DB splits

Meta description: Learn how to split a monolithic PostgreSQL database into microservices using logical replication — covering slot management, WAL pressure, lag monitoring, and safe cutover windows.

Tags: backend microservices architecture api cleanarchitecture

---

TL;DR

Splitting a monolithic PostgreSQL database into service-owned databases is one of the highest-risk migrations in backend engineering. Logical replication gives you a live data bridge that lets you cut over with near-zero downtime — but slot mismanagement, sequence drift, and DDL constraints will silently wreck your migration if you don’t account for them upfront. This post walks you through the mechanics, the gotchas, and the monitoring you need before you flip the switch.

---

Why logical replication and not pg_dump?

In my experience building production systems, teams default to dump-and-restore because it feels safer. It isn’t. A dump of a 500 GB transactional database means hours of downtime, a write freeze, and a rollback path that amounts to “restore the original.” Logical replication gives you a continuously synchronized replica with a cutover window measured in seconds, not hours.

Approach	Downtime Window	Rollback Path	Write Safety
pg_dump / restore	Hours	Manual restore	None during freeze
Logical replication	< 30 seconds	Re-route traffic	Bidirectional sync window
Streaming replication	N/A (same cluster)	N/A	N/A

---

Step 1: Create the replication slot before you touch anything else

The replication slot is the foundation. Create it on the source database immediately — before schema migrations, before any application changes.

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

Most teams get this wrong: an inactive slot causes WAL retention to accumulate indefinitely. PostgreSQL holds every WAL segment from the slot’s creation point until the subscriber catches up. On a write-heavy system, your disk fills up while you’re still in the setup phase.

Set a hard limit immediately:

ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

---

Step 2: Publish only what the target service owns

Create a publication scoped to the exact tables crossing service boundaries. Don’t publish the entire database.

-- Publish only orders-domain tables
CREATE PUBLICATION orders_pub
FOR TABLE orders, order_items, order_status_history
WITH (publish = 'insert, update, delete');

Then on the target (new service 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);

---

Step 3: Monitor lag continuously — not just before cutover

Replication lag is your primary health signal. Run this query on the source:

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

Alert at 50 MB of lag. Sustained lag growth means your subscriber can’t keep up with write volume — fix that before scheduling a cutover window.

---

The cutover window: bidirectional sync and write quiescence

Let me walk you through the cutover moment itself. You have roughly a 10–30 second window where both databases must be consistent. The sequence:

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

The bidirectional sync trap: if any application writes to the source during this window, those writes won’t replicate forward after you drop the subscription. Your feature flag must be atomic. A partial rollout here means data divergence.

---

The two constraints that will break you

Sequences are not replicated. Logical replication does not sync sequence state. If your source has orders_id_seq at 9,400,000 when you cut over, your target sequence might be at 1. Set it explicitly:

-- On target, after subscription is established but before cutover
SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders) + 10000);

DDL is not replicated either. Schema changes on the source do not propagate, and any ALTER TABLE on a published table will break the replication stream. Lock your schema on published tables for the entire migration window and apply DDL to both databases manually and in sync.

---

Before you cut over

Create the replication slot on day one and set max_slot_wal_keep_size immediately. WAL accumulation from an unmonitored slot isn’t a hypothetical — it will fill your disk.

Don’t rely on a one-shot lag check. Run the monitoring query on a 10-second interval for at least 24 hours before your cutover window to understand your baseline lag under real write load.

Fix your sequences and audit every DDL change before you begin. Both will silently corrupt your migration. Both take maybe 30 minutes to address upfront.