Upgrading

Because EDB Postgres Distributed consists in multiple software components,the upgrade strategy depends partially on which components are being upgraded.

In general it’s possible to upgrade the cluster with almost zero upgrade, byusing an approach called Rolling Upgrade where nodes are upgraded one by one, andthe application connections are switched over to already upgraded nodes.

Ii’s also possible to stop all nodes, perform the upgrade on all nodes andonly then restart the entire cluster, just like with a standard PostgreSQL setup.This strategy of upgrading all nodes at the same time avoids running withmixed versions of software and therefore is the simplest, but obviously incurssome downtime and is not recommended unless the Rolling Upgrade is not possiblefor some reason.

To upgrade an EDB Postgres Distributed cluster, perform the following steps:

  1. Plan the upgrade.1. Prepare for the upgrade.1. Upgrade the server software.1. Check and validate the upgrade.

Upgrade Planning

There are broadly two ways to upgrade each node.

Both of these approaches can be done in a rolling manner.

Rolling Upgrade considerations

While the cluster is going through a rolling upgrade, mixed versions of softwareare running in the cluster. For example, nodeA has BDR 3.7.16, whilenodeB and nodeC has 4.1.0. In this state, the replication and groupmanagement uses the protocol and features from the oldest version (3.7.16in case of this example), so any new features provided by the newer versionwhich require changes in the protocol are disabled. Once all nodes areupgraded to the same version, the new features are automatically enabled.

Similarly, when a cluster with WAL decoder enabled nodes is going through arolling upgrade, WAL decoder on a higher version of BDR node produces LCRswith a higher pglogical version and WAL decoder on a lower version of BDR node produces LCRs with lower pglogical version. As a result, WAL senders on a higherversion of BDR nodes are not expected to use LCRs due to a mismatch in protocolversions while on a lower version of BDR nodes, WAL senders may continue to useLCRs. Once all the BDR nodes are on the same BDR version, WAL senders useLCRs.

A rolling upgrade starts with a cluster with all nodes at a prior release,then proceeds by upgrading one node at a time to the newer release, untilall nodes are at the newer release. There should never be more than two versionsof any component running at the same time, which means the new upgrade must notbe initiated until the previous upgrade process has fully finished on all nodes.

An upgrade process may take an extended period of time when the user decidescaution is required to reduce business risk, though it’s not recommendedto run the mixed versions of the software indefinitely.

While Rolling Upgrade can be used for upgrading major version of the softwareit is not supported to mix PostgreSQL, EDB Postgres Extended andEDB Postgres Advanced Server in one cluster, so this approach cannotbe used to change the Postgres variant.

Warning

Downgrades of the EDB Postgres Distributed are not supported and require manual rebuild of the cluster.

Rolling Server Software Upgrades

A rolling upgrade is the process where the ServerSoftware Upgrade process is performed on each node in thecluster one after another, while keeping the remainder of the clusteroperational.

The actual procedure depends on whether the Postgres component is beingupgraded to a new major version or not.

During the upgrade process, the application can be switched over to a nodewhich is currently not being upgraded to provide continuous availability ofthe database for applications.

Rolling Upgrade Using Node Join

The other method of upgrade of the server software, is to join a new nodeto the cluster and later drop one of the existing nodes runningthe older version of the software.

For this approach, the procedure is always the same, however because itincludes node join, the potentially large data transfer is required.

Care must be taken to not use features that are available only inthe newer Postgres versions, until all nodes are upgraded to thenewer and same release of Postgres. This is especially true for anynew DDL syntax that may have been added to a newer release of Postgres.

Note

bdr_init_physical makes a byte-by-byte of the source node so it cannot be used while upgrading from one major Postgres version to another. In fact, currently bdr_init_physical requires that even the BDR version of the source and the joining node is exactly the same. It cannot be used for rolling upgrades via joining a new node method. Instead, a logical join must be used.

Upgrading a CAMO-Enabled Cluster

CAMO protection requires at least one of the nodes of a CAMO pair tobe operational. For upgrades, we recommend to ensure that no CAMOprotected transactions are running concurrent to the upgrade, or touse a rolling upgrade strategy, giving the nodes enough time toreconcile in between the upgrades and the corresponding node downtimedue to the upgrade.

Upgrade Preparation

Each major release of the software contains several changes that may affectcompatibility with previous releases. These may affect the Postgresconfiguration, deployment scripts, as well as applications using BDR. Werecommend to consider and possibly adjust in advance of the upgrade.

Please see individual changes mentioned in Release notes and any versionspecific upgrade notes in this topic.

Server Software Upgrade

The upgrade of EDB Postgres Distributed on individual nodes happens in-place.There is no need for backup and restore when upgrading the BDR extension.

BDR Extension Upgrade

BDR extension upgrade process consists of few simple steps.

Stop Postgres

During the upgrade of binary packages, it’s usually best to stop the runningPostgres server first to ensure that mixed versions don’t get loaded in caseof unexpected restart during the upgrade.

Upgrade Packages

The first step in the upgrade is to install the new version of the BDR packages, whichinstalls both the new binary and the extension SQL script. This step is operating system-specific.

Start Postgres

Once packages are upgraded the Postgres instance can be started, the BDRextension is automatically upgraded upon start when the new binariesdetect older version of the extension.

Postgres Upgrade

The process of in-place upgrade of Postgres highly depends on whether you areupgrading to new minor version of Postgres of to new major version of Postgres.

Minor Version Postgres Upgrade

Upgrading to a new minor version of Postgres is similar to upgradingthe BDR extension. Stopping Postgres, upgrading packages,and starting Postgres again is typically all that’s needed.

However, sometimes additional steps like reindexing may be recommended forspecific minor version upgrades. Refer to the Release Notes of thespecific version of Postgres you are upgrading to.

Major Version Postgres Upgrade

Upgrading to a new major version of Postgres is a more complicated process.

EDB Postgres Distributed provides a bdr_pg_upgrade command line utility,which can be used to do a In-place Postgres Major Version Upgrades .

Note

When upgrading to new major version of any software, including Postgres, the BDR extension and others, it’s always important to ensure the compatibility of your application with the target version of a given software.

Upgrade Check and Validation

After this procedure, your BDR node is upgraded. You can verify the currentversion of BDR4 binary like this:

SELECT bdr.bdr_version();

Always check the Monitoring after upgrade of a node to confirmthat the upgraded node is working as expected.

Application Schema Upgrades

Similar to the upgrade of BDR itself, there are two approaches toupgrading the application schema. The simpler option is to stop allapplications affected, preform the schema upgrade, and restart theapplication upgraded to use the new schema variant. Again, thisimposes some downtime.

To eliminate this downtime, BDR offers ways to perform a rollingapplication schema upgrade.

Rolling Application Schema Upgrades

By default, DDL will automatically be sent to all nodes. This can becontrolled manually, as described in DDL Replication , whichcould be used to create differences between database schemas across nodes.BDR is designed to allow replication to continue even while minordifferences exist between nodes. These features are designed to allowapplication schema migration without downtime, or to allow logicalstandby nodes for reporting or testing.

Warning

Rolling Application Schema Upgrades have to be managed outside of BDR. Careful scripting is required to make this work correctly on production clusters. Extensive testing is advised.

See Replicating between nodes with differences for details.

When one node runs DDL that adds a new table, nodes that have notyet received the latest DDL need to handle the extra table.In view of this, the appropriate setting for rolling schema upgradesis to configure all nodes to apply the skip resolver in case of atarget_table_missing conflict. This must be performed before anynode has additional tables added and is intended to be a permanentsetting.

This is done with the following query, that must be executedseparatelyoneachnode , after replacing node1 with the actualnode name:

SELECT bdr.alter_node_set_conflict_resolver(node1,
        target_table_missing, skip);

When one node runs DDL that adds a column to a table, nodes that have notyet received the latest DDL need to handle the extra columns.In view of this, the appropriate setting for rolling schemaupgrades is to configure all nodes to apply the ignore resolver incase of a target_column_missing conflict. This must be performedbefore one node has additional columns added and is intended to be apermanent setting.

This is done with the following query, that must be executedseparatelyoneachnode , after replacing node1 with the actualnode name:

SELECT bdr.alter_node_set_conflict_resolver(node1,
        target_column_missing, ignore);

When one node runs DDL that removes a column from a table, nodes thathave not yet received the latest DDL need to handle the missing column.This situation will cause a source_column_missing conflict, which usesthe use_default_value resolver. Thus, columns that neitheraccept NULLs nor have a DEFAULT value require a two step process:

  1. Remove NOT NULL constraint or add a DEFAULT value for a column on all nodes.2. Remove the column.

Constraints can be removed in a rolling manner.There is currently no supported way for handling adding tableconstraints in a rolling manner, one node at a time.

When one node runs a DDL that changes the type of an existing column,depending on the existence of binary coercibility between the currenttype and the target type, the operation may not rewrite the underlyingtable data. In that case, it will be only a metadata update of theunderlying column type. Rewrite of a table is normally restricted.However, in controlled DBA environments, it is possible to changethe type of a column to an automatically castable one by adoptinga rolling upgrade for the type of this column in a non-replicatedenvironment on all the nodes, one by one. See

ALTER TABLE for more details. section.