On Tue, 23 Jun 2026 at 06:33, Peter Smith <[email protected]> wrote: > > On Sat, Jun 20, 2026 at 1:59 AM vignesh C <[email protected]> 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
v2-0001-Include-sequences-in-publications-created-by-pg_c.patch
Description: Binary data
