Upgrading VMware Postgres

This topic describes how to upgrade VMware Postgres from a previous major version to v14.6.0.

Upgrading from an Older Major Version

Caution: The major to major upgrade procedure involves shutting down the source Postgres server. Follow these steps under an agreed maintenance window.

VMware Postgres 14.6 supports upgrading from any prior VMware Postgres major version to 14.6.

Note: PostGIS extension customers on VMware Postgres 10.x, 11.x, or 12.x cannot upgrade to VMware Postgres 14.6 unless they upgrade their 10.x, 11.x, or 12.x to the latest minor version, which upgrades PostGIS to 3.X.

Refer to Upgrading from an Older Major Version with Patroni HA if you’re using VMware Postgres with Patroni as the high availability solution.

Prerequisites

Ensure you have login access to the Postgres host you’re upgrading.
Download the VMware Postgres Server RPM distribution from VMware Tanzu Network and copy to the host you’re upgrading.
Any extensions installed on the source Postgres server must be manually added to the new target release. Alternatively, remove them from the source Postgres cluster before starting the upgrade.
Confirm you have enough disk space to run pg_upgrade. The default pg_upgrade setting copies all data files from the source to the target server. Alternatively, review the --link option, that provides reduced upgrade time and disk space benefits but impacts the revert process. For more details see Reverting an old cluster. For a list of all the pg_upgrade options see Options in the pg_upgrade Open Source Postgres documentation.

Procedure

The following procedure assumes no extensions are present on the source cluster. The following procedure relies on the Open Source Postgres pg_upgrade utility. For full details refer to pg_upgrade Usage.

On the host you’re upgrading, login and become the Postgres user:
```
su - postgres
```
Locate the downloaded Postgres release file, unzip it, and use yum to install the target version. For example:
```
cd ~/Downloads
```
```
sudo yum install vmware-postgres14-14.6-0.el7.x86_64.rpm
```
To avoid using utilities from the source version, remove the old version binaries from your $PATH. For example:
```
export PATH=`echo ${PATH} | awk -v RS=: -v ORS=: '/vmware/ {next} {print}'| sed 's/\s*:\s*$//'`
```
Then add the new version to the $PATH:
```
export PATH=`pg_config --bindir`:$PATH
```
Alternatively, you could use the command alternatives which creates the necessary symbolic links.
Stop the existing Postgres server, for example:
```
pg_ctl -D <path-to-source-cluster> stop
```
Save any custom source Postgres server settings by saving any configuration files like postgresql.conf, postgresql.auto.conf, or pg_hba.conf.
To successfully initialize the target Postgres version, and to avoid overwritting existing data, move the source version data directories to a new location. For example, if you are upgrading from 11.x and the data is in the default /var/lib/pgsql/data location, the /var/lib/pgsql/data can exist, but needs to be empty. So use a command similar to:
```
cd /var/lib/pgsql
```
```
mv data 11_data
```
where data is the old data directory and 11_data is the new data directory copy.

For more details on this step, refer to step 1 in pg_upgrade Usage.
Use pg_ctl to initialize the data structure of the target Postgres version:
```
pg_ctl init -o --data-checksums
```
Before starting the target cluster, review step 5 in pg_upgrade Usage, and check your extensions, using a command similar to:
```
pg_upgrade --check --old-datadir=<source-version>_data/ --new-datadir=data/ --old-bindir=/opt/vmware/postgres/<source-version>/bin/ --new-bindir=/opt/vmware/postgres/<target-version>/bin/
```
replacing <source-version> and <target-version> with the source and target versions of your upgrade. For example, if you’re upgrading from 11.X to 14.X, the command would be similar to:
```
pg_upgrade --check --old-datadir=11_data/ --new-datadir=data/ --old-bindir=/opt/vmware/postgres/11/bin/ --new-bindir=/opt/vmware/postgres/14/bin/
```
Execute any steps required for your extensions. These instructions assume no extensions are present in the source Postgres server.

Run the pg_upgrade command, while providing the old and the new data directory:

pg_upgrade --old-datadir=11_data/ --new-datadir=data/ --old-bindir=/opt/vmware/postgres/11/bin/ --new-bindir=/opt/vmware/postgres/14/bin/

Upgrading from an Older Major Version with Patroni HA

This topic describes the major to major upgrade process when the source Postgres server is using Patroni HA. In this example, the source cluser is version 11.17 and the target cluster is 14.6.

The following procedure assumes the DCS is etcd, as per the architecture in VMware Postgres High Availability with Patroni.

Caution: The major to major upgrade procedure involves shutting down the source Postgres server. Follow these steps under an agreed maintenance window.

Upgrade the primary Patroni node

Download the target VMware Postgres RPM packages from VMware Tanzu Network. If you’re upgrading to VMware Postgres 13.X, 14.X or 15.X, download the target version Patroni RPM packages which are provided separately to the server RPM.
Stop the Patroni process on all hosts in the HA cluster.
Upgrade the primary node Postgres major version using pg_upgrade and the steps in Upgrading from an Older Major Version.
Install the target server Patroni RPM if you’re upgrading to a VMware major version where the Patroni packages are separate to the server RPM (13.X, 14.X and 15.X):
```
yum install ~/Downloads/vmware-postgres14-patroni-2.1.4-1.e17.x86_64.rpm 
```
Replace ~/Downloads with your specific location.
Review your Patroni config YAML file and verify that the target server $PGDATA path is set to the right location, as per your major to major VMware Postgres upgrade in step 3.
If you have temporarily changed the $PATH during the Postgres upgrade, make the change permanent across all terminal sessions by editing the .bash_profile file. For example:
```
su - postgres
```
```
vi .bash_profile
```
and change it from:
```
PATH=/opt/vmware/postgres/11/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin
```
to:
```
PATH=/opt/vmware/postgres/14/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin
```
Verify you have the right $PATH and binary versions:
```
patroni --version
```
or
```
which patroni
```
The output should show the target version binaries, for example:
```
/opt/vmware/postgres/14/bin/patroni
```
Copy any patroni.dynamic.json files from the old data directory to the new one. This step will preserve some of the PostgreSQL parameters you might have setup on the source cluster.
Remove the source PostgreSQL initialize key from etcd. When pg_upgade runs initdb it will create the target PostgreSQL system with a new identifier.
```
patronictl -c patroni.yml patroni_cluster
```
Replace patroni_cluster with the name of your Patroni HA system.

Verify that the key has been removed by running:
```
etcdctl ls /service/patroni_cluster
```
Copy the pg_hba.conf file from the copied source data directory to the target data directory. This step avoids Patroni host communication or synchronization issues:
```
cp /var/lib/pgsql/11_data/pg_hba.conf /var/lib/pgsql/data/
```

Start Patroni on the primary node by running:

patroni patroni patroni.yml > patroni log 2>&1 &

Upgrade the standby Patroni nodes

Download or copy the target release packages onto the standby nodes.
Repeat the major to major VMware Postgres upgrade procedure for each standby node. For details refer to Upgrading from an Older Major Version.
Remove the $PGDATA directory. The data will be synchornized when Patroni starts and all the nodes join the cluster.
```
rm -rf $PGDATA
```
If you prefer to use rsync to synchronize the data directories, refer to Upgrade streaming replication and log-shipping standby servers.
Repeat steps 4 to 7 in Upgrade the primary Patroni node.

Start Patroni on the standby node:

patroni patroni patroni.yml > patroni log 2>&1 &

Verify that the Postgres server is running by using:
```
ps -ef | grep postgres
```
Repeat these steps for any remaining standby nodes.
When all standby nodes are upgraded, verify the Patroni cluster is in a healthy state, with the primary and standby nodes as expected:
```
patronictl -c patroni.yml list
```

Upgrading from an Older Minor Version

If you are upgrading a minor VMware Postgres installation (for example 14.0) to a later VMware Postgres minor release, use the yum update command to install the new RPM:

cd ~/Downloads
sudo yum update vmware-postgres<version>-<postgres-version>.el7.x86_64.rpm

Restart the Postgres server:

su postgres
pg_ctl restart

Note that the Postgres JDBC driver is not installed as an RPM. Simply extract and use the newer JAR file in your CLASSPATH.