Upgrade PostgreSQL to v15

CentOS 7 and RHEL 7 Guide to upgrade from PostgreSQL 9.x/13 to PostgreSQL 15 on the v4 server.

This guide is for v4 servers to prepare them for the upgrade to v5

Older installations of Polarity Server v4 will likely be running PostgreSQL 9.x, which has reached End of Support. We recommend upgrading to PostgreSQL 15 (the default for newer releases of Polarity Server).

This guide will step you through updating an existing PostgreSQL 9.5 database server located on the Polarity Server to the latest version of PostgreSQL 15.

Before You Begin:

1. Log into the Polarity Server CLI as root or a user with sudo access.

2. Take a full backup of the Polarity Server v4 database before you begin.

   • su - postgres -c '/app/polarity-server/data/backups/db-backup.sh'

3. Install the latest Polarity Server v4.x RPMs (which support PostgreSQL 9.5+)

   • If you are running Polarity Server v5, then this upgrade has already occurred as a part of the upgrade process.

These instructions are for customers running an EL 7 distro, such as CentOS 7, RHEL 7, AL2, etc.

Customers running RHEL 8, Oracle Linux 8, or other EL 8 distro should already be running PostgreSQL v13 and can upgrade to v15, but is not required to migrate to Polarity Server v5.

Upgrade Steps

Stop the Polarity Server and PostgreSQL 9.5 services

systemctl stop polarityd
systemctl stop postgresql-9.5

Install the latest PostgreSQL v15 packages

If you have not already installed the PGDG repository that downloads and installs PostgreSQL YUM packages directly from the PostgreSQL YUM repository, or need to update it please run:

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

After the PostgreSQL repo is updated, you can install the PostgreSQL 15:

yum install postgresql15 postgresql15-server ip4r_15 postgresql15-libs postgresql15-contrib -y

Initialize the PostgreSQL 15 server

su - postgres -c "/usr/pgsql-15/bin/initdb -D /var/lib/pgsql/15/data"

Upgrade and Transition Data to the PostgreSQL 15 Server

The first and second commands are exactly the same, except for the --check option given on the first execution. This option checks whether or not the execution will succeed without making any actual changes.

Note that the backslashes (\) at the end of each line allow the command to continue on the next line. This allows us to display the whole command in chunks for clarity.

time /usr/pgsql-15/bin/pg_upgrade \
--old-bindir /usr/pgsql-9.5/bin \
--new-bindir /usr/pgsql-15/bin \
--old-datadir /var/lib/pgsql/9.5/data \
--new-datadir /var/lib/pgsql/15/data --link --check

If the output from above shows *Clusters are compatible*, proceed to the next step to run the upgrade.

time /usr/pgsql-15/bin/pg_upgrade \
--old-bindir /usr/pgsql-9.5/bin \
--new-bindir /usr/pgsql-15/bin \
--old-datadir /var/lib/pgsql/9.5/data \
--new-datadir /var/lib/pgsql/15/data --link

You may see a message saying you must "update your extensions". The message will look like this:

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.

If you see this message, it will be addressed in the next step.

Transition Server Processes

Disable and the PostgreSQL 9.5 database server, start the PostgreSQL 15 database server, and restart the Polarity Service:

systemctl disable --now postgres-9.5
systemctl enable --now postgres-15
systemctl restart polarityd

---

If you need to update your extensions (see message from above), run the following command now that the PostgreSQL 15 database is running:

su - postgres -c "psql -f /var/lib/pgsql/update_extensions.sql"

---

Refresh the user stats materialized view:

sudo su - postgres -c 'psql -d breach -c "REFRESH MATERIALIZED VIEW polarity.user_stats;"'

If there have been any errors to this point, please contact Polarity Support for assistance.

Finish Up

Confirm the PostgreSQL 15 database and Polarity Servers are running:

systemctl status postgresql-15
systemctl status polarityd

You should see the new postgresql-15 service running and active.

Attempt to log into the Polarity User Interface (Web or Desktop Client) and making some sample searches to confirm that your Polarity Server is functional.

Once you have confirmed this, delete the older version of PostgreSQL to avoid potential complications during the upgrade process to Polarity Server v5:

yum remove postgresql95 -y
rm -rf /var/lib/pgsql/9.5