pgsql/doc/src/sgml/html/logical-replication-upgrade.html - master - Ao/MaxReport

pgsql/doc/src/sgml/html/logical-replication-upgrade.html
462 строки31 KB
AoAnima
first_commit
13 июл 2026, 17:47
13 июл 2026, 17:47f1a670a
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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.4 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>
   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>
   is possible only when all the members of the old logical replication
   clusters are version 17.0 or later.
  </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>
    <span class="application">pg_upgrade</span> 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.
   </p><p>
    Before you start upgrading the publisher cluster, ensure that the
    subscription is temporarily disabled, by executing
    <a class="link" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>.
    Re-enable the subscription after the upgrade.
   </p><p>
    There are some prerequisites for <span class="application">pg_upgrade</span> to
    be able to upgrade the logical slots. If these are not met an error
    will be reported.
   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
      The new cluster must have
      <a class="link" href="runtime-config-wal.html#GUC-WAL-LEVEL"><code class="varname">wal_level</code></a> as
      <code class="literal">logical</code>.
     </p></li><li class="listitem"><p>
      The new cluster must have
      <a class="link" href="runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS"><code class="varname">max_replication_slots</code></a>
      configured to a value greater than or equal to the number of slots
      present in the old cluster.
     </p></li><li class="listitem"><p>
      The output plugins referenced by the slots on the old cluster must be
      installed in the new PostgreSQL executable directory.
     </p></li><li class="listitem"><p>
      The old cluster has replicated all the transactions and logical decoding
      messages to subscribers.
     </p></li><li class="listitem"><p>
      All slots on the old cluster must be usable, i.e., there are no slots
      whose
      <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.<code class="structfield">conflicting</code>
      is not <code class="literal">true</code>.
     </p></li><li class="listitem"><p>
      The new cluster must not have permanent logical slots, i.e.,
      there must be no slots where
      <a class="link" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">pg_replication_slots</a>.<code class="structfield">temporary</code>
      is <code class="literal">false</code>.
     </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>
    Setup the <a class="link" href="logical-replication-config.html#LOGICAL-REPLICATION-CONFIG-SUBSCRIBER" title="29.12.2. Subscribers">
    subscriber configurations</a> in the new subscriber.
    <span class="application">pg_upgrade</span> attempts to migrate subscription
    dependencies which includes the subscription's table information present in
    <a class="link" href="catalog-pg-subscription-rel.html" title="52.55. pg_subscription_rel">pg_subscription_rel</a>
    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.
   </p><p>
    There are some prerequisites for <span class="application">pg_upgrade</span> to
    be able to upgrade the subscriptions. If these are not met an error
    will be reported.
   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
      All the subscription tables in the old subscriber should be in state
      <code class="literal">i</code> (initialize) or <code class="literal">r</code> (ready). This
      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>.
     </p></li><li class="listitem"><p>
      The replication origin entry corresponding to each of the subscriptions
      should exist in the old cluster. This can be found by checking
      <a class="link" href="catalog-pg-subscription.html" title="52.54. pg_subscription">pg_subscription</a> and
      <a class="link" href="catalog-pg-replication-origin.html" title="52.44. pg_replication_origin">pg_replication_origin</a>
      system tables.
     </p></li><li class="listitem"><p>
      The new cluster must have
      <a class="link" href="runtime-config-replication.html#GUC-MAX-ACTIVE-REPLICATION-ORIGINS"><code class="varname">max_active_replication_origins</code></a>
      configured to a value greater than or equal to the number of
      subscriptions present in the old cluster.
     </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>
    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.
   </p><div class="note"><h3 class="title">Note</h3><p>
     The logical replication restrictions apply to logical replication cluster
     upgrades also. See <a class="xref" href="logical-replication-restrictions.html" title="29.8. Restrictions">Section 29.8</a> for
     details.
    </p><p>
     The prerequisites of publisher upgrade apply to logical replication
     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>
     for details.
    </p><p>
     The prerequisites of subscriber upgrade apply to logical replication
     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>
     for details.
    </p></div><div class="warning"><h3 class="title">Warning</h3><p>
     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
     <a class="xref" href="continuous-archiving.html#BACKUP-BASE-BACKUP" title="25.3.2. Making a Base Backup">Section 25.3.2</a>.
    </p></div><p>
    The steps to upgrade the following logical replication clusters are
    detailed below:
    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
       Follow the steps specified in
       <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
       a two-node logical replication cluster.
      </p></li><li class="listitem"><p>
       Follow the steps specified in
       <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
       a cascaded logical replication cluster.
      </p></li><li class="listitem"><p>
       Follow the steps specified in
       <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>
       to upgrade a two-node circular logical replication cluster.
      </p></li></ul></div><p>
   </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>
      Let's say publisher is in <code class="literal">node1</code> and subscriber is
      in <code class="literal">node2</code>. The subscriber <code class="literal">node2</code> has
      a subscription <code class="literal">sub1_node1_node2</code> which is subscribing
      the changes from <code class="literal">node1</code>.
     </p><div class="procedure"><ol class="procedure" type="1"><li class="step" id="TWO-NODE-CLUSTER-DISABLE-SUBSCRIPTIONS-NODE2"><p>
        Disable all the subscriptions on <code class="literal">node2</code> that are
        subscribing the changes from <code class="literal">node1</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
</pre><p>
       </p></li><li class="step"><p>
        Stop the publisher server in <code class="literal">node1</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data1 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data1_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the publisher <code class="literal">node1</code>'s server to the
        required newer version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded publisher server in <code class="literal">node1</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
</pre><p>
       </p></li><li class="step"><p>
        Stop the subscriber server in <code class="literal">node2</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data2 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data2_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the subscriber <code class="literal">node2</code>'s server to
        the required new version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded subscriber server in <code class="literal">node2</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
</pre><p>
       </p></li><li class="step"><p>
        On <code class="literal">node2</code>, create any tables that were created in
        the upgraded publisher <code class="literal">node1</code> server between
        <a class="xref" href="logical-replication-upgrade.html#TWO-NODE-CLUSTER-DISABLE-SUBSCRIPTIONS-NODE2" title="Step 1">Step 1</a>
        and now, e.g.:
</p><pre class="programlisting">
/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</pre><p>
       </p></li><li class="step"><p>
        Enable all the subscriptions on <code class="literal">node2</code> that are
        subscribing the changes from <code class="literal">node1</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
</pre><p>
       </p></li><li class="step"><p>
        Refresh the <code class="literal">node2</code> subscription's publications using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
</pre><p>
       </p></li></ol></div><div class="note"><h3 class="title">Note</h3><p>
       In the steps described above, the publisher is upgraded first, followed
       by the subscriber. Alternatively, the user can use similar steps to
       upgrade the subscriber first, followed by the publisher.
      </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>
      Let's say we have a cascaded logical replication setup
      <code class="literal">node1</code>-&gt;<code class="literal">node2</code>-&gt;<code class="literal">node3</code>.
      Here <code class="literal">node2</code> is subscribing the changes from
      <code class="literal">node1</code> and <code class="literal">node3</code> is subscribing
      the changes from <code class="literal">node2</code>. The <code class="literal">node2</code>
      has a subscription <code class="literal">sub1_node1_node2</code> which is
      subscribing the changes from <code class="literal">node1</code>. The
      <code class="literal">node3</code> has a subscription
      <code class="literal">sub1_node2_node3</code> which is subscribing the changes from
      <code class="literal">node2</code>.
     </p><div class="procedure"><ol class="procedure" type="1"><li class="step" id="CASCADED-CLUSTER-DISABLE-SUB-NODE1-NODE2"><p>
        Disable all the subscriptions on <code class="literal">node2</code> that are
        subscribing the changes from <code class="literal">node1</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
</pre><p>
       </p></li><li class="step"><p>
        Stop the server in <code class="literal">node1</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data1 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data1_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the <code class="literal">node1</code>'s server to the required newer
        version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded server in <code class="literal">node1</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
</pre><p>
       </p></li><li class="step" id="CASCADED-CLUSTER-DISABLE-SUB-NODE2-NODE3"><p>
        Disable all the subscriptions on <code class="literal">node3</code> that are
        subscribing the changes from <code class="literal">node2</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
</pre><p>
       </p></li><li class="step"><p>
        Stop the server in <code class="literal">node2</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data2 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data2_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the <code class="literal">node2</code>'s server to the required
        new version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded server in <code class="literal">node2</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
</pre><p>
       </p></li><li class="step"><p>
        On <code class="literal">node2</code>, create any tables that were created in
        the upgraded publisher <code class="literal">node1</code> server between
        <a class="xref" href="logical-replication-upgrade.html#CASCADED-CLUSTER-DISABLE-SUB-NODE1-NODE2" title="Step 1">Step 1</a>
        and now, e.g.:
</p><pre class="programlisting">
/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</pre><p>
       </p></li><li class="step"><p>
        Enable all the subscriptions on <code class="literal">node2</code> that are
        subscribing the changes from <code class="literal">node1</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
</pre><p>
       </p></li><li class="step"><p>
        Refresh the <code class="literal">node2</code> subscription's publications using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
</pre><p>
       </p></li><li class="step"><p>
        Stop the server in <code class="literal">node3</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data3 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data3_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the <code class="literal">node3</code>'s server to the required
        new version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded server in <code class="literal">node3</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile
</pre><p>
       </p></li><li class="step"><p>
        On <code class="literal">node3</code>, create any tables that were created in
        the upgraded <code class="literal">node2</code> between
        <a class="xref" href="logical-replication-upgrade.html#CASCADED-CLUSTER-DISABLE-SUB-NODE2-NODE3" title="Step 6">Step 6</a> and now,
        e.g.:
</p><pre class="programlisting">
/* node3 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</pre><p>
       </p></li><li class="step"><p>
        Enable all the subscriptions on <code class="literal">node3</code> that are
        subscribing the changes from <code class="literal">node2</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
</pre><p>
       </p></li><li class="step"><p>
        Refresh the <code class="literal">node3</code> subscription's publications using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
</pre><p>
       </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>
      Let's say we have a circular logical replication setup
      <code class="literal">node1</code>-&gt;<code class="literal">node2</code> and
      <code class="literal">node2</code>-&gt;<code class="literal">node1</code>. Here
      <code class="literal">node2</code> is subscribing the changes from
      <code class="literal">node1</code> and <code class="literal">node1</code> is subscribing
      the changes from <code class="literal">node2</code>. The <code class="literal">node1</code>
      has a subscription <code class="literal">sub1_node2_node1</code> which is
      subscribing the changes from <code class="literal">node2</code>. The
      <code class="literal">node2</code> has a subscription
      <code class="literal">sub1_node1_node2</code> which is subscribing the changes from
      <code class="literal">node1</code>.
     </p><div class="procedure"><ol class="procedure" type="1"><li class="step" id="CIRCULAR-CLUSTER-DISABLE-SUB-NODE2"><p>
        Disable all the subscriptions on <code class="literal">node2</code> that are
        subscribing the changes from <code class="literal">node1</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
</pre><p>
       </p></li><li class="step"><p>
        Stop the server in <code class="literal">node1</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data1 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data1_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the <code class="literal">node1</code>'s server to the required
        newer version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded server in <code class="literal">node1</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
</pre><p>
       </p></li><li class="step"><p>
        Enable all the subscriptions on <code class="literal">node2</code> that are
        subscribing the changes from <code class="literal">node1</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
</pre><p>
       </p></li><li class="step"><p>
        On <code class="literal">node1</code>, create any tables that were created in
        <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>
        and now, e.g.:
</p><pre class="programlisting">
/* node1 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</pre><p>
       </p></li><li class="step"><p>
        Refresh the <code class="literal">node1</code> subscription's publications to
        copy initial table data from <code class="literal">node2</code> using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
</pre><p>
       </p></li><li class="step" id="CIRCULAR-CLUSTER-DISABLE-SUB-NODE1"><p>
        Disable all the subscriptions on <code class="literal">node1</code> that are
        subscribing the changes from <code class="literal">node2</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><code class="command">ALTER SUBSCRIPTION ... DISABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
</pre><p>
       </p></li><li class="step"><p>
        Stop the server in <code class="literal">node2</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data2 stop
</pre><p>
       </p></li><li class="step"><p>
        Initialize <code class="literal">data2_upgraded</code> instance by using the
        required newer version.
       </p></li><li class="step"><p>
        Upgrade the <code class="literal">node2</code>'s server to the required
        new version, e.g.:
</p><pre class="programlisting">
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"
</pre><p>
       </p></li><li class="step"><p>
        Start the upgraded server in <code class="literal">node2</code>, e.g.:
</p><pre class="programlisting">
pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
</pre><p>
       </p></li><li class="step"><p>
        Enable all the subscriptions on <code class="literal">node1</code> that are
        subscribing the changes from <code class="literal">node2</code> by using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><code class="command">ALTER SUBSCRIPTION ... ENABLE</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
</pre><p>
       </p></li><li class="step"><p>
        On <code class="literal">node2</code>, create any tables that were created in
        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>
        and now, e.g.:
</p><pre class="programlisting">
/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
</pre><p>
       </p></li><li class="step"><p>
        Refresh the <code class="literal">node2</code> subscription's publications to
        copy initial table data from <code class="literal">node1</code> using
        <a class="link" href="sql-altersubscription.html#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code></a>,
        e.g.:
</p><pre class="programlisting">
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
</pre><p>
       </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.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 29.14. Quick Setup</td></tr></table></div></body></html>