Systems and protocols · Intermediate

PostgreSQL logical replication: subscribing to row changes

Trace publication and subscription behavior while identifying schema, replica-identity and retained-WAL responsibilities.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Replicate selected logical changes
  2. Identify a changed row
  3. Plan schema compatibility separately
  4. Monitor lag and retained history
  5. Ask Logeia about this articleCheck your understanding

Replicate selected logical changes

PostgreSQL logical replication uses publications to define data changes offered by a publisher and subscriptions to receive them. Unlike physical replication’s block-oriented representation, logical replication works with data objects and changes identified at the row level. It is not automatically a complete clone of every database object. Inspect which tables and operations are included by the configuration. Initial data synchronization and ongoing change application are different phases, and a migration must account for both. A connected subscription alone does not prove that the initial copy is finished or that later changes have caught up.

Identify a changed row

Suppose an orders row with order ID 42 receives a new amount. The subscriber needs to identify the row to update, not merely receive the new numeric value. Replica identity supplies the necessary identification, commonly through a primary key. Insufficient identity information causes problems for replicated updates and deletes. A design must therefore consider which before/after information an operation requires. Independent writes on the subscriber can also introduce key conflicts or mismatched rows. Define ownership and reconciliation policy instead of assuming that replication makes arbitrary writes on both sides safe.

Plan schema compatibility separately

Ordinary logical replication does not automatically propagate every DDL change or sequence state as if it were a complete database image. Adding a column requires planning compatible schemas and change ordering so the subscriber can apply the incoming data. Table existence, column compatibility and privileges are separate prerequisites. Features and restrictions also vary by version, so consult the documentation for the deployed releases. One successful replicated insert cannot establish that every later schema migration is safe. Rehearse the specific transition and inspect application of changes after the schema boundary, not just before it.

Monitor lag and retained history

A subscriber that applies changes slowly can serve stale results. Replication slots may retain WAL needed for later delivery, so a stalled or abandoned subscription can also increase publisher disk usage. Monitor receive/apply progress and retained history rather than only connection status. Logical replication is not a complete replacement for backups or every step of automated failover. Design stopping, restarting, resynchronizing and retiring subscriptions as well as initial setup. Operational cleanup matters because a dataflow that is no longer useful can still hold resources needed by the rest of the system.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path