Controlling Initial Table Copy with copy_data

The copy_data subscription option decides whether a new subscription runs an initial table COPY or starts streaming immediately from the slot’s position, and that one boolean is the difference between a subscriber that begins byte-consistent with the publisher and one that silently serves a table missing every row committed before it attached. Get it wrong and there is no error: the affected relations sit permanently empty or half-populated while every other table streams cleanly, so analytics and replicas diverge with nothing in the logs to explain why.

This page isolates the operational behavior of copy_data on both CREATE SUBSCRIPTION and ALTER SUBSCRIPTION … REFRESH PUBLICATION, how it drives the tablesync workers and the pg_subscription_rel.srsubstate machine, the LSN handoff from the initial COPY to streaming, and exactly when skipping the COPY is safe. It builds directly on the subscription sync procedures that drive a subscription to steady state; here the focus is the single option that decides where the baseline comes from. All behavior is validated against PostgreSQL 14 through 17.

Copy-and-Stream Semantics #

copy_data controls only the baseline half of subscription synchronization, not the streaming half. With true (the default) the apply worker exports a consistent snapshot and dispatches one tablesync worker per relation to COPY the rows as of that snapshot; each worker records the LSN it finished at in pg_subscription_rel.srsublsn, catches up on any WAL that arrived during the copy, and hands the relation to the main apply worker at srsubstate = 'r'. With false there is no snapshot, no tablesync COPY, and no per-table catch-up — the relation is marked ready and the subscription streams from the slot’s confirmed_flush_lsn onward, trusting that a byte-identical baseline is already present.

The reason false is dangerous is the LSN alignment it silently assumes. A subscription that skips the COPY begins applying at the slot’s confirmed_flush_lsn, so the only correct external baseline is one taken at the exact point the replication slot reached consistency — the snapshot the slot exported when it was created. Load a pg_dump taken a minute earlier and any row changed in that minute is a phantom: an UPDATE streamed for a row your baseline never held becomes a no-op the apply worker cannot locate, and rows committed and never re-touched after the dump simply never appear. There is no automatic reconciliation, which is why copy_data = false belongs only to a deliberately orchestrated snapshot handoff, never to a routine subscription.

Diagnostic Patterns #

Because a mis-set copy_data produces no error, the diagnostic is a row-level and state-level comparison, not a log scan. First confirm every relation actually reached the streaming state rather than being marked ready without data:

-- Subscriber. srsublsn is the LSN the COPY finished at; NULL under copy_data=false.
SELECT sr.srrelid::regclass AS table_name, sr.srsubstate, sr.srsublsn
FROM pg_subscription_rel sr
JOIN pg_subscription s ON s.oid = sr.srsubid
WHERE s.subname = 'orders_sub'
ORDER BY (sr.srsubstate = 'r'), table_name;

A relation at r with a NULL srsublsn was brought up without a tablesync COPY — expected under copy_data = false, but a red flag if you believed you ran a full copy. The definitive check is a row-count reconciliation against the publisher for any table you suspect skipped its baseline:

-- Run on both ends. A subscriber count far below the publisher on a freshly
-- created subscription is the signature of an unintended copy_data=false.
SELECT 'orders'::text AS table_name, count(*) FROM public.orders;

Threshold guidance: any newly created subscription where a relation’s subscriber row count sits below the publisher’s by more than the change rate could explain since attach is a missing-baseline incident, not lag — re-seed that table rather than waiting for it to converge, because it never will. While the initial COPY is in flight under copy_data = true, watch publisher slot retention, since the slot cannot advance restart_lsn until every table finishes copying:

-- Publisher. During a large initial COPY, retained WAL climbs until the slot
-- can advance. Alert if it approaches max_slot_wal_keep_size.
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name = 'orders_sub';

Treat retained_wal climbing past 1 GB during a copy, or a relation stuck below srsubstate = 'r' for longer than its size warrants (benchmark roughly 1 GB/min on commodity storage), as a paging condition.

Safe Deployment Sequence #

Skipping the COPY is only correct when the baseline was captured at the slot’s consistent point. Sequence it so the snapshot LSN and the dump are provably aligned, with a re-seed always one step away.

1. Create the slot and export its snapshot. Over the replication protocol, CREATE_REPLICATION_SLOT … LOGICAL pgoutput EXPORT_SNAPSHOT returns both the slot’s consistent_point LSN and a snapshot name — the two facts that make copy_data = false safe. Provisioning and recovering this cursor is covered in initializing replication slots.

2. Dump the baseline against that exact snapshot. Pin pg_dump to the exported snapshot so the dump is the byte-for-byte state at the slot’s consistent_point.

# The snapshot name comes from the CREATE_REPLICATION_SLOT response in step 1.
pg_dump "host=pub.internal dbname=app user=repl" \
  --snapshot='00000003-0000001B-1' \
  --no-publications --no-subscriptions \
  -t public.orders -t public.order_items \
  | psql "host=sub.internal dbname=app"

3. Attach the subscription without a second copy. Point it at the pre-created slot and skip the COPY the dump already performed.

-- Subscriber. Baseline is already loaded at the slot's consistent point.
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=pub.internal port=5432 dbname=app user=repl sslmode=verify-full'
  PUBLICATION orders_pub
  WITH (copy_data = false, create_slot = false, slot_name = 'orders_sub');

4. Verify convergence. Confirm every relation is at r and reconcile row counts against the publisher before declaring the subscription trustworthy.

5. Keep the re-seed ready. If any table diverges, force a clean baseline for just that relation — truncate locally and refresh with the COPY re-enabled, which re-COPIES only tables the apply worker does not already see as ready.

-- Repair one divergent table without disturbing the rest.
TRUNCATE public.orders;
ALTER SUBSCRIPTION orders_sub REFRESH PUBLICATION WITH (copy_data = true);

Because the reload path is non-disruptive, the cost of a wrong copy_data = false is bounded by how fast the row-count check in step 4 catches it — which is the argument for making that check mandatory in the runbook, not optional.

Pipeline Integration #

An orchestration layer should never treat copy_data as a free choice; it should treat false as a contract that a matching snapshot exists. When a Python controller drives the cutover, keep the slot’s consistent_point and the dump snapshot name in the same state record so the two can never drift apart, and reconcile counts before marking the subscription healthy.

# Gate a copy_data=false attach on a proven row-count match; re-seed otherwise.
import psycopg2

def verify_or_reseed(sub_dsn: str, pub_dsn: str, subname: str, table: str) -> None:
    with psycopg2.connect(pub_dsn) as p, psycopg2.connect(sub_dsn) as s:
        p.autocommit = s.autocommit = True
        with p.cursor() as pc, s.cursor() as sc:
            pc.execute(f"SELECT count(*) FROM {table}")
            sc.execute(f"SELECT count(*) FROM {table}")
            pub_n, sub_n = pc.fetchone()[0], sc.fetchone()[0]
        if sub_n < pub_n:                      # baseline never landed
            with s.cursor() as sc:
                sc.execute(f"TRUNCATE {table}")
                sc.execute(
                    f"ALTER SUBSCRIPTION {subname} "
                    "REFRESH PUBLICATION WITH (copy_data = true)"
                )

Idempotent apply is the second defense. Even a correctly aligned copy_data = false handoff can double-deliver a row that was committed at the very edge of the snapshot boundary, so downstream writes should use INSERT … ON CONFLICT DO UPDATE (or PG 15+ MERGE) keyed on a deterministic primary key — the same discipline that lets a Debezium connector snapshot mode and a native subscription share a slot without diverging. Export the count of relations at r with a NULL srsublsn and the per-table publisher-versus-subscriber delta to Prometheus, and alert when either indicates a table came up without its baseline, using the dashboards in asynchronous monitoring integration.

Failover sharpens the same rule. After a promotion, a subscription re-created with copy_data = false against the new primary assumes the promoted node’s WAL position aligns with the old baseline — an assumption that rarely holds once the timeline forks. Treat any post-failover re-attach as copy_data = true unless you have re-exported a snapshot from the new primary, and record that branch in the runbook so a skipped COPY never silently ships stale rows to analytics.

Authoritative references #

• PostgreSQL: CREATE SUBSCRIPTION — the copy_data parameter and its interaction with create_slot and slot_name.
• PostgreSQL: ALTER SUBSCRIPTION — REFRESH PUBLICATION WITH (copy_data = …) and which relations it re-copies.
• PostgreSQL: pg_subscription_rel — the srsubstate machine and the srsublsn copy-completion LSN.

Related #

• Subscription sync procedures — the parent workflow that drives tablesync workers and the srsubstate machine to steady state.
• Resolving subscription initialization failures — catalog-level triage when a sync stalls before reaching ready.
• Initializing replication slots — provision the slot and export the consistent-point snapshot a copy_data = false attach depends on.