Re: Documentation to upgrade logical replication cluster
On Fri, 23 Feb 2024 at 04:58, Peter Smith wrote: > > Hi Vignesh, I have no further comments. Patch v9 LGTM. The v9 version patch was not applying on top of HEAD because of few commits, the updated v10 version patch is rebased on top of HEAD. Regards, Vignesh From 0850792d2d52368af9a54698c03543b48e1f6681 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Mon, 6 May 2024 10:29:01 +0530 Subject: [PATCH v10] Documentation for upgrading logical replication cluster. Documentation for upgrading logical replication cluster. --- doc/src/sgml/glossary.sgml| 10 + doc/src/sgml/logical-replication.sgml | 805 ++ doc/src/sgml/ref/pgupgrade.sgml | 131 + 3 files changed, 823 insertions(+), 123 deletions(-) diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index a81c17a869..bb2ba81b18 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1068,6 +1068,16 @@ + + Logical replication cluster + + + A set of publisher and subscriber instances with the publisher instance + replicating changes to the subscriber instance. + + + + Log record diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ec2130669e..e3b7162702 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1926,6 +1926,811 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER + + Upgrade + + + Migration of logical replication clusters + is possible only when all the members of the old logical replication + clusters are version 17.0 or later. + + + + Prepare for publisher upgrades + + +pg_upgrade attempts to migrate logical +slots. This helps avoid the need for manually defining the same +logical slots on the new publisher. Migration of logical slots is +only supported when the old cluster is version 17.0 or later. +Logical slots on clusters before version 17.0 will silently be +ignored. + + + +Before you start upgrading the publisher cluster, ensure that the +subscription is temporarily disabled, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + + + +There are some prerequisites for pg_upgrade to +be able to upgrade the logical slots. If these are not met an error +will be reported. + + + + + + The new cluster must have + wal_level as + logical. + + + + + The new cluster must have + max_replication_slots + configured to a value greater than or equal to the number of slots + present in the old cluster. + + + + + The output plugins referenced by the slots on the old cluster must be + installed in the new PostgreSQL executable directory. + + + + + The old cluster has replicated all the transactions and logical decoding + messages to subscribers. + + + + + All slots on the old cluster must be usable, i.e., there are no slots + whose + pg_replication_slots.conflicting + is not true. + + + + + The new cluster must not have permanent logical slots, i.e., + there must be no slots where + pg_replication_slots.temporary + is false. + + + + + + + Prepare for subscriber upgrades + + +Setup the +subscriber configurations in the new subscriber. +pg_upgrade attempts to migrate subscription +dependencies which includes the subscription's table information present in +pg_subscription_rel +system catalog and also the subscription's replication origin. This allows +logical replication on the new subscriber to continue from where the +old subscriber was up to. Migration of subscription dependencies is only +supported when the old cluster is version 17.0 or later. Subscription +dependencies on clusters before version 17.0 will silently be ignored. + + + +There are some prerequisites for pg_upgrade to +be able to upgrade the subscriptions. If these are not met an error +will be reported. + + + + + + All the subscription tables in the old subscriber should be in state + i (initialize) or r (ready). This + can be verified by checking pg_subscription_rel.srsubstate. + + + + + The replication origin entry corresponding to each of the subscriptions + should exist in the old cluster. This can be found by checking + pg_subscription and + pg_replication_origin + system tables. + + + + + The new cluster must have + max_replication_slots + configured to a value greater than or equal to the number of + subscriptions present in the old cluster. + + + + + + + Upgrading logical replication clusters + + +
Re: Documentation to upgrade logical replication cluster
Hi Vignesh, I have no further comments. Patch v9 LGTM. == Kind Regards, Peter Smith. Fujitsu Australia
Re: Documentation to upgrade logical replication cluster
On Thu, 22 Feb 2024 at 09:35, Peter Smith wrote: > > Here are some minor comments for patch v8-0001. > > == > doc/src/sgml/glossary.sgml > > 1. > + > + > + A set of publisher and subscriber instances with publisher instance > + replicating changes to the subscriber instance. > + > + > > /with publisher instance/with the publisher instance/ Modified > ~~~ > > 2. > There are 2 SQL fragments but they are wrapped differently (see > below). e.g. it is not clear why is the 2nd fragment wrapped since it > is shorter than the 1st. OTOH, maybe you want the 1st fragment to > wrap. Either way, consistency wrapping would be better. Modified Thanks for the comments, the attached v9 version patch has the changes for the same. I have added a commitfest entry for this: https://commitfest.postgresql.org/47/4848/ Regards, Vignesh From 3ff1910ec221c4e5f2e1eb8291848528d6ec479a Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Tue, 30 Jan 2024 08:55:20 +0530 Subject: [PATCH v9] Documentation for upgrading logical replication cluster. Documentation for upgrading logical replication cluster. --- doc/src/sgml/glossary.sgml| 10 + doc/src/sgml/logical-replication.sgml | 821 ++ doc/src/sgml/ref/pgupgrade.sgml | 131 +--- 3 files changed, 839 insertions(+), 123 deletions(-) diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index 8c2f11480d..45cea16e8f 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1068,6 +1068,16 @@ + + Logical replication cluster + + + A set of publisher and subscriber instances with the publisher instance + replicating changes to the subscriber instance. + + + + Log record diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ec2130669e..d1eebe60e8 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1926,6 +1926,827 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER + + Upgrade + + + Migration of logical replication clusters + is possible only when all the members of the old logical replication + clusters are version 17.0 or later. + + + + Prepare for publisher upgrades + + + attempts to migrate logical +slots. This helps avoid the need for manually defining the same +logical slots on the new publisher. Migration of logical slots is +only supported when the old cluster is version 17.0 or later. +Logical slots on clusters before version 17.0 will silently be +ignored. + + + +Before you start upgrading the publisher cluster, ensure that the +subscription is temporarily disabled to avoid the subscriber connection +failures during publisher upgrade, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + + + +The following prerequisites are required for pg_upgrade +to be able to upgrade the logical slots. If these are not met an error +will be reported. + + + + + + The new cluster must have + wal_level as + logical. + + + + + The new cluster must have + max_replication_slots + configured to a value greater than or equal to the number of slots + present in the old cluster. + + + + + The output plugins referenced by the slots on the old cluster must be + installed in the new PostgreSQL executable + directory. + + + + + The old cluster must have replicated all the transactions and logical + decoding messages to subscribers. + + + + + All slots on the old cluster must be usable, i.e., there are no slots + whose + pg_replication_slots.conflict_reason + is not NULL, e.g.: + +postgres=# SELECT slot_name +postgres-# FROM pg_replication_slots +postgres-# WHERE slot_type = 'logical' AND conflict_reason IS NOT NULL; + slot_name +--- +(0 rows) + + + + + + The new cluster must not have permanent logical slots, i.e., + there must be no slots listed with: + +SELECT count(*) + FROM pg_replication_slots + WHERE slot_type = 'logical' AND temporary IS false; + + + + + + + + Prepare for subscriber upgrades + + +Setup the +subscriber configurations in the new subscriber. + + + +pg_upgrade attempts to migrate subscription +dependencies which includes the subscription's table information present in +pg_subscription_rel +system catalog and also the subscription's replication origin. This allows +logical replication on the new subscriber to continue from where the +old subscriber was up to. Migration of subscription dependencies is only +supported when the old cluster is version 17.0 or later. Subscription +dependencies on clusters before
Re: Documentation to upgrade logical replication cluster
Here are some minor comments for patch v8-0001. == doc/src/sgml/glossary.sgml 1. + + + A set of publisher and subscriber instances with publisher instance + replicating changes to the subscriber instance. + + /with publisher instance/with the publisher instance/ ~~~ 2. There are 2 SQL fragments but they are wrapped differently (see below). e.g. it is not clear why is the 2nd fragment wrapped since it is shorter than the 1st. OTOH, maybe you want the 1st fragment to wrap. Either way, consistency wrapping would be better. postgres=# SELECT slot_name FROM pg_replication_slots WHERE slot_type = 'logical' AND conflict_reason IS NOT NULL; slot_name --- (0 rows) versus SELECT count(*) FROM pg_replication_slots WHERE slot_type = 'logical' AND temporary IS false; == Kind Regards, Peter Smith. Fujitsu Australia
Re: Documentation to upgrade logical replication cluster
On Mon, 12 Feb 2024 at 14:33, vignesh C wrote: > > On Fri, 9 Feb 2024 at 12:30, Peter Smith wrote: > > > > Here are some review comments for patch v7-0001. > > > > == > > doc/src/sgml/glossary.sgml > > > > 1. > > + > > + Logical replication cluster > > + > > + > > + A set of publisher and subscriber instance with publisher instance > > + replicating changes to the subscriber instance. > > + > > + > > + > > > > 1a. > > /instance with/instances with/ > > Modified > > > ~~~ > > > > 1b. > > The description then made me want to look up the glossary definition > > of a "publisher instance" and "subscriber instance", but then I was > > quite surprised that even "Publisher" and "Subscriber" terms are not > > described in the glossary. Should this patch add those, or should we > > start another thread for adding them? > > I felt it is better to start a new thread for this A new patch has been posted at [1] to address this. [1] - https://www.postgresql.org/message-id/CANhcyEXa%3D%2BshzbdS2iW9%3DY%3D_Eh7aRWZbQKJjDHVYiCmuiE1Okw%40mail.gmail.com Regards, Vignesh
Re: Documentation to upgrade logical replication cluster
On Fri, 9 Feb 2024 at 12:30, Peter Smith wrote: > > Here are some review comments for patch v7-0001. > > == > doc/src/sgml/glossary.sgml > > 1. > + > + Logical replication cluster > + > + > + A set of publisher and subscriber instance with publisher instance > + replicating changes to the subscriber instance. > + > + > + > > 1a. > /instance with/instances with/ Modified > ~~~ > > 1b. > The description then made me want to look up the glossary definition > of a "publisher instance" and "subscriber instance", but then I was > quite surprised that even "Publisher" and "Subscriber" terms are not > described in the glossary. Should this patch add those, or should we > start another thread for adding them? I felt it is better to start a new thread for this > == > doc/src/sgml/logical-replication.sgml > > 2. > + > + Migration of logical replication clusters is possible only when all the > + members of the old logical replication clusters are version 17.0 or later. > + > > Here is where "logical replication clusters" is mentioned. Shouldn't > this first reference be linked to that new to the glossary entry -- > e.g. logical replication clusters Modified > ~~~ > > 3. > + > +Following are the prerequisites for pg_upgrade > +to be able to upgrade the logical slots. If these are not met an error > +will be reported. > + > > SUGGESTION > The following prerequisites are required for ... > > ~~~ > > 4. > + > + All slots on the old cluster must be usable, i.e., there are no slots > + whose > + linkend="view-pg-replication-slots">pg_replication_slots.conflict_reason > + is not NULL. > + > > The double-negative is too tricky "no slots whose ... not NULL", needs > rewording. Maybe it is better to instead use an example as the next > bullet point does. The other way is to mention "all slots should have conflic_reason is NULL", but in this case i feel checking for records is not NULL is better. So I have kept the wording the same and added an example to avoid any confusion. > ~~~ > > 5. > + > + > +Following are the prerequisites for > pg_upgrade to > +be able to upgrade the subscriptions. If these are not met an error > +will be reported. > + > > SUGGESTION > The following prerequisites are required for ... Modified > == > doc/src/sgml/ref/pgupgrade.sgml > > 6. > + > + > + The steps to upgrade logical replication clusters are not covered here; > + refer to for details. > + > + > > Maybe here too there should be a link to the glossary term "logical > replication clusters". Modified Thanks for the comments, the attached v8 version patch has the changes for the patch. Regards, Vignesh From e071c349fb2b6d5d26d8a639e0c3f0faa836bf10 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Tue, 30 Jan 2024 08:55:20 +0530 Subject: [PATCH v8] Documentation for upgrading logical replication cluster. Documentation for upgrading logical replication cluster. --- doc/src/sgml/glossary.sgml| 10 + doc/src/sgml/logical-replication.sgml | 818 ++ doc/src/sgml/ref/pgupgrade.sgml | 131 + 3 files changed, 836 insertions(+), 123 deletions(-) diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index 8c2f11480d..901e9a216e 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1068,6 +1068,16 @@ + + Logical replication cluster + + + A set of publisher and subscriber instances with publisher instance + replicating changes to the subscriber instance. + + + + Log record diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ec2130669e..246600f9b6 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1926,6 +1926,824 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER + + Upgrade + + + Migration of logical replication clusters + is possible only when all the members of the old logical replication + clusters are version 17.0 or later. + + + + Prepare for publisher upgrades + + + attempts to migrate logical +slots. This helps avoid the need for manually defining the same +logical slots on the new publisher. Migration of logical slots is +only supported when the old cluster is version 17.0 or later. +Logical slots on clusters before version 17.0 will silently be +ignored. + + + +Before you start upgrading the publisher cluster, ensure that the +subscription is temporarily disabled to avoid the subscriber connection +failures during publisher upgrade, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + + + +The following prerequisites are required for pg_upgrade +to be able to upgrade the logical slots. If these are not met an
Re: Documentation to upgrade logical replication cluster
On Thu, 1 Feb 2024 at 14:50, vignesh C wrote: > > On Wed, 31 Jan 2024 at 11:42, Hayato Kuroda (Fujitsu) > wrote: > > > > Dear Vignesh, > > > > Thanks for updating the patch! Here are my comments for v6. > > > > 01. > > ``` > > + Logical replication cluster > > + > > + > > + A set of publisher and subscriber instance with publisher instance > > + replicating changes to the subscriber instance. > > + > > + > > ``` > > > > Should we say 1:N relationship is allowed? > > I felt this need not be mentioned here, just wanted to give an > indication wherever this terminology is used, it means a set of > publisher and subscriber instances. Detail information should be added > in the logical replication related pages > > > 02. > > ``` > > @@ -70,6 +70,7 @@ PostgreSQL documentation > > pg_upgrade supports upgrades from 9.2.X and later to the current > > major release of PostgreSQL, including > > snapshot and beta releases. > > > > + > > > > ``` > > > > Unnecessary blank. > > Removed it. > > > 03. > > ``` > > > > - These are the steps to perform an upgrade > > - with pg_upgrade: > > + Below are the steps to perform an upgrade > > + with pg_upgrade. > > > > ``` > > > > I'm not sure it should be included in this patch. > > This is not required in this patch, removed it. > > > 04. > > ``` > > + If the old primary is prior to version 17.0, then no slots on the > > primary > > + are copied to the new standby, so all the slots on the old standby > > must > > + be recreated manually. > > ``` > > > > I think that "all the slots on the old standby" must be created manually in > > any > > cases. Therefore, the preposition ", so" seems not correct. > > I felt that this change is not related to this patch. I'm removing > these changes from the patch. Let's handle rephrasing of the base code > change in a separate thread. > > > 05. > > ``` > > If the old primary is version 17.0 or later, then > > + only logical slots on the primary are copied to the new standby, but > > + other slots on the old standby are not copied, so must be recreated > > + manually. > > ``` > > > > How about replacing this paragraph to below? > > > > ``` > > All the slots on the old standby must be recreated manually. If the old > > primary > > is version 17.0 or later, then only logical slots on the primary are copied > > to the > > new standby. > > ``` > > I felt that this change is not related to this patch. I'm removing > these changes from the patch. Let's handle rephrasing of the base code > change in a separate thread. A new thread is started for the same and a patch is attached at [1]. Let's discuss this change at the new thread. [1] - https://www.postgresql.org/message-id/CAHv8RjJHCw0jpUo9PZxjcguzGt3j2W1_NH%3DQuREoN0nYiVdVeA%40mail.gmail.com Regards, Vignesh
Re: Documentation to upgrade logical replication cluster
Here are some review comments for patch v7-0001. == doc/src/sgml/glossary.sgml 1. + + Logical replication cluster + + + A set of publisher and subscriber instance with publisher instance + replicating changes to the subscriber instance. + + + 1a. /instance with/instances with/ ~~~ 1b. The description then made me want to look up the glossary definition of a "publisher instance" and "subscriber instance", but then I was quite surprised that even "Publisher" and "Subscriber" terms are not described in the glossary. Should this patch add those, or should we start another thread for adding them? == doc/src/sgml/logical-replication.sgml 2. + + Migration of logical replication clusters is possible only when all the + members of the old logical replication clusters are version 17.0 or later. + Here is where "logical replication clusters" is mentioned. Shouldn't this first reference be linked to that new to the glossary entry -- e.g. logical replication clusters ~~~ 3. + +Following are the prerequisites for pg_upgrade +to be able to upgrade the logical slots. If these are not met an error +will be reported. + SUGGESTION The following prerequisites are required for ... ~~~ 4. + + All slots on the old cluster must be usable, i.e., there are no slots + whose + pg_replication_slots.conflict_reason + is not NULL. + The double-negative is too tricky "no slots whose ... not NULL", needs rewording. Maybe it is better to instead use an example as the next bullet point does. ~~~ 5. + + +Following are the prerequisites for pg_upgrade to +be able to upgrade the subscriptions. If these are not met an error +will be reported. + SUGGESTION The following prerequisites are required for ... == doc/src/sgml/ref/pgupgrade.sgml 6. + + + The steps to upgrade logical replication clusters are not covered here; + refer to for details. + + Maybe here too there should be a link to the glossary term "logical replication clusters". == Kind Regards, Peter Smith. Fujitsu Australia
Re: Documentation to upgrade logical replication cluster
On Wed, 31 Jan 2024 at 11:42, Hayato Kuroda (Fujitsu) wrote: > > Dear Vignesh, > > Thanks for updating the patch! Here are my comments for v6. > > 01. > ``` > + Logical replication cluster > + > + > + A set of publisher and subscriber instance with publisher instance > + replicating changes to the subscriber instance. > + > + > ``` > > Should we say 1:N relationship is allowed? I felt this need not be mentioned here, just wanted to give an indication wherever this terminology is used, it means a set of publisher and subscriber instances. Detail information should be added in the logical replication related pages > 02. > ``` > @@ -70,6 +70,7 @@ PostgreSQL documentation > pg_upgrade supports upgrades from 9.2.X and later to the current > major release of PostgreSQL, including > snapshot and beta releases. > > + > > ``` > > Unnecessary blank. Removed it. > 03. > ``` > > - These are the steps to perform an upgrade > - with pg_upgrade: > + Below are the steps to perform an upgrade > + with pg_upgrade. > > ``` > > I'm not sure it should be included in this patch. This is not required in this patch, removed it. > 04. > ``` > + If the old primary is prior to version 17.0, then no slots on the > primary > + are copied to the new standby, so all the slots on the old standby > must > + be recreated manually. > ``` > > I think that "all the slots on the old standby" must be created manually in > any > cases. Therefore, the preposition ", so" seems not correct. I felt that this change is not related to this patch. I'm removing these changes from the patch. Let's handle rephrasing of the base code change in a separate thread. > 05. > ``` > If the old primary is version 17.0 or later, then > + only logical slots on the primary are copied to the new standby, but > + other slots on the old standby are not copied, so must be recreated > + manually. > ``` > > How about replacing this paragraph to below? > > ``` > All the slots on the old standby must be recreated manually. If the old > primary > is version 17.0 or later, then only logical slots on the primary are copied > to the > new standby. > ``` I felt that this change is not related to this patch. I'm removing these changes from the patch. Let's handle rephrasing of the base code change in a separate thread. Thanks for the comments, the attached v7 version patch has the changes for the same. Regards, Vignesh From 23d167e3600054223ea29cafa4f465b28c8e75de Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Tue, 30 Jan 2024 08:55:20 +0530 Subject: [PATCH v7] Documentation for upgrading logical replication cluster. Documentation for upgrading logical replication cluster. --- doc/src/sgml/glossary.sgml| 10 + doc/src/sgml/logical-replication.sgml | 811 ++ doc/src/sgml/ref/pgupgrade.sgml | 130 + 3 files changed, 828 insertions(+), 123 deletions(-) diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index 8c2f11480d..1f9166f27b 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1068,6 +1068,16 @@ + + Logical replication cluster + + + A set of publisher and subscriber instance with publisher instance + replicating changes to the subscriber instance. + + + + Log record diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ec2130669e..e124d699c0 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1926,6 +1926,817 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER + + Upgrade + + + Migration of logical replication clusters is possible only when all the + members of the old logical replication clusters are version 17.0 or later. + + + + Prepare for publisher upgrades + + + attempts to migrate logical +slots. This helps avoid the need for manually defining the same +logical slots on the new publisher. Migration of logical slots is +only supported when the old cluster is version 17.0 or later. +Logical slots on clusters before version 17.0 will silently be +ignored. + + + +Before you start upgrading the publisher cluster, ensure that the +subscription is temporarily disabled to avoid the subscriber connection +failures during publisher upgrade, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + + + +Following are the prerequisites for pg_upgrade +to be able to upgrade the logical slots. If these are not met an error +will be reported. + + + + + + The new cluster must have + wal_level as + logical. + + + + + The new cluster must have + max_replication_slots + configured to a value greater than or equal to the number of
RE: Documentation to upgrade logical replication cluster
Dear Vignesh, Thanks for updating the patch! Here are my comments for v6. 01. ``` + Logical replication cluster + + + A set of publisher and subscriber instance with publisher instance + replicating changes to the subscriber instance. + + ``` Should we say 1:N relationship is allowed? 02. ``` @@ -70,6 +70,7 @@ PostgreSQL documentation pg_upgrade supports upgrades from 9.2.X and later to the current major release of PostgreSQL, including snapshot and beta releases. + ``` Unnecessary blank. 03. ``` - These are the steps to perform an upgrade - with pg_upgrade: + Below are the steps to perform an upgrade + with pg_upgrade. ``` I'm not sure it should be included in this patch. 04. ``` + If the old primary is prior to version 17.0, then no slots on the primary + are copied to the new standby, so all the slots on the old standby must + be recreated manually. ``` I think that "all the slots on the old standby" must be created manually in any cases. Therefore, the preposition ", so" seems not correct. 05. ``` If the old primary is version 17.0 or later, then + only logical slots on the primary are copied to the new standby, but + other slots on the old standby are not copied, so must be recreated + manually. ``` How about replacing this paragraph to below? ``` All the slots on the old standby must be recreated manually. If the old primary is version 17.0 or later, then only logical slots on the primary are copied to the new standby. ``` Best Regards, Hayato Kuroda FUJITSU LIMITED https://www.fujitsu.com/
Re: Documentation to upgrade logical replication cluster
On Mon, 29 Jan 2024 at 16:01, Bharath Rupireddy wrote: > > On Mon, Jan 29, 2024 at 10:10 AM vignesh C wrote: > > > > Thanks for the comments, the attached v5 version patch has the changes > > for the same. > > Thanks for working on this. Here are some comments on the v5 patch: > > 1. > + > + Migration of logical replication clusters is possible only when all the > + members of the old logical replication clusters are version 17.0 or later. > > Perhaps define what logical replication cluster is either in glossary > or within a parenthesis next to the first use in the docs? This will > help developers understand it better and will not confuse them with > postgres cluster. I see it being used for the first time in code > comments 9a17be1e2, but this patch uses it for the first time in the > docs. I have added it in glossary. > 2. > + Before reading this section, refer page for > + more details about pg_upgrade. > + > > This looks extraneous, we can just link to pg_upgrade on the first use > of pg_upgrade, change the following > > + > +pg_upgrade attempts to migrate logical > +slots. This helps avoid the need for manually defining the same > > to > > + > + attempts to migrate logical > +slots. This helps avoid the need for manually defining the same Modified > 3. > + transactional, the user is advised to take backups. Backups can be taken > + as described in . > + > > How about simplifying the above to "the user is advised to take > backups as described in " instead > of two statements? Modified > 4. > subscription is temporarily disabled, by executing > +ALTER SUBSCRIPTION > ... DISABLE. > +Re-enable the subscription after the upgrade. > + > > Is it to avoid repeated failures of logical replication apply workers > on the subscribers? Isn't it good to say why subscription needs to be > disabled? Added it > 5. > + > +There are some prerequisites for pg_upgrade to > +be able to upgrade the logical slots. If these are not met an error > +will be reported. > + > > I think it's better to be "Following are prerequisites for > pg_upgrade to.."? Modified > 6. > + > + > + The old cluster has replicated all the transactions and logical > decoding > + messages to subscribers. > + > > I think it's better to be "The old cluster must have replicated all > the transactions and "? Modified > 7. > + > + The new cluster must not have permanent logical slots, i.e., > + there must be no slots where > + linkend="view-pg-replication-slots">pg_replication_slots.temporary > + is false. > > I think we better specify a full SQL query as opposed to just > specifying one output column and the view name. > > > The new cluster must not have permanent logical slots, i.e., a query like: > > SELECT count(*) FROM pg_replication_slots WHERE slot_type = 'logical' > AND temporary IS false; > > must return 0. > Modified > 8. > + If the old cluster is prior to 17.0, then no slots on the primary are > + copied to the new standby, so all the slots must be recreated > manually. > + If the old cluster is 17.0 or later, then only logical slots on the > > I think it's better to say "version 17.0" instead of just "17.0". Modified > 9. > + primary are copied to the new standby, but other slots on the > old standby > > "but other slots on the old standby" - is it slots on the old standby > or old cluster? > > I think it's the other way around: the old cluster needs to be > replaced with the old standby in the newly added paragraph. Modified it to old primary as we upgrade primary and do a rsync > 10. > Change > + primary are copied to the new standby, but other slots on the > old standby > + are not copied so must be recreated manually. > > to > > + primary are copied to the new standby, but other slots on the > old standby > + are not copied, so must be recreated manually. Modified > 11. > + > + > + The logical replication restrictions apply to logical replication > cluster > + upgrades also. See > for > + the details of logical replication restrictions. > + > > How about just say "See linkend="logical-replication-restrictions"/> for details." instead of > using logical replication restrictions more than once in the same > para? Modified > 12. > + > + The prerequisites of publisher upgrade apply to logical replication > + cluster upgrades also. See > + for the details of publisher upgrade prerequisites. > > How about just say "See > for details." instead of using publisher upgrade prerequisites more > than once in the same para? Modified > 13. > + > + The prerequisites of subscriber upgrade apply to logical replication > + cluster upgrades also. See > + for the details of subscriber upgrade prerequisites. > + > > How about just say "See > for details." instead of using
Re: Documentation to upgrade logical replication cluster
On Mon, Jan 29, 2024 at 10:10 AM vignesh C wrote: > > Thanks for the comments, the attached v5 version patch has the changes > for the same. Thanks for working on this. Here are some comments on the v5 patch: 1. + + Migration of logical replication clusters is possible only when all the + members of the old logical replication clusters are version 17.0 or later. Perhaps define what logical replication cluster is either in glossary or within a parenthesis next to the first use in the docs? This will help developers understand it better and will not confuse them with postgres cluster. I see it being used for the first time in code comments 9a17be1e2, but this patch uses it for the first time in the docs. 2. + Before reading this section, refer page for + more details about pg_upgrade. + This looks extraneous, we can just link to pg_upgrade on the first use of pg_upgrade, change the following + +pg_upgrade attempts to migrate logical +slots. This helps avoid the need for manually defining the same to + + attempts to migrate logical +slots. This helps avoid the need for manually defining the same 3. + transactional, the user is advised to take backups. Backups can be taken + as described in . + How about simplifying the above to "the user is advised to take backups as described in " instead of two statements? 4. subscription is temporarily disabled, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + Is it to avoid repeated failures of logical replication apply workers on the subscribers? Isn't it good to say why subscription needs to be disabled? 5. + +There are some prerequisites for pg_upgrade to +be able to upgrade the logical slots. If these are not met an error +will be reported. + I think it's better to be "Following are prerequisites for pg_upgrade to.."? 6. + + + The old cluster has replicated all the transactions and logical decoding + messages to subscribers. + I think it's better to be "The old cluster must have replicated all the transactions and "? 7. + + The new cluster must not have permanent logical slots, i.e., + there must be no slots where + pg_replication_slots.temporary + is false. I think we better specify a full SQL query as opposed to just specifying one output column and the view name. The new cluster must not have permanent logical slots, i.e., a query like: SELECT count(*) FROM pg_replication_slots WHERE slot_type = 'logical' AND temporary IS false; must return 0. 8. + If the old cluster is prior to 17.0, then no slots on the primary are + copied to the new standby, so all the slots must be recreated manually. + If the old cluster is 17.0 or later, then only logical slots on the I think it's better to say "version 17.0" instead of just "17.0". 9. + primary are copied to the new standby, but other slots on the old standby "but other slots on the old standby" - is it slots on the old standby or old cluster? I think it's the other way around: the old cluster needs to be replaced with the old standby in the newly added paragraph. 10. Change + primary are copied to the new standby, but other slots on the old standby + are not copied so must be recreated manually. to + primary are copied to the new standby, but other slots on the old standby + are not copied, so must be recreated manually. 11. + + + The logical replication restrictions apply to logical replication cluster + upgrades also. See for + the details of logical replication restrictions. + How about just say "See for details." instead of using logical replication restrictions more than once in the same para? 12. + + The prerequisites of publisher upgrade apply to logical replication + cluster upgrades also. See + for the details of publisher upgrade prerequisites. How about just say "See for details." instead of using publisher upgrade prerequisites more than once in the same para? 13. + + The prerequisites of subscriber upgrade apply to logical replication + cluster upgrades also. See + for the details of subscriber upgrade prerequisites. + How about just say "See for details." instead of using subscriber upgrade prerequisites more than once in the same para? 14. + Upgrading logical replication cluster requires multiple steps to be + performed on various nodes. Because not all operations are Per comment #1, defining logical replication clusters and nodes helps clearly distinguish. For instance, one can get confused with the various terms in hand - postgres cluster, logical replication cluster, node etc. 15. + two subscriptions sub1_node1_node2 and + sub2_node1_node2 which are subscribing the changes + from node1. Why confluse with subsription names by including node1 and
Re: Documentation to upgrade logical replication cluster
On Mon, 29 Jan 2024 at 06:34, Peter Smith wrote: > > Hi Vignesh, > > Here are some review comments for patch v4. > > These are cosmetic only; otherwise v4 LGTM. > > == > doc/src/sgml/ref/pgupgrade.sgml > > 1. > Configure the servers for log shipping. (You do not need to run > pg_backup_start() and > pg_backup_stop() > or take a file system backup as the standbys are still synchronized > - with the primary.) Only logical slots on the primary are copied to > the > - new standby, but other slots on the old standby are not copied so must > - be recreated manually. > + with the primary.) If the old cluster is prior to 17.0, then no slots > + on the primary are copied to the new standby, so all the slots must be > + recreated manually. If the old cluster is 17.0 or later, then only > + logical slots on the primary are copied to the new standby, but other > + slots on the old standby are not copied so must be recreated manually. > > > Perhaps the part from "If the old cluster is prior..." should be in a > new paragraph. Modified > == > doc/src/sgml/logical-replication.sgml > > 2. > + > +Setup the > +subscriber configurations in the new subscriber. > +pg_upgrade attempts to migrate subscription > +dependencies which includes the subscription's table information present > in > +pg_subscription_rel > +system catalog and also the subscription's replication origin. This > allows > +logical replication on the new subscriber to continue from where the > +old subscriber was up to. Migration of subscription dependencies is only > +supported when the old cluster is version 17.0 or later. Subscription > +dependencies on clusters before version 17.0 will silently be ignored. > + > > Perhaps the part from "pg_upgrade attempts..." should be in a new paragraph. Modified Thanks for the comments, the attached v5 version patch has the changes for the same. Regards, Vignesh From 08d42bc4b8601c9af9302e9778523640a238f8d5 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Wed, 13 Dec 2023 14:11:58 +0530 Subject: [PATCH v5] Documentation for upgrading logical replication cluster. Documentation for upgrading logical replication cluster. --- doc/src/sgml/logical-replication.sgml | 810 ++ doc/src/sgml/ref/pgupgrade.sgml | 147 + 2 files changed, 829 insertions(+), 128 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ec2130669e..650a3151ff 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1926,6 +1926,816 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER + + Upgrade + + + Migration of logical replication clusters is possible only when all the + members of the old logical replication clusters are version 17.0 or later. + Before reading this section, refer page for + more details about pg_upgrade. + + + + Prepare for publisher upgrades + + +pg_upgrade attempts to migrate logical +slots. This helps avoid the need for manually defining the same +logical slots on the new publisher. Migration of logical slots is +only supported when the old cluster is version 17.0 or later. +Logical slots on clusters before version 17.0 will silently be +ignored. + + + +Before you start upgrading the publisher cluster, ensure that the +subscription is temporarily disabled, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + + + +There are some prerequisites for pg_upgrade to +be able to upgrade the logical slots. If these are not met an error +will be reported. + + + + + + The new cluster must have + wal_level as + logical. + + + + + The new cluster must have + max_replication_slots + configured to a value greater than or equal to the number of slots + present in the old cluster. + + + + + The output plugins referenced by the slots on the old cluster must be + installed in the new PostgreSQL executable + directory. + + + + + The old cluster has replicated all the transactions and logical decoding + messages to subscribers. + + + + + All slots on the old cluster must be usable, i.e., there are no slots + whose + pg_replication_slots.conflicting + is true. + + + + + The new cluster must not have permanent logical slots, i.e., + there must be no slots where + pg_replication_slots.temporary + is false. + + + + + + + Prepare for subscriber upgrades + + +Setup the +subscriber configurations in the new subscriber. + + + +pg_upgrade attempts to migrate subscription +
RE: Documentation to upgrade logical replication cluster
Dear Vignesh, Thanks for updating the patch! For now, v4 patch LGTM. Best Regards, Hayato Kuroda FUJITSU LIMITED https://www.fujitsu.com/
Re: Documentation to upgrade logical replication cluster
Hi Vignesh, Here are some review comments for patch v4. These are cosmetic only; otherwise v4 LGTM. == doc/src/sgml/ref/pgupgrade.sgml 1. Configure the servers for log shipping. (You do not need to run pg_backup_start() and pg_backup_stop() or take a file system backup as the standbys are still synchronized - with the primary.) Only logical slots on the primary are copied to the - new standby, but other slots on the old standby are not copied so must - be recreated manually. + with the primary.) If the old cluster is prior to 17.0, then no slots + on the primary are copied to the new standby, so all the slots must be + recreated manually. If the old cluster is 17.0 or later, then only + logical slots on the primary are copied to the new standby, but other + slots on the old standby are not copied so must be recreated manually. Perhaps the part from "If the old cluster is prior..." should be in a new paragraph. == doc/src/sgml/logical-replication.sgml 2. + +Setup the +subscriber configurations in the new subscriber. +pg_upgrade attempts to migrate subscription +dependencies which includes the subscription's table information present in +pg_subscription_rel +system catalog and also the subscription's replication origin. This allows +logical replication on the new subscriber to continue from where the +old subscriber was up to. Migration of subscription dependencies is only +supported when the old cluster is version 17.0 or later. Subscription +dependencies on clusters before version 17.0 will silently be ignored. + Perhaps the part from "pg_upgrade attempts..." should be in a new paragraph. == Kind Regards, Peter Smith. Fujitsu Australia
Re: Documentation to upgrade logical replication cluster
On Thu, 25 Jan 2024 at 05:45, Peter Smith wrote: > > Here are some review comments for patch v3. > > == > doc/src/sgml/ref/pgupgrade.sgml > > 1. > + > + > + This page does not cover steps to upgrade logical replication > clusters, refer > +for details on upgrading > + logical replication clusters. > + > + > > I felt that maybe this note was misplaced. Won't it be better to put > this down in the "Usage" section of this page? > > BEFORE > These are the steps to perform an upgrade with pg_upgrade: > > SUGGESTION (or something like this) > Below are the steps to perform an upgrade with pg_upgrade. > > Note, the steps to upgrade logical replication clusters are not > covered here; refer to > for details. Modified > ~~~ > > 2. > Configure the servers for log shipping. (You do not need to run > pg_backup_start() and > pg_backup_stop() > or take a file system backup as the standbys are still synchronized > - with the primary.) Only logical slots on the primary are copied to > the > - new standby, but other slots on the old standby are not copied so must > - be recreated manually. > + with the primary.) In version 17.0 or later, only logical slots on > the > + primary are copied to the new standby, but other slots on the > old standby > + are not copied so must be recreated manually. > > > This para was still unclear to me. What is version 17.0 referring to > -- the old_cluster version? Do you mean something like: > If the old cluster is < v17 then logical slots are not copied. If the > old_cluster is >= v17 then... Yes, I have rephrased it now. > == > doc/src/sgml/logical-replication.sgml > > 3. > + > +While upgrading a subscriber, write operations can be performed in the > +publisher, these changes will be replicated to the subscriber once the > +subscriber upgradation is completed. > + > > 3a. > /publisher, these changes/publisher. These changes/ Modified > ~ > > 3b. > "upgradation" ??. See [1] > > maybe just /upgradation/upgrade/ Modified > ~~~ > > 4. GENERAL - prompts/paths > > I noticed in v3 you removed all the cmd prompts like: > dba@node1:/opt/PostgreSQL/postgres/17/bin$ > dba@node2:/opt/PostgreSQL/postgres/18/bin$ > etc. > > I thought those were helpful to disambiguate which server/version was > being operated on. I wonder if there is some way to keep information > still but not make it look like a real current directory that > Kuroda-san did not like: > > e.g. Maybe something like the below is possible? > > (dba@node1: v17) pg_upgrade... > (dba@node2: v18) pg_upgrade... I did not want to add this as our current documentation is consistent with how it is documented in the pg_upgrade page at [1]. The v4 version patch attached at [2] has the changes for the same. [1] - https://www.postgresql.org/docs/devel/pgupgrade.html [2] - https://www.postgresql.org/message-id/CALDaNm1wCHmBwpLM%3Dd9oBoZqKXOe-TwC-LCcHC9gFy0bazZU6Q%40mail.gmail.com Regards, Vignesh
Re: Documentation to upgrade logical replication cluster
On Wed, 24 Jan 2024 at 15:16, Hayato Kuroda (Fujitsu) wrote: > > Dear Vignesh, > > Thanks for updating the patch! Basically your patch looks good. > Below lines are my comments for v3. > > 01. > > ``` > > The output plugins referenced by the slots on the old cluster must be > installed in the new PostgreSQL executable directory. > > ``` > > PostgreSQL must be marked as . Modified > 02. > > ``` > > pg_ctl -D /opt/PostgreSQL/data1 stop -l logfile > > ``` > > I checked that found that -l was no-op when `pg_ctl stop` was specified. Can > we remove? > The documentation is not listed -l for the stop command. > All the similar lines should be fixed as well. Modified > 03. > > ``` > On node3, create any tables that were created in > the upgraded node2 between > and now, > ``` > > If tables are newly defined on node1 between 1 - 11, they are not defined on > node3. > So they must be defined on node3 as well. The new node1 tables will be created in node2 in step-11. Since we have mentioned that, create the tables that were created between step 6 and now, all of node1 and node2 tables will get created > 04. > > ``` > > > ``` > > Even if the referred steps is correct, ID should be allocated to step, not > para. > That's why the rendering is bit a strange. Modified The attached v4 version patch has the changes for the same. Regards, Vignesh From 4abe4ed86d68a5ecb60c3bf29249bb883605fb76 Mon Sep 17 00:00:00 2001 From: Vignesh C Date: Wed, 13 Dec 2023 14:11:58 +0530 Subject: [PATCH v4] Documentation for upgrading logical replication cluster. Documentation for upgrading logical replication cluster. --- doc/src/sgml/logical-replication.sgml | 807 ++ doc/src/sgml/ref/pgupgrade.sgml | 143 + 2 files changed, 822 insertions(+), 128 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index ec2130669e..81501f9b92 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1926,6 +1926,813 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER + + Upgrade + + + Migration of logical replication clusters is possible only when all the + members of the old logical replication clusters are version 17.0 or later. + Before reading this section, refer page for + more details about pg_upgrade. + + + + Prepare for publisher upgrades + + +pg_upgrade attempts to migrate logical +slots. This helps avoid the need for manually defining the same +logical slots on the new publisher. Migration of logical slots is +only supported when the old cluster is version 17.0 or later. +Logical slots on clusters before version 17.0 will silently be +ignored. + + + +Before you start upgrading the publisher cluster, ensure that the +subscription is temporarily disabled, by executing +ALTER SUBSCRIPTION ... DISABLE. +Re-enable the subscription after the upgrade. + + + +There are some prerequisites for pg_upgrade to +be able to upgrade the logical slots. If these are not met an error +will be reported. + + + + + + The new cluster must have + wal_level as + logical. + + + + + The new cluster must have + max_replication_slots + configured to a value greater than or equal to the number of slots + present in the old cluster. + + + + + The output plugins referenced by the slots on the old cluster must be + installed in the new PostgreSQL executable + directory. + + + + + The old cluster has replicated all the transactions and logical decoding + messages to subscribers. + + + + + All slots on the old cluster must be usable, i.e., there are no slots + whose + pg_replication_slots.conflicting + is true. + + + + + The new cluster must not have permanent logical slots, i.e., + there must be no slots where + pg_replication_slots.temporary + is false. + + + + + + + Prepare for subscriber upgrades + + +Setup the +subscriber configurations in the new subscriber. +pg_upgrade attempts to migrate subscription +dependencies which includes the subscription's table information present in +pg_subscription_rel +system catalog and also the subscription's replication origin. This allows +logical replication on the new subscriber to continue from where the +old subscriber was up to. Migration of subscription dependencies is only +supported when the old cluster is version 17.0 or later. Subscription +dependencies on clusters before version 17.0 will silently be ignored. + + + +There are some prerequisites for pg_upgrade to +be able to upgrade the subscriptions. If these are not met an
Re: Documentation to upgrade logical replication cluster
Here are some review comments for patch v3. == doc/src/sgml/ref/pgupgrade.sgml 1. + + + This page does not cover steps to upgrade logical replication clusters, refer +for details on upgrading + logical replication clusters. + + I felt that maybe this note was misplaced. Won't it be better to put this down in the "Usage" section of this page? BEFORE These are the steps to perform an upgrade with pg_upgrade: SUGGESTION (or something like this) Below are the steps to perform an upgrade with pg_upgrade. Note, the steps to upgrade logical replication clusters are not covered here; refer to for details. ~~~ 2. Configure the servers for log shipping. (You do not need to run pg_backup_start() and pg_backup_stop() or take a file system backup as the standbys are still synchronized - with the primary.) Only logical slots on the primary are copied to the - new standby, but other slots on the old standby are not copied so must - be recreated manually. + with the primary.) In version 17.0 or later, only logical slots on the + primary are copied to the new standby, but other slots on the old standby + are not copied so must be recreated manually. This para was still unclear to me. What is version 17.0 referring to -- the old_cluster version? Do you mean something like: If the old cluster is < v17 then logical slots are not copied. If the old_cluster is >= v17 then... == doc/src/sgml/logical-replication.sgml 3. + +While upgrading a subscriber, write operations can be performed in the +publisher, these changes will be replicated to the subscriber once the +subscriber upgradation is completed. + 3a. /publisher, these changes/publisher. These changes/ ~ 3b. "upgradation" ??. See [1] maybe just /upgradation/upgrade/ ~~~ 4. GENERAL - prompts/paths I noticed in v3 you removed all the cmd prompts like: dba@node1:/opt/PostgreSQL/postgres/17/bin$ dba@node2:/opt/PostgreSQL/postgres/18/bin$ etc. I thought those were helpful to disambiguate which server/version was being operated on. I wonder if there is some way to keep information still but not make it look like a real current directory that Kuroda-san did not like: e.g. Maybe something like the below is possible? (dba@node1: v17) pg_upgrade... (dba@node2: v18) pg_upgrade... == [1] https://english.stackexchange.com/questions/192187/upgradation-not-universally-accepted#:~:text=Not%20all%20dictionaries%20(or%20native,by%20most%20non%2DIE%20speakers. Kind Regards, Peter Smith. Fujitsu Australia
RE: Documentation to upgrade logical replication cluster
Dear Vignesh, Thanks for updating the patch! Basically your patch looks good. Below lines are my comments for v3. 01. ``` The output plugins referenced by the slots on the old cluster must be installed in the new PostgreSQL executable directory. ``` PostgreSQL must be marked as . 02. ``` pg_ctl -D /opt/PostgreSQL/data1 stop -l logfile ``` I checked that found that -l was no-op when `pg_ctl stop` was specified. Can we remove? The documentation is not listed -l for the stop command. All the similar lines should be fixed as well. 03. ``` On node3, create any tables that were created in the upgraded node2 between and now, ``` If tables are newly defined on node1 between 1 - 11, they are not defined on node3. So they must be defined on node3 as well. 04. ``` ``` Even if the referred steps is correct, ID should be allocated to step, not para. That's why the rendering is bit a strange. Best Regards, Hayato Kuroda FUJITSU LIMITED
Re: Documentation to upgrade logical replication cluster
On Mon, 15 Jan 2024 at 14:39, Hayato Kuroda (Fujitsu) wrote: > > Dear Vignesh, > > Thanks for updating the patch! > > > > 7. > > > ``` > > > + > > > +dba@node1:/opt/PostgreSQL/postgres//bin$ pg_ctl -D > > /opt/PostgreSQL/pub_data stop -l logfile > > > + > > > ``` > > > > > > Hmm. I thought you did not have to show the current directory. You were > > > in the > > > bin dir, but it is not our requirement, right? > > > > I kept this just to show the version being used > > > > Hmm, but by default, the current directory is not set as PATH. So this example > looks strange for me. I have removed the paths shown to avoid confusion. > Below lines are my comments for v2 patch. > > 01. > > ``` > + > +Upgrade logical replication clusters > + > + > + Refer logical replication > upgrade section > + for details on upgrading logical replication clusters. > + > + > + > ``` > > I think we do not have to write it as one of steps. I think we can move to > "Usage" part and modify like: > > This page only focus on nodes which are not logical replication participant. > See > for upgrading such nodes. I have removed it from usage and moved it to the description section. > 02. > > ``` >with the primary.) Only logical slots on the primary are copied to the >new standby, but other slots on the old standby are not copied so must >be recreated manually. > ``` > > A description for logical slots were remained. If you want to keep, we must > say that it would be done for PG17+. Mentioned as 17 or later. > 03. > > I think the numbering seems bit confusing. sectX sgml tags should be used in > this case. How about formatting like below? > > Upgrade (sect1) > --- Prerequisites (sect2) > --- For upgrading a publisher node (sect3) > --- For upgrading a subscriber node (sect3) > --- Examples (sect2) > --- Two-node logical replication cluster (sect3) > --- Cascaded logical replication cluster (sect3) > --- Two-node circular logical replication cluster (sect3) I felt this is better and changed it like: 30.11. Upgrade --- 30.11.1. Prepare for publisher upgrades --- 30.11.2. Prepare for subscriber upgrades --- 30.11.3. Upgrading logical replication clusters --- 30.11.3.1. Steps to upgrade a two-node logical replication cluster --- 30.11.3.2. Steps to upgrade a cascaded logical replication cluster --- 30.11.3.3. Steps to upgrade a two-node circular logical replication cluster > 04. > > Missing introduction in the head of this section. E.g., > > Both publishers and subscribers can be upgraded, but there are some notes. > Before reading this section, you should read page. Added it with slight changes > 05. > > ``` > + > +Prepare for publisher upgrades > ... > ``` > > Should we describe in this page that publications can be upgraded in any > versions? I felt that need not be mentioned, as these are being upgraded from earlier versions too > 06. > > ``` > + > +Prepare for subscriber upgrades ``` > > Same as 5, should we describe in this page that subscriptions can be upgraded > in any versions? I felt that need not be mentioned, as these are being upgraded from earlier versions too > 07. > > Basic considerations should be described before describing concrete steps. The steps clearly mention the order in which it should be upgraded, I'm not sure if we should repeat it again. > E.g., publishers must be upgraded first. Also: While upgrading a subscriber, > publisher can accept changes from users. I have added this. > 08. > > ``` > + two subscriptions sub1_node1_node2 and sub2_node1_node2 which is > + subscribing the changes from node1. > ``` > > Both "sub1_node1_node2" and "sub2_node1_node2" must be rendered. Modified > 09. > > ``` > + > + > + Initialize data1_upgraded instance by using the required newer > + version. > + > ``` > > Missing rendering. All similar paragraphs must be fixed. Modified > 10. > > ``` > + On node2, create any tables that were created in > + the upgraded publisher node1 server between > + > + when the subscriptions where disabled in > node2 > + and now, e.g.: > ``` > > a. > > I think the link is not correct, it should refer Step 6. Can we add the step > number? > All similar paragraphs must be fixed. I have kept it as step1 just in case any table is created before the server is stopped in node1. So I felt it is better to refer to the step of disabled subscription now. > b. > > Not sure, but s/where disabled/were disabled/ ? > All similar paragraphs must be fixed. This is removed > 11. > > ``` > + > + Refresh the node2 subscription's publications > using > + linkend="sql-altersubscription-params-refresh-publication">ALTER > SUBSCRIPTION ... REFRESH PUBLICATION, > + e.g.: > + > +node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; > +ALTER SUBSCRIPTION >
Re: Documentation to upgrade logical replication cluster
On Mon, 15 Jan 2024 at 09:01, Peter Smith wrote: > > Hi Vignesh, here are some review comments for patch v2-0001. > > == > doc/src/sgml/ref/pgupgrade.sgml > > 1. > + > +Upgrade logical replication clusters > + > + > + Refer logical > replication upgrade section > + for details on upgrading logical replication clusters. > + > + > + > + > > This renders like: > Refer logical replication upgrade section for details on upgrading > logical replication clusters. > > ~ > > IMO it would be better to use xref instead of link, which will render > more normally like: > See Section 30.11 for details on upgrading logical replication clusters. > > SUGGESTION > > See > for details on upgrading logical replication clusters. > Modified > == > doc/src/sgml/logical-replication.sgml > > 2. GENERAL - blurb > > + > + Upgrade > + > + > + > +Prepare for publisher upgrades > > I felt there should be a short (1 or 2 sentence) general blurb about > pub/sub upgrade before jumping straight into: > > "1. Prepare for publisher upgrades" > "2. Prepare for subscriber upgrades" > "3. Upgrading logical replication cluster" Added > ~ > > Specifically, at first, it looks strange that the HTML renders as > steps 1,2,3 instead of sub-sections (30.11.1, 30.11.2, 30.11.3); Maybe > "steps" are fine, but then at least there needs to be some intro > sentence saying like "follow these steps:" > ~~~ Modified > > 3. > + > +Upgrading logical replication cluster > > /cluster/clusters/ Modified > ~~~ > > 4. > + > + The steps to upgrade the following logical replication clusters are > + detailed below: > + > + > + > +Two-node > logical replication cluster. > + > + > + > + > +Cascaded > logical replication cluster. > + > + > + > + > + linkend="steps-two-node-circular-logical-replication-cluster">Two-node > circular logical replication cluster. > + > + > + > + > > Isn't there a better way to accomplish this by using xref and > 'xreflabel' so you don't have to type the link text here? Modified > > // > Steps to upgrade a two-node logical replication cluster > // > > 5. > + > + Let's say publisher is in node1 and subscriber is > + in node2. The subscriber node2 > has > + two subscriptions sub1_node1_node2 and sub2_node1_node2 which is > + subscribing the changes from node1. > + > > 5a > Those subscription names should also be rendered as literals. Modified > ~ > > 5b > /which is/which are/ Modified > ~~~ > > 6. > + > + > + Initialize data1_upgraded instance by using the required newer > + version. > + > + > > data1_upgraded should be rendered as literal. Modified > ~~~ > > 7. > + > + > + > + Initialize data2_upgraded instance by using the required newer > + version. > + > + > > data2_upgraded should be rendered as literal. Modified > ~~~ > > 8. > + > + > + > + On node2, create any tables that were created in > + the upgraded publisher node1 server between > + > + when the subscriptions where disabled in > node2 > + and now, e.g.: > + > +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name > varchar(40)); > +CREATE TABLE > + > + > + > > 8a. > This link to the earlier step renders badly like: > On node2, create any tables that were created in the upgraded > publisher node1 server between when the subscriptions where disabled > in node2 and now, e.g.: > > IMO this link should be like "Step N", not some words -- maybe it is > another opportunity for using xreflabel? Modified > ~ > > 8b. > Also has typos "when the subscriptions where disabled" (??) This is not required after using xref, removed it. > // > Steps to upgrade a cascaded logical replication clusters > // > > 9. > + > + > + Steps to upgrade a cascaded logical replication clusters > > The title has a strange mix of singular "a" and plural "clusters" Changed it to keep it consistent > ~~~ > > 10. > + > + Let's say we have a cascaded logical replication setup > + > node1->node2->node3. > + Here node2 is subscribing the changes from > + node1 and node3 is subscribing > + the changes from node2. The > node2 > + has two subscriptions sub1_node1_node2 and sub2_node1_node2 which is > + subscribing the changes from node1. The > + node3 has two subscriptions sub1_node2_node3 and > + sub2_node2_node3 which is subscribing the changes from > + node2. > + > > 10a. > Those subscription names should also be rendered as literals. Modified > ~ > > 10b. > /which is/which are/ (occurs 2x) Modified > ~~~ > > 11. > + > + > + > + Initialize
RE: Documentation to upgrade logical replication cluster
Dear Vignesh, Thanks for updating the patch! > > 7. > > ``` > > + > > +dba@node1:/opt/PostgreSQL/postgres//bin$ pg_ctl -D > /opt/PostgreSQL/pub_data stop -l logfile > > + > > ``` > > > > Hmm. I thought you did not have to show the current directory. You were in > > the > > bin dir, but it is not our requirement, right? > > I kept this just to show the version being used > Hmm, but by default, the current directory is not set as PATH. So this example looks strange for me. Below lines are my comments for v2 patch. 01. ``` + +Upgrade logical replication clusters + + + Refer logical replication upgrade section + for details on upgrading logical replication clusters. + + + ``` I think we do not have to write it as one of steps. I think we can move to "Usage" part and modify like: This page only focus on nodes which are not logical replication participant. See for upgrading such nodes. 02. ``` with the primary.) Only logical slots on the primary are copied to the new standby, but other slots on the old standby are not copied so must be recreated manually. ``` A description for logical slots were remained. If you want to keep, we must say that it would be done for PG17+. 03. I think the numbering seems bit confusing. sectX sgml tags should be used in this case. How about formatting like below? Upgrade (sect1) --- Prerequisites (sect2) --- For upgrading a publisher node (sect3) --- For upgrading a subscriber node (sect3) --- Examples (sect2) --- Two-node logical replication cluster (sect3) --- Cascaded logical replication cluster (sect3) --- Two-node circular logical replication cluster (sect3) 04. Missing introduction in the head of this section. E.g., Both publishers and subscribers can be upgraded, but there are some notes. Before reading this section, you should read page. 05. ``` + +Prepare for publisher upgrades ... ``` Should we describe in this page that publications can be upgraded in any versions? 06. ``` + +Prepare for subscriber upgradesnode1. ``` Both "sub1_node1_node2" and "sub2_node1_node2" must be rendered. 09. ``` + + + Initialize data1_upgraded instance by using the required newer + version. + ``` Missing rendering. All similar paragraphs must be fixed. 10. ``` + On node2, create any tables that were created in + the upgraded publisher node1 server between + + when the subscriptions where disabled in node2 + and now, e.g.: ``` a. I think the link is not correct, it should refer Step 6. Can we add the step number? All similar paragraphs must be fixed. b. Not sure, but s/where disabled/were disabled/ ? All similar paragraphs must be fixed. 11. ``` + + Refresh the node2 subscription's publications using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; +ALTER SUBSCRIPTION +node2=# ALTER SUBSCRIPTION sub2_node1_node2 REFRESH PUBLICATION; +ALTER SUBSCRIPTION + + ``` Not sure, but should we clarify that copy_data must be on? 12. ``` + has two subscriptions sub1_node1_node2 and sub2_node1_node2 which is + subscribing the changes from node1. The + node3 has two subscriptions sub1_node2_node3 and + sub2_node2_node3 which is subscribing the changes from ``` Name of subscriptions must be rendered. 13. ``` + + On node1, Create any tables that were created in + node2 between + when the subscriptions where disabled in node2 + and now, e.g.: + +node1=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + ... + + On node2, Create any tables that were created in + the upgraded node1 between + when the subscriptions where disabled in node1 + and now, e.g.: + +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + ``` Same tables were created, they must have another name. Best Regards, Hayato Kuroda FUJITSU LIMITED
Re: Documentation to upgrade logical replication cluster
Hi Vignesh, here are some review comments for patch v2-0001. == doc/src/sgml/ref/pgupgrade.sgml 1. + +Upgrade logical replication clusters + + + Refer logical replication upgrade section + for details on upgrading logical replication clusters. + + + + This renders like: Refer logical replication upgrade section for details on upgrading logical replication clusters. ~ IMO it would be better to use xref instead of link, which will render more normally like: See Section 30.11 for details on upgrading logical replication clusters. SUGGESTION See for details on upgrading logical replication clusters. == doc/src/sgml/logical-replication.sgml 2. GENERAL - blurb + + Upgrade + + + +Prepare for publisher upgrades I felt there should be a short (1 or 2 sentence) general blurb about pub/sub upgrade before jumping straight into: "1. Prepare for publisher upgrades" "2. Prepare for subscriber upgrades" "3. Upgrading logical replication cluster" ~ Specifically, at first, it looks strange that the HTML renders as steps 1,2,3 instead of sub-sections (30.11.1, 30.11.2, 30.11.3); Maybe "steps" are fine, but then at least there needs to be some intro sentence saying like "follow these steps:" ~~~ 3. + +Upgrading logical replication cluster /cluster/clusters/ ~~~ 4. + + The steps to upgrade the following logical replication clusters are + detailed below: + + + +Two-node logical replication cluster. + + + + +Cascaded logical replication cluster. + + + + +Two-node circular logical replication cluster. + + + + Isn't there a better way to accomplish this by using xref and 'xreflabel' so you don't have to type the link text here? // Steps to upgrade a two-node logical replication cluster // 5. + + Let's say publisher is in node1 and subscriber is + in node2. The subscriber node2 has + two subscriptions sub1_node1_node2 and sub2_node1_node2 which is + subscribing the changes from node1. + 5a Those subscription names should also be rendered as literals. ~ 5b /which is/which are/ ~~~ 6. + + + Initialize data1_upgraded instance by using the required newer + version. + + data1_upgraded should be rendered as literal. ~~~ 7. + + + + Initialize data2_upgraded instance by using the required newer + version. + + data2_upgraded should be rendered as literal. ~~~ 8. + + + + On node2, create any tables that were created in + the upgraded publisher node1 server between + + when the subscriptions where disabled in node2 + and now, e.g.: + +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + 8a. This link to the earlier step renders badly like: On node2, create any tables that were created in the upgraded publisher node1 server between when the subscriptions where disabled in node2 and now, e.g.: IMO this link should be like "Step N", not some words -- maybe it is another opportunity for using xreflabel? ~ 8b. Also has typos "when the subscriptions where disabled" (??) // Steps to upgrade a cascaded logical replication clusters // 9. + + + Steps to upgrade a cascaded logical replication clusters The title has a strange mix of singular "a" and plural "clusters" ~~~ 10. + + Let's say we have a cascaded logical replication setup + node1->node2->node3. + Here node2 is subscribing the changes from + node1 and node3 is subscribing + the changes from node2. The node2 + has two subscriptions sub1_node1_node2 and sub2_node1_node2 which is + subscribing the changes from node1. The + node3 has two subscriptions sub1_node2_node3 and + sub2_node2_node3 which is subscribing the changes from + node2. + 10a. Those subscription names should also be rendered as literals. ~ 10b. /which is/which are/ (occurs 2x) ~~~ 11. + + + + Initialize data1_upgraded instance by using the required newer version. + + data1_upgraded should be rendered as literal. ~~~ 12. + + + + Initialize data2_upgraded instance by using the required newer version. + + data2_upgraded should be rendered as literal. ~~~ 13. + + + + On node2, create any tables that were created in + the upgraded publisher node1 server between + + when the subscriptions where disabled in node2 + and now, e.g.: + +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + 13a. This link to the earlier step renders badly like: On
Re: Documentation to upgrade logical replication cluster
On Fri, 5 Jan 2024 at 10:49, Peter Smith wrote: > > Here are some review comments for patch v1-0001. > > == > doc/src/sgml/ref/pgupgrade.sgml > > 1. GENERAL - blank lines > > Most (but not all) of your procedure steps are preceded by blank lines > to make them more readable in the SGML. Add the missing blank lines > for the steps that didn't have them. Modified > 2. GENERAL - for e.g.: > > All the "for e.g:" that precedes the code examples can just say > "e.g.:" like in other examples on this page. Modified > ~~~ > 3. GENERAL - reference from elsewhere > > I was wondering if "Chapter 30. Logical Replication" should include a > section that references back to all this just to make it easier to > find. I have moved this to Chapter 30 now as it is more applicable there and also based on feedback from Amit at [1]. > ~~~ > > 4. > + > + Migration of logical replication clusters can be done when all the > members > + of the old logical replication clusters are version 17.0 or later. > + > > /can be done when/is possible only when/ Modified > ~~~ > > 5. > + > + The prerequisites of publisher upgrade applies to logical Replication > + cluster upgrades also. See > + for the details of publisher upgrade prerequisites. > + > > /applies to/apply to/ > /logical Replication/logical replication/ Modified > ~~~ > > 6. > + > + The prerequisites of subscriber upgrade applies to logical Replication > + cluster upgrades also. See > + for the details of subscriber upgrade prerequisites. > + > + > > /applies to/apply to/ > /logical Replication/logical replication/ Modified > ~~~ > > 7. > + > + The steps to upgrade logical replication clusters in various scenarios > are > + given below. > + > > The 3 titles do not render very prominently, so it is too easy to get > lost scrolling up and down looking for the different scenarios. If the > title rendering can't be improved, at least a list of 3 links here > (like a TOC) would be helpful. I added a list of these 3 links in the beginning. > ~~~ > > // > Steps to Upgrade 2 node logical replication cluster > // > > 8. GENERAL - server names > > I noticed in this set of steps you called the servers 'pub_data' and > 'pub_upgraded_data' and 'sub_data' and 'sub_upgraded_data'. I see it > is easy to read like this, it is also different from all the > subsequent procedures where the names are just like 'data1', 'data2', > 'data3', and 'data1_upgraded', 'data2_upgraded', 'data3_upgraded'. > > I felt maybe it is better to use a consistent naming for all the procedures. Modified > ~~~ > > 9. > + > + Steps to Upgrade 2 node logical replication cluster > > SUGGESTION > Steps to upgrade a two-node logical replication cluster Modified > ~~~ > > 10. > + > + > + > + > + Let's say publisher is in node1 and subscriber > is > + in node2. > + > + > > 10a. > This renders as Step 1. But IMO this should not be a "step" at all -- > it's just a description of the scenario. Modified > ~ > > 10b. > The subsequent steps refer to subscriptions 'sub1_node1_node2' and > 'sub2_node1_node2'. IMO it would help with the example code if those > are named up front here too. e.g. > > node2 has two subscriptions for changes from node1: > sub1_node1_node2 > sub2_node1_node2 Modified > ~~~ > > 11. > + > + > + Upgrade the publisher node node1's server to the > + required newer version, for e.g.: > > The wording repeating node/node1 seems complicated. > > SUGGESTION > Upgrade the publisher node's server to the required newer version, e.g.: Modified > ~~~ > > 12. > + > + > + Start the upgraded publisher node > node1's server, for e.g.: > > IMO better to use the similar wording used for the "Stop" step > > SUGGESTION > Start the upgraded publisher server in node1, e.g.: Modified > ~~~ > > 13. > + > + > + Upgrade the subscriber node node2's server to > + the required new version, for e.g.: > > The wording repeating node/node2 seems complicated. > > SUGGESTION > Upgrade the subscriber node's server to the required newer version, e.g.: Modified > ~~~ > > 14. > + > + > + Start the upgraded subscriber node node2's > server, > + for e.g.: > > IMO better to use the similar wording used for the "Stop" step > > SUGGESTION > Start the upgraded subscriber server in node2, e.g.: Modified > ~~~ > > 15. > + > + > + Create any tables that were created in the upgraded > publisher node1 > + server between step-5 and now, for e.g.: > + > +node2=# CREATE TABLE distributors ( > +node2(# did integer CONSTRAINT no_null NOT NULL, > +node2(# name varchar(40) NOT NULL > +node2(# ); > +CREATE TABLE > + > + > + > > 15a > Maybe it is better to have a link to setp5 instead of
Re: Documentation to upgrade logical replication cluster
On Fri, 5 Jan 2024 at 09:08, Hayato Kuroda (Fujitsu)
wrote:
>
> Dear Vignesh,
>
> Thanks for making a patch! Below part is my comments.
>
> 1.
> Only two steps were added an id, but I think it should be for all the steps.
> See [1].
I have added wherever it is required as of now.
> 2.
> I'm not sure it should be listed as step 10. I felt that it should be new
> section.
> At that time other steps like "Prepare for {publisher|subscriber} upgrades"
> can be moved as well.
> Thought?
I have moved all of these to a separate page in logical-replication
under Upgrade
> 3.
> ```
> + The prerequisites of publisher upgrade applies to logical Replication
> ```
>
> Replication -> replication
Modified
> 4.
> ```
> +
> + Let's say publisher is in node1 and subscriber
> is
> + in node2.
> +
> ```
>
> I felt it is more friendly if you added the name of directory for each
> instance.
I have listed this in the pg_upgrade command execution, since it is
mentioned there I have not added here too.
> 5.
> You did not write the initialization of new node. Was it intentional?
Added it now
> 6.
> ```
> +
> + Disable all the subscriptions on node2 that are
> + subscribing the changes from node1 by using
> + linkend="sql-altersubscription-params-disable">ALTER SUBSCRIPTION
> ... DISABLE,
> + for e.g.:
> +
> +node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node1_node2 DISABLE;
> +ALTER SUBSCRIPTION
> +
> +
> ```
>
> Subscriptions are disabled after stopping a publisher, but it leads ERRORs on
> the publisher.
> I think it's better to swap these steps.
Modified
> 7.
> ```
> +
> +dba@node1:/opt/PostgreSQL/postgres//bin$ pg_ctl -D
> /opt/PostgreSQL/pub_data stop -l logfile
> +
> ```
>
> Hmm. I thought you did not have to show the current directory. You were in the
> bin dir, but it is not our requirement, right?
I kept this just to show the version being used
> 8.
> ```
> +
> +dba@node1:/opt/PostgreSQL/postgres//bin$ pg_upgrade
> +--old-datadir "/opt/PostgreSQL/postgres/17/pub_data"
> +--new-datadir
> "/opt/PostgreSQL/postgres//pub_upgraded_data"
> +--old-bindir "/opt/PostgreSQL/postgres/17/bin"
> +--new-bindir "/opt/PostgreSQL/postgres//bin"
> +
> ```
>
> For PG17, both old and new bindir look the same. Can we use 18 as new-bindir?
Modfied
> 9.
> ```
> +
> + Create any tables that were created in node2
> + between step-2 and now, for e.g.:
> +
> +node2=# CREATE TABLE distributors (
> +node2(# did integer CONSTRAINT no_null NOT NULL,
> +node2(# name varchar(40) NOT NULL
> +node2(# );
> +CREATE TABLE
> +
> +
> ```
>
> I think this SQLs must be done on node1, because it has not boot between
> step-2
> and step-7.
Modified
> 10.
> ```
> +
> +
> + Enable all the subscriptions on node2 that are
> + subscribing the changes from node1 by using
> + ALTER
> SUBSCRIPTION ... ENABLE,
> + for e.g.:
> +
> +node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node1_node2 ENABLE;
> +ALTER SUBSCRIPTION
> +
> +
> +
> +
> +
> +
> + Refresh the publications using
> + linkend="sql-altersubscription-params-refresh-publication">ALTER
> SUBSCRIPTION ... REFRESH PUBLICATION,
> + for e.g.:
> +
> +node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node1_node2 REFRESH PUBLICATION;
> +ALTER SUBSCRIPTION
> +
> +
> +
> ```
>
> I was very confused the location where they would be really do. If my above
> comment is correct, should they be executed on node1 as well? Could you
> please all
> the notation again?
Modified
> 11.
> ```
> +
> + Disable all the subscriptions on node1 that are
> + subscribing the changes from node2 by using
> + linkend="sql-altersubscription-params-disable">ALTER SUBSCRIPTION
> ... DISABLE,
> + for e.g.:
> +
> +node2=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node2_node1 DISABLE;
> +ALTER SUBSCRIPTION
> +
> +
> ```
>
> They should be on node1, but noted as node2.
Modified
> 12.
> ```
> +
> + Enable all the subscriptions on node1 that are
> + subscribing the changes from node2 by using
> + ALTER
> SUBSCRIPTION ... ENABLE,
> + for e.g.:
> +
> +node2=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node2_node1 ENABLE;
> +ALTER SUBSCRIPTION
> +
> +
> ```
>
> You said that "enable all the subscription on node1", but SQLs are done on
> node2.
Modified
Thanks for the comments, the attached v2 version patch has
Re: Documentation to upgrade logical replication cluster
On Wed, 10 Jan 2024 at 15:50, Amit Kapila wrote: > > On Thu, Jan 4, 2024 at 2:22 PM vignesh C wrote: > > > > We have documentation on how to upgrade "publisher" and "subscriber" > > at [1], but currently we do not have any documentation on how to > > upgrade logical replication clusters. > > Here is a patch to document how to upgrade different logical > > replication clusters: a) Upgrade 2 node logical replication cluster b) > > Upgrade cascaded logical replication cluster c) Upgrade 2 node > > circular logical replication cluster. > > > > Today, off-list, I had a short discussion on this documentation with > Jonathan and Michael. I was not sure whether we should add this in the > main documentation of the upgrade or maintain it as a separate wiki > page. My primary worry was that this seemed to be taking too much > space on pgupgrade page and making the information on that page a bit > unreadable. Jonathan suggested that we can add this information to the > logical replication page [1] and add a reference in the pgupgrade > page. That suggestion makes sense to me considering we have > sub-sections like Monitoring, Security, and Configuration Settings on > the logical replication page. We can have a new sub-section Upgrade on > the same lines. What do you think? I feel that would be better, also others like Kuroda-san had said in the similar lines at comment-2 at [1] and Peter also had similar opinion at [2]. I will handle this in the next version. [1] - https://www.postgresql.org/message-id/TY3PR01MB9889BD1202530E8310AC9B3DF5662%40TY3PR01MB9889.jpnprd01.prod.outlook.com [2] - https://www.postgresql.org/message-id/CAHut%2BPs4AtGB9MMK51%3D1Z1JQ1FUK%2BX0oXQuAdEad1kEEuw7%2BkA%40mail.gmail.com Regards, Vignesh
Re: Documentation to upgrade logical replication cluster
On Mon, Jan 8, 2024 at 12:52 PM Bharath Rupireddy wrote: > > On Thu, Jan 4, 2024 at 2:22 PM vignesh C wrote: > > > > Hi, > > > > We have documentation on how to upgrade "publisher" and "subscriber" > > at [1], but currently we do not have any documentation on how to > > upgrade logical replication clusters. > > Here is a patch to document how to upgrade different logical > > replication clusters: a) Upgrade 2 node logical replication cluster b) > > Upgrade cascaded logical replication cluster c) Upgrade 2 node > > circular logical replication cluster. > > Thoughts? > > > > [1] - https://www.postgresql.org/docs/devel/pgupgrade.html > > Thanks for this. It really helps developers a lot. In addition to the > docs, why can't all of these steps be put into a perl/shell script or > a C tool sitting in the src/bin directory? > > I prefer a postgres src/bin tool which takes publisher and subscriber > connection strings as the inputs, talks to them and upgrades both > publisher and subscriber. Of course, one can write such a tool outside > of postgres in their own programming language, but the capability to > upgrade postgres servers with logical replication is such an important > task one would often require it. Therefore, an off-the-shelf tool not > only avoids manual efforts but makes it effortless for the users, > after all, if any of the steps isn't performed as stated in the docs > the servers may end up in an inconsistent state. > This idea has merits but not sure if we just add a few tests that users can refer to if they want or provide a utility as you described. I would prefer a test or two for now and if there is a demand then we can consider having such a utility. In either case, I feel it is better discussed in a separate thread. -- With Regards, Amit Kapila.
Re: Documentation to upgrade logical replication cluster
On Thu, Jan 4, 2024 at 2:22 PM vignesh C wrote: > > We have documentation on how to upgrade "publisher" and "subscriber" > at [1], but currently we do not have any documentation on how to > upgrade logical replication clusters. > Here is a patch to document how to upgrade different logical > replication clusters: a) Upgrade 2 node logical replication cluster b) > Upgrade cascaded logical replication cluster c) Upgrade 2 node > circular logical replication cluster. > Today, off-list, I had a short discussion on this documentation with Jonathan and Michael. I was not sure whether we should add this in the main documentation of the upgrade or maintain it as a separate wiki page. My primary worry was that this seemed to be taking too much space on pgupgrade page and making the information on that page a bit unreadable. Jonathan suggested that we can add this information to the logical replication page [1] and add a reference in the pgupgrade page. That suggestion makes sense to me considering we have sub-sections like Monitoring, Security, and Configuration Settings on the logical replication page. We can have a new sub-section Upgrade on the same lines. What do you think? [1] - https://www.postgresql.org/docs/devel/logical-replication.html -- With Regards, Amit Kapila.
Re: Documentation to upgrade logical replication cluster
On Thu, Jan 4, 2024 at 2:22 PM vignesh C wrote: > > Hi, > > We have documentation on how to upgrade "publisher" and "subscriber" > at [1], but currently we do not have any documentation on how to > upgrade logical replication clusters. > Here is a patch to document how to upgrade different logical > replication clusters: a) Upgrade 2 node logical replication cluster b) > Upgrade cascaded logical replication cluster c) Upgrade 2 node > circular logical replication cluster. > Thoughts? > > [1] - https://www.postgresql.org/docs/devel/pgupgrade.html Thanks for this. It really helps developers a lot. In addition to the docs, why can't all of these steps be put into a perl/shell script or a C tool sitting in the src/bin directory? I prefer a postgres src/bin tool which takes publisher and subscriber connection strings as the inputs, talks to them and upgrades both publisher and subscriber. Of course, one can write such a tool outside of postgres in their own programming language, but the capability to upgrade postgres servers with logical replication is such an important task one would often require it. Therefore, an off-the-shelf tool not only avoids manual efforts but makes it effortless for the users, after all, if any of the steps isn't performed as stated in the docs the servers may end up in an inconsistent state. Thoughts? -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: Documentation to upgrade logical replication cluster
On Fri, Jan 5, 2024 at 2:38 PM Hayato Kuroda (Fujitsu)
wrote:
...
> 2.
> I'm not sure it should be listed as step 10. I felt that it should be new
> section.
> At that time other steps like "Prepare for {publisher|subscriber} upgrades"
> can be moved as well.
> Thought?
During my review, I also felt that step 10 is now so long that it is a
distraction from the other content on this page.
==
Kind Regards,
Peter Smith.
Fujitsu Australia
Re: Documentation to upgrade logical replication cluster
Here are some review comments for patch v1-0001. == doc/src/sgml/ref/pgupgrade.sgml 1. GENERAL - blank lines Most (but not all) of your procedure steps are preceded by blank lines to make them more readable in the SGML. Add the missing blank lines for the steps that didn't have them. 2. GENERAL - for e.g.: All the "for e.g:" that precedes the code examples can just say "e.g.:" like in other examples on this page. ~~~ 3. GENERAL - reference from elsewhere I was wondering if "Chapter 30. Logical Replication" should include a section that references back to all this just to make it easier to find. ~~~ 4. + + Migration of logical replication clusters can be done when all the members + of the old logical replication clusters are version 17.0 or later. + /can be done when/is possible only when/ ~~~ 5. + + The prerequisites of publisher upgrade applies to logical Replication + cluster upgrades also. See + for the details of publisher upgrade prerequisites. + /applies to/apply to/ /logical Replication/logical replication/ ~~~ 6. + + The prerequisites of subscriber upgrade applies to logical Replication + cluster upgrades also. See + for the details of subscriber upgrade prerequisites. + + /applies to/apply to/ /logical Replication/logical replication/ ~~~ 7. + + The steps to upgrade logical replication clusters in various scenarios are + given below. + The 3 titles do not render very prominently, so it is too easy to get lost scrolling up and down looking for the different scenarios. If the title rendering can't be improved, at least a list of 3 links here (like a TOC) would be helpful. ~~~ // Steps to Upgrade 2 node logical replication cluster // 8. GENERAL - server names I noticed in this set of steps you called the servers 'pub_data' and 'pub_upgraded_data' and 'sub_data' and 'sub_upgraded_data'. I see it is easy to read like this, it is also different from all the subsequent procedures where the names are just like 'data1', 'data2', 'data3', and 'data1_upgraded', 'data2_upgraded', 'data3_upgraded'. I felt maybe it is better to use a consistent naming for all the procedures. ~~~ 9. + + Steps to Upgrade 2 node logical replication cluster SUGGESTION Steps to upgrade a two-node logical replication cluster ~~~ 10. + + + + + Let's say publisher is in node1 and subscriber is + in node2. + + 10a. This renders as Step 1. But IMO this should not be a "step" at all -- it's just a description of the scenario. ~ 10b. The subsequent steps refer to subscriptions 'sub1_node1_node2' and 'sub2_node1_node2'. IMO it would help with the example code if those are named up front here too. e.g. node2 has two subscriptions for changes from node1: sub1_node1_node2 sub2_node1_node2 ~~~ 11. + + + Upgrade the publisher node node1's server to the + required newer version, for e.g.: The wording repeating node/node1 seems complicated. SUGGESTION Upgrade the publisher node's server to the required newer version, e.g.: ~~~ 12. + + + Start the upgraded publisher node node1's server, for e.g.: IMO better to use the similar wording used for the "Stop" step SUGGESTION Start the upgraded publisher server in node1, e.g.: ~~~ 13. + + + Upgrade the subscriber node node2's server to + the required new version, for e.g.: The wording repeating node/node2 seems complicated. SUGGESTION Upgrade the subscriber node's server to the required newer version, e.g.: ~~~ 14. + + + Start the upgraded subscriber node node2's server, + for e.g.: IMO better to use the similar wording used for the "Stop" step SUGGESTION Start the upgraded subscriber server in node2, e.g.: ~~~ 15. + + + Create any tables that were created in the upgraded publisher node1 + server between step-5 and now, for e.g.: + +node2=# CREATE TABLE distributors ( +node2(# did integer CONSTRAINT no_null NOT NULL, +node2(# name varchar(40) NOT NULL +node2(# ); +CREATE TABLE + + + 15a Maybe it is better to have a link to setp5 instead of just hardwiring "Step-5" in the text. ~ 15b. I didn't think it was needed to spread the CREATE TABLE across multiple lines. It is just a dummy example anyway so IMO better to use up less space. ~~~ 16. + + + Refresh the publications using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, /Refresh the publications/Refresh the subscription's publications/ ~~~ // Steps to upgrade cascaded logical replication clusters // (these comments are similar to those in the previous procedure, but I will give them all again) 17. + + + Steps to upgrade cascaded logical replication clusters + + + +
RE: Documentation to upgrade logical replication cluster
Dear Vignesh,
Thanks for making a patch! Below part is my comments.
1.
Only two steps were added an id, but I think it should be for all the steps.
See [1].
2.
I'm not sure it should be listed as step 10. I felt that it should be new
section.
At that time other steps like "Prepare for {publisher|subscriber} upgrades" can
be moved as well.
Thought?
3.
```
+ The prerequisites of publisher upgrade applies to logical Replication
```
Replication -> replication
4.
```
+
+ Let's say publisher is in node1 and subscriber is
+ in node2.
+
```
I felt it is more friendly if you added the name of directory for each instance.
5.
You did not write the initialization of new node. Was it intentional?
6.
```
+
+ Disable all the subscriptions on node2 that are
+ subscribing the changes from node1 by using
+ ALTER
SUBSCRIPTION ... DISABLE,
+ for e.g.:
+
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+
+
```
Subscriptions are disabled after stopping a publisher, but it leads ERRORs on
the publisher.
I think it's better to swap these steps.
7.
```
+
+dba@node1:/opt/PostgreSQL/postgres//bin$ pg_ctl -D
/opt/PostgreSQL/pub_data stop -l logfile
+
```
Hmm. I thought you did not have to show the current directory. You were in the
bin dir, but it is not our requirement, right?
8.
```
+
+dba@node1:/opt/PostgreSQL/postgres//bin$ pg_upgrade
+--old-datadir "/opt/PostgreSQL/postgres/17/pub_data"
+--new-datadir
"/opt/PostgreSQL/postgres//pub_upgraded_data"
+--old-bindir "/opt/PostgreSQL/postgres/17/bin"
+--new-bindir "/opt/PostgreSQL/postgres//bin"
+
```
For PG17, both old and new bindir look the same. Can we use 18 as new-bindir?
9.
```
+
+ Create any tables that were created in node2
+ between step-2 and now, for e.g.:
+
+node2=# CREATE TABLE distributors (
+node2(# did integer CONSTRAINT no_null NOT NULL,
+node2(# name varchar(40) NOT NULL
+node2(# );
+CREATE TABLE
+
+
```
I think this SQLs must be done on node1, because it has not boot between step-2
and step-7.
10.
```
+
+
+ Enable all the subscriptions on node2 that are
+ subscribing the changes from node1 by using
+ ALTER
SUBSCRIPTION ... ENABLE,
+ for e.g.:
+
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+
+
+
+
+
+
+ Refresh the publications using
+ ALTER
SUBSCRIPTION ... REFRESH PUBLICATION,
+ for e.g.:
+
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+
+
+
```
I was very confused the location where they would be really do. If my above
comment is correct, should they be executed on node1 as well? Could you please
all
the notation again?
11.
```
+
+ Disable all the subscriptions on node1 that are
+ subscribing the changes from node2 by using
+ ALTER
SUBSCRIPTION ... DISABLE,
+ for e.g.:
+
+node2=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node2_node1 DISABLE;
+ALTER SUBSCRIPTION
+
+
```
They should be on node1, but noted as node2.
12.
```
+
+ Enable all the subscriptions on node1 that are
+ subscribing the changes from node2 by using
+ ALTER
SUBSCRIPTION ... ENABLE,
+ for e.g.:
+
+node2=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node2_node1 ENABLE;
+ALTER SUBSCRIPTION
+
+
```
You said that "enable all the subscription on node1", but SQLs are done on
node2.
[1]:
https://www.postgresql.org/message-id/tyapr01mb58667ae04d291924671e2051f5...@tyapr01mb5866.jpnprd01.prod.outlook.com
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
