MIME-Version: 1.0
From: Cagri Biroglu <cagri.biroglu@adyen.com>
Date: Tue, 7 Jul 2026 11:41:55 +0200
Message-ID: 
 <CAA36mso9zgvMVr4UNHMMAQyU-dQy33EqxjZ=yEDG8SAB6yYDJw@mail.gmail.com>
Subject: Per-table resync for logical replication subscriptions
To: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000042158b065602343e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA36mso9zgvMVr4UNHMMAQyU-dQy33EqxjZ%3DyEDG8SAB6yYDJw%40mail.gmail.com>
Precedence: bulk

--00000000000042158b065602343e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =E2=80=9Cnew table was added to the publication=
=E2=80=9D 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 =3D true, truncate =3D true) ];

or, if people prefer the wording:

ALTER SUBSCRIPTION name RESYNC TABLE table_name
    [ WITH (copy_data =3D true, truncate =3D 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

--00000000000042158b065602343e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><p>Hi hackers,</p><p>I would like to propose a subscriber-=
side way to re-copy a single table that is already part of a logical replic=
ation subscription.</p><p>At the moment, I do not think PostgreSQL has a su=
pported way to do this for one table on one subscriber. <code>ALTER SUBSCRI=
PTION ... REFRESH PUBLICATION</code> only handles tables that were added to=
 the subscribed publications since the subscription was created, or since t=
he last refresh. It does not re-copy tables that are already known to the s=
ubscription.</p><p>That is fine for the normal =E2=80=9Cnew table was added=
 to the publication=E2=80=9D case, but it leaves a gap when only one subscr=
iber needs one table to be re-seeded. Some examples are:</p><ul><li><p>the =
subscriber table became inconsistent;</p></li><li><p>a row filter changed a=
nd the subscriber needs a fresh copy;</p></li><li><p>a forwarder/subscriber=
 needs to repair one table without disturbing other subscribers.</p></li></=
ul><p>The usual workaround is to temporarily change publication membership,=
 refresh the subscription, truncate the local table, add the table back, an=
d refresh again. That works in simple topologies, but it is not really loca=
l to the subscriber. In a fan-out topology it can affect sibling subscriber=
s, and in a forwarding topology the truncate can be replicated further down=
stream if truncates are published.</p><p>There is also the unsupported opti=
on of updating <code>pg_subscription_rel</code> by hand to put the table ba=
ck into the initial sync state. That is not safe either, because it races w=
ith the apply worker and does not handle the surrounding cleanup and invali=
dation work.</p><p>There is already a similar mechanism for sequences. <cod=
e>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</code> can re-synchronize sequen=
ce data for sequences already known to the subscription, while <code>REFRES=
H PUBLICATION</code> only discovers newly added ones. This proposal is esse=
ntially the table-side equivalent of that idea.</p><p>What I am proposing i=
s something like:</p><pre><code class=3D"gmail-language-sql">ALTER SUBSCRIP=
TION name REFRESH TABLE table_name
    [ WITH (copy_data =3D true, truncate =3D true) ];
</code></pre><p>or, if people prefer the wording:</p><pre><code class=3D"gm=
ail-language-sql">ALTER SUBSCRIPTION name RESYNC TABLE table_name
    [ WITH (copy_data =3D true, truncate =3D true) ];
</code></pre><p>The command would be subscriber-local and would not change =
publication membership. Internally, it would reuse the existing table synch=
ronization machinery by resetting the selected relation back to the initial=
 sync state, so that a tablesync worker copies it again and moves it throug=
h the normal <code>i -&gt; d -&gt; s -&gt; r</code> states.</p><p>I tested =
the current behavior on current devel with a small three-node setup:</p><ul=
><li><p>upstream publisher <code>U</code>;</p></li><li><p>forwarding subscr=
iber/publisher <code>F</code>;</p></li><li><p>downstream subscriber <code>D=
</code>;</p></li><li><p>sibling subscriber <code>S</code>.</p></li></ul><p>=
The test script keeps a writer running and prints per-table checksums at ea=
ch step.</p><p>The important observations were:</p><ol><li><p><code>ALTER S=
UBSCRIPTION ... REFRESH PUBLICATION</code> on <code>F</code> 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 start=
ed.</p></li><li><p>The publication-membership workaround is not local. In t=
he forwarding case, truncating the table on <code>F</code> also wiped the t=
able on <code>D</code>. In the fan-out case, the sibling subscriber <code>S=
</code> was left behind because the publication changes were global but the=
 repair was only intended for <code>F</code>.</p></li><li><p>A controlled s=
ubscriber-local reset of only the selected table on <code>F</code> produced=
 matching checksums again without touching the publication or <code>S</code=
>. Doing the same thing as a bare catalog update while the subscription rem=
ained enabled was not reliable; the apply worker could continue with cached=
 state and the table diverged.</p></li></ol><p>So I do not think the hard p=
art is inventing a new synchronization mechanism. The existing tablesync pa=
th already does most of the work. The missing piece is a supported command =
that performs the reset safely: stopping or coordinating with workers, clea=
ning up per-table sync state and slots/origins where needed, optionally tru=
ncating the target table, invalidating caches, taking the right locks, and =
validating that the requested table is actually part of the subscription.</=
p><p>For a first version, I think it would be reasonable to require the sub=
scription to be disabled. That avoids the apply-worker race and keeps the p=
atch smaller. A later version could support the enabled case, probably by m=
aking the running worker notice and re-read the changed relation state.</p>=
<p>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 wh=
ether this direction makes sense ?</p><p>Regards,<br></p><p>Cagri Biroglu</=
p></div>

--00000000000042158b065602343e--