Application Schema Upgrades

In this chapter we discuss upgrading software on a BDR cluster and howto minimize downtime for applications during the upgrade.

Overview

BDR cluster has two sets of software, the underlying PostgreSQL softwareor some flavor of it and the PGLogical/BDR software. We will discussupgrading either or both of these softwares versions to their supportedmajor releases.

To upgrade a BDR cluster, the following steps need to be performed oneach node:

• plan the upgrade
  

• prepare for the upgrade
  

• upgrade the server software
  

• restart Postgres
  

• check and validate the upgrade
  

Upgrade Planning

While the BDR 3.7 release supports PostgreSQL 11 - 13, BDR 4.0 supportsPostgreSQL versions 12 - 14. Please refer to (product-matrix.md)page for the full list compatible software. Since BDR 4.0 supports newerPostgreSQL releases, while upgrading from BDR 3.7 to BDR 4.0, it’s alsopossible to upgrade the newer PostgreSQL releases with minimum or noapplication downtime.

There are broadly two ways to upgrade the BDR version.

• Upgrading one node at a time to the newer BDR version.
  

• Joining a new node running a newer version of the BDR software and
  

  • then optionally drop one of the old nodes.

If you are only interested in upgrading the BDR software, any of the twomethods can be used. But if you also want to upgrade the PostgreSQLversion, then the second method must be used.

!!! Warning * The first method cannot be currently used to upgrade from BDR 3.7 to BDR 4.0. Only way to upgrade 3.7 to 4.0 is to join 4.0 nodes into BDR 3.7 cluster as described in Rolling Upgrade Using Node Join section. This restriction may be lifted in future versions of BDR 4.

Rolling Server Software Upgrades

A rolling upgrade is the process where the below ServerSoftware Upgrade is performed on each node in theBDR Group one after another, while keeping the replication working.

An upgrade to 4.0 is only supported from 3.7, using a specific minimummaintenance release (e.g. 3.7.13.1). Please consult the Release Notesfor the actual required minimum version. So if a nodeis running with an older 3.7 release, it must first be upgraded tothe minimum required version and can only then be upgraded to 4.0.

Just as with a single-node database, it’s possible to stop all nodes,perform the upgrade on all nodes and only then restart the entirecluster. This strategy of upgrading all nodes at the same time avoidsrunning with mixed BDR versions and therefore is the simplest, butobviously incurs some downtime.

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

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

A BDR cluster is designed to be easily upgradeable. Most BDR releasessupport rolling upgrades, which means running part of the cluster on onerelease level and the remaining part of the cluster on a second, compatible,release level.

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. Should problems occur, do not attemptto downgrade without contacting Technical Support to discuss and provideoptions.

An upgrade process may take an extended period of time when the user decidescaution is required to reduce business risk, though this should not take anylonger than 30 days without discussion and explicit agreement from TechnicalSupport to extend the period of coexistence of two release levels.

In case of problems during upgrade, do not initiate a second upgrade to anewer/different release level. Two upgrades should never occur concurrentlyin normal usage. Nodes should never be upgraded to a third release withoutspecific and explicit instructions from Technical Support. A case wherethat might occur is if an upgrade failed for some reason and a Hot Fix wasrequired to continue the current cluster upgrade process to successfulconclusion. BDR has been designed and tested with more than 2 releaselevels, but this cannot be relied upon for production usage except inspecific cases.

Rolling Upgrade Using Node Join

The other method of upgrading BDR software, along with or without upgradingthe underlying PostgreSQL major version, is to join a new nodeto the cluster and later drop one of the existing nodes runningthe older version of the software. Even with this method, some featuresthat are available only in the newer version of the software may remainunavailable until all nodes are finally upgraded to the newer versions.

A new node running this release of BDR 4.0 can join a 3.7 cluster,where each node in the cluster is running the latest 3.7.x version ofBDR. The joining node may run any of the supported PostgreSQL versions12-14 but mixing of PostgreSQL, EDB Postgres Extended and EDB Postgres Advancedis currently not supported.

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

Note that bdr_init_physical makes a byte-by-byte of the source node.So it cannot be used while upgrading from one major PostgreSQL versionto another. In fact, currently bdr_init_physical requires that evenBDR version of the source and the joining node is exactly the same. Soit cannot be used for rolling upgrades via joining a new node method. Inall such cases, 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.

Configuration of CAMO pairs has changed significantly compared to BDR3.7: instead of GUCs in postgresql.conf, the pairing is now stored inBDR system catalog bdr.camo_pairs. To upgrade a BDR cluster withCAMO pairs from 3.7 to 4.0, the following steps need to be performed:

• Eliminate `bdr.camo_partner_of` and `bdr.camo_origin_for`
  

  • configuration on all nodes.

• Restart all nodes affected by this change (still using the existing
  

  • BDR version), one at a time. This will temporarily disable CAMO

  • and attempting to run transactions with bdr.enable_camo set will

  • result in warnings.

• Upgrade the entire BDR cluster, either all nodes at once or using a
  

  • rolling upgrade.

• Re-configure CAMO via `bdr.add_camo_pair`.  This function will
  

  • only work after all nodes in the BDR cluster are upgraded.

Upgrade Preparation

Each major release of BDR contains several changes that may affectcompatibility withprevious releases. These may affect the Postgres configuration,deployment scripts as well as applications using BDR. We recommend toconsider and possibly adjust in advance of the upgrade.

pglogical

There is no pglogical4 and BDR4 will not work if any version of pglogical isloaded via shared_preload_libraries to the same instance of Postgres.

Node Management

The bdr.create_node_group() function has seen a number of changes:

• It is now possible to create sub-groups, resulting in a tree-of-groups
  

  • structure of the whole BDR cluster. Monitoring views were updated

  • accordingly.

• The deprecated parameters `insert_to_update`, `update_to_insert`,
  

  • ignore_redundant_updates, check_full_tuple and apply_delay were

  • removed.

  • Use bdr.alter_node_set_conflict_resolver() instead of insert_to_update,

  • update_to_insert. The check_full_tuple is no longer needed as it is

  • handled automatically based on table conflict detection configuration.

Conflicts

The configuration of conflict resolution and logging is now copied fromjoin source node to the newly joining node, rather than using defaults on thenew node.

The default conflict resolution for some of the conflict types was changed.See (conflicts.md#default-conflict-resolvers) for the new defaults.

The conflict logging interfaces have changed from bdr.alter_node_add_log_configand bdr.alter_node_remove_log_config to bdr.alter_node_set_log_config.

The default conflict logging table is now named bdr.conflict_history and theold bdr.apply_log no longer exists. The new table is partitioned using theAutopartition feature of BDR.

All conflicts are now logged by default to both log file and the conflicttable.

Deprecated functions bdr.row_version_tracking_enable() andbdr.row_version_tracking_disable() were removed. Usebdr.alter_table_conflict_detection() instead.

Some of the configuration for conflict handling is no longer stored inpglogical schema. Any diagnostic queries that were using the pglogicaltables directly will have to switch to appropriate tables in bdr schema.Queries using bdr.node_group, bdr.local_node_summary,bdr.local_node_summary orbdr.node_local_info will need to use the new columns sub_repsets andpub_repsets instead of replication_sets.

Removed Or Renamed Settings (GUCs)

All the pglogical. prefixed configuration variables were renamed to use bdr.prefix instead.

Server Software Upgrade

The upgrade of BDR software on individual nodes happens in-place. There is noneed for backup and restore when upgrading the BDR extension.

!!! Warning * This method cannot be currently used for upgrading BDR 3.7 to 4.0. Only way to upgrade 3.7 to 4.0 is to join 4.0 nodes into BDR 3.7 cluster as described in Rolling Upgrade Using Node Join section. This restriction may be lifted in future versions of BDR 4.

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

Restart Postgres

Upgrading the binary and extension scripts by itself does not upgrade BDRin the running instance of PostgreSQL. To do that, the PostgreSQL instanceneeds to be restarted so that the new BDR binary can be loaded (the BDR binaryis loaded at the start of the PostgreSQL server). After that, the node isupgraded. The extension SQL upgrade scripts are executed automatically asneeded.

!!! Warning * It’s important to never run the ALTER EXTENSION ... UPDATE command before the PostgreSQL instance is restarted, as that will only upgrade the SQL-visible extension but keep the old binary, which can cause unpredictable behaviour or even crashes. The ALTER EXTENSION ... UPDATE command should never be needed; BDR4 maintains the SQL-visible extension automatically as needed.

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 upgradeof a node to confirm that the upgraded node is working as expected.

Database Encoding

We recommend using UTF-8 encoding in all replicated databases.BDR does not support replication between databases with differentencoding. There is currently no supported path to upgrade/alter encoding.

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 as documented in the following section.

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 * Application Schema Upgrades are managed by the user, not by BDR. Careful scripting will be required to make this work correctly on production clusters. Extensive testing is advised.

Details of this are covered hereReplicating between nodes with differences.

When one node runs DDL that adds a new table, nodes that have notyet received the latest DDL will need to cope with 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 executedseparately on each node, 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 will need to cope with 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 executedseparately on each node, 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 will need to cope with 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 will 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 coping with 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. More details are provided in theALTER TABLE section.