Per-table resync for logical replication subscriptions
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
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:
Show quoted text
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
Attachments:
v1-0001-refresh-table.patchapplication/octet-stream; name=v1-0001-refresh-table.patchDownload+391-1
On Wed, Jul 8, 2026 at 8:55 PM Cagri Biroglu <cagri.biroglu@adyen.com> wrote:
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) ]
...
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).
(My feedback only about the syntax question)
As you said in the OP, this proposal is a bit like a table equivalent
of "ALTER SUBSCRIPTION sub REFRESH SEQUENCES".
IIRC we went through similar syntax debates when that command was
being developed.
Some alternatives were proposed [1]/messages/by-id/CALDaNm30iA_N4Dfeyp__YdLnbm9oatw_+VyCfNfbQmZiS_ybCw@mail.gmail.com
ALTER SUBSCRIPTION sub RESYNC PUBLICATION SEQUENCES;
ALTER SUBSCRIPTION sub RESYNC SEQUENCES;
And, then more [2]/messages/by-id/CAA4eK1JJUv2Kykb=JvdTzq+qDzkbD3m+YPAF6qz6Sm-qJ+wF8g@mail.gmail.com
ALTER SUBSCRIPTION sub REPLICATE PUBLICATION SEQUENCES;
ALTER SUBSCRIPTION sub RESYNC PPUBLICATION SEQUENCES;
ALTER SUBSCRIPTION sub SYNC PUBLICATION SEQUENCES;
ALTER SUBSCRIPTION sub MERGE PUBLICATION SEQUENCES;
It went back and forth for a while...
Finally, I proposed [3]/messages/by-id/CAHut+PvOmCe2fO4LPwJtROierhGLtHY+++r9PXF8=LLQ2m=AXw@mail.gmail.com and gave some reasons for just having:
ALTER SUBSCRIPTION sub REFRESH SEQUENCES;
And that's where it ended up...
~~~
If your proposal is to proceed, then IMO it would be better to
continue the same pattern, avoiding introducing new keywords unless
absolutely necessary.
So, your v1 patch syntax "REFRESH TABLE" looked good to me. A similar
reasoning to "REFRESH SEQUENCES" [3]/messages/by-id/CAHut+PvOmCe2fO4LPwJtROierhGLtHY+++r9PXF8=LLQ2m=AXw@mail.gmail.com can be used to explain it.
======
[1]: /messages/by-id/CALDaNm30iA_N4Dfeyp__YdLnbm9oatw_+VyCfNfbQmZiS_ybCw@mail.gmail.com
[2]: /messages/by-id/CAA4eK1JJUv2Kykb=JvdTzq+qDzkbD3m+YPAF6qz6Sm-qJ+wF8g@mail.gmail.com
[3]: /messages/by-id/CAHut+PvOmCe2fO4LPwJtROierhGLtHY+++r9PXF8=LLQ2m=AXw@mail.gmail.com
Kind Regards,
Peter Smith.
Fujitsu Australia
Hi,
On Wed, Jul 8, 2026 at 3:55 AM Cagri Biroglu <cagri.biroglu@adyen.com> wrote:
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).
REFRESH TABLE is better to me. It's more consistent with the existing
commands such as REFRESH REQUENCES.
I've not looked at the patch in depth yet but I have a few comments:
I think it might be a good idea that the REFRESH TABLE command can
take multiple tables rather than one table.
---
Why does the REFRESH TABLE command require for the subscription to be
disabled while REFRESH PUBLICATIONS doesn't?
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Thanks a lot for looking at this.
Before the point-by-point: one change after getting feedback on this
from the sketch in my first mail
is that I dropped the WITH (copy_data, truncate) options. For refreshing
a table that is already subscribed, only one combination is useful, so
REFRESH TABLE now always truncates the local copy and re-copies (what
would have been copy_data = true, truncate = true). Without truncating,
the re-copy hits duplicate-key errors or doubles the rows; skipping the
copy leaves the drift in place. So neither other combination is
meaningful, and there is nothing to configure.REFRESH TABLE is better to me. It's more consistent with the existing
commands such as REFRESH SEQUENCES.Great, I've kept REFRESH TABLE.
I think it might be a good idea that the REFRESH TABLE command can
take multiple tables rather than one table.Agreed. v2 makes REFRESH TABLE take a comma-separated list:
ALTER SUBSCRIPTION name REFRESH TABLE t1, t2, ...
It's all-or-nothing: every named relation is validated (part of the
subscription, not a sequence, exists) before any of them is reset, so a
bad table name aborts the whole command without touching the others. The
listed tables are also truncated together, which lets a set connected by
foreign keys be re-seeded as a unit.One note on scope: this doesn't add resource risk over doing one table
at a time, because the re-copy is still performed by tablesync workers
that are capped by max_sync_workers_per_subscription; resetting N tables
just queues them rather than starting N copies at once.Why does the REFRESH TABLE command require for the subscription to be
disabled while REFRESH PUBLICATIONS doesn't?Good question, but I'd actually argue the disabled requirement is a
reasonable design choice here rather than a limitation to remove.The reason the two commands differ is that REFRESH PUBLICATION only adds
*new* relations to pg_subscription_rel (in init state), whereas REFRESH
TABLE resets a relation that is already in ready state, and those hit
different caches in the apply worker:- The set of not-ready relations is cached (table_states_not_ready in
syncutils.c) and correctly invalidated by the SUBSCRIPTIONRELMAP
syscache callback (InvalidateSyncingRelStates). So after the reset the
apply worker does notice the table is non-ready and does launch a
tablesync worker for it. That part works whether enabled or not.- However, whether the apply worker applies incoming changes for a
relation is decided by should_apply_changes_for_rel(), which reads the
per-relation LogicalRepRelMapEntry.state. That state is only refreshed
from the catalog while it is not READY:/* relation.c, logicalrep_rel_open() */
if (entry->state != SUBREL_STATE_READY)
entry->state = GetSubscriptionRelState(...);For an already-ready table the entry stays READY, and the relcache
invalidation only clears localrelvalid (rebuilds the attrmap); it does
not reset entry->state. So the running apply worker keeps treating the
table as READY and keeps applying live changes to it, at the same time
as the freshly launched tablesync worker re-copies it, with no handoff
on the sync LSN. In my testing that reliably diverges the table.For a newly added table (REFRESH PUBLICATION) there is no stale READY
entry, so the normal init -> datasync -> syncdone -> ready handoff works
without any disable.Requiring the subscription to be disabled sidesteps this cleanly. When
you re-enable, a fresh apply worker starts and reads the reset table's
state from the catalog as init, so the same init -> datasync -> syncdone
-> ready handoff kicks in, exactly as for a newly added table. And the
disable only needs to bracket the quick catalog reset, not the re-copy
itself: REFRESH TABLE just truncates and flips the state, and after
ENABLE the tablesync worker does the (possibly long) copy while the
apply worker resumes the other tables as usual, so the pause is short
and scoped to the metadata change, not the data copy.Supporting the enabled case would instead mean reaching into the running
apply worker to force it to drop and re-read the cached state of the
reset relation mid-flight. That is doable, but it adds moving parts and
a new invalidation path to get right, for what is a deliberate,
occasional maintenance action where briefly disabling the subscription
does no harm. So my inclination is to keep the disabled requirement here
rather than trade that simplicity away. Happy to reconsider if you think
the enabled case matters enough to justify the extra machinery or i am
missing sth.
Regards,
Show quoted text
Cagri Biroglu