PostgreSQL logical replication

Logical replication runs on three objects that work together. A publication on the publisher lists the tables and the types of change, such as inserts, updates and deletes, that are offered to other databases. A subscription on the subscriber names the publisher connection and the publication it wants. A replication slot on the publisher records how far that subscriber has consumed the change stream. Each slot belongs to one subscription, so the slot is the part to inspect first when you need to know what a subscriber has received. Before you design anything, confirm in the high availability and upgrading documentation which PostgreSQL versions sit on each side of the link and which settings, such as wal_level and the replication slot limits, your versions require.

When a subscription is created or refreshed, each included table goes through an initial synchronization phase. The subscriber copies the existing rows of each table from the publisher in a consistent snapshot. Changes that happen during the copy are kept and applied after the copy finishes, so the subscriber catches up without a gap. Until a table finishes this phase, its state is visible in the subscriber's catalog views, and you should check that every table reaches the ready state before you cut over traffic. The initial copy is usually the longest and most resource-heavy part of the work, so plan the window and network capacity around it rather than around the steady-state traffic.

After synchronization, changes stream continuously from the publisher's write-ahead log to the subscriber, which applies them in commit order. The slot is what makes this reliable. The publisher keeps the WAL the subscriber still needs and does not remove it until the subscriber confirms that it has received and applied those changes. If the subscriber is stopped, slow or unreachable, WAL accumulates on the publisher's disk, and this can fill the volume and stop the primary. Ask your team to monitor the lag of each slot, set alerts on retained WAL size, and agree in advance who may drop an unused slot. Also confirm, in the failover documentation for your version, how slots are handled after a failover, because the publisher's role can change during an incident.

The same mechanism is why logical replication suits some major version upgrades. The upgrading documentation states that logical replication works between different major versions, which allows a major upgrade with only several seconds of downtime at cutover. The sequence is to build the new cluster, load the schema, let the subscription catch up, verify row counts and checksums on agreed tables, and then move application connections. Schema changes and sequence values are not carried by the stream in the same way as table rows, so each of those needs its own step. With PostgreSQL 14 reaching end of support on November 12, 2026, start the test runs early and time the cutover against your change calendar.

Logical replication copies row changes for the tables in a publication. It does not copy everything else that a database team treats as part of the system, so the work outside replication needs its own plan. The first category is schema. Added columns, new tables, changed types and dropped objects are not applied to the subscriber by the replication stream. When the publisher's schema moves ahead of the subscriber, the apply process can fail on rows it cannot accept, and the lag grows until someone intervenes. Treat schema change as a parallel process: write every DDL change as a versioned migration, apply it to both sides in a fixed order, and get approval from the owner of the replicated tables before it runs. Confirm in the documentation for your PostgreSQL version which DDL the replication model handles, because the answer determines how strict this process must be.

The second category is sequence values. Replication moves the rows that use a sequence, but the current value of a sequence on the publisher is not carried to the subscriber. If the new primary starts issuing values from a stale position, inserts can collide with rows already replicated, and where no unique constraint exists, duplicate values can go unnoticed. Plan a sequence synchronization step that runs at cutover. After writes stop on the source, read the current value of each sequence and set the matching sequence on the target above it. Include identity columns and sequences referenced by column defaults. Name an owner for this step, script it, and rehearse it on a copy of the environment. Ask the application team whether any sequence is cached, since cached blocks can also create gaps or overlaps after a switch.

The third category is objects that the replication model does not transmit. The list of restrictions in the PostgreSQL documentation for your major version is the authoritative source, and it changes between releases, so pull the current version when you plan the work. Teams commonly build the following by hand on the subscriber: roles and privileges, functions and procedures, triggers, extensions, and row-level security policies. Ownership and default privileges also need a decision, because objects created on the subscriber will not match the publisher unless someone defines them. Record each object type in an inventory with a named owner, a method for creating it on the target, and a comparison that runs before cutover.

Run the schema migration and sequence synchronization as a process with its own runbook, separate from the application release. Each run should have a version identifier, a record of which DDL was applied to which side, and a comparison of the two catalogs before replication resumes. Compare column definitions, constraints, indexes and sequence positions with a schema diff tool or a scripted query, and keep the output as evidence for the change record. Decide in advance whether DDL on the publisher is frozen during the cutover window. Teams that skip this step often find drift only after the switch, when fixing it means stopping writes again. This is also where the platform owner confirms that the replication role has the privileges it needs to read every published table.

Before cutover, the platform owner should run this checklist and record a sign-off for each item: (1) every publisher DDL change since the last rehearsal is applied to the subscriber and recorded; (2) column definitions, constraints and indexes match between the two sides for each published table; (3) each sequence on the target is set above its source value after writes stop; (4) every object in the inventory of unreplicated objects exists on the target and has been compared; (5) the replication role and its grants are verified; (6) the restrictions list for your PostgreSQL version has been reviewed and each item has an owner; (7) a DDL freeze is in effect for the cutover window; (8) the rollback path and its sequence handling are documented and have been rehearsed.

For a major version move, the choice of method comes down to how much downtime the business will accept and how much preparation the team can carry. Dump and restore with pg_dumpall is the simplest method to reason about, but the database is unavailable for writes while the dump is taken, loaded and verified, so the window grows with data size. pg_upgrade converts the existing data directory in place and avoids copying most of the data, but the cluster must still be stopped for the upgrade run. Logical replication moves the cutover itself into a short window. The PostgreSQL upgrade documentation states that logical replication works between major versions, allowing major upgrades with only several seconds of downtime, which is why it suits systems that cannot take a planned outage.

The target release also shapes the decision. The PostgreSQL 18 release announcement notes that pg_upgrade retains planner statistics across major upgrades and adds parallel jobs and a --swap option. Those improvements make pg_upgrade more attractive where a short maintenance window is acceptable and the data can be checked in place. They do not change the trade-off for a system that must stay writable during the move. Logical replication requires the source to run with wal_level set to logical, a publication on the source, and a subscription on the target that copies the initial table data and then streams changes. Confirm with application owners whether each replicated table has a primary key or another replica identity, because updates and deletes depend on it.

The trade-off is that the team takes on validation work that dump and restore or pg_upgrade largely handle for them. Logical replication copies rows through a logical stream, so the team must confirm that the subscriber matches the publisher before cutover. Practical checks include row counts and checksums per replicated table, comparison of indexes, constraints and column types on both sides, and confirmation that the publication covers every table the application writes. Sequences are a specific risk. Standard logical replication does not carry sequence values in the versions covered by this page, and schema changes are not replicated either. Each sequence must be set on the new primary at cutover to a value above the highest key in use, and the schema freeze must be enforced in the change process. Confirm sequence and DDL handling against the documentation for your target version before writing the cutover script.

Managing the window before cutover is an operational task that runs for the whole synchronization period. The publisher holds a replication slot, and while the subscriber lags or is stopped, the publisher retains WAL that the slot still needs, so disk use on the source can grow quickly. Set alerts on slot lag and on free disk space before the subscription starts. Decide in advance who may run schema changes during the sync period. Rehearse the cutover on a copy: stop writes to the old primary, wait until the subscriber has caught up, set sequences, repoint the application and any connection pooler, and verify. Keep the old cluster available so it can be restarted if the new primary fails, and agree in advance the point after which rolling back would lose data.

Start the method decision with the version calendar. The PostgreSQL versioning policy lists November 12, 2026 as the end of support for version 14, so a team still running version 14 has a fixed deadline to plan around. Confirm the current date and the support end date for your version against the policy before scheduling. When you ask a partner for a quote, request a written scope that covers the replication design, validation scripts, sequence procedure, cutover runbook, rollback criteria and rehearsal plan, and identify any post-cutover support separately. Effort depends on data volume, number of tables, write rate and how many applications share the database, so ask how each of these was assumed in the quote.

Go-live starts the work that keeps a logical replication subscription healthy. The platform team needs to watch three things continuously: how far the subscriber trails the publisher, whether the apply workers are erroring, and whether the replication slot on the publisher is still being consumed. On the publisher, the pg_replication_slots view shows each logical slot, whether it is active, and how far it has confirmed. The pg_stat_replication view shows the walsender connection and its lag positions. On the subscriber, pg_stat_subscription shows worker state and the last received and reported positions, and pg_stat_subscription_stats counts apply errors. Confirm the exact columns and their meaning against the documentation for your PostgreSQL version before you build dashboards on them.

The replication slot is the part of this work that most often causes an outage. A logical slot tells the publisher to keep WAL until the subscriber confirms it has consumed that WAL. If the subscriber is offline, paused, or has a failed apply worker, the slot stays inactive while WAL keeps accumulating on the publisher's disk. A forgotten slot from a decommissioned subscriber can fill that volume and stop the primary. The max_slot_wal_keep_size setting caps how much WAL a slot may hold, but a slot that exceeds the cap is invalidated and the subscriber must be resynchronized. Decide in advance which outcome you prefer. Size the WAL volume from measured write rates and the longest outage you are willing to tolerate, and ask your team to confirm both numbers before go-live.

Set alerts and a runbook before the subscription is switched on, not after the first incident. At minimum, alert on free space on the volume holding WAL, on any inactive logical slot, on growth in confirmed lag over a set window, on apply errors or a disabled subscription, and on an invalidated slot. Each alert should name an owner and a first action. The runbook should cover how to identify the slot and its subscriber, how to decide between restoring the subscriber and abandoning it, how to resolve an apply conflict or skip a failing transaction using the version-specific options in the subscription documentation, and how to drop an abandoned subscription. When a publisher is unreachable, the subscriber must first disassociate the slot before the subscription can be dropped, so rehearse that sequence on a non-production pair.

WAL held for a logical slot is separate from the WAL your continuous archiving retains for point-in-time recovery, and the two plans must agree. Archiving keeps segments for restores, while a slot keeps segments for consumers, so a recovery drill can behave differently from production when slots are involved. After a restore, replication slots may not exist on the restored publisher, and subscribers will need to be re-pointed or resynchronized. Review the continuous archiving documentation with your backup owner, check that archive retention covers your recovery targets, and test a restore of the publisher with the subscription configuration in place. Record which team owns the slot runbook and which owns the restore runbook, so neither is written in isolation.