From 7fdeaf5774d05245e82632e763665ff62db5598e Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Wed, 25 Sep 2024 10:06:10 +0530 Subject: [PATCH] Doc: Add the steps for upgrading the logical replication cluster. Author: Vignesh C Reviewed-by: Peter Smith, Amit Kapila, Hayato Kuroda, Bharath Rupireddy Discussion: https://postgr.es/m/CALDaNm1_iDO6srWzntqTr0ZDVkk2whVhNKEWAvtgZBfSmuBeZQ@mail.gmail.com --- doc/src/sgml/glossary.sgml | 10 + doc/src/sgml/logical-replication.sgml | 775 ++++++++++++++++++++++++++ doc/src/sgml/ref/pgupgrade.sgml | 131 +---- 3 files changed, 793 insertions(+), 123 deletions(-) diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index 405fe6dc8b9..f54f25c1c6b 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1069,6 +1069,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 df62eb45ff8..536d03995eb 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -2231,6 +2231,781 @@ 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 + + + While upgrading a subscriber, write operations can be performed in the + publisher. These changes will be replicated to the subscriber once the + subscriber upgrade is completed. + + + + + The logical replication restrictions apply to logical replication cluster + upgrades also. See for + details. + + + The prerequisites of publisher upgrade apply to logical replication + cluster upgrades also. See + for details. + + + The prerequisites of subscriber upgrade apply to logical replication + cluster upgrades also. See + for details. + + + + + + Upgrading logical replication cluster requires multiple steps to be + performed on various nodes. Because not all operations are + transactional, the user is advised to take backups as described in + . + + + + + The steps to upgrade the following logical replication clusters are + detailed below: + + + + Follow the steps specified in + to upgrade + a two-node logical replication cluster. + + + + + Follow the steps specified in + to upgrade + a cascaded logical replication cluster. + + + + + Follow the steps specified in + + to upgrade a two-node circular logical replication cluster. + + + + + + + Steps to upgrade a two-node logical replication cluster + + Let's say publisher is in node1 and subscriber is + in node2. The subscriber node2 has + a subscription sub1_node1_node2 which is subscribing + the changes from node1. + + + + + + Disable all the subscriptions on node2 that are + subscribing the changes from node1 by using + ALTER SUBSCRIPTION ... DISABLE, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; +ALTER SUBSCRIPTION + + + + + + Stop the publisher server in node1, e.g.: + +pg_ctl -D /opt/PostgreSQL/data1 stop + + + + + + + Initialize data1_upgraded instance by using the + required newer version. + + + + + + Upgrade the publisher node1's server to the + required newer version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data1" + --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded publisher server in node1, e.g.: + +pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile + + + + + + + Stop the subscriber server in node2, e.g.: + +pg_ctl -D /opt/PostgreSQL/data2 stop + + + + + + + Initialize data2_upgraded instance by using the + required newer version. + + + + + + Upgrade the subscriber node2's server to + the required new version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data2" + --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded subscriber server in node2, e.g.: + +pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile + + + + + + + On node2, create any tables that were created in + the upgraded publisher node1 server between + + and now, e.g.: + +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + + + + + Enable all the subscriptions on node2 that are + subscribing the changes from node1 by using + ALTER SUBSCRIPTION ... ENABLE, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; +ALTER SUBSCRIPTION + + + + + + + Refresh the node2 subscription's publications using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; +ALTER SUBSCRIPTION + + + + + + + + Steps to upgrade a cascaded logical replication cluster + + 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 a subscription sub1_node1_node2 which is + subscribing the changes from node1. The + node3 has a subscription + sub1_node2_node3 which is subscribing the changes from + node2. + + + + + + Disable all the subscriptions on node2 that are + subscribing the changes from node1 by using + ALTER SUBSCRIPTION ... DISABLE, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; +ALTER SUBSCRIPTION + + + + + + + Stop the server in node1, e.g.: + +pg_ctl -D /opt/PostgreSQL/data1 stop + + + + + + + Initialize data1_upgraded instance by using the + required newer version. + + + + + + Upgrade the node1's server to the required newer + version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data1" + --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded server in node1, e.g.: + +pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile + + + + + + + Disable all the subscriptions on node3 that are + subscribing the changes from node2 by using + ALTER SUBSCRIPTION ... DISABLE, + e.g.: + +node3=# ALTER SUBSCRIPTION sub1_node2_node3 DISABLE; +ALTER SUBSCRIPTION + + + + + + + Stop the server in node2, e.g.: + +pg_ctl -D /opt/PostgreSQL/data2 stop + + + + + + + Initialize data2_upgraded instance by using the + required newer version. + + + + + + Upgrade the node2's server to the required + new version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data2" + --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded server in node2, e.g.: + +pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile + + + + + + + On node2, create any tables that were created in + the upgraded publisher node1 server between + + and now, e.g.: + +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + + + + + Enable all the subscriptions on node2 that are + subscribing the changes from node1 by using + ALTER SUBSCRIPTION ... ENABLE, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; +ALTER SUBSCRIPTION + + + + + + + Refresh the node2 subscription's publications using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; +ALTER SUBSCRIPTION + + + + + + + Stop the server in node3, e.g.: + +pg_ctl -D /opt/PostgreSQL/data3 stop + + + + + + + Initialize data3_upgraded instance by using the + required newer version. + + + + + + Upgrade the node3's server to the required + new version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data3" + --new-datadir "/opt/PostgreSQL/postgres/18/data3_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded server in node3, e.g.: + +pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile + + + + + + + On node3, create any tables that were created in + the upgraded node2 between + and now, + e.g.: + +node3=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + + + + + Enable all the subscriptions on node3 that are + subscribing the changes from node2 by using + ALTER SUBSCRIPTION ... ENABLE, + e.g.: + +node3=# ALTER SUBSCRIPTION sub1_node2_node3 ENABLE; +ALTER SUBSCRIPTION + + + + + + + Refresh the node3 subscription's publications using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, + e.g.: + +node3=# ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION; +ALTER SUBSCRIPTION + + + + + + + + Steps to upgrade a two-node circular logical replication cluster + + Let's say we have a circular logical replication setup + node1->node2 and + node2->node1. Here + node2 is subscribing the changes from + node1 and node1 is subscribing + the changes from node2. The node1 + has a subscription sub1_node2_node1 which is + subscribing the changes from node2. The + node2 has a subscription + sub1_node1_node2 which is subscribing the changes from + node1. + + + + + + Disable all the subscriptions on node2 that are + subscribing the changes from node1 by using + ALTER SUBSCRIPTION ... DISABLE, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE; +ALTER SUBSCRIPTION + + + + + + + Stop the server in node1, e.g.: + +pg_ctl -D /opt/PostgreSQL/data1 stop + + + + + + + Initialize data1_upgraded instance by using the + required newer version. + + + + + + Upgrade the node1's server to the required + newer version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data1" + --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded server in node1, e.g.: + +pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile + + + + + + + Enable all the subscriptions on node2 that are + subscribing the changes from node1 by using + ALTER SUBSCRIPTION ... ENABLE, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE; +ALTER SUBSCRIPTION + + + + + + + On node1, create any tables that were created in + node2 between + and now, e.g.: + +node1=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + + + + + + Refresh the node1 subscription's publications to + copy initial table data from node2 using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, + e.g.: + +node1=# ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION; +ALTER SUBSCRIPTION + + + + + + + Disable all the subscriptions on node1 that are + subscribing the changes from node2 by using + ALTER SUBSCRIPTION ... DISABLE, + e.g.: + +node1=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE; +ALTER SUBSCRIPTION + + + + + + + Stop the server in node2, e.g.: + +pg_ctl -D /opt/PostgreSQL/data2 stop + + + + + + + Initialize data2_upgraded instance by using the + required newer version. + + + + + + Upgrade the node2's server to the required + new version, e.g.: + +pg_upgrade + --old-datadir "/opt/PostgreSQL/postgres/17/data2" + --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded" + --old-bindir "/opt/PostgreSQL/postgres/17/bin" + --new-bindir "/opt/PostgreSQL/postgres/18/bin" + + + + + + + Start the upgraded server in node2, e.g.: + +pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile + + + + + + + Enable all the subscriptions on node1 that are + subscribing the changes from node2 by using + ALTER SUBSCRIPTION ... ENABLE, + e.g.: + +node1=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE; +ALTER SUBSCRIPTION + + + + + + + On node2, create any tables that were created in + the upgraded node1 between + and now, e.g.: + +node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40)); +CREATE TABLE + + + + + + + Refresh the node2 subscription's publications to + copy initial table data from node1 using + ALTER SUBSCRIPTION ... REFRESH PUBLICATION, + e.g.: + +node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION; +ALTER SUBSCRIPTION + + + + + + + + + Quick Setup diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index fc2d0ff8451..41d5566a442 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -295,6 +295,14 @@ PostgreSQL documentation with pg_upgrade: + + + The steps to upgrade logical replication clusters + are not covered here; + refer to for details. + + + Optionally move the old cluster @@ -396,129 +404,6 @@ make prefix=/usr/local/pgsql.new install - - 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. - - - - - Stop both servers -- 2.30.2