How to perform an in-place Major Version Postgres Upgrade using pg

Products

VMware Tanzu Greenplum

Issue/Introduction

For versions 13.4+, 12.8+, 11.13+, and 10.18+, you can now perform an in-place upgrade which is detailed in the documentation: https://docs.vmware.com/en/VMware-Postgres/13.6/vmware-postgres/GUID-upgrading.html

Note: This workaround applies to VMware Postgres both including and prior to the versions 13.3, 12.7, 11.12, and 10.17.

In the versions mentioned above, it is not possible to do an in-place upgrade using only VMware Postgres RPMs. This will be fixed in the next set of Postgres minor releases but for now, if you want to perform a major upgrade, you can use this article as a workaround. We will demonstrate a 11.x to 12.x in-place major upgrade.

Environment

Product Version: 12.6

Resolution

1. Shut down Postgres 11 using pg_ctl

2. Uninstall VMware Postgres 11.x (Version 11.11 in this example):

yum remove vmware-postgres.x86_64

3. Install PGDG Postgres 11 and 12. Try to match up the minor versions as best as possible:

Add the PGDG Postgres Repo and enable it:

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum -y install epel-release yum-utils

yum-config-manager --enable pgdg12

Install Postgres 11:

yum install postgresql11-server postgresql11

Install Postgres 12:

yum install postgresql12-server postgresql12

4. Initdb Postgres 12. Make sure the new PGDATA is correct and pointing to a new location:

/usr/pgsql-12/bin/initdb -D /var/lib/pgsql/12/data

5. Run pg_upgrade. If you forgot to stop Postgres 11, you can go to /usr/pgsql-11/bin and run pg_ctl stop from there to stop the Postgres Database.

-bash-4.2$ /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-12/bin --old-datadir /var/lib/pgsql/data --new-datadir /var/lib/pgsql/12/data --link
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 script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

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

6. Uninstall both PGDG Postgres 11 and 12:

yum remove postgresql11-server postgresql11 postgresql11-libs
yum remove postgresql12-server postgresql12 postgresql12-libs

7. Install VMware Postgres 12.x RPM:

yum install -y vmware-postgres-12.6-1.el7.x86_64.rpm

8. Start up Postgres or use systemctl if that is setup;

postgres -D /var/lib/pgsql/12/data >/var/lib/pgsql/data/logfile 2>&1 &

You should see that the database has been upgraded. If you have any extensions, make sure to install the new versions and test it out.