skip to content

Logical Decoding & Publications

PostgreSQL logical replication decodes WAL into row changes and ships chosen tables via publications and subscriptions. Interviewers probe REPLICA IDENTITY, what is not replicated and conflicts.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, how do you replicate three tables from an orders database to an analytics server using CREATE PUBLICATION and CREATE SUBSCRIPTION?

level: juniorimportance: must knowfreq 66%

answer

  1. two objects, two servers
  2. schema travels separately
  3. slot made on the publisher
  4. copy first, then catch up
  5. srsubstate ends at r

basics

~20 s

Set wal_level = logical on the orders server and publish the three tables there; create the same tables on the analytics server, then CREATE SUBSCRIPTION, which makes a slot, copies existing rows and streams every later change.

solid answer

~40 s

On the orders database set `wal_level = logical` (a restart), give a login role the `REPLICATION` attribute plus `SELECT` on the tables, allow it in `pg_hba.conf` under the database name, and run `CREATE PUBLICATION orders_to_analytics FOR TABLE sales.orders, sales.order_items, sales.customers`. Table definitions are not replicated, so create the same tables on the analytics server first, usually from `pg_dump --schema-only`. Then `CREATE SUBSCRIPTION analytics_sub CONNECTION '...' PUBLICATION orders_to_analytics` creates a logical slot on the publisher, copies each table's existing rows with table-sync workers (`copy_data` defaults to true), and hands each table to the apply worker once it has caught up. Every table reaching `srsubstate = 'r'` in `pg_subscription_rel` means the initial sync is done.

code

sql · 8 lines
sql
-- orders server, database shop (wal_level = logical already set)
CREATE ROLE analytics_repl WITH LOGIN REPLICATION;
GRANT USAGE ON SCHEMA sales TO analytics_repl;
GRANT SELECT ON sales.orders, sales.order_items, sales.customers
  TO analytics_repl;

CREATE PUBLICATION orders_to_analytics
  FOR TABLE sales.orders, sales.order_items, sales.customers;

go deeper

for a junior

Recall the two objects and where each lives: the publication on the orders server, the subscription on the analytics server. Remember that you create the tables yourself.

for a middle

Explain what CREATE SUBSCRIPTION actually starts: a slot on the publisher, table-sync workers doing the copy and catch-up, then the apply worker, with srsubstate showing the progress.

for a senior

Show the operational details: pg_hba matching by database name, REFRESH PUBLICATION for new tables, what a drop-and-recreate costs, and pg_createsubscriber when the initial copy is too slow.

for a principal

Weigh logical replication as the analytics feed against the alternatives on cost: a slot and decoding load on the source, schema changes made twice, and a separate copy to keep consistent.

## Two objects on two servers PostgreSQL's built-in logical replication has two user-facing objects. A **publication** (`CREATE PUBLICATION`) lives in one database on the source and names the tables whose row changes are sent. A **subscription** (`CREATE SUBSCRIPTION`) lives in a database on the target; it holds a connection string and one or more publication names, and it owns the workers that apply the changes. In the orders-to-analytics case the publisher is the `shop` database on the orders server and the subscriber is the `analytics` database on the reporting server. ## Prerequisites on the publisher | Item | What it needs | Why | |---|---|---| | `wal_level` | `logical` (the default is `replica`; a change needs a restart) | the WAL must carry enough to decode rows | | `max_replication_slots`, `max_wal_senders` | default 10 each; one slot per subscription plus spares for table sync | each subscription streams from its own slot | | replication role | `LOGIN` and `REPLICATION`, plus `SELECT` on the published tables | it runs the walsender and the initial copy | | `pg_hba.conf` | a line whose database field is `shop` (or `all`) | the `replication` keyword matches only physical replication connections | | publication owner | ownership of every table added | adding a table to a publication requires owning it | The publication itself is one statement. By default it publishes `insert, update, delete, truncate`. ## Preparing the subscriber - **Create the tables yourself.** Schema and DDL are never replicated, and `CREATE SUBSCRIPTION` fails if a published table is missing locally; `pg_dump --schema-only` with one `-t` per table, restored into `analytics`, is the usual way. - Tables are matched by **schema-qualified name** and columns **by name**. Column order may differ, a target column may have another type if the text form converts (for example `integer` to `bigint`), and extra local columns are filled from their defaults. - The target must be a regular or partitioned table; you cannot replicate into a view. - The role that runs `CREATE SUBSCRIPTION` needs the privileges of `pg_create_subscription` plus `CREATE` on the database, or superuser. - The analytics server is free to carry its own indexes and summary tables; only the subscribed tables receive changes. ## What CREATE SUBSCRIPTION does 1. It connects to the publisher and creates a **logical replication slot**, named after the subscription unless `slot_name` says otherwise, using the built-in `pgoutput` output plugin. Because it creates a slot, the command cannot run inside a transaction block. 2. With `copy_data = true` (the default) it starts **table synchronization workers**, at most `max_sync_workers_per_subscription` (default 2) at a time. Each takes its own short-lived slot, copies one table's current rows, then streams the changes made during its copy until it has caught up. 3. Each caught-up table is handed to the subscription's **apply worker**, which applies decoded changes in the publisher's commit order as they arrive. One detail surprises people: the initial copy ignores the publication's `publish` list. A publication that publishes only inserts still copies every existing row. ## Watching the initial sync - `pg_subscription_rel.srsubstate` per table: `i` initialize, `d` data being copied, `f` copy finished, `s` synchronized, `r` ready (normal replication). - `pg_stat_subscription` on the subscriber has one row per worker: the apply worker plus any sync or parallel apply workers, with `received_lsn` and `latest_end_lsn`. - On the publisher the subscription shows up as a walsender and as a row in `pg_replication_slots`; how long that slot holds WAL is a separate topic. ## Changing the set later - **New table:** create it on the subscriber, run `ALTER PUBLICATION ... ADD TABLE` on the publisher, then `ALTER SUBSCRIPTION ... REFRESH PUBLICATION` on the subscriber so it starts syncing that table. - **Drop and re-create** loses the sync state: the data has to be copied again, and `DROP SUBSCRIPTION` also drops the remote slot when the publisher is reachable. - Several subscriptions between the same pair of servers should not overlap in the tables they subscribe to, or the same changes arrive through both. ## Common first-time snags - **Workers run out.** On the subscriber, `max_logical_replication_workers` (default 4, a restart to change) is shared by apply, table-sync and parallel apply workers, so several subscriptions syncing many tables at once simply queue. - **Slots run out.** On the publisher, each subscription holds a slot and each table being synced briefly holds another; the default of 10 `max_replication_slots` goes quickly when several subscribers start together. - **Same-cluster subscriptions hang.** When publisher and subscriber databases live in one cluster, `CREATE SUBSCRIPTION` hangs if it tries to create the slot itself. Create the slot first with `pg_create_logical_replication_slot(..., 'pgoutput')` and subscribe with `create_slot = false`. - **Dumps skip subscriptions.** `pg_dump` includes subscriptions only when run by a superuser; otherwise it warns and leaves them out.

  • How do you add a fourth table, sales.refunds, to this running setup?
    Create `sales.refunds` on the analytics server, run `ALTER PUBLICATION orders_to_analytics ADD TABLE sales.refunds` on the orders server, then `ALTER SUBSCRIPTION analytics_sub REFRESH PUBLICATION` on the subscriber. The refresh starts a table-sync worker that copies the table's existing rows (its `copy_data` option defaults to true) before streaming. Without the refresh the subscriber never learns that the publication grew.
  • The orders database is 4 TB and the initial copy would take days. What does PostgreSQL 17 and later offer?
    `pg_createsubscriber` turns a stopped physical standby of the same major version into a logical subscriber. It creates a `FOR ALL TABLES` publication and a slot per database on the source, recovers the standby to that slot's LSN, promotes it, creates a subscription that copies nothing, and gives the server a new system identifier with `pg_resetwal`. The catch here: every table in the database comes along, not just three.
  • What goes wrong if you drop the subscription and create it again with the defaults?
    The sync state is lost, so `copy_data = true` copies every table again into tables that still hold the old rows. Tables with a primary key hit unique violations and the sync workers keep failing. Truncate the subscriber's tables first, or deliberately use `copy_data = false` only when you can prove the data is already consistent.

  • CREATE SUBSCRIPTION creates the tables on the analytics server automatically.
  • The replication keyword in pg_hba.conf covers the subscription's connection.
  • Adding a table to the publication is enough; the subscriber picks it up by itself.
  • Logical replication works with the default wal_level of replica.
  • A publication that publishes only inserts also copies only newly inserted rows.
open as a page

Why do UPDATEs on a PostgreSQL table without a primary key fail once it joins a publication, and which REPLICA IDENTITY setting fixes it?

level: middleimportance: must knowfreq 62%

basics

~20 s

Published UPDATEs and DELETEs need a replica identity; DEFAULT means the primary key, so without one the publisher rejects them with SQLSTATE 55000. Add a primary key, use USING INDEX on a qualifying unique index, or fall back to FULL.

open as a page

In a PostgreSQL publication, how do a row filter and a column list limit what reaches the subscriber, and what rules constrain each?

level: middleimportance: should knowfreq 36%

basics

~20 s

A row filter (WHERE after the table name) sends only matching rows; a column list sends only named columns. If updates or deletes are published, both must stay within the replica identity, or the publisher's next UPDATE or DELETE fails.

open as a page

A PostgreSQL 18 subscription stops applying changes and logs conflict=insert_exists; how do you diagnose the cause and resume replication safely?

level: seniorimportance: should knowfreq 42%

basics

~20 s

An incoming insert hit a unique key already present on the subscriber, so the apply worker errors and retries in a loop. Read the logged key and finish LSN, fix the local row or skip that transaction, then stop local writes.

open as a page

In PostgreSQL logical replication, which changes never reach the subscriber, and how do you roll out an ALTER TABLE ADD COLUMN without breaking replication?

level: seniorimportance: should knowfreq 52%

basics

~20 s

Only row changes travel: DDL, sequence state, large objects and views do not. Add a column on the subscriber first and the publisher second, because a subscriber may have extra columns but errors when a replicated column is missing.

open as a page

After updating PostgreSQL to 18.6, why does a wal2json change consumer fail with 'may not be used as an output plugin', and what fixes it?

level: middleimportance: nice to knowfreq 18%

basics

~10 s

PostgreSQL 18.6 added output_plugin_libraries, an allow-list that defaults to 'pgoutput, test_decoding'. Third-party plugins such as wal2json are refused until an administrator reviews them, adds them to the list and reloads the configuration.

open as a page