Performing a Postgres major version rolling upgrade on a PGD cluster built with TPA v5

Upgrading Postgres major versions

Upgrading a Postgres database's major version to access improved features, performance enhancements, and security updates is a common administration task. Doing the same for a Postgres Distributed (PGD) cluster deployed with Trusted Postgres Architect (TPA) is essentially the same process but performed as a rolling upgrade.

The rolling upgrade process allows updating individual cluster nodes to a new major Postgres version while maintaining cluster availability and operational continuity. This approach minimizes downtime and ensures data integrity by allowing the rest of the cluster to remain operational as each node is upgraded sequentially.

The following overview of the general instructions and worked example help to provide a smooth and controlled upgrade process.

Prepare the upgrade

To prepare for the upgrade, identify the subgroups and nodes you're trying to upgrade and note an initial upgrade order.

To do this, connect to one of the nodes using SSH and run the pgd show-nodes command:

sudo -u postgres pgd show-nodes

The pgd show-nodes command shows you all the nodes in your PGD cluster and the subgroup to which each node belongs. Then you want to find out which node is the write leader in each subgroup by running:

sudo -u postgres pgd show-groups

This command outputs a list of the different groups/subgroups running in your cluster and the write leader of each group. To maintain operational continuity, you need to switch write leaders over to another node in their subgroup before you can upgrade them. To keep the number of planned switchovers to a minimum, when upgrading a subgroup of nodes, upgrade the writer leaders last.

Even though you verified which node is the current write leader for planning purposes, the write leader of a subgroup could change to another node at any moment for operational reasons before you upgrade that node. Therefore, you still need to verify that a node isn't the write leader just before upgrading that node.

You now have enough information to determine your upgrade order, one subgroup at a time, aiming to upgrade the identified write leader node last in each subgroup.

Perform the upgrade on each node

Using the preliminary order, perform the following steps on each node while connected via SSH:

• Confirm the current Postgres version
  • View versions from PGD by running sudo -u postgres pgd show-version.
  • Ensure that the expected major version is running.

• Verify that the target node isn't the write leader
  • Check whether the target node is the write leader for the group you're upgrading using sudo -u postgres pgd show-groups.
  • If the target node is the current write leader for the group/subgroup you're upgrading, perform a planned switchover to another node.
    • sudo -u postgres pgd switchover --group-name <group_name> --node-name <new_write_leader_node_name>

• Stop Postgres on the target node
  • Stop the Postgres service on the current node by running sudo systemctl stop postgres.
  • The target node is no longer actively participating as a node in the cluster.

• Install PGD and utilities
  • Install PGD and its utilities compatible with the Postgres version you're upgrading to.
    • sudo apt install edb-bdr<new_pgd_version_number>-pg<new_postgres_version_number> edb-bdr-utilities

• Initialize the new Postgres instance
  • Create a directory that will house the database files for the new version of PostgreSQL:
    • sudo mkdir -p /opt/postgres/datanew
    • Ensure that user postgres has ownership permissions (chown) to the directory.
  • Initialize a new PostgreSQL database cluster in the directory you just created.
    • This step involves using the initdb command provided by the newly installed version of PostgreSQL.
    • Replace <path_to_postgres_bin> with the path to the bin directory of the newly installed PostgreSQL version: sudo -u postgres <path_to_postgres_bin>/initdb -D /opt/postgres/datanew.
    • You may need to run this command as the postgres user or another user with appropriate permissions.
    • Make sure to include the --data-checksums flag to ensure the cluster uses data checksums.

• Migrate configuration to the new Postgres version
  • Locate the following configuration files in your current PostgreSQL data directory:
    • postgresql.conf
      • The main configuration file containing settings related to the database system.
    • postgresql.auto.conf
      • Contains settings set by PostgreSQL, such as those modified by the ALTER SYSTEM command.
    • pg_hba.conf
      • Manages client authentication, specifying which users can connect to which databases from which hosts.
    • The entire conf.d directory (if present)
      • Allows for organizing configuration settings into separate files for better manageability.
  • Copy these files and the conf.d directory to the new data directory you created for the upgraded version of PostgreSQL.

• Verify the Postgres service is inactive
  • Before proceeding, it's important to ensure that no PostgreSQL processes are active for both the old and the new data directories. This verification step prevents any data corruption or conflicts during the upgrade process.
  • Use the sudo systemctl status postgres command to verify that Postgres was stopped.
  • If it isn't stopped, run systemctl stop postgres and verify again that it was stopped.

• Swap PGDATA directories for version upgrade
  • Rename /opt/postgres/data to /opt/postgres/dataold and /opt/postgres/datanew to /opt/postgres/data.
  • This step readies your system for the next crucial phase: running pg_upgrade to finalize the PostgreSQL version transition.

• Verify upgrade feasibility
  • The bdr_pg_upgrade tool offers a --check option designed to perform a preliminary scan of your current setup, identifying any potential issues that could hinder the upgrade process.
  • You need to run this check from an upgrade directory with ownership given to user postgres, such as /home/upgrade/, so that the upgrade log files created by bdr_pg_upgrade can be stored.
  • To initiate the safety check, append the --check option to your bdr_pg_upgrade command.
  • This operation simulates the upgrade process without making any changes, providing insights into any compatibility issues, deprecated features, or configuration adjustments required for a successful upgrade.
  • Address any warnings or errors indicated by this check to ensure an uneventful transition to the new version.

• Execute the Postgres major version upgrade
  • Execute the upgrade process by running the bdr_pg_upgrade command without the --check option.
  • It's essential to monitor the command output for any errors or warnings that require attention.
  • The time the upgrade process take depends on the size of your database and the complexity of your setup.

• Update the Postgres service configuration
  • Update the service configuration to reflect the new PostgreSQL version by updating the version number in the postgres.service file:
    • sudo sed -i -e 's/<old_version_number>/<new_version_number>/g' /etc/systemd/system/postgres.service
  • Refresh the system's service manager to apply these changes:
    • sudo systemctl daemon-reload

• Restart Postgres
  • Proceed to restart the PostgreSQL service.
    • systemctl start postgres

• Validate the new Postgres version
  • Verify that your PostgreSQL instance is now upgraded by again running sudo -u postgres pgd show-version.

• Clean up post-upgrade
  • Run vacuumdb with the ANALYZE option immediately after the upgrade but before introducing a heavy production load.
    • Running this command minimizes the immediate performance impact, preparing the database for more accurate testing.
  • Remove the old version's data directory, /opt/postgres/dataold.

Reconcile the upgrade with TPA

TPA needs to continue to manage the deployment effectively after all the nodes have been upgraded. Therefore, it's necessary to reconcile the upgraded nodes with TPA.

Follow these steps to update the configuration and redeploy the PGD cluster through TPA.

• Update the config.yml
  • Change the config.yml of the TPA-managed cluster to the new version.
    • cluster_vars: postgres_version: '<new_version_number>'

• Use tpaexec to redeploy the PGD cluster with the updated config.yml
  • tpaexec deploy <cluster_name>

The worked example that follows shows upgrading the Postgres major version from 15 to 16 on a PGD 5 cluster deployed with TPA in detail.

Worked example

This worked example starts with a TPA-managed PGD cluster deployed using the AWS Quickstart. The cluster has three nodes: kaboom, kaolin, and kaftan, all running Postges 15.

This example starts with kaboom.

Confirm the current Postgres version

SSH into kaboom to confirm the major version of Postgres is expected by running:

sudo -u postgres pgd show-version

The output will be similar to this for your cluster:

Node   BDR Version Postgres Version               
----   ----------- ----------------               
kaboom 5.4.0       15.6 (Debian 15.6-2EDB.buster) 
kaftan 5.4.0       15.6 (Debian 15.6-2EDB.buster) 
kaolin 5.4.0       15.6 (Debian 15.6-2EDB.buster) 

Confirm that the Postgres version is the expected version.

Verify that the target node isn't the write leader

The cluster must be available throughout the process (that is, a rolling upgrade). There must always be an available write leader to maintain continuous cluster availability. So, if the target node is the current write leader, you must perform a planned switchover of the write leader node before upgrading it so that a write leader is always available.

While connected via SSH to kaboom, see which node is the current write leader of the group you're upgrading using the pgd show-groups command:

sudo -u postgres pgd show-groups

In this case, you can see that kaboom is the current write leader of the sole subgroup dc1_subgroup:

Group        Group ID   Type   Parent Group Location Raft Routing Write Leader 
-----        --------   ----   ------------ -------- ---- ------- ------------ 
democluster  1935823863 global                       true false                
dc1_subgroup 1302278103 data   democluster  dc1      true true    kaboom  

So you must perform a planned switchover of the write leader of dc1_subgroup to another node in the cluster.

Perform a planned switchover

Change the write leader to kaftan so kaboom's Postgres instance can be stopped:

sudo -u postgres pgd switchover --group-name dc1_subgroup --node-name kaftan

After the switchover is successful, it's safe to stop Postgres on the target node. Of course, if kaftan is still the write leader when you come to upgrading it, you'll need to perform another planned switchover at that time.

Stop Postgres on the target node

While connected via SSH to the target node (in this case, kaboom), stop Postgres on the node by running:

sudo systemctl stop postgres

This command halts the server on kaboom. Your cluster continues running using the other two nodes.

Install PGD and utilities

Next, install the new version of Postgres (PG16) and the upgrade tool:

sudo apt install edb-bdr5-pg16 edb-bdr-utilities

Initialize the new Postgres instance

Make a new data directory for the upgraded Postgres, and give the postgres user ownership of the directory:

sudo mkdir /opt/postgres/datanew
sudo chown -R postgres:postgres /opt/postgres/datanew

Then, initialize Postgres 16 in the new directory:

sudo -u postgres /usr/lib/postgresql/16/bin/initdb \
  -D /opt/postgres/datanew \
  -E UTF8 \
  --lc-collate=en_US.UTF-8 \
  --lc-ctype=en_US.UTF-8 \
  --data-checksums

This command creates a PG16 data directory for configuration, /opt/postgres/datanew.

Migrate configuration to the new Postgres version

The next step copies the configuration files from the old Postgres version (PG15) to the new Postgres version's (PG16). Configuration files reside in each version's data directory.

Copy over the postgresql.conf, postgresql.auto.conf, and pg_hba.conf files and the whole conf.d directory:

sudo -u postgres cp /opt/postgres/data/postgresql.conf /opt/postgres/datanew/
sudo -u postgres cp /opt/postgres/data/postgresql.auto.conf /opt/postgres/datanew/
sudo -u postgres cp /opt/postgres/data/pg_hba.conf /opt/postgres/datanew/
sudo -u postgres cp -r /opt/postgres/data/conf.d/ /opt/postgres/datanew/

Verify the Postgres service is inactive

Although you previously stopped the Postgres service on the target node, kaboom, to verify it's stopped, run the systemctl status postgres command:

sudo systemctl status postgres

The output of the status command shows that the Postgres service has stopped running:

● postgres.service - Postgres 15 (TPA)
   Loaded: loaded (/etc/systemd/system/postgres.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Wed 2024-03-20 15:32:18 UTC; 4min 9s ago
 Main PID: 24396 (code=exited, status=0/SUCCESS)

Mar 20 15:32:18 kaboom postgres[25032]: [22-1] 2024-03-20 15:32:18 UTC [pgdproxy@10.33.125.89(20108)/[unknown]/bdrdb:25032]: [1] FA
Mar 20 15:32:18 kaboom postgres[25033]: [22-1] 2024-03-20 15:32:18 UTC [pgdproxy@10.33.125.89(20124)/[unknown]/bdrdb:25033]: [1] FA
Mar 20 15:32:18 kaboom postgres[25034]: [22-1] 2024-03-20 15:32:18 UTC [pgdproxy@10.33.125.88(43534)/[unknown]/bdrdb:25034]: [1] FA
Mar 20 15:32:18 kaboom postgres[25035]: [22-1] 2024-03-20 15:32:18 UTC [pgdproxy@10.33.125.88(43538)/[unknown]/bdrdb:25035]: [1] FA
Mar 20 15:32:18 kaboom postgres[25036]: [22-1] 2024-03-20 15:32:18 UTC [pgdproxy@10.33.125.87(37292)/[unknown]/bdrdb:25036]: [1] FA
Mar 20 15:32:18 kaboom postgres[25037]: [22-1] 2024-03-20 15:32:18 UTC [pgdproxy@10.33.125.87(37308)/[unknown]/bdrdb:25037]: [1] FA
Mar 20 15:32:18 kaboom postgres[24398]: [24-1] 2024-03-20 15:32:18 UTC [@//:24398]: [15] LOG:  checkpoint complete: wrote 394 buffe
Mar 20 15:32:18 kaboom postgres[24396]: [22-1] 2024-03-20 15:32:18 UTC [@//:24396]: [23] LOG:  database system is shut down
Mar 20 15:32:18 kaboom systemd[1]: postgres.service: Succeeded.
Mar 20 15:32:18 kaboom systemd[1]: Stopped Postgres 15 (TPA).

Swap PGDATA directories for version upgrade

Next, swap the PG15 and PG16 data directories:

sudo mv /opt/postgres/data /opt/postgres/dataold
sudo mv /opt/postgres/datanew /opt/postgres/data

sudo mv /opt/postgres/data /opt/postgres/datafailed
sudo mv /opt/postgres/dataold /opt/postgres/data

Verify upgrade feasibility

The bdr_pg_upgrade tool has a --check option, which performs a dry run of some of the upgrade process. You can use this option to ensure the upgrade goes smoothly.

However, first, you need a directory for the files created by bdr_pg_upgrade. For this example, create an /upgrade directory in the /home directory. Then give ownership of the directory to the user postgres.

sudo mkdir /home/upgrade
sudo chown postgres:postgres /home/upgrade

Next, navigate to /home/upgrade and run:

sudo -u postgres /usr/bin/bdr_pg_upgrade \
  --old-bindir /usr/lib/postgresql/15/bin/ \
  --new-bindir /usr/lib/postgresql/16/bin/ \
  --old-datadir /opt/postgres/dataold/ \
  --new-datadir /opt/postgres/data/ \
  --database bdrdb \
  --check

The following is the output:

Performing BDR Postgres Checks
------------------------------
Collecting pre-upgrade new cluster control data             ok
Checking new cluster state is shutdown                      ok
Checking BDR versions                                       ok

Passed all bdr_pg_upgrade checks, now calling pg_upgrade

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

*Clusters are compatible

old cluster uses data checksums but the new one does not

Execute the Postgres major version upgrade

You're ready to run the upgrade. On the target node, run:

sudo -u postgres /usr/bin/bdr_pg_upgrade \
  --old-bindir /usr/lib/postgresql/15/bin/ \
  --new-bindir /usr/lib/postgresql/16/bin/ \
  --old-datadir /opt/postgres/dataold/ \
  --new-datadir /opt/postgres/data/ \
  --database bdrdb

The following is the expected output:

Performing BDR Postgres Checks
------------------------------
Collecting pre-upgrade new cluster control data             ok
Checking new cluster state is shutdown                      ok
Checking BDR versions                                       ok
Starting old cluster (if shutdown)                          ok
Connecting to old cluster                                   ok
Checking if bdr schema exists                               ok
Turning DDL replication off                                 ok
Terminating connections to database                         ok
Disabling connections to database                           ok
Waiting for all slots to be flushed                         ok
Disconnecting from old cluster                              ok
Stopping old cluster                                        ok
Starting old cluster with BDR disabled                      ok
Connecting to old cluster                                   ok
Collecting replication origins                              ok
Collecting replication slots                                ok
Disconnecting from old cluster                              ok
Stopping old cluster                                        ok

Passed all bdr_pg_upgrade checks, now calling pg_upgrade

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas                           ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster               ok
Copying user relation files                                 ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.


Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/pgsql-15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

pg_upgrade complete, performing BDR post-upgrade steps
------------------------------------------------------
Collecting old cluster control data                         ok
Collecting new cluster control data                         ok
Checking LSN of new cluster                                 ok
Starting new cluster with BDR disabled                      ok
Connecting to new cluster                                   ok
Creating replication origin (bdr_bdrdb_rb69_bdr2)           ok
Advancing replication origin (bdr_bdrdb_rb69_bdr2, 0/1F4... ok
Creating replication origin (bdr_bdrdb_rb69_bdr1)           ok
Advancing replication origin (bdr_bdrdb_rb69_bdr1, 0/1E8... ok
Creating replication slot (bdr_bdrdb_rb69_bdr1)             ok
Creating replication slot (bdr_bdrdb_rb69)                  ok
Creating replication slot (bdr_bdrdb_rb69_bdr2)             ok
Stopping new cluster

Update the Postgres service configuration

The Postgres service on the system is configured to start the old version of Postgres (PG15). You need to modify the postgres.service file to start the new version (PG16).

You can do this using sed to replace the old version number 15 with 16 throughout the file.

sudo sed -i -e  's/15/16/g' /etc/systemd/system/postgres.service

After you've changed the version number, you can tell the systemd daemon to reload the configuration. On the target node, run:

sudo systemctl daemon-reload

Restart Postgres

Start the modified Postgres service by running:

sudo systemctl start postgres

Validate the new Postgres version

Repeating the first step, check the version of Postgres to confirm that you upgraded kaboom correctly. While still on kaboom, run:

sudo -u postgres pgd show-version

Use the output to confirm that kaboom is running the upgraded Postgres version:

Node   BDR Version Postgres Version               
----   ----------- ----------------               
kaboom 5.4.0       16.2 (Debian 16.2-2EDB.buster) 
kaftan 5.4.0       15.6 (Debian 15.6-2EDB.buster) 
kaolin 5.4.0       15.6 (Debian 15.6-2EDB.buster) 

Here kaboom has been upgraded to major version 16.

Clean up post-upgrade

As a best practice, run a vacuum over the database at this point. When the upgrade ran, you may have noticed the post-upgrade report included:

Once you start the new server, consider running:
    /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages

You can run the vacuum now. On the target node, run:

sudo -u postgres /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages

If you're sure you don't need to revert this node, you can also clean up the old data directory folder dataold:

sudo rm -r /opt/postgres/dataold

Upgrading the target node is now complete.

Next steps

After completing the upgrade on kaboom, run the same steps on kaolin and kaftan.

If you followed along with this example and kaftan is the write leader, to ensure availability, you must perform a planned switchover to another, already upgraded node before running the upgrade steps on kaftan.

Check Postgres versions across the cluster

After completing the upgrade on all nodes, while connected to one of the nodes, you can once again check your versions:

sudo -u postgres pgd show-version

The output will be similar to the following:

Node   BDR Version Postgres Version               
----   ----------- ----------------               
kaboom 5.4.0       16.2 (Debian 16.2-2EDB.buster) 
kaftan 5.4.0       16.2 (Debian 16.2-2EDB.buster) 
kaolin 5.4.0       16.2 (Debian 16.2-2EDB.buster) 

This output shows that all the nodes are successfully upgraded to the new Postgres version 16.

Reconcile with TPA

After all the nodes are upgraded, you still need to reconcile the upgraded version of Postgres with TPA so you can continue to use TPA to manage the cluster in the future.

To do this, return to the command line where your TPA cluster directory resides. In this worked example, the TPA cluster directory is /home/ubuntu/democluster on the instance where you originally deployed the cluster using TPA.

After navigating to your cluster directory, use a code editor to edit config.yml and change cluster_vars: from postgres_version: '15' to postgres_version: '16'.

Unless they were already added to your .bashrc or .bash_profile, ensure the TPA tools are accessible in your command line session by adding TPA's binary directory to your PATH:

export PATH=$PATH:/opt/EDB/TPA/bin

Finally, redeploy the cluster:

tpaexec deploy democluster

This command applies the configuration changes to the cluster managed by TPA. If the deployment is successful, the reconciliation of the new version of Postgres with TPA and the upgrade procedure as a whole is complete.

• On this page
• Upgrading Postgres major versions
  • Prepare the upgrade
  • Perform the upgrade on each node
  • Reconcile the upgrade with TPA
• Worked example
  • Confirm the current Postgres version
  • Verify that the target node isn't the write leader
  • Stop Postgres on the target node
  • Install PGD and utilities
  • Initialize the new Postgres instance
  • Migrate configuration to the new Postgres version
  • Verify the Postgres service is inactive
  • Swap PGDATA directories for version upgrade
  • Verify upgrade feasibility
  • Execute the Postgres major version upgrade
  • Update the Postgres service configuration
  • Restart Postgres
  • Validate the new Postgres version
  • Clean up post-upgrade
  • Next steps