Upgrading PGD clusters manually =============================== Because EDB Postgres Distributed consists of multiple software components, the upgrade strategy depends partially on the components that are being upgraded. In general, you can upgrade the cluster with almost zero downtime by using an approach called *rolling upgrade*. Using this approach, nodes are upgraded one by one, and the application connections are switched over to already upgraded nodes. You can also stop all nodes, perform the upgrade on all nodes, and only then restart the entire cluster. This approach is the same as with a standard PostgreSQL setup. This strategy of upgrading all nodes at the same time avoids running with mixed versions of software and therefore is the simplest. However, it incurs downtime and we don’t recommend it unless you can’t perform the rolling upgrade for some reason. To upgrade an EDB Postgres Distributed cluster: 1. Plan the upgrade. 2. Prepare for the upgrade. 3. Upgrade the server software. 4. Check and validate the upgrade. Upgrade planning ---------------- There are broadly two ways to upgrade each node: - Upgrade nodes in place to the newer software version. See :ref:`Rolling server software upgrades ` . - Replace nodes with ones that have the newer version installed. See :ref:`Rolling upgrade using node join ` . You can use both of these approaches in a rolling manner. Rolling upgrade considerations ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ While the cluster is going through a rolling upgrade, mixed versions of software are running in the cluster. For example, suppose nodeA has PGD 4.3.6, while nodeB and nodeC have 5.6.1. In this state, the replication and group management uses the protocol and features from the oldest version (4.3.6 in this example), so any new features provided by the newer version that require changes in the protocol are disabled. Once all nodes are upgraded to the same version, the new features are enabled. Similarly, when a cluster with WAL-decoder-enabled nodes is going through a rolling upgrade, WAL decoder on a higher version of PGD node produces :ref:`logical change records (LCRs) ` with a higher pglogical version. WAL decoder on a lower version of PGD node produces LCRs with a lower pglogical version. As a result, WAL senders on a higher version of PGD nodes aren’t expected to use LCRs due to a mismatch in protocol versions. On a lower version of PGD nodes, WAL senders can continue to use LCRs. Once all the PGD nodes are on the same PGD version, WAL senders use LCRs. A rolling upgrade starts with a cluster with all nodes at a prior release. It then proceeds by upgrading one node at a time to the newer release, until all nodes are at the newer release. There must be no more than two versions of the software running at the same time. An upgrade must be completed, with all nodes fully upgraded, before starting another upgrade. Where additional caution is required to reduce business risk, more time may be required to perform an upgrade. For maximum caution and to reduce the time required upgrading production systems, we suggest performing the upgrades in a separate test environment first. Don’t run with mixed versions of the software for any longer than is absolutely necessary to complete the upgrade. You can check on the versions in the cluster using the :ref:`pgd commit-scopes list ` command. The longer you run with mixed versions, the more likely you are to encounter issues, the more difficult it is to diagnose and resolve them. We recommend upgrading in off peak hours for your business, and over a short period of time. While you can use a rolling upgrade for upgrading a major version of the software, we don’t support mixing PostgreSQL, EDB Postgres Extended, and EDB Postgres Advanced Server in one cluster. So you can’t use this approach to change the Postgres variant. .. Warning:: Downgrades of EDB Postgres Distributed aren't supported. They require that you manually rebuild the cluster. Rolling server software upgrades ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ A rolling upgrade is where the :ref:`Rolling server software upgrades ` is upgraded sequentially on each node in a cluster without stopping the cluster. Each node is temporarily stopped from participating in the cluster and its server software is upgraded. Once updated, it’s returned to the cluster, and it then catches up with the cluster’s activity during its absence. The actual procedure depends on whether the Postgres component is being upgraded to a new major version. During the upgrade process, you can switch the application over to a node that’s currently not being upgraded to provide continuous availability of the database for applications. Rolling upgrade using node join ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The other method to upgrade the server software is to join a new node to the cluster and later drop one of the existing nodes running the older version of the software. For this approach, the procedure is always the same. However, because it includes node join, a potentially large data transfer is required. Take care not to use features that are available only in the newer Postgres version until all nodes are upgraded to the newer and same release of Postgres. This is especially true for any new DDL syntax that was added to a newer release of Postgres. .. Note:: `bdr_init_physical` makes a byte-by-byte copy of the source node so you can't use it while upgrading from one major Postgres version to another. In fact, currently `bdr_init_physical` requires that even the PGD version of the source and the joining node be exactly the same. You can't use it for rolling upgrades by way of joining a new node method. Instead, use a logical join. Upgrading a CAMO-enabled cluster ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Upgrading a CAMO-enabled cluster requires upgrading CAMO groups one by one while disabling the CAMO protection for the group being upgraded and reconfiguring it using the new :ref:`Migration to commit scopes ` -based settings. We recommended the following approach for upgrading two BDR nodes that constitute a CAMO pair to PGD 5.0: 1. Ensure ``bdr.enable_camo`` remains ``off`` for transactions on any of the two nodes, or redirect clients away from the two nodes. Removing the CAMO pairing while attempting to use CAMO leads to errors and prevents further transactions. 2. For BDR 4.x, deconfigure CAMO by using ``bdr.remove_camo_pair`` to uncouple the pair. 3. Upgrade the two nodes to PGD 5.0. 4. Create a dedicated node group for the two nodes and move them into that node group. 5. Create a :ref:`Migration to commit scopes ` for this node group and thus the pair of nodes to use CAMO. 6. Reactivate CAMO protection again either by setting a ``default_commit_scope`` or by changing the clients to explicitly set ``bdr.commit_scope`` instead of ``bdr.enable_camo`` for their sessions or transactions. 7. If necessary, allow clients to connect to the CAMO-protected nodes again. Upgrade preparation ------------------- Each major release of the software contains several changes that might affect compatibility with previous releases. These might affect the Postgres configuration, deployment scripts, as well as applications using PGD. We recommend considering these changes and making any needed adjustments in advance of the upgrade. See individual changes mentioned in the `release notes `_ and any version-specific upgrade notes. Server software upgrade ----------------------- Upgrading EDB Postgres Distributed on individual nodes happens in place. You don’t need to back up and restore when upgrading the BDR extension. BDR extension upgrade ^^^^^^^^^^^^^^^^^^^^^ The BDR extension upgrade process consists of the following high-level steps: Fence the node ^^^^^^^^^^^^^^ To make sure the node being upgraded does not become a write leader until the upgrade is complete, you should fence the node before initiating the upgrade. Stop Postgres ^^^^^^^^^^^^^ During the upgrade of binary packages, it’s usually best to stop the running Postgres server first. Doing so ensures that mixed versions don’t get loaded in case of an unexpected restart during the upgrade. Upgrade packages ^^^^^^^^^^^^^^^^ The first step in the upgrade is to install the new version of the BDR packages. This installation installs both the new binary and the extension SQL script. This step is specific to the operating system. Start Postgres ^^^^^^^^^^^^^^ Once packages are upgraded, you can start the Postgres instance. The BDR extension is upgraded upon start when the new binaries detect the older version of the extension. Unfence the node ^^^^^^^^^^^^^^^^ You can unfence the node after the node upgrade is completed. .. Note:: A PGD 4 cluster must be running PGD 4.4.1 before upgrading to PGD Version 6.4. Upgrading from PGD 4.4.1 to PGD Version 6.4 requires additional steps to move from Harp proxy to Connection Manager. For more information, see the worked example: :ref:`Upgrade PGD 4 to PGD Version 6.4 ` . A PGD 5 cluster must be running PGD 5.9 before upgrading to PGD Version 6.4. Upgrading from PGD 5.9 to PGD Version 6.4 requires additional steps to move from PGD proxy to Connection Manager. For more information, see the worked example: :ref:`Upgrade PGD 5 to PGD Version 6.4 ` . .. :: ### Postgres upgrade The process of in-place upgrade of Postgres depends on whether you’re upgrading to a new minor version of Postgres or to a new major version of Postgres. Minor version Postgres upgrade ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Upgrading to a new minor version of Postgres is similar to :ref:`BDR extension upgrade ` . Stopping Postgres, upgrading packages, and starting Postgres again is typically all that’s needed. However, sometimes more steps, like reindexing, might be recommended for specific minor version upgrades. Refer to the release notes of the version of Postgres you’re upgrading to. Major version Postgres upgrade ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Upgrading to a new major version of Postgres is more complicated than upgrading to a minor version. EDB Postgres Distributed provides a ``pgd node upgrade`` command line utility, which you can use to do `in-place Postgres major version upgrades `_ . .. Note:: When upgrading to a new major version of any software, including Postgres, the BDR extension, and others, it's always important to ensure your application is compatible with the target version of the software you're upgrading. Upgrade check and validation ---------------------------- After you upgrade your PGD node, you can verify the current version of the binary: .. code:: sql SELECT bdr.bdr_version(); Always check your :ref:`Monitoring the Connection Manager ` after upgrading a node to confirm that the upgraded node is working as expected. PGD 5 - Moving from PGD Proxy to Connection Manager --------------------------------------------------- Use the following steps to move from PGD Proxy to Connection Manager: 1. From one of the PGD 5.9 nodes, as a database superuser, and while connected to the PGD-enabled database, run the following query to ensure that SCRAM hashes of all user passwords are the same across all nodes: - .. code:: sql DO $$ DECLARE rec RECORD; command TEXT; password TEXT; BEGIN FOR rec IN SELECT rolname,rolpassword FROM pg_authid WHERE rolcanlogin = true AND rolpassword like SCRAM-SHA% LOOP password := rec.rolpassword; command := ALTER ROLE || quote_ident(rec.rolname) || WITH ENCRYPTED PASSWORD || quote_literal(password); EXECUTE command; END LOOP; END; $$; SELECT bdr.wait_slot_confirm_lsn(NULL, NULL); - .. Note:: No new users should be added to 5.9 after executing this. If they are added, run the query again. The above block does not change the passwords, it just ensure SCRAM hashes are same across the cluster on all nodes. .. Note:: 2. Fence a node in this cluster with `pgd node set-option route_fence true` so that it does not become the write leader. 3. Enable the GUC `bdr.enable_builtin_connection_manager` to _true_. 4. Restart the server. 5. Stop PGD Proxy running on the server. 6. Restart the server. It will start with the Connection Manager running on the default port. If the proxy read and write ports were different, the Connection Manager port read and write ports can be changed to be the same as proxy by `bdr.alter_node_group_option()` . 7. Unfence the node. This node can now accept connections from the user and route to the write leader via Connection Manager. 8. Repeat this for each node in the cluster. This will ensure all nodes are now routing via Connection Manager. 9. If you're also performing a major version upgrade from PGD 5 to PGD Version 6.4, proceed with the :ref:`rolling upgrade steps ` .