Setting Up PostgreSQL Logical Replication

PostgreSQL Logical Replication: Slots Grow, Subscription Lags

PostgreSQL logical replication breaks quietly.

A replication slot pins a position in the write-ahead log and holds it until confirmation from the subscriber — if the subscription is stopped, lagging, or cannot keep up applying changes, the primary database keeps writing but cannot release the WAL.

Free space on the data partition is consumed at the write rate, and it is not replication that stops, but the database itself.

Then a chain reaction kicks in: storage overflow, the WAL has nowhere to write, writes stall. Autovacuum of dead rows stops next, so tables bloat, and after freeing space you still need to catch up the subscriber's accumulated lag.

An emergency restart of the primary database at this point stretches into hours of write unavailability: the application gets failures, and recovery requires manual analysis — which slot is holding WAL, how far the subscription has lagged, which tables have diverged.

Subscriber lag rarely has a single cause.

Usually several combine: the subscription hits an apply conflict and fails in a loop, the publication lists extra tables, the subscriber lacks resources for the apply workload, and long transactions on the source hold a snapshot and prevent the position from advancing.

Without monitoring replication slot state and subscription lag, this is noticed only when the disk is full.

Replication setup begins with safeguards: a limit on how much WAL a slot may retain, disabling unused slots, restricting the list of published tables, measuring subscription lag. Below we look at how this is addressed in practice.

What Changes in Operations: Updates Without Service Downtime

Logical replication transmits changes at the row level rather than copying the database entirely, so the target environment remains live and available for reads throughout the migration.

The publication on the source and subscription on the receiver are set up so that cutover takes minutes: first the table structure is applied, then the data, and only after checksum verification is traffic switched to the new database.

Updating table structure stops being an event with a planned overnight downtime window — it is a change in production mode.

Reporting workload moves to a separate environment. Heavy joins, full scans, and aggregations no longer compete for memory, connections, and CPU time with write transactions — lock queues between writes and analytics simply do not occur.

The primary database serves the application workload, and write response time no longer depends on who is building a report at that moment.

A separate class of failures that goes away with this is the long blocking window and lack of space when copying the entire database.

Industry incident analyses during migration, DBMS Operations Reports, show that the main share of unplanned downtime comes from these two scenarios, not from change transmission itself.

Practical result: schema changes on the production database are performed without service downtime, reporting queries do not affect application queries, and rollback to the previous environment remains possible at any step while data verification is in progress.

Replication Modes: Publications, Subscriptions, and Limitations

Logical replication is built around the publication — subscription pair: on the source, the publication describes which tables and which operations enter the stream; on the receiver, the subscription reads the slot and applies changes.

Unlike a full cluster copy, the stream is assembled at the row and table level, so the receiver can have a different configuration and accept only part of the schema.

Hence the fork: one mechanism covers migrating individual tables, exchanging between environments, and exporting to analytics. The choice determines whether writes to the receiver are needed, how many tables are involved, and who is responsible for conflict resolution.

Publications and subscriptions do not replace physical replication: schema changes, sequences, and objects outside tables are transferred separately. Conflict resolution rules are fixed before enabling exchange — otherwise divergence will surface in the data.

Work Stages: From Workload Audit to Handover to Operations

Logical replication transfers not a copy of pages but a stream of changes, so before launch a workload profile is needed: which tables produce the main write volume, how often the schema changes, whether primary keys exist. The order of stages below is not accidental — each subsequent one relies on data from the previous.

1. Workload and current schema audit. We collect statistics on change volume and frequency, long-running queries, privileges, and object composition. We separately flag tables without a primary key — they need their own approach.
2. Data transfer schema design. We fix what goes into replication and what remains local, how sequences and schema changes are handled. The output is a flow diagram and a list of risks with a plan for each.
3. Preparation of objects and privileges. We create replication roles with the minimum required privileges, align replica identifiers, and verify versions and extensions on the source and receiver.
4. Setting up publications and subscriptions. We bring up the publication on the source and the subscription on the receiver, and start initial synchronization so that it does not block production writes or slow down applications.
5. Lag monitoring on the production workload. We observe lag under real load: write peaks, long transactions, apply conflicts. We set up alerts with thresholds so growth is noticed before it becomes critical.
6. Load switchover rehearsal. We stop writes, wait for zero lag, switch applications to the new node, and check sequences and background jobs. We go through rollback the same way.
7. Handover of the operations runbook. We provide documentation: how to read lag metrics, what to do on conflict, how to add a table to a publication. The on-call shift walks through scenarios on the test environment.

What Remains in Your Environment: Configurations and Runbooks

Replication work ends not with a running subscription but with a package of artifacts that physically remains with you. Below is what we hand over to the customer's environment and without which the change stream survives only until the first abnormal situation.

• Configuration files for the publishing and subscribing sides with comments on what was changed and why: a new node is brought up by copying, not from an engineer's memory.
• Description of transferred objects and replication documentation: which tables are included in the publication, what is deliberately excluded, how sequences and write conflicts are handled.
• Data flow diagram: where changes enter the publication, through which nodes they pass, where the stream can be broken without losing integrity.
• Access rights matrix: separate roles for publication and subscription, who is allowed to read slot state, and who is responsible for monitoring.
• Lag alerts: thresholds for apply delay, recipients, and escalation procedure — we learn about divergence before the business notices it.
• On-call shift instructions: typical failures, how to read subscription state, when to stop the stream, and whom to escalate to.
• Switchover and rollback runbook to the previous schema plus a memo on recovering an interrupted subscription — including the case where the slot exists but the stream has stopped.

Project Case Study: Moving Reporting Workload Without Downtime

What Was

The reporting environment lived on the same server as the production database PostgreSQL. Nightly schema updates required stopping writes for several hours, and heavy analytical queries during peak hours held locks and pushed subscription lag up to tens of minutes.

The on-call shift handled such tickets almost every shift: some due to report delays, some due to hanging transactions.

What Became

Replication in the production environment was split off into a dedicated analytics environment: a publication for a specific set of tables, a subscription with its own slot, and initial data copying without stopping the source.

New tables are added to the publication as a separate step, key conflicts are cleared by reapplying — schema updates no longer block writes.

Metrics before and after: the schema update window shrank from several hours to zero, replication lag stays within a minute even during peak hours, and on a quiet workload — seconds.

On-call shift tickets related to reporting workload dropped fourfold in the first month of the environment's operation.

The measurements are confirmed by two sources: a replication lag graph in the monitoring system and the subscription switchover log. Both remain with the customer, so degradation is visible before report users notice it.

What to Do If a Full Cluster Copy Is Already Deployed?

A full cluster copy and a logical subscription do not conflict — they work at different levels.

Physical replication sends the change stream of the entire cluster as a whole, while logical replication parses it at the table level and transmits only those listed in the publication.

Therefore the publication can be brought up on the source alongside the already running copy, let the subscription catch up, and switch consumers gradually, without service downtime.

Settings are reused in this case: roles, access rights, network rules, and slots on the target node already exist; the publication is added on top.

We perform initial synchronization of the selected tables not from scratch but from the current copy — it already contains an up-to-date snapshot, and all that remains is to catch up on changes by log position. The transition fits into working hours, not a downtime window.

While the two schemes coexist, there remains a single source of truth. Read consumers continue to go the previous route, while we direct new queries to the selected tables and verify the result: row counts, checksums for key tables, subscription lag in seconds.

As long as lag is not zero or verification shows discrepancies, the old environment remains primary.

We disable it when the subscription maintains zero lag under production load and verification passes several times in a row without discrepancies.

Procedure: stop writes via the previous route, make sure the latest changes have arrived, drop the subscription, then remove the physical copy and free its disk space. A slot left without a subscriber is the main threat: it accumulates WAL and fills the disk.

Technical Doubts: Slots, Lag, Conflicts

Logical replication is a stream of changes from a publication to a subscription, and failures occur not at startup but in abnormal modes. We examine five situations that most often surface already in the production environment.

Can only some tables be transferred?

Yes, the publication is assembled from a list of tables or schemas; the rest does not leave the source. Receivers must exist in advance with a compatible structure and primary key.

How to handle conflicts when writing to both environments?

A conflicting row stops apply, and the subscription waits for intervention. We delimit write zones by table, and discrepancies are resolved by a resolution rule, not by manual row edits.

What happens to slots during downtime?

The slot persists even if the subscription is dropped, and continues to retain WAL — hence disk overflow. The runbook includes lag monitoring and removal of unused slots.

Is it necessary to stop the service when changing the schema?

Adding a column proceeds on a live system; changing a type or primary key requires pausing the subscription and resynchronizing the table. The order is receiver, then source, then the stream.

Let's Discuss the Task: Send the Diagram of Your Current Environment

To come back with an estimate of the work rather than general considerations, a minimum of inputs is needed — usually your team already has them. The more precise the input data, the fewer clarification rounds and the more realistic the work plan in terms of timelines.

• Diagram of the current environment: where the source and receiver are located, how they are connected over the network, whether there are intermediate nodes and load balancers.
• List of tables and sequences: what we transfer entirely, what partially, where partitioning, foreign keys, and generated values exist.
• Requirements for the update window: acceptable receiver lag, whether individual tables need to be transferred without stopping writes.
• Target environment: DBMS version and edition, replication direction — unidirectional or bidirectional, whether a cascade to multiple receivers is needed.
• Current monitoring systems: log collection, alerts on replication slot lag and on change apply conflicts.
• Load constraints: write profile during peak hours, daily change volume, behavior during connection loss.

Describe the task in free form and attach what you already have — this is enough to start. We will come back with an estimate and work plan: stages, switchover procedure, and rollback points at each step.