Performing a Postgres major upgrade v6.5.0

Move a cluster to a new Postgres major version, without changing your PGD version, using one of three approaches: full downtime, a rolling upgrade in place, or a rolling upgrade that replaces nodes one at a time.

Upgrading with full downtime

Choose this approach when a maintenance window is acceptable and finishing quickly matters more than staying online. See Choosing between a rolling and full-downtime upgrade for the trade-offs against the two rolling approaches described below.

Disconnect applications from the cluster, then on each node, run the same steps as upgrading with a rolling upgrade, in place below, except you can skip checking or switching the write leader and fencing or unfencing the node, since nothing is serving traffic during the upgrade. Once every node is upgraded, confirm each one is running the new Postgres version and healthy, then reconnect applications.

Upgrading with a rolling upgrade, in place

Plan your upgrade order across the cluster, one node at a time, upgrading each group's write leader last. Disconnect applications from the old Postgres cluster, or redirect them to another node in the PGD cluster.

Use the command-line utility pgd node upgrade to upgrade a node in place. It wraps the standard pg_upgrade, adding PGD-specific steps around it so replication slots and origins carry over correctly.

For each node, one at a time:

1. Install the new major version's PGD packages alongside the existing ones:

   → WrapCopy

   sudo apt install edb-pgd6-expanded-pg<postgres_version>

   Package managers install each Postgres major version to its own path, so the old binaries are untouched.

2. Create a fresh, empty data directory for the new cluster using the new version's initdb, matching the old cluster's checksum and locale settings. Leave this new cluster shut down.

3. Copy the old cluster's postgresql.conf and postgresql.auto.conf, pg_hba.conf, and conf.d directory (if present) into the new data directory, adjusting for any configuration parameters that changed between Postgres major versions.

4. Check whether the node you're upgrading is the write leader for its group:

   → WrapCopy

   pgd group <group_name> show --summary

   If it is, switch leadership to another node first, since pgd node upgrade doesn't manage this switch for you:

   → WrapCopy

   pgd group <group_name> set-leader <new_leader_node_name>

5. Fence the node so it can't become the write leader again while it's being upgraded:

   → WrapCopy

   pgd node <node_name> set-option route_fence true

6. Make sure the old cluster is running. pgd node upgrade starts it if it's shut down.

7. Run pgd node upgrade with the --check option first to validate the upgrade without making changes:

   → WrapCopy

   pgd node <node_name> upgrade --check \
     --old-bindir <old_bindir> \
     --new-bindir <new_bindir> \
     --old-datadir <old_datadir> \
     --new-datadir <new_datadir> \
     --database <database_name> \
     --username <install_user>

   --old-bindir and --new-bindir are the old and new Postgres installation's bin directories. --old-datadir and --new-datadir are the old cluster's data directory and the empty new data directory you created earlier. --database is the PGD-enabled database name, and --username is the cluster's install user. --check runs the PGD-specific validation and pg_upgrade --check, without modifying either cluster. See Modes of operation for what running with --check validates.

8. Run pgd node upgrade again without --check to perform the upgrade, using the same options:

   → WrapCopy

   pgd node <node_name> upgrade \
     --old-bindir <old_bindir> \
     --new-bindir <new_bindir> \
     --old-datadir <old_datadir> \
     --new-datadir <new_datadir> \
     --database <database_name> \
     --username <install_user>

9. Unfence the node:

   → WrapCopy

   pgd node <node_name> set-option route_fence false

10. Confirm the node is healthy and running the new Postgres version before moving on:

    → WrapCopy

    pgd nodes list --versions

11. Move to the next node in your planned upgrade order, upgrading write leaders last in each group.

For the full set of options and flags, see the pgd node upgrade command reference.

Example

Upgrade the node kaolin from Postgres 16 to Postgres 17:

pgd node kaolin upgrade \
  --old-bindir /usr/lib/postgresql/16/bin \
  --new-bindir /usr/lib/postgresql/17/bin \
  --old-datadir /var/lib/postgresql/16/main \
  --new-datadir /var/lib/postgresql/17/main \
  --database bdrdb

See the command reference for more examples, including hard links, file cloning, and Transparent Data Encryption (TDE).

Upgrading with a rolling upgrade, by replacing nodes

Instead of upgrading a node in place, join a new node that already has the new Postgres version installed, then drop one of the existing nodes running the old version. This approach avoids pg_upgrade entirely, at the cost of a full data transfer to the new node.

1. Provision a new machine, or a new instance, with the new Postgres major version and the same PGD version as the rest of the cluster already installed.

2. Join the new node to the cluster using a logical join:

   → WrapCopy

   pgd node <node_name> setup --dsn "<new_node_dsn>" --cluster-dsn "<existing_node_dsn>" -D <pg_data>

   Note

   Don't use bdr_init_physical, it requires the source and joining node to run the same Postgres major version, so it can't be used across a major version change.

   See the pgd node setup command reference for the full set of options.

3. Wait for the new node to catch up with the cluster before routing traffic to it.

4. Confirm the new node is healthy and running the new Postgres version:

   → WrapCopy

   pgd nodes list --versions

5. Update your application's connection configuration, or the Connection Manager, to include the new node.

6. Disconnect applications from the old node before parting it, or redirect them to another node in the PGD cluster.

7. Part the old node, then decommission it:

   → WrapCopy

   pgd node <node_name> part

8. Repeat for each remaining old node, one at a time, until the whole cluster is on the new version.