Re: Logical Replication of sequences

On Wed, Jun 5, 2024 at 12:51 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 04.06.24 12:57, Amit Kapila wrote:
> > 2. Provide a command say Alter Subscription ... Replicate Sequences
> > (or something like that) which users can perform before shutdown of
> > the publisher node during upgrade. This will allow copying all the
> > sequences from the publisher node to the subscriber node directly.
> > Similar to previous approach, this could also be inconvenient for
> > users.
>
> I would start with this. In any case, you're going to need to write
> code to collect all the sequence values, send them over some protocol,
> apply them on the subscriber. The easiest way to start is to trigger
> that manually. Then later you can add other ways to trigger it, either
> by timer or around shutdown, or whatever other ideas there might be.
>

Agreed. To achieve this, we can allow sequences to be copied during
the initial CREATE SUBSCRIPTION command similar to what we do for
tables. And then later by new/existing command, we re-copy the already
existing sequences on the subscriber.

The options for the new command could be:
Alter Subscription ... Refresh Sequences
Alter Subscription ... Replicate Sequences

In the second option, we need to introduce a new keyword Replicate.
Can you think of any better option?

In addition to the above, the command Alter Subscription .. Refresh
Publication will fetch any missing sequences similar to what it does
for tables.

Thoughts?

--
With Regards,
Amit Kapila.