begriffs open source - ai-pg/blob - full-docs/src/sgml/html/logical-replication-upgrade.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>29.13. Upgrade</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="logical-replication-config.html" title="29.12. Configuration Settings" /><link rel="next" href="logical-replication-quick-setup.html" title="29.14. Quick Setup" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">29.13. Upgrade</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logical-replication-config.html" title="29.12. Configuration Settings">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logical-replication.html" title="Chapter 29. Logical Replication">Up</a></td><th width="60%" align="center">Chapter 29. Logical Replication</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logical-replication-quick-setup.html" title="29.14. Quick Setup">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGICAL-REPLICATION-UPGRADE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">29.13. Upgrade <a href="#LOGICAL-REPLICATION-UPGRADE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="logical-replication-upgrade.html#PREPARE-PUBLISHER-UPGRADES">29.13.1. Prepare for Publisher Upgrades</a></span></dt><dt><span class="sect2"><a href="logical-replication-upgrade.html#PREPARE-SUBSCRIBER-UPGRADES">29.13.2. Prepare for Subscriber Upgrades</a></span></dt><dt><span class="sect2"><a href="logical-replication-upgrade.html#UPGRADING-LOGICAL-REPLICATION-CLUSTERS">29.13.3. Upgrading Logical Replication Clusters</a></span></dt></dl></div><p>
   3    Migration of <a class="glossterm" href="glossary.html#GLOSSARY-LOGICAL-REPLICATION-CLUSTER"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-LOGICAL-REPLICATION-CLUSTER" title="Logical replication cluster">logical replication clusters</a></em></a>
   4    is possible only when all the members of the old logical replication
   5    clusters are version 17.0 or later.
   6   </p><div class="sect2" id="PREPARE-PUBLISHER-UPGRADES"><div class="titlepage"><div><div><h3 class="title">29.13.1. Prepare for Publisher Upgrades <a href="#PREPARE-PUBLISHER-UPGRADES" class="id_link">#</a></h3></div></div></div><p>
   7     <span class="application">pg_upgrade</span> attempts to migrate logical
   8     slots. This helps avoid the need for manually defining the same
   9     logical slots on the new publisher. Migration of logical slots is
  10     only supported when the old cluster is version 17.0 or later.
  11     Logical slots on clusters before version 17.0 will silently be
  12     ignored.
  13    </p><p>
  14     Before you start upgrading the publisher cluster, ensure that the
  15     subscription is temporarily disabled, by executing
  16     <a class="link" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>.
  17     Re-enable the subscription after the upgrade.
  18    </p><p>
  19     There are some prerequisites for <span class="application">pg_upgrade</span> to
  20     be able to upgrade the logical slots. If these are not met an error
  21     will be reported.
  22    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  23       The new cluster must have
  24       <a class="link" href="runtime-config-wal.html#GUC-WAL-LEVEL"><code class="varname">wal_level</code></a> as
  25       <code class="literal">logical</code>.
  26      </p></li><li class="listitem"><p>
  27       The new cluster must have
  28       <a class="link" href="runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS"><code class="varname">max_replication_slots</code></a>
  29       configured to a value greater than or equal to the number of slots
  30       present in the old cluster.
  31      </p></li><li class="listitem"><p>
  32       The output plugins referenced by the slots on the old cluster must be
  33       installed in the new PostgreSQL executable directory.
  34      </p></li><li class="listitem"><p>
  35       The old cluster has replicated all the transactions and logical decoding
  36       messages to subscribers.
  37      </p></li><li class="listitem"><p>
  38       All slots on the old cluster must be usable, i.e., there are no slots
  39       whose
  40       <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.<code class="structfield">conflicting</code>
  41       is not <code class="literal">true</code>.
  42      </p></li><li class="listitem"><p>
  43       The new cluster must not have permanent logical slots, i.e.,
  44       there must be no slots where
  45       <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.<code class="structfield">temporary</code>
  46       is <code class="literal">false</code>.
  47      </p></li></ul></div></div><div class="sect2" id="PREPARE-SUBSCRIBER-UPGRADES"><div class="titlepage"><div><div><h3 class="title">29.13.2. Prepare for Subscriber Upgrades <a href="#PREPARE-SUBSCRIBER-UPGRADES" class="id_link">#</a></h3></div></div></div><p>
  48     Setup the <a class="link" href="logical-replication-config.html#LOGICAL-REPLICATION-CONFIG-SUBSCRIBER" title="29.12.2. Subscribers">
  49     subscriber configurations</a> in the new subscriber.
  50     <span class="application">pg_upgrade</span> attempts to migrate subscription
  51     dependencies which includes the subscription's table information present in
  52     <a class="link" href="catalog-pg-subscription-rel.html" title="52.55. pg_subscription_rel">pg_subscription_rel</a>
  53     system catalog and also the subscription's replication origin. This allows
  54     logical replication on the new subscriber to continue from where the
  55     old subscriber was up to. Migration of subscription dependencies is only
  56     supported when the old cluster is version 17.0 or later. Subscription
  57     dependencies on clusters before version 17.0 will silently be ignored.
  58    </p><p>
  59     There are some prerequisites for <span class="application">pg_upgrade</span> to
  60     be able to upgrade the subscriptions. If these are not met an error
  61     will be reported.
  62    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  63       All the subscription tables in the old subscriber should be in state
  64       <code class="literal">i</code> (initialize) or <code class="literal">r</code> (ready). This
  65       can be verified by checking <a class="link" href="catalog-pg-subscription-rel.html" title="52.55. pg_subscription_rel">pg_subscription_rel</a>.<code class="structfield">srsubstate</code>.
  66      </p></li><li class="listitem"><p>
  67       The replication origin entry corresponding to each of the subscriptions
  68       should exist in the old cluster. This can be found by checking
  69       <a class="link" href="catalog-pg-subscription.html" title="52.54. pg_subscription">pg_subscription</a> and
  70       <a class="link" href="catalog-pg-replication-origin.html" title="52.44. pg_replication_origin">pg_replication_origin</a>
  71       system tables.
  72      </p></li><li class="listitem"><p>
  73       The new cluster must have
  74       <a class="link" href="runtime-config-replication.html#GUC-MAX-ACTIVE-REPLICATION-ORIGINS"><code class="varname">max_active_replication_origins</code></a>
  75       configured to a value greater than or equal to the number of
  76       subscriptions present in the old cluster.
  77      </p></li></ul></div></div><div class="sect2" id="UPGRADING-LOGICAL-REPLICATION-CLUSTERS"><div class="titlepage"><div><div><h3 class="title">29.13.3. Upgrading Logical Replication Clusters <a href="#UPGRADING-LOGICAL-REPLICATION-CLUSTERS" class="id_link">#</a></h3></div></div></div><p>
  78     While upgrading a subscriber, write operations can be performed in the
  79     publisher. These changes will be replicated to the subscriber once the
  80     subscriber upgrade is completed.
  81    </p><div class="note"><h3 class="title">Note</h3><p>
  82      The logical replication restrictions apply to logical replication cluster
  83      upgrades also. See <a class="xref" href="logical-replication-restrictions.html" title="29.8. Restrictions">Section 29.8</a> for
  84      details.
  85     </p><p>
  86      The prerequisites of publisher upgrade apply to logical replication
  87      cluster upgrades also. See <a class="xref" href="logical-replication-upgrade.html#PREPARE-PUBLISHER-UPGRADES" title="29.13.1. Prepare for Publisher Upgrades">Section 29.13.1</a>
  88      for details.
  89     </p><p>
  90      The prerequisites of subscriber upgrade apply to logical replication
  91      cluster upgrades also. See <a class="xref" href="logical-replication-upgrade.html#PREPARE-SUBSCRIBER-UPGRADES" title="29.13.2. Prepare for Subscriber Upgrades">Section 29.13.2</a>
  92      for details.
  93     </p></div><div class="warning"><h3 class="title">Warning</h3><p>
  94      Upgrading logical replication cluster requires multiple steps to be
  95      performed on various nodes. Because not all operations are
  96      transactional, the user is advised to take backups as described in
  97      <a class="xref" href="continuous-archiving.html#BACKUP-BASE-BACKUP" title="25.3.2. Making a Base Backup">Section 25.3.2</a>.
  98     </p></div><p>
  99     The steps to upgrade the following logical replication clusters are
 100     detailed below:
 101     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
 102        Follow the steps specified in
 103        <a class="xref" href="logical-replication-upgrade.html#STEPS-TWO-NODE-LOGICAL-REPLICATION-CLUSTER" title="29.13.3.1. Steps to Upgrade a Two-node Logical Replication Cluster">Section 29.13.3.1</a> to upgrade
 104        a two-node logical replication cluster.
 105       </p></li><li class="listitem"><p>
 106        Follow the steps specified in
 107        <a class="xref" href="logical-replication-upgrade.html#STEPS-CASCADED-LOGICAL-REPLICATION-CLUSTER" title="29.13.3.2. Steps to Upgrade a Cascaded Logical Replication Cluster">Section 29.13.3.2</a> to upgrade
 108        a cascaded logical replication cluster.
 109       </p></li><li class="listitem"><p>
 110        Follow the steps specified in
 111        <a class="xref" href="logical-replication-upgrade.html#STEPS-TWO-NODE-CIRCULAR-LOGICAL-REPLICATION-CLUSTER" title="29.13.3.3. Steps to Upgrade a Two-node Circular Logical Replication Cluster">Section 29.13.3.3</a>
 112        to upgrade a two-node circular logical replication cluster.
 113       </p></li></ul></div><p>
 114    </p><div class="sect3" id="STEPS-TWO-NODE-LOGICAL-REPLICATION-CLUSTER"><div class="titlepage"><div><div><h4 class="title">29.13.3.1. Steps to Upgrade a Two-node Logical Replication Cluster <a href="#STEPS-TWO-NODE-LOGICAL-REPLICATION-CLUSTER" class="id_link">#</a></h4></div></div></div><p>
 115       Let's say publisher is in <code class="literal">node1</code> and subscriber is
 116       in <code class="literal">node2</code>. The subscriber <code class="literal">node2</code> has
 117       a subscription <code class="literal">sub1_node1_node2</code> which is subscribing
 118       the changes from <code class="literal">node1</code>.
 119      </p><div class="procedure"><ol class="procedure" type="1"><li class="step" id="TWO-NODE-CLUSTER-DISABLE-SUBSCRIPTIONS-NODE2"><p>
 120         Disable all the subscriptions on <code class="literal">node2</code> that are
 121         subscribing the changes from <code class="literal">node1</code> by using
 122         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
 123         e.g.:
 124 </p><pre class="programlisting">
 125 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
 126 </pre><p>
 127        </p></li><li class="step"><p>
 128         Stop the publisher server in <code class="literal">node1</code>, e.g.:
 129 </p><pre class="programlisting">
 130 pg_ctl -D /opt/PostgreSQL/data1 stop
 131 </pre><p>
 132        </p></li><li class="step"><p>
 133         Initialize <code class="literal">data1_upgraded</code> instance by using the
 134         required newer version.
 135        </p></li><li class="step"><p>
 136         Upgrade the publisher <code class="literal">node1</code>'s server to the
 137         required newer version, e.g.:
 138 </p><pre class="programlisting">
 139 pg_upgrade
 140         --old-datadir "/opt/PostgreSQL/postgres/17/data1"
 141         --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
 142         --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 143         --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 144 </pre><p>
 145        </p></li><li class="step"><p>
 146         Start the upgraded publisher server in <code class="literal">node1</code>, e.g.:
 147 </p><pre class="programlisting">
 148 pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
 149 </pre><p>
 150        </p></li><li class="step"><p>
 151         Stop the subscriber server in <code class="literal">node2</code>, e.g.:
 152 </p><pre class="programlisting">
 153 pg_ctl -D /opt/PostgreSQL/data2 stop
 154 </pre><p>
 155        </p></li><li class="step"><p>
 156         Initialize <code class="literal">data2_upgraded</code> instance by using the
 157         required newer version.
 158        </p></li><li class="step"><p>
 159         Upgrade the subscriber <code class="literal">node2</code>'s server to
 160         the required new version, e.g.:
 161 </p><pre class="programlisting">
 162 pg_upgrade
 163        --old-datadir "/opt/PostgreSQL/postgres/17/data2"
 164        --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
 165        --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 166        --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 167 </pre><p>
 168        </p></li><li class="step"><p>
 169         Start the upgraded subscriber server in <code class="literal">node2</code>, e.g.:
 170 </p><pre class="programlisting">
 171 pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
 172 </pre><p>
 173        </p></li><li class="step"><p>
 174         On <code class="literal">node2</code>, create any tables that were created in
 175         the upgraded publisher <code class="literal">node1</code> server between
 176         <a class="xref" href="logical-replication-upgrade.html#TWO-NODE-CLUSTER-DISABLE-SUBSCRIPTIONS-NODE2" title="Step 1">Step 1</a>
 177         and now, e.g.:
 178 </p><pre class="programlisting">
 179 /* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
 180 </pre><p>
 181        </p></li><li class="step"><p>
 182         Enable all the subscriptions on <code class="literal">node2</code> that are
 183         subscribing the changes from <code class="literal">node1</code> by using
 184         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
 185         e.g.:
 186 </p><pre class="programlisting">
 187 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
 188 </pre><p>
 189        </p></li><li class="step"><p>
 190         Refresh the <code class="literal">node2</code> subscription's publications using
 191         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
 192         e.g.:
 193 </p><pre class="programlisting">
 194 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
 195 </pre><p>
 196        </p></li></ol></div><div class="note"><h3 class="title">Note</h3><p>
 197        In the steps described above, the publisher is upgraded first, followed
 198        by the subscriber. Alternatively, the user can use similar steps to
 199        upgrade the subscriber first, followed by the publisher.
 200       </p></div></div><div class="sect3" id="STEPS-CASCADED-LOGICAL-REPLICATION-CLUSTER"><div class="titlepage"><div><div><h4 class="title">29.13.3.2. Steps to Upgrade a Cascaded Logical Replication Cluster <a href="#STEPS-CASCADED-LOGICAL-REPLICATION-CLUSTER" class="id_link">#</a></h4></div></div></div><p>
 201       Let's say we have a cascaded logical replication setup
 202       <code class="literal">node1</code>-&gt;<code class="literal">node2</code>-&gt;<code class="literal">node3</code>.
 203       Here <code class="literal">node2</code> is subscribing the changes from
 204       <code class="literal">node1</code> and <code class="literal">node3</code> is subscribing
 205       the changes from <code class="literal">node2</code>. The <code class="literal">node2</code>
 206       has a subscription <code class="literal">sub1_node1_node2</code> which is
 207       subscribing the changes from <code class="literal">node1</code>. The
 208       <code class="literal">node3</code> has a subscription
 209       <code class="literal">sub1_node2_node3</code> which is subscribing the changes from
 210       <code class="literal">node2</code>.
 211      </p><div class="procedure"><ol class="procedure" type="1"><li class="step" id="CASCADED-CLUSTER-DISABLE-SUB-NODE1-NODE2"><p>
 212         Disable all the subscriptions on <code class="literal">node2</code> that are
 213         subscribing the changes from <code class="literal">node1</code> by using
 214         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
 215         e.g.:
 216 </p><pre class="programlisting">
 217 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
 218 </pre><p>
 219        </p></li><li class="step"><p>
 220         Stop the server in <code class="literal">node1</code>, e.g.:
 221 </p><pre class="programlisting">
 222 pg_ctl -D /opt/PostgreSQL/data1 stop
 223 </pre><p>
 224        </p></li><li class="step"><p>
 225         Initialize <code class="literal">data1_upgraded</code> instance by using the
 226         required newer version.
 227        </p></li><li class="step"><p>
 228         Upgrade the <code class="literal">node1</code>'s server to the required newer
 229         version, e.g.:
 230 </p><pre class="programlisting">
 231 pg_upgrade
 232         --old-datadir "/opt/PostgreSQL/postgres/17/data1"
 233         --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
 234         --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 235         --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 236 </pre><p>
 237        </p></li><li class="step"><p>
 238         Start the upgraded server in <code class="literal">node1</code>, e.g.:
 239 </p><pre class="programlisting">
 240 pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
 241 </pre><p>
 242        </p></li><li class="step" id="CASCADED-CLUSTER-DISABLE-SUB-NODE2-NODE3"><p>
 243         Disable all the subscriptions on <code class="literal">node3</code> that are
 244         subscribing the changes from <code class="literal">node2</code> by using
 245         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
 246         e.g.:
 247 </p><pre class="programlisting">
 248 /* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
 249 </pre><p>
 250        </p></li><li class="step"><p>
 251         Stop the server in <code class="literal">node2</code>, e.g.:
 252 </p><pre class="programlisting">
 253 pg_ctl -D /opt/PostgreSQL/data2 stop
 254 </pre><p>
 255        </p></li><li class="step"><p>
 256         Initialize <code class="literal">data2_upgraded</code> instance by using the
 257         required newer version.
 258        </p></li><li class="step"><p>
 259         Upgrade the <code class="literal">node2</code>'s server to the required
 260         new version, e.g.:
 261 </p><pre class="programlisting">
 262 pg_upgrade
 263         --old-datadir "/opt/PostgreSQL/postgres/17/data2"
 264         --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
 265         --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 266         --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 267 </pre><p>
 268        </p></li><li class="step"><p>
 269         Start the upgraded server in <code class="literal">node2</code>, e.g.:
 270 </p><pre class="programlisting">
 271 pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
 272 </pre><p>
 273        </p></li><li class="step"><p>
 274         On <code class="literal">node2</code>, create any tables that were created in
 275         the upgraded publisher <code class="literal">node1</code> server between
 276         <a class="xref" href="logical-replication-upgrade.html#CASCADED-CLUSTER-DISABLE-SUB-NODE1-NODE2" title="Step 1">Step 1</a>
 277         and now, e.g.:
 278 </p><pre class="programlisting">
 279 /* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
 280 </pre><p>
 281        </p></li><li class="step"><p>
 282         Enable all the subscriptions on <code class="literal">node2</code> that are
 283         subscribing the changes from <code class="literal">node1</code> by using
 284         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
 285         e.g.:
 286 </p><pre class="programlisting">
 287 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
 288 </pre><p>
 289        </p></li><li class="step"><p>
 290         Refresh the <code class="literal">node2</code> subscription's publications using
 291         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
 292         e.g.:
 293 </p><pre class="programlisting">
 294 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
 295 </pre><p>
 296        </p></li><li class="step"><p>
 297         Stop the server in <code class="literal">node3</code>, e.g.:
 298 </p><pre class="programlisting">
 299 pg_ctl -D /opt/PostgreSQL/data3 stop
 300 </pre><p>
 301        </p></li><li class="step"><p>
 302         Initialize <code class="literal">data3_upgraded</code> instance by using the
 303         required newer version.
 304        </p></li><li class="step"><p>
 305         Upgrade the <code class="literal">node3</code>'s server to the required
 306         new version, e.g.:
 307 </p><pre class="programlisting">
 308 pg_upgrade
 309         --old-datadir "/opt/PostgreSQL/postgres/17/data3"
 310         --new-datadir "/opt/PostgreSQL/postgres/18/data3_upgraded"
 311         --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 312         --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 313 </pre><p>
 314        </p></li><li class="step"><p>
 315         Start the upgraded server in <code class="literal">node3</code>, e.g.:
 316 </p><pre class="programlisting">
 317 pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile
 318 </pre><p>
 319        </p></li><li class="step"><p>
 320         On <code class="literal">node3</code>, create any tables that were created in
 321         the upgraded <code class="literal">node2</code> between
 322         <a class="xref" href="logical-replication-upgrade.html#CASCADED-CLUSTER-DISABLE-SUB-NODE2-NODE3" title="Step 6">Step 6</a> and now,
 323         e.g.:
 324 </p><pre class="programlisting">
 325 /* node3 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
 326 </pre><p>
 327        </p></li><li class="step"><p>
 328         Enable all the subscriptions on <code class="literal">node3</code> that are
 329         subscribing the changes from <code class="literal">node2</code> by using
 330         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
 331         e.g.:
 332 </p><pre class="programlisting">
 333 /* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
 334 </pre><p>
 335        </p></li><li class="step"><p>
 336         Refresh the <code class="literal">node3</code> subscription's publications using
 337         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
 338         e.g.:
 339 </p><pre class="programlisting">
 340 /* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
 341 </pre><p>
 342        </p></li></ol></div></div><div class="sect3" id="STEPS-TWO-NODE-CIRCULAR-LOGICAL-REPLICATION-CLUSTER"><div class="titlepage"><div><div><h4 class="title">29.13.3.3. Steps to Upgrade a Two-node Circular Logical Replication Cluster <a href="#STEPS-TWO-NODE-CIRCULAR-LOGICAL-REPLICATION-CLUSTER" class="id_link">#</a></h4></div></div></div><p>
 343       Let's say we have a circular logical replication setup
 344       <code class="literal">node1</code>-&gt;<code class="literal">node2</code> and
 345       <code class="literal">node2</code>-&gt;<code class="literal">node1</code>. Here
 346       <code class="literal">node2</code> is subscribing the changes from
 347       <code class="literal">node1</code> and <code class="literal">node1</code> is subscribing
 348       the changes from <code class="literal">node2</code>. The <code class="literal">node1</code>
 349       has a subscription <code class="literal">sub1_node2_node1</code> which is
 350       subscribing the changes from <code class="literal">node2</code>. The
 351       <code class="literal">node2</code> has a subscription
 352       <code class="literal">sub1_node1_node2</code> which is subscribing the changes from
 353       <code class="literal">node1</code>.
 354      </p><div class="procedure"><ol class="procedure" type="1"><li class="step" id="CIRCULAR-CLUSTER-DISABLE-SUB-NODE2"><p>
 355         Disable all the subscriptions on <code class="literal">node2</code> that are
 356         subscribing the changes from <code class="literal">node1</code> by using
 357         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
 358         e.g.:
 359 </p><pre class="programlisting">
 360 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
 361 </pre><p>
 362        </p></li><li class="step"><p>
 363         Stop the server in <code class="literal">node1</code>, e.g.:
 364 </p><pre class="programlisting">
 365 pg_ctl -D /opt/PostgreSQL/data1 stop
 366 </pre><p>
 367        </p></li><li class="step"><p>
 368         Initialize <code class="literal">data1_upgraded</code> instance by using the
 369         required newer version.
 370        </p></li><li class="step"><p>
 371         Upgrade the <code class="literal">node1</code>'s server to the required
 372         newer version, e.g.:
 373 </p><pre class="programlisting">
 374 pg_upgrade
 375         --old-datadir "/opt/PostgreSQL/postgres/17/data1"
 376         --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
 377         --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 378         --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 379 </pre><p>
 380        </p></li><li class="step"><p>
 381         Start the upgraded server in <code class="literal">node1</code>, e.g.:
 382 </p><pre class="programlisting">
 383 pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
 384 </pre><p>
 385        </p></li><li class="step"><p>
 386         Enable all the subscriptions on <code class="literal">node2</code> that are
 387         subscribing the changes from <code class="literal">node1</code> by using
 388         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
 389         e.g.:
 390 </p><pre class="programlisting">
 391 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
 392 </pre><p>
 393        </p></li><li class="step"><p>
 394         On <code class="literal">node1</code>, create any tables that were created in
 395         <code class="literal">node2</code> between <a class="xref" href="logical-replication-upgrade.html#CIRCULAR-CLUSTER-DISABLE-SUB-NODE2" title="Step 1">Step 1</a>
 396         and now, e.g.:
 397 </p><pre class="programlisting">
 398 /* node1 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
 399 </pre><p>
 400        </p></li><li class="step"><p>
 401         Refresh the <code class="literal">node1</code> subscription's publications to
 402         copy initial table data from <code class="literal">node2</code> using
 403         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
 404         e.g.:
 405 </p><pre class="programlisting">
 406 /* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
 407 </pre><p>
 408        </p></li><li class="step" id="CIRCULAR-CLUSTER-DISABLE-SUB-NODE1"><p>
 409         Disable all the subscriptions on <code class="literal">node1</code> that are
 410         subscribing the changes from <code class="literal">node2</code> by using
 411         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
 412         e.g.:
 413 </p><pre class="programlisting">
 414 /* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
 415 </pre><p>
 416        </p></li><li class="step"><p>
 417         Stop the server in <code class="literal">node2</code>, e.g.:
 418 </p><pre class="programlisting">
 419 pg_ctl -D /opt/PostgreSQL/data2 stop
 420 </pre><p>
 421        </p></li><li class="step"><p>
 422         Initialize <code class="literal">data2_upgraded</code> instance by using the
 423         required newer version.
 424        </p></li><li class="step"><p>
 425         Upgrade the <code class="literal">node2</code>'s server to the required
 426         new version, e.g.:
 427 </p><pre class="programlisting">
 428 pg_upgrade
 429         --old-datadir "/opt/PostgreSQL/postgres/17/data2"
 430         --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
 431         --old-bindir "/opt/PostgreSQL/postgres/17/bin"
 432         --new-bindir "/opt/PostgreSQL/postgres/18/bin"
 433 </pre><p>
 434        </p></li><li class="step"><p>
 435         Start the upgraded server in <code class="literal">node2</code>, e.g.:
 436 </p><pre class="programlisting">
 437 pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
 438 </pre><p>
 439        </p></li><li class="step"><p>
 440         Enable all the subscriptions on <code class="literal">node1</code> that are
 441         subscribing the changes from <code class="literal">node2</code> by using
 442         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
 443         e.g.:
 444 </p><pre class="programlisting">
 445 /* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
 446 </pre><p>
 447        </p></li><li class="step"><p>
 448         On <code class="literal">node2</code>, create any tables that were created in
 449         the upgraded <code class="literal">node1</code> between <a class="xref" href="logical-replication-upgrade.html#CIRCULAR-CLUSTER-DISABLE-SUB-NODE1" title="Step 9">Step 9</a>
 450         and now, e.g.:
 451 </p><pre class="programlisting">
 452 /* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
 453 </pre><p>
 454        </p></li><li class="step"><p>
 455         Refresh the <code class="literal">node2</code> subscription's publications to
 456         copy initial table data from <code class="literal">node1</code> using
 457         <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
 458         e.g.:
 459 </p><pre class="programlisting">
 460 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
 461 </pre><p>
 462        </p></li></ol></div></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logical-replication-config.html" title="29.12. Configuration Settings">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logical-replication.html" title="Chapter 29. Logical Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logical-replication-quick-setup.html" title="29.14. Quick Setup">Next</a></td></tr><tr><td width="40%" align="left" valign="top">29.12. Configuration Settings </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 29.14. Quick Setup</td></tr></table></div></body></html>