PostgreSQL Logical Replication: A Practical Guide

Teams often need a copy of some PostgreSQL data somewhere else: a reporting database that should not slow down production, a separate service that needs a few tables, or a newer PostgreSQL version being prepared for an upgrade. Exporting and importing on a schedule works, but the copy is always out of date and the jobs are fragile. PostgreSQL logical replication offers a built-in alternative: it streams row changes from chosen tables on one server to another, continuously and in order. It is powerful, but it has clear limits on what it copies and how it handles conflicts. This guide explains how publications and subscriptions work, the initial copy, what is not replicated and how to plan schema changes and monitoring.

How PostgreSQL logical replication works

The PostgreSQL documentation describes logical replication as a method of replicating data objects and their changes based on their replication identity, usually a primary key, in contrast to physical replication, which uses exact block addresses and byte-by-byte replication (PostgreSQL: Logical replication). It uses a publish and subscribe model: one or more subscribers subscribe to publications on a publisher node and pull data from them.

A publication is a set of changes generated from a table or group of tables. The documentation describes it as a change set or replication set, defined on the publisher. A subscription, defined on the subscriber, connects to the publisher and names the publications it wants. Because replication is defined per table, you can copy only the tables you need rather than a whole server.

The initial copy and ongoing changes

When logical replication of a table starts, PostgreSQL typically takes a snapshot of the table's data on the publisher and copies it to the subscriber. Once that is complete, changes made on the publisher since the initial copy are sent continually. The documentation explains that the subscriber applies the data in the same order as the publisher, so that transactional consistency is guaranteed for publications within a single subscription.

For large tables, the initial copy can take time and add load to the publisher. Plan it for a quiet period, monitor progress and confirm row counts on both sides once it completes, before any reports or services start relying on the subscriber.

Typical uses

The documentation lists typical use cases, including:

• Sending incremental changes in a database, or a subset of it, to subscribers as they occur
• Consolidating multiple databases into one, for example for analytics
• Replicating between different major versions of PostgreSQL
• Replicating between instances on different platforms
• Giving different groups of users access to replicated data
• Sharing a subset of a database between multiple databases

For streaming changes into systems other than PostgreSQL, such as a message broker or a SaaS application, change data capture tools built on PostgreSQL's logical decoding are usually a better fit. Our guide to change data capture covers that approach.

Set replica identity for updates and deletes

The documentation states that a published table must have a replica identity configured to replicate UPDATE and DELETE operations, so the subscriber can find the rows to change. By default, this is the primary key. Another suitable unique index can be used, and as a last resort the whole row can serve as the identity. Check every table you plan to publish has a primary key; tables without one need attention before replication will handle their updates and deletes.

Know what is not replicated

The documentation lists restrictions that shape any design:

• The database schema and DDL commands are not replicated; the initial schema can be copied with pg_dump using its schema-only option, and later schema changes must be kept in sync manually
• Sequence data is not replicated; serial and identity column values arrive with the rows, but the subscriber's sequences stay at their start values
• Large objects are not replicated
• Replication is only supported for tables, including partitioned tables
• Truncate is supported, but needs care with tables linked by foreign keys

Each restriction implies a task. For example, if a subscriber may one day become the primary, plan how to set its sequences to safe values first (PostgreSQL: Logical replication restrictions).

Plan schema changes

Because schema changes are not replicated, they need a procedure. The restrictions page explains that if the publisher's schema changes and replicated data no longer fits the subscriber's table, replication will error until the subscriber's schema is updated, and that in many cases intermittent errors can be avoided by applying additive schema changes to the subscriber first. A practical order for adding a column: add it on the subscriber, then on the publisher, then start writing to it.

Avoid and handle conflicts

The documentation notes that when a subscriber is treated as read-only by applications, a single subscription produces no conflicts, but other writes to the same tables can cause them. Its section on conflicts explains that if incoming data violates a constraint, replication stops, and that updates or deletes for missing rows are skipped and logged (PostgreSQL: Logical replication conflicts). The simplest rule is to keep replicated tables read-only on the subscriber. If you need two-way flows, design conflict handling deliberately rather than relying on defaults.

Worked example: a hypothetical replication plan

The table below is a hypothetical plan for replicating selected tables from a production database to a reporting server. It illustrates the checks involved, not tested settings.

ItemDecisionReasonTables publishedOrders, order lines, customers, productsNeeded for reportsTables excludedSessions, audit logsNot needed; high volumeReplica identityPrimary key on all published tablesRequired for updates and deletesInitial copyOff-peak, monitoredLarge orders tableSubscriber writesNone to replicated tablesAvoid conflictsSchema changesApply to subscriber first, then publisherAvoid replication errorsSequencesNot needed on reporting serverNever promoted to primaryMonitoringLag, subscription errors, conflict statisticsDetect stalls early

Monitor replication health

Watch replication lag, subscription status and errors, and the conflict statistics the documentation mentions in the pg_stat_subscription_stats view. A stopped subscription is easy to miss: the reporting database keeps answering queries with increasingly old data. Also watch storage on the publisher, because replication slots retain the write-ahead log a subscriber still needs. Our guide to PostgreSQL read replicas compares physical standbys, which suit full read copies, with logical replication's table-level approach.

Frequently Asked Questions

What is PostgreSQL logical replication?

A built-in feature that streams row changes from published tables on one PostgreSQL server to subscribers, starting with an initial copy and then sending changes continually in order.

Does logical replication copy schema changes?

No. Schema and DDL commands are not replicated, so schema changes must be applied to both sides, usually additive changes on the subscriber first.

Can I write to tables on the subscriber?

You can, but other writes to replicated tables can cause conflicts. Keeping them read-only avoids conflicts from a single subscription.

How is it different from a read replica?

A physical read replica copies the whole server exactly. Logical replication copies chosen tables, can cross major versions and lets the subscriber hold other data too.

Replicate PostgreSQL data with confidence

We design and run PostgreSQL replication and sync setups, including schema change procedures and monitoring. See our real-time CRM and database synchronization service, or talk to us about PostgreSQL logical replication.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.