Initial Table Synchronization, Ongoing Apply, Conflict Classes, and Monitoring

Learning outcomes

The subscription exists, but “subscription exists” is not the same as “all tables are synchronized and apply is healthy.” Initial copy uses dedicated table-synchronization workers, ongoing apply uses a leader apply worker plus optional parallel apply workers, and conflicts can stop progress even while the subscription catalog row remains present.

This lesson turns those states into observable evidence. It also creates a controlled uniqueness conflict on the subscriber, diagnoses it through PostgreSQL 18 logs and pg_stat_subscription_stats, repairs the target data, and proves that queued source changes catch up without destroying and recreating the subscription.

1. Initial synchronization is snapshot + catch-up

When copy_data=true, PostgreSQL does not simply run one giant copy and then begin streaming. Each table synchronization worker takes a snapshot, copies existing table data through its own temporary synchronization machinery, then streams changes that occurred during that copy until the table reaches the leader apply worker's position. Only then does the main apply process take over the table.

SELECT s.subname,       r.srrelid::regclass AS relation,       r.srsubstate,       CASE r.srsubstate         WHEN 'i' THEN 'initialize'         WHEN 'd' THEN 'copying data'         WHEN 'f' THEN 'finished table copy'         WHEN 's' THEN 'synchronized with leader'         WHEN 'r' THEN 'ready / normal replication'       END AS state_meaning,       r.srsublsnFROM pg_subscription_rel AS rJOIN pg_subscription AS s ON s.oid = r.srsubidWHERE s.subname = 'ch15_servicehub_sub';

On a tiny lab table you may only observe r because synchronization completes quickly. That does not mean the intermediate states do not exist. Use a larger disposable table if you want to observe table-synchronization workers in real time.

SELECT subname, worker_type, pid, leader_pid,       relid::regclass AS relation,       received_lsn, latest_end_lsn,       last_msg_receipt_timeFROM pg_stat_subscriptionWHERE subname = 'ch15_servicehub_sub'ORDER BY worker_type, pid;

2. Source slot and subscriber worker evidence answer different questions

pg_stat_subscription is subscriber-side process/progress evidence. pg_replication_slots on the publisher shows WAL-retention and decoding progress. Healthy logical replication requires both sides to make sense together.

SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,       wal_status, safe_wal_size, inactive_since,       pg_size_pretty(         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)       ) AS retained_from_restartFROM pg_replication_slotsWHERE slot_name = 'ch15_servicehub_sub';

The exact slot name defaults to the subscription name unless you configured another one. confirmed_flush_lsn is logical-consumer progress; restart_lsn is the oldest WAL still potentially required. Do not delete pg_wal files manually to “fix” a retaining logical slot.

3. Create a controlled insert_exists conflict

Logical replication is safest when the subscriber is not independently writing to the same key space. To make that risk concrete, intentionally create a local target row whose primary key will later arrive from the publisher.

INSERT INTO app.ch15_work_orders(work_order_id, customer_id, region, status, labor_minutes, changed_at)VALUES(15100, 9001, 'local', 'subscriber_only', 0, clock_timestamp());SELECT * FROM app.ch15_work_ordersWHERE work_order_id = 15100;

INSERT INTO app.ch15_work_orders(work_order_id, customer_id, region, status, labor_minutes, changed_at)VALUES(15100, 5100, 'east', 'queued', 0, clock_timestamp());

The subscriber apply worker cannot insert the source row because the local primary key already exists. PostgreSQL 18 classifies this as insert_exists. The apply transaction errors and replication cannot move past that transaction until the error is resolved or deliberately skipped.

4. Diagnose before repairing

SELECT subname,       apply_error_count,       sync_error_count,       confl_insert_exists,       confl_update_exists,       confl_update_missing,       confl_delete_missing,       confl_multiple_unique_conflicts,       stats_resetFROM pg_stat_subscription_statsWHERE subname = 'ch15_servicehub_sub';

Read the subscriber server log as well. PostgreSQL 18 logs the relation, conflict class, key, existing local row and remote row where privileges permit. If track_commit_timestamp is enabled, origin/commit detail can be richer. The log is the authoritative evidence for the failing transaction; the statistics view is cumulative telemetry.

SELECT work_order_id, customer_id, region, statusFROM app.ch15_work_ordersWHERE work_order_id = 15100;SELECT subname, worker_type, pid, received_lsn, latest_end_lsnFROM pg_stat_subscriptionWHERE subname = 'ch15_servicehub_sub';

5. Repair subscriber state and prove catch-up

For this lab, the source is authoritative and the local subscriber row is disposable. Disable the subscription so the apply worker stops retrying while you repair the target. Then remove the conflicting local row and re-enable the subscription.

ALTER SUBSCRIPTION ch15_servicehub_sub DISABLE;DELETE FROM app.ch15_work_ordersWHERE work_order_id = 15100  AND status = 'subscriber_only';ALTER SUBSCRIPTION ch15_servicehub_sub ENABLE;

SELECT work_order_id, customer_id, region, statusFROM app.ch15_work_ordersWHERE work_order_id = 15100;SELECT *FROM pg_stat_subscription_statsWHERE subname = 'ch15_servicehub_sub';

The repaired row should now contain the publisher values. The historical conflict counters do not automatically return to zero; they are evidence that a failure occurred. Reset them only when your monitoring process intentionally establishes a new baseline.

6. Skipping is a data-loss decision, not a retry mechanism

PostgreSQL also supports ALTER SUBSCRIPTION ... SKIP for a known remote transaction finish LSN. That skips the whole source transaction, including non-conflicting changes in the same transaction. Use it only after you can prove the resulting subscriber state is acceptable. In many incidents the safer response is to repair target data/permissions/schema so the source transaction can apply normally.

7. Monitoring checklist

SELECT now() AS observed_at,       s.subname, s.worker_type, s.pid,       s.received_lsn, s.latest_end_lsn,       s.last_msg_receipt_timeFROM pg_stat_subscription AS sWHERE s.subname = 'ch15_servicehub_sub';SELECT subname, apply_error_count, sync_error_count,       confl_insert_exists, confl_update_exists,       confl_update_missing, confl_delete_missingFROM pg_stat_subscription_statsWHERE subname = 'ch15_servicehub_sub';

Alert on sustained absence of an expected apply worker, growing source-slot retention, repeated apply/sync errors, and business-state divergence. One “last message time” or one slot LSN is not enough to diagnose logical replication on its own.

8. Conflict triage as an operational decision tree

When apply stops, first classify the failure rather than immediately changing replication state. A uniqueness violation, target permission failure, row-level-security conflict, missing column, and unavailable target relation can all stop apply, but they require different repairs. Preserve the first useful server-log entry, identify the relation and remote transaction context, then compare the local target row or schema with the source contract.

1. Data conflict: decide whether source or local target state is authoritative, then reconcile explicitly.
2. Schema conflict: restore subscriber compatibility before allowing the original source transaction to retry.
3. Privilege/RLS conflict: repair target ownership/permissions/policy; do not grant superuser merely to silence replication.
4. Capacity/worker failure: inspect subscriber worker limits and publisher slot retention before restarting services.

Keep the subscription disabled only as long as diagnosis requires. While it is disabled, the publisher's logical slot can continue retaining WAL, so a paused incident is also a storage-capacity incident unless retention is monitored.

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 Architecture
• PostgreSQL 18 — Logical Replication Conflicts
• PostgreSQL 18 — pg_stat_subscription / pg_stat_subscription_stats
• PostgreSQL 18 — pg_subscription_rel
• PostgreSQL 18 — ALTER SUBSCRIPTION