Logical Decoding Mental Model, Replication Identity, Publications, and Subscriptions

Learning outcomes

ServiceHub now needs to move a selected operational dataset into another PostgreSQL database without copying an entire physical cluster. The publisher may remain writable while a subscriber receives changes, and the two databases may even run different PostgreSQL major versions during a migration. This is the problem space for logical replication.

Logical replication is not “SQL statement replay.” PostgreSQL reads Write-Ahead Log (WAL), logically decodes row changes, filters them through a publication, sends them through the streaming replication protocol, and applies them to same-named target tables on the subscriber. The lab uses two disposable PostgreSQL 18 clusters: publisher port 55439 and subscriber port 55440.

1. From WAL record to applied row

On the publisher, a WAL sender starts logical decoding. PostgreSQL's built-in logical replication uses the standard pgoutput output plugin. The plugin turns WAL-decoded changes into the logical replication protocol and applies publication rules such as table membership, row filters, column lists, and published operation types. The subscriber's leader apply worker receives those changes and applies them in transactional order.

SELECT name, setting, context, source, pending_restartFROM pg_settingsWHERE name IN (  'wal_level','max_wal_senders','max_replication_slots',  'max_logical_replication_workers','max_sync_workers_per_subscription',  'max_parallel_apply_workers_per_subscription','max_active_replication_origins')ORDER BY name;

The publisher requires wal_level=logical. It also needs enough WAL senders and replication slots for subscriptions and initial table-synchronization workers. On the subscriber, logical-replication workers compete for the global max_worker_processes budget. Capacity settings are limits, not targets to maximize.

2. Build the disposable publisher and subscriber

initdb -D ./ch15_pubinitdb -D ./ch15_subpg_ctl -D ./ch15_pub -o "-p 55439" -l ./ch15_pub.log startpg_ctl -D ./ch15_sub -o "-p 55440" -l ./ch15_sub.log start

ALTER SYSTEM SET wal_level = 'logical';ALTER SYSTEM SET max_wal_senders = '10';ALTER SYSTEM SET max_replication_slots = '10';

Restart the publisher after those settings if pending_restart is true. Do not change the long-lived ServiceHub lab cluster for this exercise; the chapter deliberately uses separate data directories.

CREATE DATABASE servicehub_logical_pub;\c servicehub_logical_pubCREATE ROLE ch15_pub_owner NOLOGIN;CREATE ROLE ch15_repl LOGIN REPLICATION;-- Set a disposable lab password interactively:\password ch15_replCREATE SCHEMA app AUTHORIZATION ch15_pub_owner;CREATE TABLE app.ch15_work_orders (  work_order_id bigint PRIMARY KEY,  customer_id   bigint NOT NULL,  region        text NOT NULL,  status        text NOT NULL,  labor_minutes integer NOT NULL CHECK (labor_minutes >= 0),  changed_at    timestamptz NOT NULL DEFAULT clock_timestamp());ALTER TABLE app.ch15_work_orders OWNER TO ch15_pub_owner;INSERT INTO app.ch15_work_ordersVALUES  (15001, 501, 'north', 'queued',      0, clock_timestamp()),  (15002, 502, 'south', 'assigned',   15, clock_timestamp()),  (15003, 503, 'north', 'in_progress',40, clock_timestamp());GRANT CONNECT ON DATABASE servicehub_logical_pub TO ch15_repl;GRANT USAGE ON SCHEMA app TO ch15_repl;GRANT SELECT ON app.ch15_work_orders TO ch15_repl;

The replication connection role needs LOGIN and REPLICATION, a matching pg_hba.conf rule, and SELECT on published tables for initial copy. In a real environment, use SCRAM, certificates, or another approved secret-distribution mechanism; never commit a subscription connection password to source control.

3. Replica identity answers “which target row?”

For inserts, PostgreSQL only needs the new row. For updates and deletes, the subscriber must identify which existing target row corresponds to the source change. By default the primary key is the replica identity. A qualifying unique index can be designated instead. REPLICA IDENTITY FULL sends the old row as identity, but it can be much more expensive and should be a fallback, not the default design.

CREATE TABLE app.ch15_identity_bad (  note text,  status text);CREATE PUBLICATION ch15_bad_pubFOR TABLE app.ch15_identity_bad;INSERT INTO app.ch15_identity_bad VALUES ('x','new');-- Expected to fail because the publication publishes UPDATE by default:UPDATE app.ch15_identity_badSET status = 'done'WHERE note = 'x';

The publisher rejects that update because the table has no replica identity while its publication includes updates. Repair the model with a durable business key or primary key rather than hiding the problem with FULL unless there is a deliberate reason.

ALTER TABLE app.ch15_identity_bad  ADD COLUMN identity_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;UPDATE app.ch15_identity_badSET status = 'done'WHERE note = 'x';DROP PUBLICATION ch15_bad_pub;DROP TABLE app.ch15_identity_bad;

4. Create publication and target schema

CREATE PUBLICATION ch15_servicehub_pubFOR TABLE app.ch15_work_orders;SELECT p.pubname, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncateFROM pg_publication AS pWHERE p.pubname = 'ch15_servicehub_pub';SELECT pubname, schemaname, tablenameFROM pg_publication_tablesWHERE pubname = 'ch15_servicehub_pub';

A publication does not copy schema definitions. The subscriber must already contain the target table with the same fully-qualified name. Column order can differ, but column names and convertible data types must line up with what is published.

CREATE DATABASE servicehub_logical_sub;\c servicehub_logical_subCREATE ROLE ch15_sub_owner LOGIN;GRANT pg_create_subscription TO ch15_sub_owner;GRANT CREATE ON DATABASE servicehub_logical_sub TO ch15_sub_owner;CREATE SCHEMA app AUTHORIZATION ch15_sub_owner;SET ROLE ch15_sub_owner;CREATE TABLE app.ch15_work_orders (  work_order_id bigint PRIMARY KEY,  customer_id   bigint NOT NULL,  region        text NOT NULL,  status        text NOT NULL,  labor_minutes integer NOT NULL CHECK (labor_minutes >= 0),  changed_at    timestamptz NOT NULL);RESET ROLE;

PostgreSQL 18 allows a role with membership in pg_create_subscription plus database CREATE privilege to create a subscription. Apply privileges remain a separate concern. In this lab the subscription owner also owns the target table, avoiding an unnecessary superuser apply path.

5. Create the subscription and observe the slot

SET ROLE ch15_sub_owner;CREATE SUBSCRIPTION ch15_servicehub_subCONNECTION 'host=127.0.0.1 port=55439 dbname=servicehub_logical_pub user=ch15_repl password=<LAB_PASSWORD> application_name=ch15_servicehub_sub'PUBLICATION ch15_servicehub_pubWITH (copy_data = true, streaming = parallel);RESET ROLE;

The streaming default is parallel in PostgreSQL 18, but the lab states it explicitly. Initial table synchronization takes a source snapshot, copies existing rows, then catches that table up to the leader apply worker before handing it to normal ongoing replication.

SELECT subname, worker_type, pid, relid::regclass,       received_lsn, latest_end_lsn,       last_msg_receipt_time, latest_end_timeFROM pg_stat_subscriptionWHERE subname = 'ch15_servicehub_sub'ORDER BY worker_type, relid;SELECT s.subname, r.srrelid::regclass AS relation,       r.srsubstate, r.srsublsnFROM pg_subscription_rel AS rJOIN pg_subscription AS s ON s.oid = r.srsubidWHERE s.subname = 'ch15_servicehub_sub';

SELECT slot_name, slot_type, plugin, database, active,       restart_lsn, confirmed_flush_lsn, wal_status,       safe_wal_size, inactive_since, invalidation_reasonFROM pg_replication_slotsWHERE slot_type = 'logical';

The subscription's main logical slot retains WAL until decoded changes are confirmed. A disabled, broken, or abandoned subscription can therefore become a disk-capacity problem on the publisher. Slot lifecycle belongs in every logical-replication runbook.

6. Prove data movement and origin tracking

UPDATE app.ch15_work_ordersSET status = 'completed',    labor_minutes = 62,    changed_at = clock_timestamp()WHERE work_order_id = 15002;INSERT INTO app.ch15_work_ordersVALUES (15004, 504, 'west', 'queued', 0, clock_timestamp());

SELECT work_order_id, customer_id, region, status, labor_minutesFROM app.ch15_work_ordersORDER BY work_order_id;SELECT external_id, remote_lsn, local_lsnFROM pg_replication_origin_statusORDER BY external_id;

Replication-origin progress prevents a logical consumer from repeatedly applying the same remote progress point and supports more advanced replication-loop controls. Do not treat origin metadata as an application audit trail; it is replication progress state.

7. Verification and cleanup

For production acceptance, verify data, worker health, slot retention, permissions, logs, and schema compatibility. For this disposable chapter lab, keep the topology for Lessons 2–5. If you need to remove it, drop the subscription before deleting publisher slots so PostgreSQL can clean up its remote resources normally.

ALTER SUBSCRIPTION ch15_servicehub_sub DISABLE;DROP SUBSCRIPTION ch15_servicehub_sub;DROP DATABASE servicehub_logical_sub WITH (FORCE);

Authoritative references

Logical replication is version-, privilege-, topology-, and schema-sensitive. These PostgreSQL 18 primary sources define the behavior used in this lesson.

• PostgreSQL 18 — Logical Replication
• PostgreSQL 18 — Publication and Replica Identity
• PostgreSQL 18 — Subscription
• PostgreSQL 18 — Logical Replication Security
• PostgreSQL 18 — Logical Replication Architecture