Thread

1. Per-table resync for logical replication subscriptions

   Cagri Biroglu <cagri.biroglu@adyen.com> — 2026-07-07T09:41:55Z

   Hi hackers,
   
   I would like to propose a subscriber-side way to re-copy a single table
   that is already part of a logical replication subscription.
   
   At the moment, I do not think PostgreSQL has a supported way to do this for
   one table on one subscriber. ALTER SUBSCRIPTION ... REFRESH PUBLICATION
   only handles tables that were added to the subscribed publications since
   the subscription was created, or since the last refresh. It does not
   re-copy tables that are already known to the subscription.
   
   That is fine for the normal “new table was added to the publication” case,
   but it leaves a gap when only one subscriber needs one table to be
   re-seeded. Some examples are:
   
      -
   
      the subscriber table became inconsistent;
      -
   
      a row filter changed and the subscriber needs a fresh copy;
      -
   
      a forwarder/subscriber needs to repair one table without disturbing
      other subscribers.
   
   The usual workaround is to temporarily change publication membership,
   refresh the subscription, truncate the local table, add the table back, and
   refresh again. That works in simple topologies, but it is not really local
   to the subscriber. In a fan-out topology it can affect sibling subscribers,
   and in a forwarding topology the truncate can be replicated further
   downstream if truncates are published.
   
   There is also the unsupported option of updating pg_subscription_rel by
   hand to put the table back into the initial sync state. That is not safe
   either, because it races with the apply worker and does not handle the
   surrounding cleanup and invalidation work.
   
   There is already a similar mechanism for sequences. ALTER SUBSCRIPTION ...
   REFRESH SEQUENCES can re-synchronize sequence data for sequences already
   known to the subscription, while REFRESH PUBLICATION only discovers newly
   added ones. This proposal is essentially the table-side equivalent of that
   idea.
   
   What I am proposing is something like:
   
   ALTER SUBSCRIPTION name REFRESH TABLE table_name
       [ WITH (copy_data = true, truncate = true) ];
   
   or, if people prefer the wording:
   
   ALTER SUBSCRIPTION name RESYNC TABLE table_name
       [ WITH (copy_data = true, truncate = true) ];
   
   The command would be subscriber-local and would not change publication
   membership. Internally, it would reuse the existing table synchronization
   machinery by resetting the selected relation back to the initial sync
   state, so that a tablesync worker copies it again and moves it through the
   normal i -> d -> s -> r states.
   
   I tested the current behavior on current devel with a small three-node
   setup:
   
      -
   
      upstream publisher U;
      -
   
      forwarding subscriber/publisher F;
      -
   
      downstream subscriber D;
      -
   
      sibling subscriber S.
   
   The test script keeps a writer running and prints per-table checksums at
   each step.
   
   The important observations were:
   
      1.
   
      ALTER SUBSCRIPTION ... REFRESH PUBLICATION on F is a no-op for an
      already subscribed table. The table remained in ready state, the tuple
      metadata did not change, no WAL was generated, and no sync worker was
      started.
      2.
   
      The publication-membership workaround is not local. In the forwarding
      case, truncating the table on F also wiped the table on D. In the
      fan-out case, the sibling subscriber S was left behind because the
      publication changes were global but the repair was only intended for F.
      3.
   
      A controlled subscriber-local reset of only the selected table on F
      produced matching checksums again without touching the publication or S.
      Doing the same thing as a bare catalog update while the subscription
      remained enabled was not reliable; the apply worker could continue with
      cached state and the table diverged.
   
   So I do not think the hard part is inventing a new synchronization
   mechanism. The existing tablesync path already does most of the work. The
   missing piece is a supported command that performs the reset safely:
   stopping or coordinating with workers, cleaning up per-table sync state and
   slots/origins where needed, optionally truncating the target table,
   invalidating caches, taking the right locks, and validating that the
   requested table is actually part of the subscription.
   
   For a first version, I think it would be reasonable to require the
   subscription to be disabled. That avoids the apply-worker race and keeps
   the patch smaller. A later version could support the enabled case, probably
   by making the running worker notice and re-read the changed relation state.
   
   I have a WIP patch for the disabled-subscription, executor changes, and a
   TAP test. Before polishing it further, I would like to get feedback on
   whether this direction makes sense ?
   
   Regards,
   
   Cagri Biroglu
   

2. Re: Per-table resync for logical replication subscriptions

   Cagri Biroglu <cagri.biroglu@adyen.com> — 2026-07-08T10:54:42Z

   here is the WIP patch for the disabled-subscription case.
   It adds:
   
       ALTER SUBSCRIPTION name REFRESH TABLE table_name
           [ WITH (copy_data = true, truncate = true) ]
   
   The command is subscriber-local: it resets just the named relation in
   pg_subscription_rel back to the initial sync state (optionally
   truncating the local copy first), so a tablesync worker re-copies that
   one table on the next enable, reusing the existing tablesync machinery.
   Publication membership and other tables are left untouched.  As
   discussed, this first version requires the subscription to be disabled.
   
   The tests are included in the same patch: a TAP test at
   src/test/subscription/t/039_refresh_table.pl that
   
     - resyncs one drifted table on a two-node setup and confirms it
       matches the publisher again after re-enabling;
     - confirms only the targeted relation is reset (a sibling table is
       left in 'r' state);
     - checks the rejection cases: enabled subscription, table not in the
       subscription, a sequence, truncate without copy_data, and running
       inside a transaction block.
   
   The full src/test/subscription suite and the core regression tests pass
   with the patch applied.
   
   Open questions if this direction makes sense ,I would still like feedback
   on: the command spelling
   (REFRESH TABLE vs RESYNC TABLE), and whether the enabled-subscription
   case is worth doing as a follow-up (it needs the running apply worker to
   notice and re-read the reset relation state).
   
   Regards,
   Cagri Biroglu
   
   On Tue, Jul 7, 2026 at 11:41 AM Cagri Biroglu <cagri.biroglu@adyen.com>
   wrote:
   
   > Hi hackers,
   >
   > I would like to propose a subscriber-side way to re-copy a single table
   > that is already part of a logical replication subscription.
   >
   > At the moment, I do not think PostgreSQL has a supported way to do this
   > for one table on one subscriber. ALTER SUBSCRIPTION ... REFRESH
   > PUBLICATION only handles tables that were added to the subscribed
   > publications since the subscription was created, or since the last refresh.
   > It does not re-copy tables that are already known to the subscription.
   >
   > That is fine for the normal “new table was added to the publication” case,
   > but it leaves a gap when only one subscriber needs one table to be
   > re-seeded. Some examples are:
   >
   >    -
   >
   >    the subscriber table became inconsistent;
   >    -
   >
   >    a row filter changed and the subscriber needs a fresh copy;
   >    -
   >
   >    a forwarder/subscriber needs to repair one table without disturbing
   >    other subscribers.
   >
   > The usual workaround is to temporarily change publication membership,
   > refresh the subscription, truncate the local table, add the table back, and
   > refresh again. That works in simple topologies, but it is not really local
   > to the subscriber. In a fan-out topology it can affect sibling subscribers,
   > and in a forwarding topology the truncate can be replicated further
   > downstream if truncates are published.
   >
   > There is also the unsupported option of updating pg_subscription_rel by
   > hand to put the table back into the initial sync state. That is not safe
   > either, because it races with the apply worker and does not handle the
   > surrounding cleanup and invalidation work.
   >
   > There is already a similar mechanism for sequences. ALTER SUBSCRIPTION
   > ... REFRESH SEQUENCES can re-synchronize sequence data for sequences
   > already known to the subscription, while REFRESH PUBLICATION only
   > discovers newly added ones. This proposal is essentially the table-side
   > equivalent of that idea.
   >
   > What I am proposing is something like:
   >
   > ALTER SUBSCRIPTION name REFRESH TABLE table_name
   >     [ WITH (copy_data = true, truncate = true) ];
   >
   > or, if people prefer the wording:
   >
   > ALTER SUBSCRIPTION name RESYNC TABLE table_name
   >     [ WITH (copy_data = true, truncate = true) ];
   >
   > The command would be subscriber-local and would not change publication
   > membership. Internally, it would reuse the existing table synchronization
   > machinery by resetting the selected relation back to the initial sync
   > state, so that a tablesync worker copies it again and moves it through the
   > normal i -> d -> s -> r states.
   >
   > I tested the current behavior on current devel with a small three-node
   > setup:
   >
   >    -
   >
   >    upstream publisher U;
   >    -
   >
   >    forwarding subscriber/publisher F;
   >    -
   >
   >    downstream subscriber D;
   >    -
   >
   >    sibling subscriber S.
   >
   > The test script keeps a writer running and prints per-table checksums at
   > each step.
   >
   > The important observations were:
   >
   >    1.
   >
   >    ALTER SUBSCRIPTION ... REFRESH PUBLICATION on F is a no-op for an
   >    already subscribed table. The table remained in ready state, the tuple
   >    metadata did not change, no WAL was generated, and no sync worker was
   >    started.
   >    2.
   >
   >    The publication-membership workaround is not local. In the forwarding
   >    case, truncating the table on F also wiped the table on D. In the
   >    fan-out case, the sibling subscriber S was left behind because the
   >    publication changes were global but the repair was only intended for F.
   >    3.
   >
   >    A controlled subscriber-local reset of only the selected table on F
   >    produced matching checksums again without touching the publication or S.
   >    Doing the same thing as a bare catalog update while the subscription
   >    remained enabled was not reliable; the apply worker could continue with
   >    cached state and the table diverged.
   >
   > So I do not think the hard part is inventing a new synchronization
   > mechanism. The existing tablesync path already does most of the work. The
   > missing piece is a supported command that performs the reset safely:
   > stopping or coordinating with workers, cleaning up per-table sync state and
   > slots/origins where needed, optionally truncating the target table,
   > invalidating caches, taking the right locks, and validating that the
   > requested table is actually part of the subscription.
   >
   > For a first version, I think it would be reasonable to require the
   > subscription to be disabled. That avoids the apply-worker race and keeps
   > the patch smaller. A later version could support the enabled case, probably
   > by making the running worker notice and re-read the changed relation state.
   >
   > I have a WIP patch for the disabled-subscription, executor changes, and a
   > TAP test. Before polishing it further, I would like to get feedback on
   > whether this direction makes sense ?
   >
   > Regards,
   >
   > Cagri Biroglu
   >