| From: | vignesh C <vignesh21(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Include sequences in publications created by pg_createsubscriber |
| Date: | 2026-06-19 13:59:03 |
| Message-ID: | CALDaNm2RYQRLjexRJOpiZOFkspp8PRgoh6S7d2y003TJ_TSSZA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
pg_createsubscriber is primarily used to facilitate major-version
upgrades of physical replication clusters by converting a physical
standby into a logical subscriber.
The current upgrade workflow is:
Step 1) Run pg_createsubscriber to convert physical replication
cluster to logical replication cluster:
Step 2) Run pg_upgrade to upgrade Node-B to the new PG19 version:
Step 3) Stop connections/data changes in Node-A
Step 4) Synchronize the incremental data changes made on Node-A during
the upgrade process to Node-B.
Step 5) Redirect Writes to Node-B after the catchup
Step 6) Decommission Node-A
Step 7) Create Standby node using pg_basebackup, Node-C from Node-B
However, there is a gap in this workflow: while table data is kept
synchronized through logical replication, sequence state is not. As a
result, sequence values on the upgraded node can lag behind the
publisher and must be synchronized manually.
Recently, commit 96b37849734673e7c82fb86c4f0a46a28f500ac8 added
support for publishing ALL SEQUENCES, and commit
5509055d6956745532e65ab218e15b99d87d66ce introduced sequence
synchronization support. These features can be leveraged by
pg_createsubscriber to include sequences in the publication and
synchronize their state.
With sequence synchronization, the workflow becomes:
Step 1) Run pg_createsubscriber to convert physical replication
cluster to logical replication cluster:
Step 2) Run pg_upgrade to upgrade Node-B to new PG19 version:
Step 3) Stop connections/data changes in Node-A
Step 4) Synchronize the incremental data changes made on Node-A during
the upgrade process to Node-B.
Step 5) Run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to synchronize
the state of all subscribed sequences.
Step 6) Redirect Writes to Node-B after the catchup
Step 7) Decommission Node-A
Step 8) Create Standby node using pg_basebackup, Node-C from Node-B
Compared to the earlier upgrade workflow, the only additional
requirement is Step 5, which synchronizes sequence state between the
publisher and subscriber.
The attached patch modifies pg_createsubscriber to include sequences
in the publication it creates, enabling this workflow.
Thoughts?
Regards,
Vignesh
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Include-sequences-in-publications-created-by-pg_crea.patch | application/octet-stream | 7.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Zakariyah Ali | 2026-06-19 13:52:57 | Re: [PATCH] Fix loose polling in 019_replslot_limit.pl test |