On Wed, Jun 5, 2024 at 9:30 PM Amit Kapila <amit.kapil...@gmail.com> wrote: > > On Wed, Jun 5, 2024 at 12:51 PM Peter Eisentraut <pe...@eisentraut.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.
+1 > 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? Another idea is doing that using options. For example, For initial sequences synchronization: CREATE SUBSCRIPTION ... WITH (copy_sequence = true); For re-copy (or update) sequences: ALTER SUBSCRIPTION ... REFRESH PUBLICATION WITH (copy_sequence = true); > > In addition to the above, the command Alter Subscription .. Refresh > Publication will fetch any missing sequences similar to what it does > for tables. On the subscriber side, do we need to track which sequences are created via CREATE/ALTER SUBSCRIPTION? Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
