Re: Include sequences in publications created by pg_createsubscriber

On Tue, 23 Jun 2026 at 06:33, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Sat, Jun 20, 2026 at 1:59 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> ...
> > The attached patch modifies pg_createsubscriber to include sequences
> > in the publication it creates, enabling this workflow.
> > Thoughts?
> >
>
> Hi Vignesh,
>
> +1 to make this change.
>
> Some review comments for patch 0001.
>
> ======
> Commit Message
>
> 1.
> This change modifies pg_createsubscriber to include all sequences in the
> publication it creates. Administrators can then run ALTER SUBSCRIPTION
> ... REFRESH SEQUENCES after the upgrade to synchronize sequence state,
> ensuring a more complete and reliable upgrade workflow.
>
> ~
>
> /include all sequences/include ALL SEQUENCES/

Modified

> ======
> doc/src/sgml/ref/pg_createsubscriber.sgml
>
> 2.
> - does not copy the initial table data. It does only the
> synchronization phase,
> - which ensures each table is brought up to a synchronized state.
> + does not copy the initial table or sequence data. It does only the
> + synchronization phase, which ensures each table and sequence is brought up
> + to a synchronized state.
> </para>
>
> I felt "does not copy the initial [...] sequence data" should not be
> said for sequences. AFAIK each sequence has only 1 value anyway, so
> bringing it "to a synchronized state" is effectively the same thing as
> copying the initial data for sequences, so saying in the first
> sentence that copy does not happen seems a contradiction.
>
> IIUC what above means is really just the same as chapter 29.7, which
> says "use CREATE SUBSCRIPTION to initially synchronize the published
> sequences.".

pg_createsubscriber does the following:
1) We get the consistent_lsn from create_logical_replication_slot.
2) Then we set recovery_target_lsn = consistent_lsn from above
3) Then advance replication origin of each subscription to this lsn
4) Then enable subscription
5) stop standby server

Here setting recovery_target_lsn to consistent_lsn and recovering it
till there. I mean sequences are brought to sync state.
Create subscription will not do any data copy in case of pg_createsubscriber.

I meant the above here, let me know if you have better wording for this.

> ~~~
>
> 3.
> linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
> - TABLES</literal></link>. If the <option>--publication</option> option
> + TABLES</literal></link> and <link
> + linkend="sql-createpublication-params-for-all-sequences"><literal>FOR ALL
> + SEQUENCES</literal></link>. If the <option>--publication</option> option
> is not specified, the publication has the following name pattern:
>
> Maybe just say "ALL SEQUENCES" here instead of "FOR ALL SEQUENCES"?

Modified

> ~~~
>
> 4.
> <para>
> Enable the subscription for each specified database on the target server.
> The subscription starts applying transactions from the replication start
> - point.
> + point. The subscription is configured to include sequences. Sequence
> + values can be synchronized at any time by running
> + <link linkend="sql-altersubscription-params-refresh-sequences">
> + <command>ALTER SUBSCRIPTION ... REFRESH SEQUENCES</command></link>, which
> + fetches the current sequence state from the publisher and advances the
> + corresponding subscriber sequences.
> </para>
>
> IIUC, all this "How it works" section is describing the internal logic
> of what pg_subscriber is internally. OTOH, this "REFRESH SEQUENCES" is
> just a note about what the Administrator can do manually later...
>
> So, maybe you can still write this note somewhere, but just don't
> include it buried in the internal "How it works" steps.

Moved it along with description

> ======
> src/bin/pg_basebackup/pg_createsubscriber.c
>
> 5.
> Patch needs also to update the function comment for
> create_publication() because currently it still says "includes all
> tables".

Modified

The attached v2 version patch has the changes for the same.

Regards,
Vignesh