Upgrading Postgres 15.6.1 to 16.6.0 with Postgis Extension.

Products

VMware Tanzu Data Suite VMware Tanzu for Postgres

Issue/Introduction

This KB explains how to upgrade Postgres 15.6.1 to 16.6.0 and install the Postgis 3.4.0 extension.

Environment

Postgres 15.6.1

Postgres 16.6.0

Cause

Postgis 3.4 is not compatible with Postgres 15.6.1.

Resolution

NOTE: 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.

Remove the Postgis extension in Postgres 15.6.1.

Before uninstalling, make sure to:

1. Connect to the specific database where PostGIS is installed.

2. Ensure you have the necessary permissions to drop the extension.

3. Be aware that this action will remove all PostGIS-related functions, types, and data from your database.

DROP EXTENSION postgis CASCADE;

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-postgres16-6.0-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/

Install Postgis 3.4 after Upgrading to Postgres 16.6.0

To install PostGIS 3.4 in PostgreSQL 16.6.0, follow these steps:

1. Ensure you have PostgreSQL 16.6.0 installed on your system.

2. Download the appropriate PostGIS 3.4 package for your operating system and PostgreSQL version.

3. Install the PostGIS package. The exact method may vary depending on your operating system:

4. Once installed, connect to your PostgreSQL database using psql or another client tool.

5. Create the PostGIS extension in your desired database by running:

CREATE EXTENSION postgis;

6. Verify the installation by checking the PostGIS version:

SELECT PostGIS_Full_Version();