Movatterモバイル変換

29.13. Upgrade
Prev	Up	Chapter 29. Logical Replication	Home	Next

29.13. Upgrade#

29.13.1. Prepare for Publisher Upgrades
29.13.2. Prepare for Subscriber Upgrades
29.13.3. Upgrading Logical Replication Clusters

Migration oflogical replication clusters is possible only when all the members of the old logical replication clusters are version 17.0 or later.

29.13.1. 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 executingALTER SUBSCRIPTION ... DISABLE. Re-enable the subscription after the upgrade.

There are some prerequisites forpg_upgrade to be able to upgrade the logical slots. If these are not met an error will be reported.

The new cluster must havewal_level aslogical.
The new cluster must havemax_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 whosepg_replication_slots.conflicting is nottrue.
The new cluster must not have permanent logical slots, i.e., there must be no slots wherepg_replication_slots.temporary isfalse.

29.13.2. 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 inpg_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 forpg_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 statei (initialize) orr (ready). This can be verified by checkingpg_subscription_rel.srsubstate.
The replication origin entry corresponding to each of the subscriptions should exist in the old cluster. This can be found by checkingpg_subscription andpg_replication_origin system tables.
The new cluster must havemax_active_replication_origins configured to a value greater than or equal to the number of subscriptions present in the old cluster.

29.13.3. 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.

Note

The logical replication restrictions apply to logical replication cluster upgrades also. SeeSection 29.8 for details.

The prerequisites of publisher upgrade apply to logical replication cluster upgrades also. SeeSection 29.13.1 for details.

The prerequisites of subscriber upgrade apply to logical replication cluster upgrades also. SeeSection 29.13.2 for details.

Warning

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 inSection 25.3.2.

The steps to upgrade the following logical replication clusters are detailed below:

Follow the steps specified inSection 29.13.3.1 to upgrade a two-node logical replication cluster.
Follow the steps specified inSection 29.13.3.2 to upgrade a cascaded logical replication cluster.
Follow the steps specified inSection 29.13.3.3 to upgrade a two-node circular logical replication cluster.

29.13.3.1. Steps to Upgrade a Two-node Logical Replication Cluster#

Let's say publisher is innode1 and subscriber is innode2. The subscribernode2 has a subscriptionsub1_node1_node2 which is subscribing the changes fromnode1.

Disable all the subscriptions onnode2 that are subscribing the changes fromnode1 by usingALTER SUBSCRIPTION ... DISABLE, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
```
Stop the publisher server innode1, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data1 stop
```
Initializedata1_upgraded instance by using the required newer version.

Upgrade the publishernode1'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 innode1, e.g.:

pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile

Stop the subscriber server innode2, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data2 stop
```
Initializedata2_upgraded instance by using the required newer version.

Upgrade the subscribernode2'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 innode2, e.g.:

pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile

Onnode2, create any tables that were created in the upgraded publishernode1 server betweenStep 1 and now, e.g.:
```
/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
```
Enable all the subscriptions onnode2 that are subscribing the changes fromnode1 by usingALTER SUBSCRIPTION ... ENABLE, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
```
Refresh thenode2 subscription's publications usingALTER SUBSCRIPTION ... REFRESH PUBLICATION, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
```

Note

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.

29.13.3.2. Steps to Upgrade a Cascaded Logical Replication Cluster#

Let's say we have a cascaded logical replication setupnode1->node2->node3. Herenode2 is subscribing the changes fromnode1 andnode3 is subscribing the changes fromnode2. Thenode2 has a subscriptionsub1_node1_node2 which is subscribing the changes fromnode1. Thenode3 has a subscriptionsub1_node2_node3 which is subscribing the changes fromnode2.

Disable all the subscriptions onnode2 that are subscribing the changes fromnode1 by usingALTER SUBSCRIPTION ... DISABLE, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
```
Stop the server innode1, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data1 stop
```
Initializedata1_upgraded instance by using the required newer version.

Upgrade thenode1'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 innode1, e.g.:

pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile

Disable all the subscriptions onnode3 that are subscribing the changes fromnode2 by usingALTER SUBSCRIPTION ... DISABLE, e.g.:
```
/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
```
Stop the server innode2, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data2 stop
```
Initializedata2_upgraded instance by using the required newer version.

Upgrade thenode2'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 innode2, e.g.:

pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile

Onnode2, create any tables that were created in the upgraded publishernode1 server betweenStep 1 and now, e.g.:
```
/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
```
Enable all the subscriptions onnode2 that are subscribing the changes fromnode1 by usingALTER SUBSCRIPTION ... ENABLE, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
```
Refresh thenode2 subscription's publications usingALTER SUBSCRIPTION ... REFRESH PUBLICATION, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
```
Stop the server innode3, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data3 stop
```
Initializedata3_upgraded instance by using the required newer version.

Upgrade thenode3'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 innode3, e.g.:

pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile

Onnode3, create any tables that were created in the upgradednode2 betweenStep 6 and now, e.g.:
```
/* node3 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
```
Enable all the subscriptions onnode3 that are subscribing the changes fromnode2 by usingALTER SUBSCRIPTION ... ENABLE, e.g.:
```
/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
```
Refresh thenode3 subscription's publications usingALTER SUBSCRIPTION ... REFRESH PUBLICATION, e.g.:
```
/* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
```

29.13.3.3. Steps to Upgrade a Two-node Circular Logical Replication Cluster#

Let's say we have a circular logical replication setupnode1->node2 andnode2->node1. Herenode2 is subscribing the changes fromnode1 andnode1 is subscribing the changes fromnode2. Thenode1 has a subscriptionsub1_node2_node1 which is subscribing the changes fromnode2. Thenode2 has a subscriptionsub1_node1_node2 which is subscribing the changes fromnode1.

Disable all the subscriptions onnode2 that are subscribing the changes fromnode1 by usingALTER SUBSCRIPTION ... DISABLE, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
```
Stop the server innode1, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data1 stop
```
Initializedata1_upgraded instance by using the required newer version.

Upgrade thenode1'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 innode1, e.g.:

pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile

Enable all the subscriptions onnode2 that are subscribing the changes fromnode1 by usingALTER SUBSCRIPTION ... ENABLE, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
```

Onnode1, create any tables that were created innode2 betweenStep 1 and now, e.g.:

/* node1 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));

Refresh thenode1 subscription's publications to copy initial table data fromnode2 usingALTER SUBSCRIPTION ... REFRESH PUBLICATION, e.g.:
```
/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
```
Disable all the subscriptions onnode1 that are subscribing the changes fromnode2 by usingALTER SUBSCRIPTION ... DISABLE, e.g.:
```
/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
```
Stop the server innode2, e.g.:
```
pg_ctl -D /opt/PostgreSQL/data2 stop
```
Initializedata2_upgraded instance by using the required newer version.

Upgrade thenode2'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 innode2, e.g.:

pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile

Enable all the subscriptions onnode1 that are subscribing the changes fromnode2 by usingALTER SUBSCRIPTION ... ENABLE, e.g.:
```
/* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
```
Onnode2, create any tables that were created in the upgradednode1 betweenStep 9 and now, e.g.:
```
/* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
```
Refresh thenode2 subscription's publications to copy initial table data fromnode1 usingALTER SUBSCRIPTION ... REFRESH PUBLICATION, e.g.:
```
/* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
```

Prev	Up	Next
29.12. Configuration Settings	Home	29.14. Quick Setup

epub pdf